distribution

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
expiries AS
(
    SELECT
        date                                              AS entry_date,
        argMin(expiration_date, abs(days_to_expiry - 45)) AS expiry
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'SPY'
      AND lower(option_type) IN ('put', 'p')
      AND iv_converged = 1
      AND volume > 0
      AND days_to_expiry BETWEEN 38 AND 52
      AND date >= '2021-08-01'
    GROUP BY date
),
short_leg AS
(
    SELECT
        e.entry_date                                                    AS entry_date,
        e.expiry                                                        AS expiry,
        argMin(toFloat64(g.strike_price), abs(abs(g.delta) - 0.30))     AS short_strike,
        argMin(toFloat64(g.option_close), abs(abs(g.delta) - 0.30))     AS short_mark,
        argMin(toFloat64(g.underlying_close), abs(abs(g.delta) - 0.30)) AS spot
    FROM global_markets.options_greeks AS g
    INNER JOIN expiries AS e
        ON g.date = e.entry_date AND g.expiration_date = e.expiry
    WHERE g.underlying_symbol = 'SPY'
      AND lower(g.option_type) IN ('put', 'p')
      AND g.iv_converged = 1
      AND g.volume > 0
      AND g.date >= '2021-08-01'
      AND abs(g.delta) BETWEEN 0.20 AND 0.40
    GROUP BY e.entry_date, e.expiry
),
long_leg AS
(
    SELECT
        s.entry_date                                                                             AS entry_date,
        argMin(toFloat64(g.strike_price), abs(toFloat64(g.strike_price) - (s.short_strike - 10))) AS long_strike,
        argMin(toFloat64(g.option_close), abs(toFloat64(g.strike_price) - (s.short_strike - 10))) AS long_mark
    FROM global_markets.options_greeks AS g
    INNER JOIN short_leg AS s
        ON g.date = s.entry_date AND g.expiration_date = s.expiry
    WHERE g.underlying_symbol = 'SPY'
      AND lower(g.option_type) IN ('put', 'p')
      AND g.iv_converged = 1
      AND g.date >= '2021-08-01'
      AND toFloat64(g.strike_price) BETWEEN s.short_strike - 12 AND s.short_strike - 8
    GROUP BY s.entry_date
),
spreads AS
(
    SELECT
        s.entry_date                   AS entry_date,
        s.spot                         AS spot,
        s.short_strike                 AS short_strike,
        s.short_strike - l.long_strike AS width,
        s.short_mark - l.long_mark     AS credit
    FROM short_leg AS s
    INNER JOIN long_leg AS l ON s.entry_date = l.entry_date
    WHERE l.long_strike < s.short_strike
)
SELECT
    count()                                                                                           AS entry_count,
    round(quantileDeterministic(0.1)(100 * credit / width, toUInt32(toUnixTimestamp(entry_date))), 1) AS credit_pct_p10,
    round(quantileDeterministic(0.5)(100 * credit / width, toUInt32(toUnixTimestamp(entry_date))), 1) AS credit_pct_median,
    round(quantileDeterministic(0.9)(100 * credit / width, toUInt32(toUnixTimestamp(entry_date))), 1) AS credit_pct_p90,
    round(quantileDeterministic(0.1)(100 * (spot - (short_strike - credit)) / spot, toUInt32(toUnixTimestamp(entry_date))), 2) AS breakeven_pct_p10,
    round(quantileDeterministic(0.5)(100 * (spot - (short_strike - credit)) / spot, toUInt32(toUnixTimestamp(entry_date))), 2) AS breakeven_pct_median,
    round(quantileDeterministic(0.9)(100 * (spot - (short_strike - credit)) / spot, toUInt32(toUnixTimestamp(entry_date))), 2) AS breakeven_pct_p90
FROM spreads
WHERE credit > 0
  AND width BETWEEN 9 AND 11
HAVING count() > 0