Search code examples
indexinggoogle-sheetsmaxgoogle-sheets-formulagoogle-finance

Filter the Google Finance formula to only display the "high" of all time

It's in reference to the Google Finance function in Google Sheets: https://support.google.com/docs/answer/3093281?hl=en

I would like to obtain the "all time LOW" (ATL) and "all time HIGH" (ATH) for a specific ticker (i.e. ABBV or GOOG) but only in 1 cell for each. Basically "What's the ATL/ATH value for this ticker?"

I've tried to do both formulas for ATL and ATH, but only ATL gives the expected result for now.

To get the ATL, you can use 

=GOOGLEFINANCE("ABBV","low","01/12/1980",TODAY(),7)

and to get the ATH you can use:

=GOOGLEFINANCE("ABBV","high","01/12/1980",TODAY(),7)

The output of this is 2 columns of data:

https://imgur.com/z72pdBb

Please note that column A, containing the timestamp, will be the one making trouble when it comes to computing the MAX function as it translates into some weird figures.

In order to get the ATL, I'll be using the MIN function which works perfectly fine: 

=MIN(GOOGLEFINANCE("ABBV","low","01/01/1980",TODAY(),7))

as it will just scan the 2 columns of data and grab the lowest value which is 32.51 in USD.

BUT when I'm trying to do the same with MAX or MAXA for the ATH using for example 

=MAX(GOOGLEFINANCE("ABBV","high","01/12/1980",TODAY(),7)

the result that comes out is 43616.66667 which seems to be a random computation of the column A containing the timestamp.

The expected result of the ATH should be 125.86 in USD.

I've tried using FILTER to excluded values >1000 but FILTER doesn't let me search in column B, so then I tried with VLOOKUP using this formula 

 =VLOOKUP(MAX(GOOGLEFINANCE("ABBV","high","01/12/1980",TODAY(),7)),GOOGLEFINANCE("ABBV","high","01/12/1980",TODAY(),7),2,FALSE)

but again it returns the value of column B but based on the MAX value of column A which end up giving me 80.1 and not the expected 125.86.

Solution

  • use:

    =MAX(INDEX(GOOGLEFINANCE("ABBV", "high", "01/12/1980", TODAY(), 7), , 2))

    43616.66667 is not a "random computation". it's date 31/05/2019 16:00:00 converted into a date value

    MAX and MIN functions return single output from all possible cells in the included range which are in your case two columns. the date is considered as a number too so maxing out those two columns will output you the max value whenever it is from 1st or 2nd column. by introducing INDEX you can skip 1st column and look for a max value only in the 2nd column.