signal_frequency

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,
            argMax(toFloat64(close), _ingest_time) AS close,
            argMax(toFloat64(high),  _ingest_time) AS high,
            argMax(toFloat64(low),   _ingest_time) AS low
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN ('AAPL','MSFT','NVDA','AMZN','GOOGL','META','JPM','JNJ','XOM','PG','KO','WMT','HD','UNH','CVX','PEP','MRK','CSCO','ORCL','MCD')
          AND date >= '2015-01-01'
          AND date <  '2026-09-01'
        GROUP BY ticker, date
    ),
    ranges AS (
        SELECT
            ticker, date, close,
            count()   OVER w14 AS n14,
            max(high) OVER w14 AS hh14,
            min(low)  OVER w14 AS ll14
        FROM bars
        WINDOW w14 AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 13 PRECEDING AND CURRENT ROW)
    ),
    fast AS (
        SELECT
            ticker, date, close,
            if(hh14 > ll14, 100 * (close - ll14) / (hh14 - ll14), 50) AS k_fast
        FROM ranges
        WHERE n14 = 14
    ),
    slow AS (
        SELECT
            ticker, date, close,
            avg(k_fast) OVER w3 AS k_slow,
            count()     OVER w3 AS n3
        FROM fast
        WINDOW w3 AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
    ),
    fwd AS (
        SELECT
            ticker, date, close, k_slow,
            lagInFrame(k_slow, 1)  OVER wl AS k_prev,
            leadInFrame(close, 20) OVER wf AS c20
        FROM slow
        WHERE n3 = 3
        WINDOW
            wl AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW),
            wf AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    )
SELECT
    toYear(date)                                        AS year,
    countIf((k_prev >= 80) AND (k_slow < 80))           AS cross_down_80_count,
    countIf((k_prev <= 20) AND (k_slow > 20))           AS cross_up_20_count
FROM fwd
WHERE date >= '2016-01-01'
  AND c20 > 0
GROUP BY year
ORDER BY year