portfolio construction — 3 answered market questions

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

most recentas 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

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

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 → See all 2,173 queries →