1

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?

  • 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 at 9:38
  • Welcome. Please remember that as per site guidelines when an answer addresses your question, you should accept it so others can benefit as well. – marikamitsos Sep 29 at 7:58
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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