signals_by_year

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
px AS (
    SELECT ticker, date,
           toFloat64(argMax(high, _ingest_time))  AS h,
           toFloat64(argMax(low, _ingest_time))   AS l,
           toFloat64(argMax(close, _ingest_time)) AS c
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('AAPL','MSFT','NVDA','AMZN','GOOGL','META','JPM','JNJ','KO','XOM')
      AND date >= '2015-01-01' AND date < today()
    GROUP BY ticker, date),
splits AS (
    SELECT ticker, groupArray(execution_date) AS split_dates
    FROM global_markets.stocks_splits
    WHERE execution_date >= '2014-01-01'
    GROUP BY ticker),
pivots AS (
    SELECT ticker, date, h, l, c,
           toUInt8(h = max(h) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 10 PRECEDING AND 10 FOLLOWING)) AS is_peak
    FROM px),
framed AS (
    SELECT ticker, date, h, is_peak,
           groupArray(h)       OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 60 PRECEDING AND CURRENT ROW) AS h_back,
           groupArray(l)       OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 60 PRECEDING AND CURRENT ROW) AS l_back,
           groupArray(is_peak) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 60 PRECEDING AND CURRENT ROW) AS p_back,
           groupArray(c)       OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN CURRENT ROW AND 90 FOLLOWING) AS c_fwd,
           groupArray(date)    OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN CURRENT ROW AND 90 FOLLOWING) AS d_fwd
    FROM pivots),
paired AS (
    SELECT ticker, date AS peak2_date, h AS peak2_high, h_back, l_back, c_fwd, d_fwd,
           arrayFirst(g -> (p_back[61 - g] = 1)
                       AND (abs((h_back[61 - g] / h) - 1) <= 0.03)
                       AND (arrayMin(arraySlice(l_back, 61 - g, g + 1)) <= (0.95 * least(h_back[61 - g], h))),
                      range(15, 61)) AS gap
    FROM framed
    WHERE is_peak = 1 AND length(l_back) = 61 AND length(c_fwd) = 91),
necked AS (
    SELECT ticker, peak2_date, gap,
           greatest(h_back[61 - gap], peak2_high)          AS peak_high,
           arrayMin(arraySlice(l_back, 61 - gap, gap + 1)) AS neckline,
           c_fwd, d_fwd
    FROM (
        SELECT p.ticker AS ticker, p.peak2_date AS peak2_date, p.peak2_high AS peak2_high,
               p.h_back AS h_back, p.l_back AS l_back, p.c_fwd AS c_fwd, p.d_fwd AS d_fwd,
               p.gap AS gap, s.split_dates AS split_dates
        FROM paired AS p
        LEFT JOIN splits AS s ON p.ticker = s.ticker)
    WHERE gap > 0
      AND arrayCount(d -> (d >= (peak2_date - 150)) AND (d <= (peak2_date + 180)), split_dates) = 0),
confirmed AS (
    SELECT ticker,
           d_fwd[k_rel + 11]                                   AS confirm_date,
           round(100 * ((peak_high - neckline) / peak_high), 2) AS depth_pct
    FROM (SELECT *, arrayFirstIndex(x -> x < neckline, arraySlice(c_fwd, 12, 20)) AS k_rel FROM necked)
    WHERE k_rel > 0
    ORDER BY ticker, confirm_date
    LIMIT 1 BY ticker, confirm_date)
SELECT
    toYear(confirm_date)                    AS year,
    count()                                 AS signal_count,
    round(quantileExact(0.5)(depth_pct), 2) AS median_depth_pct
FROM confirmed
GROUP BY year
ORDER BY year