Historical currency in Numbers not working correctly

It has finally (after more than a week) started to work.

For a short period of time today it worked only partially (some of the currencies were OK, some NOT), but as for now - it is working as it should.

Are you experiencing this issue again? I find that this will work for a couple weeks, then stop working, then start again. It's really strange as it appears to only happen for Canadian funds and Canadian currency conversion; I can see here from Yahoo Finance that it's updated up through yesterday but not reflected in Numbers: https://finance.yahoo.com/quote/CADUSD%3DX/chart?p=CADUSD%3DX

The calculation I'm using is IFERROR(CURRENCYH("CAD", "USD",0,<<cell with date>>),0). Below you can see the lack of updates since 24 April:

Does it simply take over 2 weeks for the numbers to be accurately reflected?

WKH wrote:

Thank you, but I’m actually tracking funds, and they are not tracked via stock symbols.

Not sure what you mean. Funds are tracked by symbols, the same symbols that are used in Yahoo! Finance. Again, I recommend you open the My Stocks template and enter the symbol you are using. See if it works in the template.

If it does work, then you know you need to debug something in your own document.

If it doesn't work, then double-check the symbol you are using by visiting Yahoo! Finance and seeing what is used there (Numbers gets its data from Yahoo! Finance.

If after checking everything and making sure the dates are for valid trading dates when quotations were available for that fund, then you can give feedback to Apple via Numbers > Provide Numbers Feedback in your menu. 'Bug report' is one of the feedback types.

From my experience Apple usually does not respond. But reports are read. A number of problems with CURRENCYH have been mentioned here in recent weeks. Many seem to have cleared up with a few days.

SG

As with the stock data, the currency data is provided by Yahoo Finance. It is not surprising that it has the same issues of incorrect and missing data from time to time. The stock data seems to clear itself up after a few days. I suspect the currency information will too.

WKH wrote:

Thank you, but I’m actually tracking funds, and they are not tracked via stock symbols.

Not sure what you mean. Funds are tracked by symbols, the same symbols that are used in Yahoo! Finance. Currencies are also tracked by. symbols. Again, I recommend you open the My Stocks template and enter the symbol you are using. See if it works in the template.

If it does work, then you know you need to debug something in your own document.

If it doesn't work, then double-check the symbol you are using by visiting Yahoo! Finance and seeing what is used there (Numbers gets its data from Yahoo! Finance.

If after checking everything and making sure the dates are for valid trading dates when quotations were available for that fund, then you can give feedback to Apple via Numbers > Provide Numbers Feedback in your menu. 'Bug report' is one of the feedback types.

From my experience Apple usually does not respond. But reports are read. A number of problems with STOCKH and CURRENCYH have been mentioned here in recent weeks. Many seem to have cleared up with a few days.

SG

I've opened your "My Stocks" template and I cannot see any cell where you get the historical currency exchange rate. I'm looking for the historical US dollar/Canadian dollar rate that CURRENCYH normally returns. I see CURRENCYCONVERT, but that gives the current exchange rate, not the historical one.

I do track symbols, and I can get them in their stock exchange's currency. However, I do have to convert them into country's currency in order to establish book value for income tax purposes. I will also confirm that up until more recently, CURRENCYH has been reliably working. I am not certain when it went awry, but it was in the past month or two.

quixentric wrote:

Will consider a move over to Sheets unless someone from Apple can help out here?

If you haven't done so already be sure to file a bug report with Apple. Numbers > Provide Numbers Feedback and choose 'Bug Report' under Feedback Type.

SG

Well, it still does not work as it should.

I have tested EUR/PLN pair and it works only for dates before 24.04.2021 now.

It somehow improved (from 13.04.2021 to 24.04.2021 being the last date with available historical exchange rates).

Any idea? Maybe it's yahoo's issue (provider of the data)?

I recommend studying the second sheet of the "My Stocks" template at File > New in your menu. Try substituting your symbol there to see it is working there.

SG

Yes, I see that too. CURRENCYH("USD","CAD",close,date) where date is 2021-04-15, 2021-04-14 and 2021-04-13 all return errors. It is 2021-04-16 today.