Calculate high, low, and average prices over a custom period

Wrap a SheetsFinance time series with the built-in spreadsheet functions MAX(), MIN(), and AVERAGE() to get a high, low, or average price for any date window you choose.

When you need this

Use this pattern when you want a single statistic over a custom window—for example a 3-month high, a 3-month low, or an average close over the last year—without a dedicated period formula. Pull the price column with SF_TIMESERIES, then aggregate it with a native spreadsheet function.

Pattern

Return only the metric column you need, drop the header with NH, and nest the call inside MAX, MIN, or AVERAGE.

=MAX(SF_TIMESERIES(symbol, startDate, endDate, period, "high", "NH"))
=MIN(SF_TIMESERIES(symbol, startDate, endDate, period, "low", "NH"))
=AVERAGE(SF_TIMESERIES(symbol, startDate, endDate, period, "close", "NH"))

• symbol is the ticker string or a cell reference (for example "AAPL" or A1).

• startDate and endDate are ISO dates (YYYY-MM-DD) or cell references. For rolling windows, put =TODAY() in one cell (for example B1) and reference it as $B$1.

• period is "" or "daily" for daily bars, or an inter-day interval such as "1week", "1month", or "1year".

• Use "high", "low", or "close" (or another single metric) so the aggregator receives one numeric column.

• "NH" removes the header row so text labels are not mixed into the calculation.

Example: 3-month high, low, and average from daily data

Use a daily time series over the last 90 days and aggregate the high, low, and close columns. Assume B1 holds =TODAY().

=MAX(SF_TIMESERIES("AAPL", $B$1-90, $B$1, "", "high", "NH"))
=MIN(SF_TIMESERIES("AAPL", $B$1-90, $B$1, "", "low", "NH"))
=AVERAGE(SF_TIMESERIES("AAPL", $B$1-90, $B$1, "", "close", "NH"))

Daily series support 30+ years of history. Full metric and option details are in Daily time series.

Example: 1-year high, low, and average from weekly bars

Set the period to "1week" when you want one bar per week instead of daily rows. Inter-day series have no date-range limits.

=MAX(SF_TIMESERIES("AAPL", $B$1-365, $B$1, "1week", "high", "NH"))
=MIN(SF_TIMESERIES("AAPL", $B$1-365, $B$1, "1week", "low", "NH"))
=AVERAGE(SF_TIMESERIES("AAPL", $B$1-365, $B$1, "1week", "close", "NH"))

Supported inter-day periods are "1week", "1month", and "1year". See Inter-day time series for the full metric list.

Example: fixed date window

Pin start and end dates when the window should not move with today.

=MAX(SF_TIMESERIES("AAPL", "2025-01-01", "2025-03-31", "", "high", "NH"))
=MIN(SF_TIMESERIES("AAPL", "2025-01-01", "2025-03-31", "", "low", "NH"))
=AVERAGE(SF_TIMESERIES("AAPL", "2025-01-01", "2025-03-31", "", "close", "NH"))

Example: high of day or low of day from intra-day data

Apply the same pattern to an intra-day high or low column when you need the high or low inside a shorter session window.

=MAX(SF_TIMESERIES("AAPL", $B$1, $B$1, "1min", "high", "NH"))
=MIN(SF_TIMESERIES("AAPL", $B$1, $B$1, "1min", "low", "NH"))

Intra-day ranges are limited by interval (for example, 1min is limited to 3 days). If the requested range is too large, the output is constrained from endDate and startDate is ignored. Details and limits are in Intra-day time series.

Reuse symbols and windows with cells

Store the symbol and window in cells so you can change the period without editing every formula.

Cell A1: AAPL
Cell B1: =TODAY()
Cell C1: =$B$1-90

=MAX(SF_TIMESERIES(A1, C1, $B$1, "", "high", "NH"))
=MIN(SF_TIMESERIES(A1, C1, $B$1, "", "low", "NH"))
=AVERAGE(SF_TIMESERIES(A1, C1, $B$1, "", "close", "NH"))

Change C1 to =$B$1-365 for a 1-year window, or point C1 and B1 at fixed dates for a static range.

What to watch for

• Pass a single numeric metric ("high", "low", or "close"). Aggregating a multi-metric result mixes columns you do not want in the statistic.

• Include "NH" so the header row is excluded from MAX, MIN, and AVERAGE.

• There is no dedicated SheetsFinance period-high or period-low function for an arbitrary window. Build the window with dates (or weekly, monthly, or yearly bars) and aggregate the returned column.

• If you need the full table on the sheet and a separate statistic cell, place the time series in a clear range, then point MAX, MIN, or AVERAGE at that range. A nested formula that spills into occupied cells can raise a #REF! array expansion error.

What is next

To pull one cell from a multi-row result without aggregating, see Get a single value from a multi-row function. For longer ranges that hit per-call limits, use Stack limited date ranges with VSTACK.