Dividend income from the five-name portfolio, 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
    positions AS
    (
        SELECT
            tupleElement(p, 1)            AS ticker,
            toFloat64(tupleElement(p, 2)) AS shares
        FROM
        (
            SELECT arrayJoin([('MSFT', 30.), ('PG', 60.), ('XOM', 80.), ('KO', 100.), ('VZ', 60.)]) AS p
        )
    ),
    payments AS
    (
        SELECT
            ticker,
            id,
            any(ex_dividend_date)       AS ex_date,
            toFloat64(any(cash_amount)) AS amount
        FROM global_markets.stocks_dividends
        WHERE ticker IN ('MSFT', 'PG', 'XOM', 'KO', 'VZ')
          AND ex_dividend_date >  today() - 365
          AND ex_dividend_date <= today()
        GROUP BY ticker, id
    )
SELECT
    formatDateTime(toStartOfMonth(pay.ex_date), '%Y-%m') AS month,
    formatDateTime(toStartOfMonth(pay.ex_date), '%b %Y') AS month_label,
    round(sum(pos.shares * pay.amount), 2)               AS income_usd
FROM payments AS pay
INNER JOIN positions AS pos ON pos.ticker = pay.ticker
GROUP BY month, month_label
ORDER BY month