One bond fund, two distribution yields, month by month

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
    monthly_px AS
    (
        SELECT
            toStartOfMonth(date)           AS m,
            argMax(toFloat64(close), date) AS px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'TLT'
          AND date >= today() - 1600
        GROUP BY m
    ),
    monthly_cash AS
    (
        SELECT
            toStartOfMonth(exd) AS m,
            sum(cash)           AS cash
        FROM
        (
            SELECT
                any(ex_dividend_date)       AS exd,
                any(toFloat64(cash_amount)) AS cash
            FROM global_markets.stocks_dividends
            WHERE ticker = 'TLT'
              AND ex_dividend_date >= today() - 1600
              AND ex_dividend_date <= today()
            GROUP BY id
        )
        GROUP BY m
    ),
    joined AS
    (
        SELECT
            p.m    AS m,
            p.px   AS px,
            c.cash AS cash
        FROM monthly_px AS p
        INNER JOIN monthly_cash AS c ON c.m = p.m
    ),
    rolled AS
    (
        SELECT
            m,
            px,
            cash,
            sum(cash) OVER (ORDER BY m ASC ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS ttm_cash,
            count()   OVER (ORDER BY m ASC ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS months_counted
        FROM joined
    )
SELECT
    formatDateTime(m, '%Y-%m')        AS month,
    formatDateTime(m, '%b %Y')        AS month_label,
    round((100 * 12 * cash) / px, 2)  AS indicated_yield_pct,
    round((100 * ttm_cash) / px, 2)   AS ttm_yield_pct
FROM rolled
WHERE months_counted = 12
ORDER BY m ASC