cat_trailing_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 monthly_price AS
(
    SELECT
        toStartOfMonth(date)            AS m,
        round(avg(toFloat64(close)), 2) AS avg_close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'CAT'
      AND date >= toStartOfMonth(subtractMonths(today(), 12))
    GROUP BY m
),
payments AS
(
    SELECT
        ex_dividend_date            AS d,
        max(toFloat64(cash_amount)) AS amount
    FROM global_markets.stocks_dividends
    WHERE ticker = 'CAT'
      AND ex_dividend_date > subtractMonths(toStartOfMonth(today()), 25)
    GROUP BY ex_dividend_date
)
SELECT
    toString(p.m)                               AS month,
    formatDateTime(p.m, '%b %Y')                AS month_label,
    round(sum(v.amount), 2)                     AS ttm_dividend_usd,
    round(100 * sum(v.amount) / p.avg_close, 2) AS ttm_dividend_yield_pct
FROM monthly_price AS p
CROSS JOIN payments AS v
WHERE v.d >  subtractYears(p.m, 1)
  AND v.d <= p.m
GROUP BY p.m, p.avg_close
ORDER BY p.m