How to fetch price for a specific date from GoogleFinance?

Question

I tried below syntax to fetch the close price of specific date:

=GOOGLEFINANCE(<Symbol_Name>,"price","07/09/2020")

While it works perfectly for US stocks, the result for Indian stocks are incorrect. For example: if you query for July 9th, the stock quote is fetched from July 10th. See below screenshots:

US Stock

Indian Stock

How to make this work for all stock symbols irrespective of date or timezone?

Answer 1

make sure that market was open on that day. you can check like this:

=GOOGLEFINANCE("nse:tcs", "all", "20/06/2020", TODAY(), "daily")

Answer 2

From the GOOGLEFINANCE docs:

Dates passed into GOOGLEFINANCE are treated as noon UTC time. Exchanges that close before that time may be shifted by a day.

Considering that the NSE closing time is 3:30 PM at India timezone (UTC +5:30), that's before noon UTC time (10:00 AM). Therefore, it gets shifted by a day, as expected.

Modifying the spreadsheet timezone won't produce any change. The best option I see here is to keep track of which tickers will get shifted and ask for the previous day instead.

Reference:

• GOOGLEFINANCE