index_versions

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
    per_year AS
    (
        SELECT
            toYear(date)                   AS yr,
            argMin(toFloat64(close), date) AS first_close,
            argMax(toFloat64(close), date) AS last_close
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'EWG'
          AND date >= '2011-01-01'
          AND date <  '2026-01-01'
        GROUP BY yr
    ),
    per_year_cash AS
    (
        SELECT
            toYear(ex_date) AS yr,
            sum(cash)       AS cash_per_share
        FROM
        (
            SELECT
                ex_dividend_date            AS ex_date,
                max(toFloat64(cash_amount)) AS cash
            FROM global_markets.stocks_dividends
            WHERE ticker = 'EWG'
              AND ex_dividend_date >= '2011-01-01'
              AND ex_dividend_date <  '2026-01-01'
            GROUP BY ex_dividend_date
        )
        GROUP BY yr
    ),
    yearly AS
    (
        SELECT
            p.yr                                                            AS yr,
            p.last_close / p.first_close                                    AS price_factor,
            (p.last_close + ifNull(c.cash_per_share, 0.0)) / p.first_close  AS reinvested_factor
        FROM per_year AS p
        LEFT JOIN per_year_cash AS c ON c.yr = p.yr
    ),
    paths AS
    (
        SELECT
            arraySort(r -> tupleElement(r, 1), groupArray((yr, price_factor, reinvested_factor))) AS ordered,
            arrayMap(r -> tupleElement(r, 1), ordered)                                            AS years,
            arrayMap(v -> round(100 * exp(v), 1),
                     arrayCumSum(arrayMap(r -> log(tupleElement(r, 2)), ordered)))                AS price_path,
            arrayMap(v -> round(100 * exp(v), 1),
                     arrayCumSum(arrayMap(r -> log(tupleElement(r, 3)), ordered)))                AS reinvested_path
        FROM yearly
    )
SELECT
    toString(tupleElement(pt, 1))                        AS year,
    tupleElement(pt, 2)                                  AS price_only_index,
    tupleElement(pt, 3)                                  AS dividends_reinvested_index,
    round(tupleElement(pt, 3) - tupleElement(pt, 2), 1)  AS gap_points
FROM
(
    SELECT arrayJoin(arrayZip(years, price_path, reinvested_path)) AS pt
    FROM paths
)
ORDER BY year