Implied daily move against the realized daily move: SPY, month by 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 iv AS (
    SELECT toStartOfMonth(date) AS month,
           avg(implied_volatility) * 100 AS iv_pct
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND date >= toDate('2025-07-01')
      AND date < toDate('2026-08-01')
      AND abs(delta) BETWEEN 0.45 AND 0.55
      AND days_to_expiry BETWEEN 23 AND 37
      AND iv_converged
      AND implied_volatility BETWEEN 0.02 AND 3
    GROUP BY month
),
px AS (
    SELECT date, toFloat64(close) AS c
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND date >= toDate('2025-06-01')
      AND date < toDate('2026-08-01')
),
moves AS (
    SELECT toStartOfMonth(date) AS month,
           abs(c / any(c) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) - 1) * 100 AS abs_move_pct
    FROM px
),
realized AS (
    SELECT month,
           avg(abs_move_pct) AS avg_abs_move_pct,
           count() AS sessions
    FROM moves
    WHERE month >= toDate('2025-07-01')
    GROUP BY month
)
SELECT iv.month AS month,
       formatDateTimeInJodaSyntax(iv.month, 'MMMM yyyy') AS month_label,
       round(iv.iv_pct / 16, 2) AS implied_daily_move_pct,
       round(realized.avg_abs_move_pct, 2) AS realized_daily_move_pct,
       round(iv.iv_pct / 16 - realized.avg_abs_move_pct, 2) AS gap_pct,
       realized.sessions AS sessions
FROM iv
INNER JOIN realized ON iv.month = realized.month
ORDER BY month