'GoogleFinance often returns #N/A and internal error messages while getting stock quotes

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?

google-sheetsnestedgoogle-sheets-formulagoogle-financeazureazure-iot-hubazure-iot-hub-device-management


Solution 1:[1]

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))

Solution 2:[2]

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.

Solution 3:[3]

I was able to refresh the spreadsheet to force a retry and that fixed the errors.

Solution 4:[4]

Please understand that The device or module ID stored in the identity registry of Azure IoT Hub is case-sensitive. Make sure you are registering the same device with case-sensitive rule in mind.

Reference: Understand the identity registry in your IoT hub

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 player0
Solution 2 Patrick Fournier
Solution 3 Nabha
Solution 4 asergaz

Related Questions