3

I can currently get the 52-week low of a stock by using this call in Google Sheets.

=GOOGLEFINANCE("NYSE:UBER","low52")

How do I get the 3-year and 5-year low of a stock? For a 3-year low I tried something like this:

=GOOGLEFINANCE("NYSE:UBER","price",TODAY(),=EDATE(TODAY(),-12*3))

However this returns a list of the past 20 prices, not a single value representing the lowest value over the past X-years.

steve238
  • 831
  • 1
  • 10
  • 19

1 Answers1

7

Get daily price data for your date range and find the mininum value

For 3-year low:

=MIN(INDEX(GOOGLEFINANCE("NYSE:UBER","price",DATE(YEAR(TODAY())-3,MONTH(TODAY()),DAY(TODAY())), TODAY()),0,2))

and for 5-year low:

=MIN(INDEX(GOOGLEFINANCE("NYSE:UBER","price",DATE(YEAR(TODAY())-5,MONTH(TODAY()),DAY(TODAY())), TODAY()),0,2))

Dynamically, add your number of years to a cell (for example A1) and use:

=MIN(INDEX(GOOGLEFINANCE("NYSE:UBER","price",DATE(YEAR(TODAY())-A1,MONTH(TODAY()),DAY(TODAY())), TODAY()),0,2))

Note for this pair the 3-year and 5-year lows are the same

Formulae Used:

Rafa Guillermo
  • 14,474
  • 3
  • 18
  • 54
  • This is exactly what I wanted - thank you! – steve238 Feb 09 '22 at 11:36
  • Getting errors with this formulae as of now. – bart-kosmala Oct 15 '22 at 13:01
  • For dynamical formula extrapolation, I would suggest fixating the cell with the number of years `$A$1`: `=MIN(INDEX(GOOGLEFINANCE("NYSE:UBER","price",DATE(YEAR(TODAY())-$A$1,MONTH(TODAY()),DAY(TODAY())), TODAY()),0,2))`. This allows you to apply the formula on multiple tickers without adjusting it. – Frumda Grayforce Apr 05 '23 at 10:48