target_curve

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
    universe AS
    (
        SELECT ticker
        FROM global_markets.stocks_daily_aggs
        WHERE date >= '2014-06-01'
          AND ifNull(otc, 0) = 0
          AND ticker NOT IN ('SPCX')
        GROUP BY ticker
        HAVING count() >= 250
           AND avg(toFloat64(close) * volume) >= 2e7
    ),
    daily AS
    (
        SELECT
            ticker,
            date,
            toFloat64(close)  AS close,
            toFloat64(high)   AS high,
            toFloat64(low)    AS low,
            toFloat64(volume) AS volume
        FROM global_markets.stocks_daily_aggs
        WHERE date >= '2014-06-01'
          AND ticker IN (SELECT ticker FROM universe)
    ),
    featured AS
    (
        SELECT
            ticker,
            date,
            close,
            volume,
            max(high)   OVER cup    AS rim,
            min(low)    OVER cup    AS cup_low,
            max(high)   OVER handle AS handle_high,
            min(low)    OVER handle AS handle_low,
            avg(volume) OVER vol50  AS adv50,
            lagInFrame(close, 120)  OVER trend AS base_start_close,
            lagInFrame(close, 240)  OVER trend AS trend_start_close,
            count()     OVER trend  AS history_bars
        FROM daily
        WINDOW
            cup    AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 120 PRECEDING AND 11 PRECEDING),
            handle AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING),
            vol50  AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 50 PRECEDING AND 1 PRECEDING),
            trend  AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 240 PRECEDING AND CURRENT ROW)
    ),
    signals AS
    (
        SELECT
            ticker,
            date       AS breakout_date,
            close      AS breakout_close,
            handle_low AS handle_low
        FROM featured
        WHERE history_bars = 241
          AND date >= '2015-01-01'
          AND date <= today() - 65
          AND rim > 0
          AND adv50 > 0
          AND close >= 10
          AND adv50 * close >= 1e8
          AND close > rim
          AND handle_high <= rim
          AND handle_low > cup_low
          AND (1 - cup_low / rim) >= 0.12
          AND (1 - cup_low / rim) <= 0.50
          AND (rim - handle_low) / rim <= 0.5 * (1 - cup_low / rim)
          AND base_start_close >= 1.10 * trend_start_close
    ),
    paths AS
    (
        SELECT
            min(if(b.low  <= s.handle_low,            b.date, toDate('2099-12-31'))) AS stop_date,
            min(if(b.high >= s.breakout_close * 1.05, b.date, toDate('2099-12-31'))) AS hit_05,
            min(if(b.high >= s.breakout_close * 1.10, b.date, toDate('2099-12-31'))) AS hit_10,
            min(if(b.high >= s.breakout_close * 1.15, b.date, toDate('2099-12-31'))) AS hit_15,
            min(if(b.high >= s.breakout_close * 1.20, b.date, toDate('2099-12-31'))) AS hit_20,
            min(if(b.high >= s.breakout_close * 1.25, b.date, toDate('2099-12-31'))) AS hit_25
        FROM daily AS b
        INNER JOIN signals AS s ON s.ticker = b.ticker
        WHERE b.date >  s.breakout_date
          AND b.date <= s.breakout_date + 60
        GROUP BY
            s.ticker,
            s.breakout_date,
            s.breakout_close,
            s.handle_low
    )
SELECT
    concat('+', toString(goal.1), '%')                    AS target_label,
    count()                                               AS signal_count,
    round(100 * countIf(goal.2 < stop_date) / count(), 1) AS hit_rate_pct,
    round(100 * countIf(goal.2 >= stop_date
                        AND stop_date < toDate('2099-12-31')) / count(), 1) AS stopped_first_pct
FROM
(
    SELECT
        stop_date,
        arrayJoin([(5, hit_05), (10, hit_10), (15, hit_15), (20, hit_20), (25, hit_25)]) AS goal
    FROM paths
)
GROUP BY target_label
ORDER BY min(goal.1)