One year of SPY, three sampling frequencies, one annualized Sharpe ratio

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('2024-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
    ),
    freqs AS
    (
        SELECT
            arrayJoin([('Daily', 252), ('Weekly', 52), ('Monthly', 12)]) AS pair,
            pair.1                                                       AS sampling,
            pair.2                                                       AS periods_per_year
    ),
    bucketed AS
    (
        SELECT
            f.sampling                         AS sampling,
            f.periods_per_year                 AS periods_per_year,
            multiIf(f.sampling = 'Daily',  d.session_date,
                    f.sampling = 'Weekly', toMonday(d.session_date),
                                           toStartOfMonth(d.session_date)) AS period_key,
            argMax(d.close_px, d.session_date) AS period_close
        FROM daily AS d
        CROSS JOIN freqs AS f
        GROUP BY sampling, periods_per_year, period_key
    ),
    stepped AS
    (
        SELECT
            sampling,
            periods_per_year,
            period_key,
            period_close,
            lagInFrame(period_close, 1) OVER (PARTITION BY sampling ORDER BY period_key
                                              ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
        FROM bucketed
    ),
    per_period AS
    (
        SELECT
            sampling,
            periods_per_year,
            period_close / prev_close - 1 - 0.0425 / periods_per_year AS ex_ret
        FROM stepped
        WHERE prev_close > 0
          AND period_key >= toDate('2025-01-01')
    )
SELECT
    sampling,
    round(avg(ex_ret) / stddevSamp(ex_ret), 4)                          AS per_period_sharpe,
    round(avg(ex_ret) / stddevSamp(ex_ret) * sqrt(periods_per_year), 2) AS annualized_sharpe
FROM per_period
GROUP BY sampling, periods_per_year
ORDER BY periods_per_year DESC