The Volatility Risk Premium, Measured

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.date              AS date,
        i.iv_pct            AS iv_pct,
        f.rv_pct            AS rv_pct,
        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
)
SELECT
    toString(toStartOfMonth(date))                AS month,
    formatDateTime(toStartOfMonth(date), '%b %Y') AS month_label,
    round(avg(iv_pct), 2)                         AS implied_vol_pct,
    round(avg(rv_pct), 2)                         AS realized_vol_pct,
    round(avg(premium_pct), 2)                    AS premium_pct
FROM paired
GROUP BY month, month_label
ORDER BY month

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)

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 7 FOLLOWING))  AS rv_07_pct,
        100 * sqrt(252 * avg(r2) OVER (ORDER BY date ROWS BETWEEN 1 FOLLOWING AND 15 FOLLOWING)) AS rv_15_pct,
        100 * sqrt(252 * avg(r2) OVER (ORDER BY date ROWS BETWEEN 1 FOLLOWING AND 31 FOLLOWING)) AS rv_31_pct,
        100 * sqrt(252 * avg(r2) OVER (ORDER BY date ROWS BETWEEN 1 FOLLOWING AND 62 FOLLOWING)) AS rv_62_pct,
        count(r2) OVER (ORDER BY date ROWS BETWEEN 1 FOLLOWING AND 62 FOLLOWING)                 AS sessions_ahead
    FROM daily_returns
    WHERE isFinite(r2)
),
by_day AS
(
    SELECT
        g.date AS date,
        multiIf(g.days_to_expiry <= 14, '07-14 days',
                g.days_to_expiry <= 30, '15-30 days',
                g.days_to_expiry <= 60, '31-60 days',
                                        '61-120 days') AS tenor,
        avg(100 * toFloat64(g.implied_volatility))      AS iv_pct,
        any(multiIf(g.days_to_expiry <= 14, f.rv_07_pct,
                    g.days_to_expiry <= 30, f.rv_15_pct,
                    g.days_to_expiry <= 60, f.rv_31_pct,
                                            f.rv_62_pct)) AS rv_pct
    FROM global_markets.options_greeks AS g
    INNER JOIN forward_rv AS f ON f.date = g.date
    WHERE g.underlying_symbol = 'SPY'
      AND g.iv_converged = 1
      AND g.volume > 0
      AND g.days_to_expiry BETWEEN 7 AND 120
      AND abs(toFloat64(g.strike_price) / toFloat64(g.underlying_close) - 1) < 0.05
      AND g.date >= '2021-08-01'
      AND f.sessions_ahead = 62
    GROUP BY date, tenor
    HAVING count() >= 5
)
SELECT
    tenor,
    round(avg(iv_pct), 2)                              AS implied_vol_pct,
    round(avg(rv_pct), 2)                              AS realized_vol_pct,
    round(avg(iv_pct - rv_pct), 2)                     AS premium_pct,
    round(100 * countIf(iv_pct > rv_pct) / count(), 1) AS positive_share_pct
FROM by_day
GROUP BY tenor
ORDER BY tenor

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)
),
contracts AS
(
    SELECT
        date,
        delta,
        toFloat64(implied_volatility)                         AS iv,
        toFloat64(strike_price) / toFloat64(underlying_close) AS moneyness_ratio
    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 date >= '2021-08-01'
),
by_day AS
(
    SELECT
        c.date AS date,
        multiIf(c.moneyness_ratio < 0.90,  '0.85-0.90',
                c.moneyness_ratio < 0.95,  '0.90-0.95',
                c.moneyness_ratio < 0.975, '0.95-0.975',
                c.moneyness_ratio < 1.00,  '0.975-1.00',
                c.moneyness_ratio < 1.025, '1.00-1.025',
                c.moneyness_ratio < 1.05,  '1.025-1.05',
                                           '1.05-1.10') AS moneyness,
        avgIf(100 * c.iv, c.delta < 0)                   AS put_iv_pct,
        avgIf(100 * c.iv, c.delta > 0)                   AS call_iv_pct,
        any(f.rv_pct)                                    AS rv_pct
    FROM contracts AS c
    INNER JOIN forward_rv AS f ON f.date = c.date
    WHERE c.moneyness_ratio BETWEEN 0.85 AND 1.10
      AND f.sessions_ahead = 21
    GROUP BY date, moneyness
    HAVING countIf(c.delta < 0) > 0
       AND countIf(c.delta > 0) > 0
)
SELECT
    moneyness,
    round(avg(put_iv_pct - rv_pct), 2)  AS put_premium_pct,
    round(avg(call_iv_pct - rv_pct), 2) AS call_premium_pct
FROM by_day
GROUP BY moneyness
ORDER BY moneyness

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.date              AS date,
        i.iv_pct            AS iv_pct,
        f.rv_pct            AS rv_pct,
        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
)
SELECT
    formatDateTime(argMin(date, premium_pct), '%b %e, %Y') AS label,
    round(argMin(iv_pct, premium_pct), 2)                  AS implied_vol_pct,
    round(argMin(rv_pct, premium_pct), 2)                  AS realized_vol_pct,
    round(-1 * min(premium_pct), 2)                        AS shortfall_pct
FROM paired
GROUP BY toStartOfMonth(date)
ORDER BY min(premium_pct) ASC
LIMIT 10