ttm_yield

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
    prices AS
    (
        SELECT
            toStartOfMonth(date)           AS m,
            argMax(toFloat64(close), date) AS month_close,
            max(date)                      AS month_last
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
          AND date >= today() - 1500
        GROUP BY m
    ),
    payouts AS
    (
        SELECT
            toDate(ex_dividend_date)    AS ex_date,
            max(toFloat64(cash_amount)) AS amount
        FROM global_markets.stocks_dividends
        WHERE ticker = 'SPY'
          AND ex_dividend_date >= today() - 1900
        GROUP BY ex_dividend_date
    )
SELECT
    toString(p.m)                                 AS month,
    formatDateTime(any(p.month_last), '%d.%m.%Y') AS as_of_label,
    round(100 * sumIf(d.amount, (d.ex_date > (p.month_last - 365)) AND (d.ex_date <= p.month_last)) / any(p.month_close), 2) AS gross_yield_pct,
    round( 70 * sumIf(d.amount, (d.ex_date > (p.month_last - 365)) AND (d.ex_date <= p.month_last)) / any(p.month_close), 2) AS net_yield_pct
FROM prices AS p
CROSS JOIN payouts AS d
WHERE p.m >= toStartOfMonth(today() - 1095)
GROUP BY p.m
ORDER BY p.m