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

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.

Answer 2

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