0

I am trying to fetch the dates of the high/low of the last 11 days using the following formula =TO_DATE(INDEX(SORT(GOOGLEFINANCE("nse:BAJFINANCE", "high", today()-11,today(),"DAILY"), 2, 0), 2,1))

=(TO_DATE(INDEX(SORT(GOOGLEFINANCE("nse:BAJFINANCE", "low", TODAY()-11,TODAY(),"DAILY"), 2, 0), 2,1))) What is fetches is 8/13/2022 for both while the correct date is 09/14/2022 for High and 9/7/2022 for low.

Where exactly am i going wrong?

Rinku
  • 81
  • 2
  • 14

1 Answers1

0

If I see the Google Finance syntax, I see that startDate should come before the end date:

GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])

See: https://support.google.com/docs/answer/3093281?hl=en

Try it this way: (notice the sequence of today()-11)

=TO_DATE(INDEX(SORT(GOOGLEFINANCE("nse:BAJFINANCE", "high", today()-11,today(),"DAILY"), 2, 0), 2,1))

Also, please watch out that Google Finance may not be showing you values with the NSE's actual close prices, but rather the close of the previous day: Dates passed into GOOGLEFINANCE are treated as noon UTC time. Exchanges that close before that time may be shifted by a day.

EDIT:

Your SORT and INDEX parameters need to be changed in order to get the correct result. Use the below:

Low:

=(TO_DATE(INDEX(SORT(GOOGLEFINANCE("nse:BAJFINANCE", "low", TODAY()-11,TODAY(),"DAILY"), 2, 1), 1,1)))

High:

=(TO_DATE(INDEX(SORT(GOOGLEFINANCE("nse:BAJFINANCE", "high", TODAY()-11,TODAY(),"DAILY"), 2, 0), 2,1)))
  • Thanks. Tried your method Sangam, however it doesn't work correctly. I have updated the code for the low too. Both dates give the date of 9/13/2022, which is incorrect. – Rinku Sep 14 '22 at 09:30
  • I would suggest to test the individual components; i.e. just run GOOGLEFINANCE first and see if these results are correct. Then you can go into the other funcs. –  Sep 14 '22 at 14:18
  • I have edited my answer to explain the issue with the params you are passing to your functions –  Sep 14 '22 at 16:07