ex_date_trace

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
    top_payer AS
    (
        SELECT ticker
        FROM global_markets.stocks_dividends
        WHERE ex_dividend_date >= today() - 365
          AND ex_dividend_date <= today()
          AND ticker NOT IN ('SPCX')
        GROUP BY ticker
        HAVING countDistinct(ex_dividend_date) >= 40
        ORDER BY countDistinct(ex_dividend_date) DESC, ticker ASC
        LIMIT 1
    ),
    payments AS
    (
        SELECT
            ticker,
            ex_dividend_date  AS ex_date,
            max(cash_amount)  AS amount
        FROM global_markets.stocks_dividends
        WHERE ex_dividend_date >= today() - 365
          AND ex_dividend_date <= today()
          AND ticker IN (SELECT ticker FROM top_payer)
        GROUP BY ticker, ex_date
    ),
    px AS
    (
        SELECT
            ticker,
            date,
            toFloat64(any(close)) AS close
        FROM global_markets.stocks_daily_aggs
        WHERE date >= today() - 400
          AND ticker IN (SELECT ticker FROM top_payer)
        GROUP BY ticker, date
    )
SELECT
    toString(d.ex_date)                                 AS ex_date,
    d.ticker                                            AS fund,
    round(toFloat64(d.amount), 4)                       AS distribution,
    round(100 * 52 * toFloat64(d.amount) / p.close, 1)  AS annualized_pct
FROM payments AS d
INNER JOIN px AS p ON p.ticker = d.ticker AND p.date = d.ex_date
ORDER BY ex_date