GoogleSheets Random #N/A [Help🎅]

does anyone else randomly get errors with google sheets ??
It was working yesterday and now it does not, if anyone knows a fix it would be greatly appreciated :slight_smile:

=(GOOGLEFINANCE(B22,“price”)-INDEX(GOOGLEFINANCE(B22,“close”,TODAY()-3655),2,2)) / INDEX(GOOGLEFINANCE(B22,“close”,TODAY()-3655),2,2)

I occasionally get errors like this. Sometimes they are persistent and other times they disappear when I reload the sheet.

If I were you, I would debug this by checking to see if each constituent part of the larger formula works and go from there.

2 Likes

The weird thing is it was working last night and today I opened it and it doesn’t work, I have the same sort of formula for other time frames and it works, but the full volume for 5 years has decided to stop working

Seems like googlefinance is buggy, I’ve also tried the classic off and on :joy:

Maybe it’s something to do with us approaching the end of the year but I really can’t see that being the problem :joy:

Working OK for me - I do get the odd issue where upon opening it pulls back data from a previous date but a refresh usually resets it.

1 Like

Is your formula the same? It seems like it works but can’t get the data as the error says

But it works for other time frames but it’s my whole colum that doesn’t work

Yes I just copied from your post ( and changed all the quotes to " ) to return a value of 2.027920978.

1 Like

I’ll give that a shot, when I’m in from work thanks for the help!

Here’s my sheet:
https://docs.google.com/spreadsheets/d/1zP53s8_6jKp52Dm5uu7YgH5dqNGphXjeEkB_tdKk-gM/edit?usp=drivesdk

OK - your spreadsheet has TODAY()-365x5 which is 25/12/2014 and TODAY()-365x3 which is 24/12/2016. Google Finance holds no close data for these dates eg:

=GOOGLEFINANCE(“LON:DGE”,“close”,“20/12/2014”,“31/12/2014”) brings back:

|22/12/2014 16:30:00|1849.5|
|23/12/2014 16:30:00|1858.5|
|24/12/2014 16:30:00|1864.5|
|29/12/2014 16:30:00|1870|
|30/12/2014 16:30:00|1840|

1 Like