Weekly z score of the KO/PEP spread, hedge ratio fitted on 2023 only

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
            date,
            anyIf(toFloat64(close), ticker = 'PEP') AS pep,
            anyIf(toFloat64(close), ticker = 'KO')  AS ko
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN ('KO', 'PEP')
          AND date BETWEEN '2023-01-01' AND '2025-12-31'
        GROUP BY date
        HAVING pep > 0 AND ko > 0
    ),
    fitted AS (
        SELECT covarSamp(pep, ko) / varSamp(ko) AS beta
        FROM daily
        WHERE date < '2024-01-01'
    ),
    spread AS (
        SELECT
            daily.date                          AS date,
            daily.pep - fitted.beta * daily.ko  AS spread_usd
        FROM daily
        CROSS JOIN fitted
    ),
    scored AS (
        SELECT
            date,
            (spread_usd - avg(spread_usd) OVER (
                 ORDER BY date ROWS BETWEEN 62 PRECEDING AND CURRENT ROW))
            / stddevSampStable(spread_usd) OVER (
                 ORDER BY date ROWS BETWEEN 62 PRECEDING AND CURRENT ROW) AS z
        FROM spread
    )
SELECT
    toString(toMonday(date))   AS week,
    round(argMax(z, date), 2)  AS z_score
FROM scored
WHERE date >= '2024-01-01'
  AND isFinite(z)
GROUP BY week
ORDER BY week