AAPL dividend versus the time value left in the nearest in-the-money call, the day before each ex date

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 ex_dates AS
(
    SELECT
        toDate(ex_dividend_date) AS ex_date,
        max(toFloat64(cash_amount)) AS dividend
    FROM global_markets.stocks_dividends
    WHERE ticker = 'AAPL'
      AND toDate(ex_dividend_date) >= toDate('2021-09-01')
      AND toDate(ex_dividend_date) < today()
    GROUP BY ex_date
),
option_sessions AS
(
    SELECT DISTINCT toDate(date) AS session_date
    FROM global_markets.options_greeks
    WHERE underlying_symbol = 'AAPL'
      AND date >= '2021-08-01'
),
cutoffs AS
(
    SELECT
        e.ex_date AS ex_date,
        e.dividend AS dividend,
        max(o.session_date) AS last_session_before
    FROM ex_dates AS e
    CROSS JOIN option_sessions AS o
    WHERE o.session_date < e.ex_date
    GROUP BY e.ex_date, e.dividend
),
picked AS
(
    SELECT
        ex_date,
        dividend,
        argMin(time_value, gap) AS time_value,
        argMin(strike_usd, gap) AS strike_usd,
        argMin(dte, gap) AS dte
    FROM
    (
        SELECT
            c.ex_date AS ex_date,
            c.dividend AS dividend,
            (toInt32(g.days_to_expiry),
             toFloat64(g.underlying_close) - toFloat64(g.strike_price)) AS gap,
            toFloat64(g.option_close)
              - (toFloat64(g.underlying_close) - toFloat64(g.strike_price)) AS time_value,
            toFloat64(g.strike_price) AS strike_usd,
            toInt32(g.days_to_expiry) AS dte
        FROM global_markets.options_greeks AS g
        INNER JOIN cutoffs AS c ON toDate(g.date) = c.last_session_before
        WHERE g.underlying_symbol = 'AAPL'
          AND lower(toString(g.option_type)) IN ('call', 'c')
          AND toFloat64(g.option_close) > 0
          AND toDate(g.expiration_date) > c.ex_date
          AND toInt32(g.days_to_expiry) <= 45
          AND toFloat64(g.strike_price) < toFloat64(g.underlying_close)
          AND toFloat64(g.strike_price) > toFloat64(g.underlying_close) * 0.97
    )
    GROUP BY ex_date, dividend
)
SELECT
    toString(ex_date) AS ex_date_iso,
    concat(monthName(ex_date), ' ', toString(toDayOfMonth(ex_date)), ', ', toString(toYear(ex_date))) AS ex_pretty,
    round(dividend, 2) AS dividend_usd,
    round(time_value, 2) AS time_value_usd,
    toString(round(strike_usd, 1)) AS strike,
    toString(dte) AS days_to_expiry,
    if(time_value < dividend, 'the dividend was larger', 'the time value was larger') AS exercise_edge
FROM picked
ORDER BY ex_date