spy_forward_curve

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
        date,
        toFloat64(close) AS c
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND date >= '2019-10-01'
      AND date <  '2026-10-01'
),
ma AS (
    SELECT
        date,
        c,
        avg(c) OVER (ORDER BY date ROWS BETWEEN 199 PRECEDING AND CURRENT ROW) AS ma_200
    FROM px
),
sig AS (
    SELECT
        date,
        c,
        any(c)                    OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_c,
        any(if(c > ma_200, 1, 0)) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS held
    FROM ma
),
daily AS (
    SELECT
        date,
        held * (c / prev_c - 1) AS r_rule,
        c / prev_c - 1          AS r_hold
    FROM sig
    WHERE date >= '2020-09-01'
      AND prev_c > 0
),
cum AS (
    SELECT
        date,
        exp(sum(log(1 + r_rule)) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS eq_rule,
        exp(sum(log(1 + r_hold)) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS eq_hold
    FROM daily
)
SELECT
    formatDateTime(toStartOfMonth(date), '%Y-%m') AS month,
    round(argMax(eq_rule, date) * 100, 1)         AS strategy_index,
    round(argMax(eq_hold, date) * 100, 1)         AS buy_hold_index
FROM cum
GROUP BY month
ORDER BY month