Apple Intelligence is now available on iPhone, iPad, and Mac!
I am recording transactions in various currencies by date and use the CURRENCYH formula to covert to Euro based on Yahoo Finance FX rate. The formula shows an error if (a) the recorded Currency is also in Euro and (b) if the transaction day is a weekend. How would i need to adjust the formula to avoid error messages? Any help appreciated.
Have a look at the 30-Day History sheet (tab) in the 'My Stocks' template at File > New in the menu (or where you find templates in Numbers for iOS/iPadOS):
If you temporarily turn off the filter you'll see how the template has a clever use of IFERROR with STOCKH in the formula together with the filter to deal with the problem of non-trading days.
The same approach will work with the CURRENCYH function.
More on IFERROR here:
More on filters here:
Filter data in Numbers on Mac - Apple Support
If you are charting the data note how 'Hidden Data' is unchecked in the Format > Chart tab on the right.
SG
Or you could use this option with WORKDAY - Apple Support
If the date is on a weekend it will return the FX rate for Friday.
Both options will do the job, you should pick what works best for your case.
Regards
Ralf
You can add an IF function
IF the Text in B2 = "EUR" then it will return a 1, in all other cases it will return the result of the currency function
Looks like there are two different issues.
Why are you trying to convert Euro to Euro?
Just use for the row with Euro the formula in Cell D7 = C7, or do you change the row very frequently?
CURRENCYH is getting confused and give a funny error message, look at the date.
Looks like the formula is working for most Monday's, but not for October 7th.
Somehow there are no data available.
Data are provided by Yahoo Finance, unfortunately this is not always working.
For a general overview of your finances it will work, but not for trading.
Thanks Ralf, not trying to convert Euro to Euro. Columns B/C are recording expenses according multiple currencies and yes, expenses in multiple currencies are ongoing. Obviously could leave out the Euro denominated expenses. But one formula working across all possible cases would simply minimise possible mistakes. Thought it might be possible to combine the CURRENCYH AND WORKDAY function with an "If" function which automatically maintains the value in case the original expense is in Euro. Understand the issue with missing data in Yahoo Finance.
Thanks Ralf but this still gives me error messages for the Monday and when the original currency is also Euro
Thanks!
Thanks mate. This was helpful!
FX Conversions on Weekends
