Distribution of the 21 session volatility risk premium, in volatility points

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
closes AS
(
    SELECT
        date,
        toFloat64(any(close)) AS px
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND date >= '2021-06-01'
    GROUP BY date
),
daily_returns AS
(
    SELECT
        date,
        pow(log(px / lagInFrame(px) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)), 2) AS r2
    FROM closes
),
forward_rv AS
(
    SELECT
        date,
        100 * sqrt(252 * avg(r2) OVER (ORDER BY date ROWS BETWEEN 1 FOLLOWING AND 21 FOLLOWING)) AS rv_pct,
        count(r2) OVER (ORDER BY date ROWS BETWEEN 1 FOLLOWING AND 21 FOLLOWING)                 AS sessions_ahead
    FROM daily_returns
    WHERE isFinite(r2)
),
implied AS
(
    SELECT
        date,
        100 * avg(toFloat64(implied_volatility)) AS iv_pct
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      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 >= '2021-08-01'
    GROUP BY date
    HAVING count() >= 20
),
paired AS
(
    SELECT i.iv_pct - f.rv_pct AS premium_pct
    FROM implied AS i
    INNER JOIN forward_rv AS f ON f.date = i.date
    WHERE f.sessions_ahead = 21
),
totals AS
(
    SELECT count() AS all_windows FROM paired
)
SELECT
    multiIf(
        p.premium_pct < -10, 'under -10',
        p.premium_pct <  -5, '-10 to -5',
        p.premium_pct <   0, '-5 to 0',
        p.premium_pct <   5, '0 to +5',
        p.premium_pct <  10, '+5 to +10',
        p.premium_pct <  15, '+10 to +15',
                             'over +15')        AS bucket,
    count()                                      AS windows,
    round(100 * count() / any(t.all_windows), 1) AS share_pct
FROM paired AS p
CROSS JOIN totals AS t
GROUP BY bucket
ORDER BY min(p.premium_pct)