iv_vs_rv

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.

SELECT
    formatDateTime(i.m, '%b %Y')              AS month,
    round(i.implied_vol, 1)                   AS implied_vol_pct,
    round(r.realized_vol, 1)                  AS realized_vol_pct_next_month,
    round(i.implied_vol - r.realized_vol, 1)  AS vol_spread
FROM
(
    SELECT
        toStartOfMonth(date)                AS m,
        addMonths(toStartOfMonth(date), 1)  AS next_m,
        avg(implied_volatility) * 100       AS implied_vol
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'AAPL'
      AND delta < 0
      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
      AND date BETWEEN '2024-01-01' AND '2026-08-31'
    GROUP BY m, next_m
) AS i
INNER JOIN
(
    SELECT
        toStartOfMonth(d)                                      AS m,
        stddevSamp(log(close_px / prev_px)) * sqrt(252) * 100   AS realized_vol
    FROM
    (
        SELECT
            date                                                               AS d,
            close_px,
            lagInFrame(close_px, 1) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_px
        FROM
        (
            SELECT
                date,
                max(toFloat64(close)) AS close_px
            FROM global_markets.stocks_daily_aggs
            WHERE ticker = 'AAPL'
              AND date BETWEEN '2023-12-01' AND '2026-09-26'
            GROUP BY date
        ) AS daily
    ) AS lagged
    WHERE prev_px > 0
    GROUP BY m
    HAVING count() >= 15
) AS r ON r.m = i.next_m
ORDER BY i.m