spy_monthly_trace

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
    px AS
    (
        SELECT
            date                  AS session,
            max(toFloat64(close)) AS px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
          AND date >= '2024-07-01'
          AND date <  '2026-08-01'
        GROUP BY session
    ),
    iv_rows AS
    (
        SELECT
            g.session AS session,
            g.iv      AS iv
        FROM
        (
            SELECT
                toDate(date)            AS session,
                toFloat64(strike_price) AS strike,
                implied_volatility      AS iv
            FROM global_markets.options_greeks
            WHERE underlying_symbol = 'SPY'
              AND date >= '2024-07-01'
              AND date <  '2026-07-01'
              AND lower(toString(option_type)) IN ('c', 'call')
              AND days_to_expiry BETWEEN 20 AND 45
              AND implied_volatility > 0
        ) AS g
        INNER JOIN px AS p ON p.session = g.session
        WHERE abs(g.strike / p.px - 1) < 0.05
    ),
    daily_returns AS
    (
        SELECT
            arrayJoin(arrayMap((a, b) -> (tupleElement(a, 1), log(tupleElement(a, 2) / tupleElement(b, 2))),
                               arraySlice(series, 2),
                               arraySlice(series, 1, length(series) - 1))) AS ret
        FROM
        (
            SELECT arraySort(groupArray((session, px))) AS series
            FROM px
        )
    ),
    realized AS
    (
        SELECT
            toStartOfMonth(tupleElement(ret, 1))                         AS rv_month,
            round(100 * sqrt(252) * stddevSamp(tupleElement(ret, 2)), 1) AS realized_vol_pct
        FROM daily_returns
        GROUP BY rv_month
        HAVING count() >= 15
    ),
    implied AS
    (
        SELECT
            iv_month,
            addMonths(iv_month, 1) AS forward_month,
            implied_vol_pct
        FROM
        (
            SELECT
                toStartOfMonth(session) AS iv_month,
                round(100 * avg(iv), 1) AS implied_vol_pct
            FROM iv_rows
            GROUP BY iv_month
            HAVING countDistinct(session) >= 15
        )
    )
SELECT
    formatDateTime(implied.iv_month, '%Y-%m')                     AS month,
    implied.implied_vol_pct                                       AS implied_vol_pct,
    realized.realized_vol_pct                                     AS realized_vol_pct,
    round(implied.implied_vol_pct - realized.realized_vol_pct, 1) AS gap_pct
FROM implied
INNER JOIN realized ON realized.rv_month = implied.forward_month
ORDER BY month