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.

      "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?

  • 2
    Can you provide the data you obtain with =GOOGLEFINANCE("nse:"&B2,"all",$A$2-80,81) or share a sample sheet ?
    – pjmg
    Sep 27, 2019 at 9:38

2 Answers 2


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


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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