Calculate yearly or end-of-year returns

To calculate yearly returns for a stock, pull the closing prices at each year-end and compute the percentage change. Use a 1year inter-day time series to get a multi-year price series in one formula, or use the Historical EOD function to fetch exact year-end prices for one or many symbols.

Use a 1-year inter-day time series

SF_TIMESERIES with the "1year" period returns the closing price at each year interval. There are no date-range limits for inter-day data, so you can span decades.

To pull year-end closes for 2021 through 2025:

=SF_TIMESERIES("AAPL", "2021-12-31", "2025-12-31", "1year", "close")

This returns one row per year. In the next column, calculate each calendar-year return with the previous row as the starting price:

=(B3-B2)/B2

If you need dividend-adjusted returns, request "adjClose" instead of "close". Use the "NH" option to remove the header row if you are nesting the formula inside another function.

Sheet space: SF_TIMESERIES returns multiple rows. If any cell in the target range already contains data, you will see a #REF! error. Clear the area before entering the formula.

Batching note: SF_TIMESERIES cannot be batched across symbols in a single call. If you need the same yearly series for many stocks, use a separate formula per stock or switch to the Historical EOD batch method described below.

Use Historical EOD for exact year-end dates

For precise control over the anchor date, use SF with the "historical" type. You can pass a fixed date string, a DATE() formula, or a reference back to a cell that contains TODAY().

Single year-end close for 2023:

=SF("AAPL", "historical", "close", "2023-12-31")

If the date is not a trading day (weekend or market holiday), SheetsFinance returns the latest EOD value prior to that date. If you get a "no data" error, check that historical data exists for the symbol near the requested date.

Dynamic year-end using DATE():

=SF("AAPL", "historical", "close", DATE(2023, 12, 31))

Relative to a helper cell that holds =TODAY():

=SF("AAPL", "historical", "close", DATE(YEAR($A$1)-1, 12, 31))

Place the formula for each year in its own column, then compute the return in a separate row:

=(C2-B2)/B2

Use Historical EOD batch for a list of symbols

If you need year-end prices for many symbols at once, pass a range of tickers to SF with "historical". The entire range counts as a single function call.

Assuming your tickers are in A2:A100, this returns the 2024 year-end close for every symbol in one row per ticker:

=SF(A2:A100, "historical", "close", "2024-12-31")

Repeat the formula across columns for each year you want to compare, then calculate returns in adjacent cells. The batch length depends on your plan level.

Tip: Add the "NH" option to remove the header row when you are stacking multiple historical calls or feeding them into another formula.

Which method to choose

Choose the 1-year inter-day time series when you want a continuous multi-year price series for one stock in a single formula.
Choose Historical EOD when you need exact calendar year-ends, or when you are working with a portfolio and want to batch the lookup across many symbols.
Choose Historical EOD batch when you are running a screener or backtest and need the same year-end price for hundreds or thousands of symbols.

For more details on inter-day data, see Inter-day time series. For single-date lookups and batching, see Historical EOD and Historical EOD Batch.

Was this helpful?