liquidity_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
    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,
            adv50 * close / 1e6 AS adv_usd_mn
        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
            s.adv_usd_mn                                                             AS adv_usd_mn,
            min(if(b.low  <= s.handle_low,            b.date, toDate('2099-12-31'))) AS stop_date,
            min(if(b.high >= s.breakout_close * 1.10, b.date, toDate('2099-12-31'))) AS hit_date
        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,
            s.adv_usd_mn
    )
SELECT
    multiIf(adv_usd_mn < 300,  'Dưới 300 triệu USD',
            adv_usd_mn < 1000, '300 triệu đến 1 tỷ USD',
            adv_usd_mn < 5000, '1 tỷ đến 5 tỷ USD',
                               'Trên 5 tỷ USD')             AS liquidity_bucket,
    count()                                                 AS signal_count,
    round(100 * countIf(hit_date < stop_date) / count(), 1) AS hit_rate_pct,
    round(100 * countIf(stop_date <= hit_date
                        AND stop_date < toDate('2099-12-31')) / count(), 1) AS stopped_first_pct
FROM paths
GROUP BY liquidity_bucket
ORDER BY min(adv_usd_mn)