obv_trace

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
            date,
            max(toFloat64(close))  AS close,
            max(toFloat64(volume)) AS volume
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'AAPL'
          AND date >= '2026-04-01'
          AND date <= '2026-06-30'
        GROUP BY date
    ),
    stepped AS
    (
        SELECT
            date,
            close,
            volume,
            lagInFrame(close, 1) OVER (ORDER BY date ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
        FROM bars
    ),
    running AS
    (
        SELECT
            date,
            close,
            first_value(close) OVER (ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS first_close,
            avg(volume)        OVER (ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS avg_volume,
            sum(if(prev_close = 0, 0, if(close > prev_close, volume, if(close < prev_close, -volume, 0))))
                OVER (ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS obv_shares,
            sum(if(prev_close = 0, 0, 100 * volume * (close / prev_close - 1)))
                OVER (ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS vpt_shares
        FROM stepped
    )
SELECT
    toString(date)                            AS date,
    round(100 * (close / first_close - 1), 2) AS price_change_pct,
    round(obv_shares / avg_volume, 2)         AS obv_ratio,
    round(vpt_shares / avg_volume, 2)         AS vpt_ratio
FROM running
ORDER BY date ASC