Trailing 12-month dividend yield, gross and net of US withholding

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.

SELECT
    d.ticker                                        AS ticker,
    round(d.ttm_usd / p.last_close * 100, 2)        AS gross_yield_pct,
    round(d.ttm_usd * 0.90 / p.last_close * 100, 2) AS after_treaty_yield_pct,
    round(d.ttm_usd * 0.70 / p.last_close * 100, 2) AS no_w8ben_yield_pct
FROM
(
    SELECT
        ticker,
        sum(cash_amount) AS ttm_usd
    FROM
    (
        SELECT
            ticker,
            ex_dividend_date,
            max(toFloat64(cash_amount)) AS cash_amount
        FROM global_markets.stocks_dividends
        WHERE ticker IN ('AAPL', 'MSFT', 'KO', 'JNJ', 'PG', 'XOM', 'CVX', 'PEP')
          AND ticker NOT IN ('SPCX')
          AND ex_dividend_date > today() - 365
          AND ex_dividend_date <= today()
        GROUP BY ticker, ex_dividend_date
    )
    GROUP BY ticker
) AS d
INNER JOIN
(
    SELECT
        ticker,
        argMax(toFloat64(close), date) AS last_close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('AAPL', 'MSFT', 'KO', 'JNJ', 'PG', 'XOM', 'CVX', 'PEP')
      AND date >= today() - 15
    GROUP BY ticker
) AS p ON d.ticker = p.ticker
ORDER BY gross_yield_pct DESC