Percent change in vega per vol point, by time to expiry (SPY)

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
    (
        SELECT max(date)
        FROM global_markets.options_greeks
        WHERE underlying_symbol = 'SPY'
    ) AS snap_date
SELECT
    dte,
    round(wing_growth, 2) AS wing_growth_pct,
    round(atm_growth, 2)  AS atm_growth_pct
FROM
(
    SELECT
        multiIf(dte_days <= 21,  '7 to 21 days',
                dte_days <= 45,  '22 to 45 days',
                dte_days <= 90,  '46 to 90 days',
                dte_days <= 180, '91 to 180 days',
                                 'over 180 days')  AS dte,
        multiIf(dte_days <= 21, 1, dte_days <= 45, 2, dte_days <= 90, 3,
                dte_days <= 180, 4, 5)             AS dte_sort,
        avgIf(d1 * d2 / sigma, abs(k) > 0.05 AND abs(k) <= 0.12) AS wing_growth,
        avgIf(d1 * d2 / sigma, abs(k) <= 0.02)                   AS atm_growth
    FROM
    (
        SELECT
            k,
            sigma,
            dte_days,
            d1,
            d1 - sigma * sqrt(t_years) AS d2
        FROM
        (
            SELECT
                k,
                sigma,
                dte_days,
                t_years,
                (log(spot / strike) + (rate + sigma * sigma / 2) * t_years)
                    / (sigma * sqrt(t_years)) AS d1
            FROM
            (
                SELECT
                    toFloat64(underlying_close)                               AS spot,
                    toFloat64(strike_price)                                   AS strike,
                    toFloat64(strike_price) / toFloat64(underlying_close) - 1 AS k,
                    toFloat64(implied_volatility)                             AS sigma,
                    toInt32(days_to_expiry)                                   AS dte_days,
                    days_to_expiry / 365.0                                    AS t_years,
                    if(toFloat64(risk_free_rate) > 1,
                       toFloat64(risk_free_rate) / 100,
                       toFloat64(risk_free_rate))                             AS rate
                FROM global_markets.options_greeks
                WHERE underlying_symbol = 'SPY'
                  AND date = snap_date
                  AND iv_converged = 1
                  AND volume > 0
                  AND vega > 0
                  AND days_to_expiry >= 7
                  AND implied_volatility BETWEEN 0.02 AND 3.0
            )
        )
    )
    GROUP BY dte, dte_sort
    HAVING countIf(abs(k) > 0.05 AND abs(k) <= 0.12) > 2
       AND countIf(abs(k) <= 0.02) > 2
)
ORDER BY dte_sort