1

The pictures don't show the ⚠️ for errors but each blank cell is an error that reads something like: There is no historical data on the requested date. The next available is on 8/7/2827673.

The weekends show no values as expected, but there are none at any point in time that I've checked for the Russian Ruble RUB or Ukranian Hyrvnia UAH.

How do I get these values?

Formulas I've used are simple conversion from AUD with =CURRENCYH("AUD","RUB",0,"2023-08-25") = /!\ error

To another currency, the same formula works: =CURRENCYH("AUD","CAD",0,"2023-08-25") = CAD 0.87

Also tried from another currency: =CURRENCYH("CAD","RUB",0,"2023-08-25") = /!\ error

results formulas

1 Answer 1

2

The prices from financial markets that can be pulled into Numbers using functions come from Yahoo Finance. You can bring in such prices, current or historical, to Numbers using the proper financial function only if such prices for a given financial instrument already exist in Yahoo Finance.

To check if historical prices exist in Yahoo Finance for a financial instrument, first go to the Yahoo Finance web site and punch in its name, code, or symbol in Yahoo Finance's search bar (CAD/RUB for example) and see if anything comes up. If it does, select the matching item in the list to come up and then check if there is data displayed in the historical data section for the instrument.

In case historical prices do not exist in Yahoo Finance for a currency pair you are interested in such as CAD/RUB, a workaround is to derive them from the respective USD pair for each currency assuming they both exist in Yahoo Finance along with historical data. For example, pull in USD/CAD and USD/RUB using CURRENCYH, and then divide the latter by the former to get CAD/RUB.

Note that the CURRENCYH function pulls in the historical market closings and only starting after midnight US time for the latest closing. You can fetch the latest quote with a bit of delay using the CURRENCY function.

5
  • The AUD/RUB value shows on yahoo finance but not in numbers.app! I used your workaround with success CURRENCYH("USD","RUB",0,"2023-08-25")×CURRENCYH("AUD","USD",0,"2023-08-25"). Any idea why the difference between the Numbers app and Yahoo Finance?
    – yarns
    Aug 26, 2023 at 18:04
  • so does that mean Yahoo Finance isn't the historical data source? or maybe a bug
    – yarns
    Aug 27, 2023 at 10:14
  • Sorry if the extra “also” in my comment confused you. I don’t think there is a bug or another source Numbers is using to get such data. Here is the slightly improved version of my previous (and now deleted) comment: “I am glad it helped. As I said in my answer, it appears that historical data should exist at Yahoo Finance in addition to the currency pair existing for CURRENCYH to work for a given currency pair. This does not seem to be the case for either AUD/RUB or CAD/RUB.”
    – Alper
    Aug 27, 2023 at 10:22
  • You should update your post with the answer first like: If historical prices do not exist for a currency pair, a workaround is to find the price compared to USD for both and get the product of the two results. the cell formula: CURRENCYH("USD","RUB",0,"2023-08-25")×CURRENCYH("AUD","USD",0,"2023-08-25")
    – yarns
    Aug 28, 2023 at 8:02
  • Thank you for the suggestion but I think the answer’s organisation is better as it is. Let me know if there is anything else you would like me to elaborate regarding my answer.
    – Alper
    Aug 29, 2023 at 1:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .