Vega and vomma across strikes, one SPY expiry

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 argMin(expiration_date, abs(toInt32(days_to_expiry) - 35))
        FROM global_markets.options_greeks
        WHERE underlying_symbol = 'SPY'
          AND date = snap_date
          AND iv_converged = 1
          AND volume > 0
          AND days_to_expiry BETWEEN 20 AND 60
    ) AS expiry
SELECT
    strike_band                                             AS moneyness,
    round(100 * avg_vega / max(avg_vega) OVER (), 1)        AS vega_pct_of_peak,
    round(100 * avg_vomma / max(abs(avg_vomma)) OVER (), 1) AS vomma_pct_of_peak,
    round(avg_growth, 2)                                    AS vega_growth_pct_per_vol_pt
FROM
(
    SELECT
        multiIf(k < -0.10, '10%+ below spot',
                k < -0.05, '5-10% below',
                k < -0.02, '2-5% below',
                k <= 0.02, 'within 2% of spot',
                k <= 0.05, '2-5% above',
                k <= 0.10, '5-10% above',
                           '10%+ above spot')     AS strike_band,
        multiIf(k < -0.10, 1, k < -0.05, 2, k < -0.02, 3,
                k <= 0.02, 4, k <= 0.05, 5, k <= 0.10, 6, 7) AS band_sort,
        avg(vega)                                  AS avg_vega,
        avg(vega * d1 * d2 / sigma)                AS avg_vomma,
        avg(d1 * d2 / sigma)                       AS avg_growth
    FROM
    (
        SELECT
            k,
            sigma,
            vega,
            d1,
            d1 - sigma * sqrt(t_years)             AS d2
        FROM
        (
            SELECT
                k,
                sigma,
                vega,
                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,
                    toFloat64(vega)                                           AS vega,
                    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 expiration_date = expiry
                  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 strike_band, band_sort
)
ORDER BY band_sort