grid

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,
            sum(if(prev_close = 0, 0, volume * (close / prev_close - 1)))
                OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS vpt,
            row_number() OVER (PARTITION BY ticker ORDER BY date ASC) AS bar_no
        FROM stepped
    ),
    marked AS
    (
        SELECT
            close,
            obv,
            vpt,
            bar_no,
            max(close)             OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN  9 PRECEDING AND CURRENT ROW) AS high_10,
            max(close)             OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS high_20,
            max(close)             OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN 39 PRECEDING AND CURRENT ROW) AS high_40,
            max(close)             OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN 59 PRECEDING AND CURRENT ROW) AS high_60,
            lagInFrame(obv, 10)    OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) AS obv_10_back,
            lagInFrame(obv, 20)    OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN 20 PRECEDING AND CURRENT ROW) AS obv_20_back,
            lagInFrame(obv, 40)    OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN 40 PRECEDING AND CURRENT ROW) AS obv_40_back,
            lagInFrame(obv, 60)    OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN 60 PRECEDING AND CURRENT ROW) AS obv_60_back,
            lagInFrame(vpt, 10)    OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) AS vpt_10_back,
            lagInFrame(vpt, 20)    OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN 20 PRECEDING AND CURRENT ROW) AS vpt_20_back,
            lagInFrame(vpt, 40)    OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN 40 PRECEDING AND CURRENT ROW) AS vpt_40_back,
            lagInFrame(vpt, 60)    OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN 60 PRECEDING AND CURRENT ROW) AS vpt_60_back,
            leadInFrame(close, 20) OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN CURRENT ROW AND 20 FOLLOWING) AS close_fwd_20
        FROM cumulative
    ),
    specs AS
    (
        SELECT
            ret,
            spec.1 AS label,
            spec.2 AS at_new_high,
            spec.3 AS obv_divergent,
            spec.4 AS vpt_divergent
        FROM
        (
            SELECT
                100 * (close_fwd_20 / close - 1) AS ret,
                arrayJoin([
                    ('10-session lookback', close >= high_10, obv < obv_10_back, vpt < vpt_10_back),
                    ('20-session lookback', close >= high_20, obv < obv_20_back, vpt < vpt_20_back),
                    ('40-session lookback', close >= high_40, obv < obv_40_back, vpt < vpt_40_back),
                    ('60-session lookback', close >= high_60, obv < obv_60_back, vpt < vpt_60_back)
                ]) AS spec
            FROM marked
            WHERE bar_no > 61
              AND close_fwd_20 > 0
        )
    )
SELECT
    label,
    round(avgIf(ret, at_new_high AND obv_divergent) - avgIf(ret, at_new_high AND NOT obv_divergent), 2) AS obv_gap_pct,
    round(avgIf(ret, at_new_high AND vpt_divergent) - avgIf(ret, at_new_high AND NOT vpt_divergent), 2) AS vpt_gap_pct,
    countIf(at_new_high AND obv_divergent) AS obv_signal_count,
    countIf(at_new_high AND vpt_divergent) AS vpt_signal_count
FROM specs
GROUP BY label
HAVING obv_signal_count > 50
   AND vpt_signal_count > 50
   AND countIf(at_new_high AND NOT obv_divergent) > 50
   AND countIf(at_new_high AND NOT vpt_divergent) > 50
ORDER BY toUInt16OrZero(splitByChar('-', label)[1]) ASC