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

Question

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?

Daguerre

I edited the tags as this seems related to the GOOGLEFINANCE function in Google Sheets, not Excel. – Thomajan

In case it helps someone, I was able to refresh the spreadsheet to force a retry and that fixed the errors. – Tentative

Even a refresh doesn't work all the time. The only conclusion: Google Finance is very buggy. Shame on you, Google! – Rumelia

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

Announce

Thanks for the response. There are not many alternatives these days that offer historic quotes. I was really hoping for someone to say how to workaround the constant Google Finance errors, however, your nested IFERROR is an interesting option to force a retry. Thanks for sharing that. I have used IFERROR to suppress the ugliness of errors but nesting them is a smart thought. The unfortunate part, though, is that once Google starts returning errors, it tends to keep doing so. Again, though, thanks. – Daguerre

This works, however, you may want to try different field - for eg. use "closeyest" instead of "price" - at times that has worked for me. Understand there might not be alternate for many fields. – Cumin

Answer 2

You can handle this #N/A by adding to your formula IFNA(...) Example:

=IFNA(googlefinance(CONCATENATE("NYSE:",A1), "shares"),"#N/A occur")

You can return 0 or something else. For more details see

Roney

Answer 3

In Google sheet , restrict row numbers to 99 for a single sheet. It works. I had 280+ rows with five columns calling googlefinance APIs in a single sheet , it used to return N/A. After moving balance rows to two different tabs, its working fine. Probably Google has a restriction.

Monitor