S&P 500 tracker by calendar year: price return vs the points added by reinvested dividends

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 daily AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           argMax(toFloat64(close), window_start) AS close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2006-01-01')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-07-31')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY d
),
yearly AS (
    SELECT toYear(d) AS year,
           argMin(close, d) AS first_px,
           argMax(close, d) AS last_px
    FROM daily
    GROUP BY year
),
divs AS (
    SELECT toYear(dv.ex_dividend_date) AS year,
           exp(sum(log(1 + toFloat64(dv.cash_amount) / dl.close))) AS factor
    FROM global_markets.stocks_dividends AS dv
    INNER JOIN daily AS dl ON dl.d = dv.ex_dividend_date
    WHERE dv.ticker = 'SPY'
      AND dv.cash_amount > 0
    GROUP BY year
)
SELECT y.year AS year,
       round(100 * (y.last_px / y.first_px - 1), 1) AS price_return_pct,
       round(100 * (y.last_px / y.first_px) * (d.factor - 1), 2) AS dividend_points_pct
FROM yearly AS y
INNER JOIN divs AS d ON y.year = d.year
ORDER BY year