Issue with Google Finance in Google Sheets for Currency Exchange Rates

Question

I am using the GOOGLEFINANCE() function in google sheets to get an exchange rate for expenses I am entering in for accounting.

The issue is that sometimes I get an #N/A error with the message

When evaluating GOOGLEFINANCE, the query for the symbol: 'PENUSD' returned no data.

The confusing part is that sometimes, perhaps with a refresh of the page after 5 minutes some cells actually start working and others receive the error. I can't pin the problem down.

On another note, it is obvious that this is a poor way to do currency exchange because it is constantly calculating the rate for a past day. Is there a way to get historical exchange rates as a value, one time? I am thinking a script that for every new "date" entered in the date column calculates the exchange rate for that day and enters in the number in the cell next to it.

Is this possible? Are there any better solutions out there? thanks for the help

Bourguiba

Answer 1

Yes, indeed very shaky. The formulas sometimes stop working when I switch to another tab and then come back. What helps me:

1. Close the tab and reopen (Ctrl-Shift-T). This usualy makes formulas work again for some time.
2. Select the cells with formulas, copy and paste back as "values only". This will hardcode them, which is actually exactly what I need.

Stratify

I agree this would work for some scenarios. While the project I was working on is more or less a thing of the past, the problem I believe is still there. There is also the question of gathering historical data, which I think is useful. Maybe with a couple more months someone will come up with a good script. – Bourguiba

Answer 2

Belated and somewhat unrelated, but you need to prefix with CURRENCY: these days. I.e. CURRENCY:PENUSD.

Crosslink

Good to know, but this has no impact on the errors from what I can tell. – Influent

Answer 3

Here is how to get historical rate for certain date

=index(GoogleFinance("CURRENCY:EURUSD", "price", 31/05/2023), 2, 2)

Instead of actual date can be cell address with date.

Jordans