Search code examples
google-sheetsgoogle-finance

How to get last closing price of a stock in google finance (In a non-trading day)

I tried using

=INDEX(GOOGLEFINANCE("NASDAQ:MSFT","price",TODAY(), 2),2,2)

And saw that on some days (e.g. 12/6/2021, a Saturday) the function failed to return value. I assume that it is because there was no trading on that day.

Is there a way for me to pick the last value of a stock prior to that day? (e.g. If I calculate on a Saturday or on a Sunday with an American stock I would get Friday's value, Thursday for an Israeli stock etc.)

Solution

  • I am not aware of a way for GOOGLEFINANCE to automatically adjust for no trading days.

    One way to do it is to get the "price" data for the last 7 days (to be safe) and then query that data to get the "price" value next to the max date.

    This formula works for me: =INDEX(QUERY(GOOGLEFINANCE("NASDAQ:MSFT","price", TODAY()-7, TODAY()),"select Col1, Col2 order by Col2 desc",1), 2, 2)