SPY distributions per share and price, both rebased to 100 at 2015

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 px AS (
    SELECT toYear(toTimeZone(window_start, 'America/New_York')) AS year,
           argMax(close, window_start) AS year_end_price
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND toYear(toTimeZone(window_start, 'America/New_York')) BETWEEN 2015 AND 2025
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY year
),
dv AS (
    SELECT toYear(ex_dividend_date) AS year,
           sum(cash_amount) AS annual_distributions
    FROM global_markets.stocks_dividends
    WHERE ticker = 'SPY'
      AND cash_amount > 0
      AND ex_dividend_date >= toDate('2015-01-01')
      AND ex_dividend_date <= toDate('2025-12-31')
    GROUP BY year
)
SELECT px.year AS year,
       round(100 * dv.annual_distributions / (SELECT annual_distributions FROM dv WHERE year = 2015), 1) AS distributions_index_2015_100,
       round(100 * px.year_end_price / (SELECT year_end_price FROM px WHERE year = 2015), 1) AS price_index_2015_100
FROM px
INNER JOIN dv ON px.year = dv.year
ORDER BY year