Every mega-cap ex-dividend event of the past six months: price path from the pre-ex close

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 rth AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           argMaxIf(toFloat64(close), window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) AS close_px,
           argMinIf(toFloat64(open), window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) AS open_px,
           countIf((toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) AS bars
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('AAPL', 'MSFT', 'JNJ', 'KO', 'PG', 'XOM', 'CVX', 'JPM', 'HD', 'MCD',
                     'PEP', 'ABBV', 'MRK', 'PFE', 'VZ', 'T', 'CSCO', 'IBM', 'WMT', 'CAT',
                     'BAC', 'WFC', 'C', 'GS', 'MS', 'UNH', 'LLY', 'AMGN', 'BMY', 'GILD',
                     'TXN', 'QCOM', 'AVGO', 'ADP', 'LMT', 'RTX', 'HON', 'GE', 'MMM', 'UPS',
                     'LOW', 'TGT', 'COST', 'SBUX', 'NKE', 'DIS', 'CMCSA', 'DUK', 'SO', 'NEE')
      AND window_start >= toDateTime(today() - 190)
    GROUP BY ticker, d
    HAVING bars > 200
),
seq AS (
    SELECT ticker, d, open_px,
           lagInFrame(close_px, 1) OVER w AS pre_close,
           close_px AS ex_close,
           leadInFrame(close_px, 1) OVER w AS c1,
           leadInFrame(close_px, 5) OVER w AS c5,
           leadInFrame(close_px, 10) OVER w AS c10
    FROM rth
    WINDOW w AS (PARTITION BY ticker ORDER BY d ROWS BETWEEN 1 PRECEDING AND 10 FOLLOWING)
),
ev AS (
    SELECT s.ticker AS ticker, s.d AS ex_d, s.pre_close AS pre_close, s.open_px AS ex_open,
           s.ex_close AS ex_close, s.c1 AS c1, s.c5 AS c5, s.c10 AS c10,
           max(dv.cash_amount) AS div_amt
    FROM seq s
    JOIN global_markets.stocks_dividends dv ON dv.ticker = s.ticker AND dv.ex_dividend_date = s.d
    WHERE dv.cash_amount > 0
      AND dv.distribution_type = 'recurring'
      AND s.d >= today() - 160
      AND s.pre_close > 0 AND s.c1 > 0 AND s.c5 > 0 AND s.c10 > 0
    GROUP BY s.ticker, s.d, s.pre_close, s.open_px, s.ex_close, s.c1, s.c5, s.c10
),
paths AS (
    SELECT ticker, ex_d, pre_close, div_amt,
           arrayJoin([('1 ex-day open', ex_open),
                      ('2 ex-day close', ex_close),
                      ('3 one session later', c1),
                      ('4 five sessions later', c5),
                      ('5 ten sessions later', c10)]) AS chk
    FROM ev
)
SELECT chk.1 AS checkpoint,
       count() AS events,
       round(avg(100 * div_amt / pre_close), 2) AS avg_dividend_pct,
       round(avg(100 * (chk.2 - pre_close) / pre_close), 2) AS avg_move_pct,
       round(quantileDeterministic(0.5)(100 * (chk.2 - pre_close) / pre_close, cityHash64(ticker, ex_d)), 2) AS median_move_pct,
       round(100 * countIf(chk.2 >= pre_close) / count(), 1) AS pct_back_above_pre_ex
FROM paths
GROUP BY checkpoint
ORDER BY checkpoint