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 dist AS
(
    SELECT
        ex_dividend_date AS ex_date,
        max(cash_amount) AS cash
    FROM global_markets.stocks_dividends
    WHERE ticker = 'SPY'
      AND ex_dividend_date >= '2018-01-01'
      AND ex_dividend_date <= today()
    GROUP BY ex_dividend_date
),
ttm AS
(
    SELECT
        ex_date,
        toFloat64(sum(cash) OVER (ORDER BY ex_date ASC ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)) AS ttm_cash,
        count() OVER (ORDER BY ex_date ASC ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)              AS quarters_in_window
    FROM dist
)
SELECT
    toString(t.ex_date)                             AS ex_date,
    formatDateTime(t.ex_date, '%b %Y')              AS quarter_label,
    round(100 * t.ttm_cash / toFloat64(a.close), 2) AS ttm_yield_pct
FROM ttm AS t
INNER JOIN
(
    SELECT date, max(close) AS close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND date >= '2018-01-01'
    GROUP BY date
) AS a ON a.date = t.ex_date
WHERE t.quarters_in_window = 4
ORDER BY t.ex_date