Anyone know workarounds to make GoogleFinance actually work? It works for a while so it is not a problem with my formulas but then periodically the cells that were showing stock quotes suddenly show "#N/A" and if you hover over them it shows that GoogleFinance experienced an internal error. It's really buggy. Changing the refresh interval doesn't help, in either direction. Anyone know workarounds to avoid the errors and have the spreadsheet simply show (and keep showing) the stock quote data without the errors?
2 Answers
7
votes
you can either use alternative to GOOGLEFINANCE (depends on what exactly are you up to)
or if you want to stick with it you can wrap it into IFERROR:
=IFERROR(GOOGLEFINANCE(your_formula_here), GOOGLEFINANCE(same_formula_here))
or even:
=IFERROR(IFERROR(
GOOGLEFINANCE(your_formula_here),
GOOGLEFINANCE(same_formula_here),
GOOGLEFINANCE(same_formula_here))
2
votes
You could add a script to your spreadsheet (Tools/Script Editor) and use the CacheService (https://developers.google.com/apps-script/reference/cache) to cache Google Finance results. Cache data when it is available, use the cache when there is an error.
See https://developers.google.com/apps-script/quickstart/custom-functions for how to write custom functions for Google Sheets.
GOOGLEFINANCEfunction in Google Sheets, not Excel. – BigBen