charm_curve

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 daily AS
(
    SELECT
        ticker,
        expiration_date,
        strike_price,
        date,
        days_to_expiry,
        toFloat64(delta)                                          AS delta,
        multiIf(toFloat64(implied_volatility) > 1.5,
                toFloat64(implied_volatility),
                toFloat64(implied_volatility) * 100)              AS iv_pt,
        toFloat64(underlying_close)                               AS spot,
        toFloat64(strike_price) / toFloat64(underlying_close) - 1 AS moneyness
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND lower(toString(option_type)) LIKE 'c%'
      AND date >= '2025-09-01'
      AND date <  '2026-09-01'
      AND iv_converged = 1
      AND volume > 0
      AND days_to_expiry <= 90
),
steps AS
(
    SELECT
        ticker,
        date,
        moneyness,
        days_to_expiry,
        delta - lagInFrame(delta) OVER (PARTITION BY ticker, expiration_date, strike_price ORDER BY date)        AS delta_change,
        abs(iv_pt - lagInFrame(iv_pt) OVER (PARTITION BY ticker, expiration_date, strike_price ORDER BY date))   AS iv_move_pt,
        abs(spot / lagInFrame(spot) OVER (PARTITION BY ticker, expiration_date, strike_price ORDER BY date) - 1) AS spot_move,
        dateDiff('day', lagInFrame(date) OVER (PARTITION BY ticker, expiration_date, strike_price ORDER BY date), date) AS gap_days
    FROM daily
)
SELECT
    multiIf(days_to_expiry <=  2, '残り0-2日',
            days_to_expiry <=  5, '残り3-5日',
            days_to_expiry <= 10, '残り6-10日',
            days_to_expiry <= 20, '残り11-20日',
            days_to_expiry <= 45, '残り21-45日',
                                  '残り46-90日') AS dte_band,
    round(quantileDeterministicIf(0.5)(delta_change, cityHash64(ticker, date), moneyness < -0.005), 4) AS itm_delta_drift,
    round(quantileDeterministicIf(0.5)(delta_change, cityHash64(ticker, date), moneyness >  0.005), 4) AS otm_delta_drift,
    count()                                                                                           AS pair_count
FROM steps
WHERE gap_days = 1
  AND spot_move < 0.002
  AND iv_move_pt < 0.5
  AND abs(moneyness) BETWEEN 0.005 AND 0.03
GROUP BY dte_band
HAVING countIf(moneyness < -0.005) >= 10
   AND countIf(moneyness >  0.005) >= 10
ORDER BY min(days_to_expiry)