Trailing twelve-month dividend income by holding

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 holdings AS
(
    SELECT 'AAPL' AS ticker, 120 AS shares
    UNION ALL SELECT 'MSFT', 45
    UNION ALL SELECT 'NVDA', 80
    UNION ALL SELECT 'KO',   300
    UNION ALL SELECT 'JNJ',  90
    UNION ALL SELECT 'XOM',  150
),
marks AS
(
    SELECT
        ticker,
        argMax(toFloat64(close), (date, _ingest_time)) AS last_close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'KO', 'JNJ', 'XOM')
      AND date >= today() - 30
    GROUP BY ticker
),
divs AS
(
    SELECT
        ticker,
        ex_dividend_date,
        max(toFloat64(cash_amount)) AS cash_amount
    FROM global_markets.stocks_dividends
    WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'KO', 'JNJ', 'XOM')
      AND currency = 'USD'
      AND ex_dividend_date >= today() - 365
      AND ex_dividend_date <  today()
    GROUP BY ticker, ex_dividend_date
),
ttm AS
(
    SELECT
        ticker,
        count()          AS payment_count,
        sum(cash_amount) AS dps
    FROM divs
    GROUP BY ticker
),
income AS
(
    SELECT
        h.ticker                             AS ticker,
        t.payment_count                      AS payment_count,
        round(t.dps, 4)                      AS ttm_dividend_per_share,
        round(h.shares * t.dps, 2)           AS ttm_income,
        round(100 * t.dps / m.last_close, 2) AS income_yield_pct
    FROM holdings AS h
    INNER JOIN ttm   AS t ON t.ticker = h.ticker
    INNER JOIN marks AS m ON m.ticker = h.ticker
)
SELECT
    ticker,
    payment_count,
    ttm_dividend_per_share,
    ttm_income,
    income_yield_pct,
    round(sum(ttm_income) OVER (), 2) AS blend_ttm_income
FROM income
ORDER BY ttm_income DESC