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

Question

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

score 5 · Accepted Answer · answered Jun 14 '21 at 06:33

5

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)

answered Jun 14 '21 at 06:33

Broly

799
3
16

When I just tried Col1 was Date and Col2 was price, so 'order by Col2 desc' returned the max price for the week. I changed it to 'order by Col1 desc' to get the last price – Gvancha Jun 02 '22 at 05:33

score 1 · Answer 2 · edited Mar 12 '22 at 11:45

You can use the formula below, in the cell that you want the price to be.

=INDEX(GOOGLEFINANCE(A5;"price";$B$2);2;2)

Where A5 contains the stock symbol, like CMI, JNJ, NEE or whatever.

And the B2 contains the following formula:

=if(weekday(B1)=2;B1-3;if(weekday(B1)=1;B1-2;B1-1))

Finally, B1 is just =today().

This will adjust the day for weekdays only. So if it is Saturday, Sunday or Monday, it will give you the price of the stock on Friday.
Basically it will give the last closing price on business days.

Best regards.

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

2 Answers2