In google sheets, how do you use googlefinance to get the high price of a stock in a date range?

Question

I wrote the following formula, and it only returns the high of the starting date and not the high of the entire date range: =INDEX(GoogleFinance(C7,"high", A7,B7,"DAILY"),2,2)

where C7 is the google sheet cell that contains the stock symbol and A7 contains the starting date and B7 contains the ending date.

How to get the date for this max price in the range?

Snowberry

Answer 1

The formula:

=MAX(INDEX(GOOGLEFINANCE(C7, "high", A7, B7), , 2))

…will return the stock's high price between the dates A7 and B7 in your example.¹

How does it work?

Using the formula:

=GOOGLEFINANCE(<symbol>, "high", <StartDate>, <EndDate>) or

=GOOGLEFINANCE(C7, "high", A7, B7) (in your example),

…will return an array which looks something like this:

Using the INDEX() function, you can convert that array to a single column of the high values:

=INDEX(GOOGLEFINANCE(C7, "high", A7, B7), , 2)

Now you can pass that array into the MAX() function to get the highest value in a single cell:

=MAX(INDEX(GOOGLEFINANCE(C7, "high", A7, B7), , 2))

¹ Important Note:

If you include a date range in GOOGLEFINANCE's "high" function, it will not include today's price during the current trading day — so if you need to include today's high price in your range, you will need to compare today's high price to the historical data explicitly.

E.g., the function =GOOGLEFINANCE(symbol, "high") gives you today's high price, so:

=MAX(GOOGLEFINANCE(C7, "high"), MAX(INDEX(GOOGLEFINANCE(C7, "high", TODAY()-30, TODAY()), , 2)))

…will give you the high price over the last 30 days, including today.

If there's a possibility that your date range might include today only (i.e. no historical data where A7 and B7 both equal today), unfortunately, the formula above will return #NA. I have the ultimate solution to generate the correct array even if it does not contain historical data.

The Bullet-Proof Solution

The formula below traps the #NA condition and builds the array to include today's high no matter what date range is specified:

=MAX(INDEX({IFNA(GOOGLEFINANCE(E7, "high", A7, TODAY()), {"Date", "High"}); NOW(), GOOGLEFINANCE(E7, "high")}, , 2))

I've been bitten by this quirk a few times and this solution seems to be cover all scenarios. Enjoy!

Harrow

Hey @Robert, In your bullet proof solution you've used E7 twice. Did you mean C7? Stock symbol? Also, if one needs historical high of a stock, then how do you propose one should alter your bullet proof solution to get the desired result? – Belinda

please explain how , , 2 works in index – Woolpack

Answer 2

Please try:

=max(INDEX(GoogleFinance(C7,"high",A7,B7,"DAILY"),0,2))

0,2 rather than 2,2 at the end to return the range rather than a cell from it and MAX for the maximum.

Sibship

Thanks, but it's still a bit off. In my spreadsheet, A7 is 10/17/17 and B7 is 10/20/17. Your formula seems to give me the high from the range 10/17/17 to 10/19/17, thus excluding 10/20/17. – Snowberry