horizons

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
    bars AS
    (
        SELECT
            ticker,
            date,
            max(toFloat64(close))  AS close,
            max(toFloat64(volume)) AS volume
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN ('MSFT', 'SPY', 'KO', 'JNJ', 'JPM', 'XOM', 'PG', 'PEP', 'MCD', 'HD')
          AND date >= '2016-01-04'
          AND date <= '2026-06-30'
        GROUP BY ticker, date
    ),
    stepped AS
    (
        SELECT
            ticker,
            date,
            close,
            volume,
            lagInFrame(close, 1) OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
        FROM bars
    ),
    cumulative AS
    (
        SELECT
            ticker,
            date,
            close,
            sum(if(prev_close = 0, 0, if(close > prev_close, volume, if(close < prev_close, -volume, 0))))
                OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS obv,
            row_number() OVER (PARTITION BY ticker ORDER BY date ASC) AS bar_no
        FROM stepped
    ),
    marked AS
    (
        SELECT
            close,
            obv,
            bar_no,
            max(close)             OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS high_20,
            lagInFrame(obv, 20)    OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN 20 PRECEDING AND CURRENT ROW) AS obv_20_back,
            leadInFrame(close, 5)  OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN CURRENT ROW AND 5 FOLLOWING)  AS close_fwd_5,
            leadInFrame(close, 10) OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN CURRENT ROW AND 10 FOLLOWING) AS close_fwd_10,
            leadInFrame(close, 20) OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN CURRENT ROW AND 20 FOLLOWING) AS close_fwd_20
        FROM cumulative
    ),
    events AS
    (
        SELECT
            obv < obv_20_back AS is_divergent,
            if(close_fwd_5  > 0, 100 * (close_fwd_5  / close - 1), NULL) AS ret_5,
            if(close_fwd_10 > 0, 100 * (close_fwd_10 / close - 1), NULL) AS ret_10,
            if(close_fwd_20 > 0, 100 * (close_fwd_20 / close - 1), NULL) AS ret_20
        FROM marked
        WHERE bar_no > 21
          AND close >= high_20
    ),
    long_form AS
    (
        SELECT
            is_divergent,
            horizon.1 AS label,
            horizon.2 AS ret
        FROM
        (
            SELECT
                is_divergent,
                arrayJoin([('5 sessions', ret_5), ('10 sessions', ret_10), ('20 sessions', ret_20)]) AS horizon
            FROM events
        )
        WHERE isNotNull(horizon.2)
    )
SELECT
    label,
    round(avgIf(ret, is_divergent), 2)                                AS divergence_avg_pct,
    round(avgIf(ret, NOT is_divergent), 2)                            AS confirmed_avg_pct,
    round(avgIf(ret, is_divergent) - avgIf(ret, NOT is_divergent), 2) AS gap_pct,
    round((avgIf(ret, is_divergent) - avgIf(ret, NOT is_divergent))
          / sqrt(varSampIf(ret, is_divergent) / countIf(is_divergent)
               + varSampIf(ret, NOT is_divergent) / countIf(NOT is_divergent)), 2) AS welch_t,
    countIf(is_divergent)                                             AS divergence_count,
    countIf(NOT is_divergent)                                         AS confirmed_count
FROM long_form
GROUP BY label
HAVING divergence_count > 50 AND confirmed_count > 50
ORDER BY toUInt16OrZero(splitByChar(' ', label)[1]) ASC