Why does close price results have a date shift (one day off) while using GOOGLEFINANCE in a Query

Question

I want to get last 40 days close price of stocks.

I used below formula for the same but the result not showing the last day close price.

=QUERY(
      GOOGLEFINANCE("nse:"&B2,"all",$A$2-80,81),
      "SELECT Col5 WHERE Col1 <= date '"&TEXT($A$2+1, "YYYY-MM-DD")&"' order by Col1 desc limit 40",False
       )

Where, B2 is ticker, A2 is Today()

It updates next day evening. I mean for example today is Friday 27 Sep 1:15 PM (India) but result is showing up to 25 Sep. Data for 26 will be shown today evening.

Can someone please help me out?

Answer 1

The key word in your formula is not Query but GOOGLEFINANCE.

Your query is correct. But please take into account the following.

As mentioned under GOOGLEFINANCE documentation page:

• attribute is one of the following for historical data:
  • "open" - The opening price for the specified date(s).
  • "close" - The closing price for the specified date(s).
  • "high" - The high price for the specified date(s).
  • "low" - The low price for the specified date(s).
  • "volume" - The volume for the specified date(s).
  • "all" - All of the above.
• end_date|num_days - [ OPTIONAL ] - The end date when fetching historical data, or the number of days from start_date for which to return data.
• If any date parameters are specified, the request is considered historical and only the historical attributes are allowed.
• Quotes are not sourced from all markets and may be delayed up to 20 minutes.
• Dates passed into GOOGLEFINANCE are treated as noon UTC time. Exchanges that close before that time may be shifted by a day.

Taking into account the above quotes as well as that there is a time difference with India (UTC +5:30) the results you get are the expected ones.

Extra: Not even changing your time zone under Spreadsheet settings will make any difference (as others tried before).

Answer 2

=index(query({GOOGLEFINANCE(A2,"price",K1+1,TODAY());{today(),**GOOGLEFINANCE(A2,"price")**}},"select sum(Col2) pivot Col1"),2)

You can add today's price in the list with the help of above query, where K1 is date.