trend_split

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,
            count()    OVER w200 AS n200,
            avg(close) OVER w200 AS ma200
        FROM bars
        WINDOW
            w14  AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 13 PRECEDING AND CURRENT ROW),
            w200 AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 199 PRECEDING AND CURRENT ROW)
    ),
    fast AS (
        SELECT
            ticker, date, close, n200, ma200,
            if(hh14 > ll14, 100 * (close - ll14) / (hh14 - ll14), 50) AS k_fast
        FROM ranges
        WHERE n14 = 14
    ),
    slow AS (
        SELECT
            ticker, date, close, n200, ma200,
            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, n200, ma200, k_slow,
            lagInFrame(k_slow, 1)  OVER wl AS k_prev,
            leadInFrame(close, 10) OVER wf AS c10
        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)
    ),
    split AS (
        SELECT
            100 * (c10 / close - 1)            AS fwd10_pct,
            cityHash64(ticker, toString(date)) AS h,
            concat(
                if((k_prev >= 80) AND (k_slow < 80), 'ตัดลง 80', 'ตัดขึ้น 20'),
                ' / ',
                if(close >= ma200, 'ราคาเหนือ MA200', 'ราคาใต้ MA200')
            )                                  AS signal_group
        FROM fwd
        WHERE date >= '2016-01-01'
          AND c10 > 0
          AND n200 = 200
          AND (((k_prev >= 80) AND (k_slow < 80)) OR ((k_prev <= 20) AND (k_slow > 20)))
    )
SELECT
    signal_group,
    round(quantileDeterministic(0.5)(fwd10_pct, h), 2) AS median_fwd_10d_pct,
    round(100 * countIf(fwd10_pct > 0) / count(), 1)   AS win_rate_10d_pct,
    count()                                            AS signal_count
FROM split
GROUP BY signal_group
ORDER BY indexOf([
    'ตัดลง 80 / ราคาเหนือ MA200',
    'ตัดลง 80 / ราคาใต้ MA200',
    'ตัดขึ้น 20 / ราคาเหนือ MA200',
    'ตัดขึ้น 20 / ราคาใต้ MA200'
], signal_group)