79784208

Date: 2025-10-07 04:24:55
Score: 0.5
Natty:
Report link

I had the same problem, and came up with this kludgy workaround:
=VLOOKUP(TODAY(),GOOGLEFINANCE(Y31,"close",TODAY()-10,10),2,TRUE)
(where the cell Y31 has the ticker / resource that I want the price of.)

The reason this works is that (at least in the case that I encountered), the N/A was coming because Google finance was buggy and suddenly didn't have prices for the past 4 days. But it still remembered the historical prices prior to that! So that call for the past 10 days returned an array that was only 9 rows long on the first buggy day, 6 rows long on the fourth buggy day, etc. But the lookup of today's date returns the most recent one available. And after the bug got solved, it returns the actual today value.

So basically, the lookup function will return today's close if there are no bugs, and during N/A bug-storm, it will still return the last data point that Google finance does have. Not ideal, but better than getting an N/A.

This is not a perfect workaround... If you want close-to-realtime stock price value, this doesn't give that. But my purpose was to get currency exchange rate for a calculation that doesn't need to be so up to date, and getting 4 day old exchange rate is close enough for me. It also involves a lot more calculation than the simple Googlefinance call, so I wouldn't use it if you have many lookups.

If you want to get a bit more accurate (and protect against an N/A buggy period that lasts more than 10 days), you can do something like:
=ifna(Z31,ifna(Z32,Z33))
where z31 is the normal direct Googlefinance call, z32 is the vlookup workaround, and z33 is a manual entry cell for worst case scenario.

Reasons:
  • Whitelisted phrase (-1): I had the same
  • RegEx Blacklisted phrase (1): I want
  • Long answer (-1):
  • No code block (0.5):
  • Low reputation (1):
Posted by: BradY