base_rates

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, 5)  OVER wf AS c5,
            leadInFrame(close, 10) OVER wf AS c10,
            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)
    ),
    tagged AS (
        SELECT
            100 * (c5  / close - 1)             AS fwd5_pct,
            100 * (c10 / close - 1)             AS fwd10_pct,
            100 * (c20 / close - 1)             AS fwd20_pct,
            cityHash64(ticker, toString(date))  AS h,
            arrayJoin(arrayConcat(
                if((k_prev >= 80) AND (k_slow < 80), ['%K ตัดลงต่ำกว่า 80'], emptyArrayString()),
                if((k_prev <= 20) AND (k_slow > 20), ['%K ตัดขึ้นเหนือ 20'], emptyArrayString()),
                ['ค่าฐาน: ทุกวันทำการ']
            ))                                  AS signal_group
        FROM fwd
        WHERE date >= '2016-01-01'
          AND c5 > 0 AND c10 > 0 AND c20 > 0
    )
SELECT
    signal_group,
    round(quantileDeterministic(0.5)(fwd5_pct,  h), 2) AS median_fwd_5d_pct,
    round(quantileDeterministic(0.5)(fwd10_pct, h), 2) AS median_fwd_10d_pct,
    round(quantileDeterministic(0.5)(fwd20_pct, h), 2) AS median_fwd_20d_pct
FROM tagged
GROUP BY signal_group
ORDER BY indexOf(['%K ตัดลงต่ำกว่า 80', '%K ตัดขึ้นเหนือ 20', 'ค่าฐาน: ทุกวันทำการ'], signal_group)