index_series

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 base AS
(
    SELECT
        ticker,
        argMin(toFloat64(close), date) AS px0
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('SPY', 'SPUS', 'HLAL')
      AND date >= '2020-01-02'
      AND date <  '2020-02-01'
    GROUP BY ticker
),
monthly AS
(
    SELECT
        toStartOfMonth(date)           AS m,
        ticker,
        argMax(toFloat64(close), date) AS px
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('SPY', 'SPUS', 'HLAL')
      AND date >= '2020-01-02'
    GROUP BY m, ticker
)
SELECT
    formatDateTime(mo.m, '%Y-%m')                             AS month,
    round(100 * maxIf(mo.px / b.px0, mo.ticker = 'SPY'),  1)  AS spy_index,
    round(100 * maxIf(mo.px / b.px0, mo.ticker = 'SPUS'), 1)  AS spus_index,
    round(100 * maxIf(mo.px / b.px0, mo.ticker = 'HLAL'), 1)  AS hlal_index
FROM monthly AS mo
INNER JOIN base AS b ON b.ticker = mo.ticker
GROUP BY mo.m
ORDER BY mo.m