I'm using google finance in one of my sheets and it is returning slightly different values for open and close when stocks are not traded on that day.

eg. sunday

=INDEX(GoogleFinance("AMZN", "open", date(2018,1,7),1,1),2,2)) gives 1236.000


=INDEX(GoogleFinance("AMZN", "close", date(2018,1,7),1,1),2,2)) gives 1246.870

The googlefinance() function returns a table of values, beginning with the date you specify. You have specified for it to return 1 day of values (num_days attribute = 1), but because the date you specified (1/7/2018) has no data, the 1 day it has returned is the next available day, 1/8/2018. This is apparent if you remove the INDEX() wrapper function and allow the googlefinance() function to show its full returned value:

=GoogleFinance("AMZN", "open", date(2018,1,7),1,1)

Google sheets googlefinance example


Seems like something is off with the dates, because the prices you have shown are the same as per 8 Jan 2018:


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.