Google Sheets – Fetching Stock Price Highs and Lows

google sheetsgoogle-finance

For shares listed on NSE I am unable to get Day's high and low while using below query if the start day and end date is same or today.

High:
=MAX(QUERY(GOOGLEFINANCE("NSE:INFY", "ALL",06/05/2021 ,06/05/2021),"select Col3",1))

Low:
=MIN(QUERY(GOOGLEFINANCE("NSE:INFY", "ALL",06/05/2021 ,06/05/2021),"select Col4",1))

Best Answer

The expression 06/05/2021 is not a date but a quotient whose value is 6 divided by 5 divided by 2021, e.g., 0.0005937654626.

Since the question was posted on 6 May 2021, I am assuming that you want to express the date 2021-05-06. To get a dateserial value, replace the hard-coded value with a reference to a cell that contains a date, or use an expression like value("2021-05-06"), as in the following examples.

Get the maximum High and the minimum Low for a single day:

=query( 
  googlefinance("NSE:INFY", "all", value("2021-05-06")), 
  "select max(Col3), min(Col4)", 
  1 
)

Get the maximum High and the minimum Low for a calendar month:

=query( 
  googlefinance( 
    "NSE:INFY", 
    "all", 
    value("2021-05-06"), 
    day(eomonth(value("2021-05-06"), 0)) 
  ), 
  "select max(Col3), min(Col4)", 
  1 
)

As said, this is usually done by using a reference to a cell that contains a date, rather than using a hard-coded value embedded in the formula, like this:

=iferror( 
  query( 
    googlefinance( 
      "NSE:" & A2, 
      "all", 
      G2, 
      H2 - G2 + 1 
    ), 
    "select max(Col3), min(Col4) 
     label max(Col3) '', min(Col4) '' ", 
    1 
  ), 
  "(no data for '" & A2 & "' " & trim(G2) & " - " & trim(H2) & ")" 
)

See this answer for an explanation of how date and time values work in spreadsheets.

Related Topic