Google Sheets – Using GOOGLEFINANCE for High & Low with Timestamp

formulasgoogle sheetsgoogle-financegoogle-sheets-arrayformulavlookup

I'm using Google Finance calls in Google Sheets for some calculations.

I've got 2 formulas, one to find the low price, and one to find the high price from a certain date:

=MIN(INDEX(GOOGLEFINANCE("TSLA","low",7/23/2018,TODAY(),1),,2))

=MAX(INDEX(GOOGLEFINANCE("TSLA","high",7/23/2018,TODAY(),1),,2))

These work as I expect to show just the low/high price, but now I'd like to also have the associated timestamp in another column next to it.

So it would roughly look like this:

|Ticker | Low    | Low Date | High   | High Date |

| TSLA  | 176.99 | 6/3/2019 | 387.46 | 8/7/2018  |

Best Answer

={"TSLA", MIN(INDEX(GOOGLEFINANCE("TSLA", "low",  "7/23/2018", TODAY(), 1),, 2)),
  VLOOKUP(MIN(INDEX(GOOGLEFINANCE("TSLA", "low",  "7/23/2018", TODAY(), 1),, 2)), 
             {INDEX(GOOGLEFINANCE("TSLA", "low",  "7/23/2018", TODAY(), 1),, 2), 
                    GOOGLEFINANCE("TSLA", "low",  "7/23/2018", TODAY(), 1)}, 2, 0), 
          MAX(INDEX(GOOGLEFINANCE("TSLA", "high", "7/23/2018", TODAY(), 1),, 2)),
  VLOOKUP(MAX(INDEX(GOOGLEFINANCE("TSLA", "high", "7/23/2018", TODAY(), 1),, 2)), 
             {INDEX(GOOGLEFINANCE("TSLA", "high", "7/23/2018", TODAY(), 1),, 2), 
                    GOOGLEFINANCE("TSLA", "high", "7/23/2018", TODAY(), 1)}, 2, 0)}

0

Related Topic