Outcomes of the repeated monthly AAPL write, by delta target

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 month_open AS
(
    SELECT min(date) AS write_date
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'AAPL'
      AND lower(toString(option_type)) IN ('call', 'c')
      AND toFloat64(option_close) > 0
      AND date >= '2021-09-01'
    GROUP BY toStartOfMonth(date)
),
picks AS
(
    SELECT
        delta_target,
        write_month,
        argMin(strike_usd, gap) AS strike_usd,
        argMin(expiry, gap) AS expiry,
        argMin(premium_usd, gap) AS premium_usd,
        argMin(spot_usd, gap) AS spot_usd
    FROM
    (
        SELECT
            tg.delta_target AS delta_target,
            toStartOfMonth(g.date) AS write_month,
            (abs(toInt32(g.days_to_expiry) - 30),
             abs(toFloat64(g.delta) - toFloat64(tg.delta_target))) AS gap,
            toFloat64(g.strike_price) AS strike_usd,
            g.expiration_date AS expiry,
            toFloat64(g.option_close) AS premium_usd,
            toFloat64(g.underlying_close) AS spot_usd
        FROM global_markets.options_greeks AS g
        CROSS JOIN
        (
            SELECT arrayJoin(['0.15', '0.20', '0.25', '0.30', '0.35', '0.40']) AS delta_target
        ) AS tg
        WHERE g.underlying_symbol = 'AAPL'
          AND lower(toString(g.option_type)) IN ('call', 'c')
          AND toFloat64(g.option_close) > 0
          AND toInt32(g.days_to_expiry) BETWEEN 21 AND 45
          AND toFloat64(g.delta) BETWEEN 0.05 AND 0.55
          AND g.expiration_date <= today() - 3
          AND g.date IN (SELECT write_date FROM month_open)
    )
    GROUP BY delta_target, write_month
)
SELECT
    p.delta_target AS delta_target,
    count() AS write_count,
    round(avg(100 * p.premium_usd / p.spot_usd), 2) AS avg_premium_pct,
    round(100 * countIf(toFloat64(s.close) > p.strike_usd) / count(), 1) AS finished_itm_pct,
    round(avg(100 * (least(toFloat64(s.close), p.strike_usd) + p.premium_usd - p.spot_usd) / p.spot_usd), 2) AS avg_write_return_pct,
    round(avg(100 * (toFloat64(s.close) - p.spot_usd) / p.spot_usd), 2) AS avg_hold_return_pct
FROM picks AS p
INNER JOIN
(
    SELECT date, close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'AAPL'
      AND date >= '2021-09-01'
) AS s ON s.date = p.expiry
GROUP BY p.delta_target
ORDER BY p.delta_target