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