0

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

1

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.

4
  • sorry but it is not working in my query =MAX(QUERY(GOOGLEFINANCE("NSE:INFY", "ALL",value("2021-05-01") ,value("2021-05-01")),"select Col3",1)) -- High =MIN(QUERY(GOOGLEFINANCE("NSE:INFY", "ALL",value("2021-05-01") ,value("2021-05-01")),"select Col4",1))--Low May 13 at 9:52
  • Edited the answer to show how to make it work. The problem in your formula is that the start date and the end date are the same. May 13 at 12:16
  • Hi please find a link for my Test sheet, where i am using the date as cell reference still i am unable to get high/low other than row 1. docs.google.com/spreadsheets/d/… May 18 at 15:55
  • Edited the answer to show how to do this with your spreadsheet. May 18 at 17:29

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.