A rolling one year mean return, the number an optimizer would be fed

Name: A rolling one year mean return, the number an optimizer would be fed
Creator: Strasmore
Published: 2026-08-16
License: https://www.strasmore.com/terms/

Answered against 22 years of US equities and 12 years of US options data and published with the query that produced it. This result is stored as of 2026-08-16, from When Equal Weight Beats Optimization.

as of 2026-08-16 series 96×3read in context →

A rolling one year mean return, the number an optimizer would be fed — 96 rows by 3 columns, computed 2026-08-16 from US exchange, SIP and OPRA data.
month	spy_trailing_mean_pct	ko_trailing_mean_pct
2017-01-01	17.7	-0.4
2017-02-01	20.7	-3.3
2017-03-01	16.1	-5.9
2017-04-01	13.2	-5.4
2017-05-01	15.2	-1
2017-06-01	15.9	1.6
2017-07-01	13.6	0.1
2017-08-01	12.3	5
2017-09-01	14.7	7.7
2017-10-01	17.8	9.6
2017-11-01	18.2	10.8
2017-12-01	17.2	10.8
2018-01-01	20.6	12.6
2018-02-01	15.3	7.5
2018-03-01	13.7	3.8
2018-04-01	12.4	2.8
2018-05-01	12.8	-3.5
2018-06-01	13.1	-3.3
2018-07-01	13.8	1.3
2018-08-01	15.9	1.6
2018-09-01	16.1	1
2018-10-01	9.4	1.2
2018-11-01	5.9	7.9
2018-12-01	-2.4	6.4
2019-01-01	-5	2.6
2019-02-01	3.1	6.9
2019-03-01	4.8	6.5
2019-04-01	10.2	8.9
2019-05-01	6.7	15.7
2019-06-01	6	17.5
2019-07-01	8.4	16.9
2019-08-01	2.9	16.9
2019-09-01	4.1	19.1
2019-10-01	7.8	17.5
2019-11-01	14.4	8.8
2019-12-01	21.7	13.2
2020-01-01	23.6	19
2020-02-01	18	23.1
2020-03-01	-3.4	5.6
2020-04-01	-0.5	2.6
2020-05-01	7.2	-2.4
2020-06-01	12.4	-4.1
2020-07-01	12.2	-6.6
2020-08-01	21.1	-6.1
2020-09-01	17.4	-3.3
2020-10-01	19.2	-2.4
2020-11-01	18.9	4.3
2020-12-01	20.6	3.9
2021-01-01	20.1	-6.6
2021-02-01	22.2	-10.5
2021-03-01	42.6	12.4
2021-04-01	42.4	17.3
2021-05-01	37.1	21.1
2021-06-01	32.3	18.8
2021-07-01	32	20
2021-08-01	28.5	18.3
2021-09-01	28.8	11
2021-10-01	27.3	10.1
2021-11-01	28.5	8.4
2021-12-01	24.3	7.6
2022-01-01	19.5	20.8
2022-02-01	14	21.7
2022-03-01	12.5	17.6
2022-04-01	6.8	19.6
2022-05-01	-1.6	17
2022-06-01	-6.6	13.3
2022-07-01	-8.9	13.7
2022-08-01	-4.7	13.8
2022-09-01	-12.1	10.3
2022-10-01	-15.4	6.1
2022-11-01	-14.7	10.8
2022-12-01	-14.8	13.4
2023-01-01	-11.7	3.6
2023-02-01	-5.6	-0.4
2023-03-01	-7.2	1.4
2023-04-01	-4.2	0.2
2023-05-01	4.7	-0.6
2023-06-01	12.5	-0.5
2023-07-01	16.5	-1.2
2023-08-01	8.5	-4
2023-09-01	14.4	-2.9
2023-10-01	15.4	-2.6
2023-11-01	14.3	-4.5
2023-12-01	18.6	-7
2024-01-01	20.6	-2.1
2024-02-01	21.4	1.1
2024-03-01	27.5	1
2024-04-01	22.4	-4.3
2024-05-01	24	0.8
2024-06-01	22.8	4.9
2024-07-01	21.4	6.4
2024-08-01	21.4	14.2
2024-09-01	24.9	22
2024-10-01	31.3	24.3
2024-11-01	29.3	11.1
2024-12-01	25.8	7.5

Rows × columns: 96 × 3
Period covered: 2017-01-01 to 2024-12-01
Computed: 2026-08-16
Completeness: No missing values
Source: US exchange, SIP and OPRA market data
Licence: Strasmore terms · free, no signup
Formats: JSON · CSV · the SQL below

What each column holds

Column definitions for A rolling one year mean return, the number an optimizer would be fed, derived from the stored result.
Column	Type	Range	Notes
`month`	date	2017-01-01 to 2024-12-01
`spy_trailing_mean_pct`	number	-15.4 to 42.6	percent
`ko_trailing_mean_pct`	number	-10.5 to 24.3	percent

Computed from Strasmore's warehouse of US exchange, SIP and OPRA market data. Equity prices are delayed; options greeks and implied volatility are end-of-day. This result is stored, not recomputed on load — it is exactly the numbers that were returned on 2026-08-16, and the query below is what returned them.

the exact SQL behind every number

WITH prices AS
(
    SELECT
        ticker,
        date,
        toFloat64(max(close)) AS c
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('SPY', 'KO')
      AND date >= '2015-10-01'
      AND date <  '2025-01-01'
    GROUP BY ticker, date
),
rets AS
(
    SELECT
        ticker,
        date,
        c / lagInFrame(c, 1) OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) - 1 AS ret
    FROM prices
),
trailing AS
(
    SELECT
        ticker,
        date,
        avg(ret) OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN 251 PRECEDING AND CURRENT ROW) * 252 * 100 AS trailing_mean_pct,
        row_number() OVER (PARTITION BY ticker ORDER BY date ASC) AS i
    FROM rets
    WHERE isFinite(ret)
)
SELECT
    toString(toStartOfMonth(date))                     AS month,
    round(avgIf(trailing_mean_pct, ticker = 'SPY'), 1) AS spy_trailing_mean_pct,
    round(avgIf(trailing_mean_pct, ticker = 'KO'), 1)  AS ko_trailing_mean_pct
FROM trailing
WHERE i >= 252
  AND date >= '2017-01-01'
GROUP BY month
ORDER BY month ASC

Run your own version of this

The same 22 years of US equities and 12 years of options data are queryable in SQL or plain English. A free account runs 100 queries a day and takes no card.

More from this analysisWhen Equal Weight Beats Optimization

Ten years of yearly estimates: the mean moves far more than the volatility ranking 6×3 → How far the mean and volatility estimates scatter by sample length ranking 5×4 → SPY realised volatility by month against a 10% target series 72×4 → Daily at-the-money implied volatility, SPY and NVDA (Apr to Jun 2026) series 62×3 → KO quarterly dividend in cents and as a percent of the share price, 2016 to 2026 series 42×4 → Where the volume sits: share of June 2026 volume by half hour, New York time series 32×3 → See all 2,170 queries →