yield_trace

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
    toString(w.wk)                                               AS week,
    formatDateTime(w.last_date, '%Y/%m/%d')                      AS as_of,
    count()                                                      AS payments_ttm,
    round(sum(d.amt), 2)                                         AS ttm_dividend_usd,
    round(argMax(d.amt, d.ex_date) * 4, 2)                       AS forward_dividend_usd,
    round(sum(d.amt) / w.close_usd * 100, 2)                     AS trailing_yield_pct,
    round(argMax(d.amt, d.ex_date) * 4 / w.close_usd * 100, 2)   AS forward_yield_pct
FROM
(
    SELECT
        toStartOfWeek(date, 1)            AS wk,
        max(date)                         AS last_date,
        toFloat64(argMax(close, date))    AS close_usd
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'GIS'
      AND date >= today() - 365
    GROUP BY wk
) AS w
CROSS JOIN
(
    SELECT
        ex_dividend_date                  AS ex_date,
        max(toFloat64(cash_amount))       AS amt
    FROM global_markets.stocks_dividends
    WHERE ticker = 'GIS'
      AND ex_dividend_date >= today() - 800
    GROUP BY ex_dividend_date
) AS d
WHERE d.ex_date <= w.last_date
  AND d.ex_date >  w.last_date - 365
GROUP BY w.wk, w.last_date, w.close_usd
ORDER BY w.wk