SPY annualized Sharpe ratio, year by year, fixed 4.25% assumed rate

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

WITH
    daily AS
    (
        SELECT
            toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
            argMax(toFloat64(close), window_start)               AS close_px
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= toDateTime('2015-12-24 00:00:00')
          AND window_start <  toDateTime('2026-01-01 05:00:00')
          AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
             + toMinute(toTimeZone(window_start, 'America/New_York'))) >= 570
          AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
             + toMinute(toTimeZone(window_start, 'America/New_York'))) < 960
        GROUP BY session_date
    ),
    stepped AS
    (
        SELECT
            session_date,
            close_px,
            lagInFrame(close_px, 1) OVER (ORDER BY session_date
                                          ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_px
        FROM daily
    ),
    rets AS
    (
        SELECT
            session_date,
            close_px / prev_px - 1                AS raw_ret,
            close_px / prev_px - 1 - 0.0425 / 252 AS ex_ret
        FROM stepped
        WHERE prev_px > 0
          AND session_date >= toDate('2016-01-01')
    )
SELECT
    toString(toYear(session_date))                         AS year,
    round(avg(ex_ret) / stddevSamp(ex_ret) * sqrt(252), 2) AS sharpe_ratio,
    round(stddevSamp(raw_ret) * sqrt(252) * 100, 2)        AS ann_volatility_pct
FROM rets
GROUP BY year
ORDER BY year