shareholder_yield_compare

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
    cash_rows AS
    (
        SELECT
            ticker,
            period_end,
            argMax(dividend_cash, (filing_date, period_end)) AS dividend_cash
        FROM
        (
            SELECT
                arrayJoin(tickers)        AS ticker,
                period_end,
                filing_date,
                abs(toFloat64(dividends)) AS dividend_cash
            FROM global_markets.stocks_cash_flow_statements
            WHERE hasAny(tickers, ['AAPL', 'MSFT', 'HD', 'CSCO', 'KO', 'JNJ'])
              AND timeframe = 'quarterly'
              AND period_end >= '2022-01-01'
        )
        WHERE ticker IN ('AAPL', 'MSFT', 'HD', 'CSCO', 'KO', 'JNJ')
        GROUP BY ticker, period_end
    ),
    ranked AS
    (
        SELECT
            ticker,
            period_end,
            dividend_cash,
            row_number() OVER (PARTITION BY ticker ORDER BY period_end DESC) AS rn
        FROM cash_rows
    ),
    trailing_year AS
    (
        SELECT
            ticker,
            sum(dividend_cash) AS dividend_cash,
            min(period_end)    AS win_start,
            max(period_end)    AS win_end
        FROM ranked
        WHERE rn <= 4
        GROUP BY ticker
        HAVING count() = 4
    ),
    share_counts AS
    (
        SELECT
            i.ticker                               AS ticker,
            argMin(i.diluted_shares, i.period_end) AS shares_before,
            argMax(i.diluted_shares, i.period_end) AS shares_latest
        FROM
        (
            SELECT
                arrayJoin(tickers)                    AS ticker,
                period_end,
                toFloat64(diluted_shares_outstanding) AS diluted_shares
            FROM global_markets.stocks_income_statements
            WHERE hasAny(tickers, ['AAPL', 'MSFT', 'HD', 'CSCO', 'KO', 'JNJ'])
              AND timeframe = 'quarterly'
              AND diluted_shares_outstanding > 0
              AND period_end >= '2022-01-01'
        ) AS i
        INNER JOIN trailing_year AS y ON i.ticker = y.ticker
        WHERE i.ticker IN ('AAPL', 'MSFT', 'HD', 'CSCO', 'KO', 'JNJ')
          AND toDate(i.period_end) >  subtractDays(toDate(y.win_start), 100)
          AND toDate(i.period_end) <= toDate(y.win_end)
        GROUP BY i.ticker
    ),
    avg_prices AS
    (
        SELECT
            d.ticker                AS ticker,
            avg(toFloat64(d.close)) AS avg_close
        FROM global_markets.stocks_daily_aggs AS d
        INNER JOIN trailing_year AS y ON d.ticker = y.ticker
        WHERE d.ticker IN ('AAPL', 'MSFT', 'HD', 'CSCO', 'KO', 'JNJ')
          AND d.date >  subtractDays(toDate(y.win_start), 100)
          AND d.date <= toDate(y.win_end)
        GROUP BY d.ticker
    ),
    market_caps AS
    (
        SELECT
            ticker,
            argMax(toFloat64(market_cap), date) AS mcap
        FROM global_markets.stocks_ratios
        WHERE ticker IN ('AAPL', 'MSFT', 'HD', 'CSCO', 'KO', 'JNJ')
          AND market_cap > 0
        GROUP BY ticker
    )
SELECT
    y.ticker                                 AS ticker,
    round(y.dividend_cash / m.mcap * 100, 2) AS dividend_yield_pct,
    round((y.dividend_cash + greatest(s.shares_before - s.shares_latest, 0) * p.avg_close) / m.mcap * 100, 2) AS shareholder_yield_pct
FROM trailing_year AS y
INNER JOIN share_counts AS s USING (ticker)
INNER JOIN avg_prices   AS p USING (ticker)
INNER JOIN market_caps  AS m USING (ticker)
ORDER BY shareholder_yield_pct DESC