The exact quarters behind each buyback 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
quarters AS
(
    SELECT
        arrayJoin(tickers) AS ticker,
        period_end,
        argMax(toFloat64(diluted_shares_outstanding), filing_date) AS shares
    FROM global_markets.stocks_income_statements
    WHERE timeframe = 'quarterly'
      AND diluted_shares_outstanding > 0
      AND period_end > today() - 800
      AND hasAny(tickers, ['AAPL', 'MSFT', 'KO', 'PEP', 'CVX', 'CSCO', 'HD', 'JNJ'])
    GROUP BY ticker, period_end
    HAVING ticker IN ('AAPL', 'MSFT', 'KO', 'PEP', 'CVX', 'CSCO', 'HD', 'JNJ')
),
latest AS
(
    SELECT
        ticker,
        max(period_end)               AS recent_period,
        argMax(shares, period_end)    AS shares_recent
    FROM quarters
    GROUP BY ticker
    HAVING recent_period > today() - 400
),
share_pairs AS
(
    SELECT
        r.ticker                                                                       AS ticker,
        any(r.recent_period)                                                           AS recent_period,
        any(r.shares_recent)                                                           AS shares_recent,
        argMin(q.period_end, abs(dateDiff('day', q.period_end, r.recent_period - 365))) AS prior_period,
        argMin(q.shares, abs(dateDiff('day', q.period_end, r.recent_period - 365)))     AS shares_prior
    FROM latest AS r
    INNER JOIN quarters AS q ON q.ticker = r.ticker
    WHERE q.period_end BETWEEN r.recent_period - 400 AND r.recent_period - 330
    GROUP BY r.ticker
)
SELECT
    ticker,
    toString(prior_period)        AS prior_quarter_end,
    toString(recent_period)       AS recent_quarter_end,
    round(shares_prior / 1e6, 0)  AS shares_prior_millions,
    round(shares_recent / 1e6, 0) AS shares_recent_millions
FROM share_pairs
ORDER BY ticker