SPY implied volatility vs the volatility realized in the following month

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(close, window_start) AS session_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2024-08-01')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-07-31')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY session_date
),
rets AS (
    SELECT session_date,
           toFloat64(session_close) AS close_px,
           any(toFloat64(session_close)) OVER (ORDER BY session_date ASC
                                               ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prior_close
    FROM daily
),
realized AS (
    SELECT toStartOfMonth(session_date) AS month_start,
           round(stddevSamp(close_px / prior_close - 1) * sqrt(252) * 100, 1) AS realized_vol_pct,
           count() AS sessions
    FROM rets
    WHERE prior_close > 0
    GROUP BY month_start
),
implied AS (
    SELECT toStartOfMonth(date) AS month_start,
           addMonths(toStartOfMonth(date), 1) AS next_month_start,
           round(quantileDeterministic(0.5)(toFloat64(implied_volatility) * 100, cityHash64(ticker)), 1) AS implied_vol_pct
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND date >= toDate('2024-08-01')
      AND date <= toDate('2026-06-30')
      AND iv_converged = 1
      AND volume > 0
      AND days_to_expiry BETWEEN 20 AND 45
      AND abs(toFloat64(strike_price) / toFloat64(underlying_close) - 1) < 0.05
    GROUP BY month_start, next_month_start
)
SELECT formatDateTime(i.month_start, '%Y-%m') AS month,
       i.implied_vol_pct AS implied_vol_pct,
       r.realized_vol_pct AS realized_vol_pct,
       round(i.implied_vol_pct - r.realized_vol_pct, 1) AS premium_pct,
       round(avg(i.implied_vol_pct - r.realized_vol_pct) OVER (), 1) AS avg_premium_pct
FROM implied AS i
INNER JOIN realized AS r ON i.next_month_start = r.month_start
WHERE r.sessions >= 15
ORDER BY month