You can make a difference in the Apple Support Community!
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
I'm having a problem using the CURRENCYH function for historical currency rates. If the date field is a weekend, the formula returns an error. Instead, I'd need it to use Friday's closing price. Any thoughts?
I suppose I could create a nested IF/THEN statement so that if an error is returned, it subtracts 1 from the date, and if that returns an error, it subtracts 2. But was hoping for a more elegant solution.
MacBook Pro 15″, macOS 11.6
Please check one thing before you implement this.
What will you get if the Monday or Friday is a public holiday? Don't think that there will be an official closing price.
Example: Monday, May 31st 2021 was Memorial Day in the US, therefore a public holiday.
WORKDAY is an elegant way for most weekends, but you may need IF/THEN for some special weekends.
Ralf
I don't know about all holidays but May 31, 2021 and Dec 25, 2020 both have values from CURRENCYH. I have not used the function extensively so I don't know if there are other holidays for which it has no data (not counting problems with the data server). Those days will be errors if there are any. STOCKH does not have data on holidays.
As SGIII said, the My Stocks template changes error values into "-" and filters them out so you don't see them. It will hide weekends, holidays, and any other day without data. Unfortunately, the server (Yahoo Finance) too often does not give data on valid days and those days will be covered up by the filter. As I write this, the server quit providing any data for stocks or currencies. Could just be me but everything else internet-related seems to be working fine and it was too, at the beginning.
If a date is in A2, I believe WORKDAY(A2+1,−1) will convert Saturday and Sunday into Friday. You could incorporate that into your CURRENCYH formula.
Thank you - very helpful - I knew there had to be a more elegant solution.
Also note how the problem is addressed in the 'My Stocks' template at File > New in your menu. Clever use of a filter.
SG
CURRENCYH function on weekends
