From the whole settlement file down to the screened list, one rule at a time

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 latest AS (
    SELECT max(settlement_date) AS d FROM global_markets.stocks_short_interest
),
sessions AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS session
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= now() - INTERVAL 20 DAY
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60
        + toMinute(toTimeZone(window_start, 'America/New_York')) >= 570
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60
        + toMinute(toTimeZone(window_start, 'America/New_York')) < 960
    GROUP BY session
    HAVING count() >= 380
    ORDER BY session DESC
    LIMIT 6
),
reported AS (
    SELECT ticker, days_to_cover, avg_daily_volume
    FROM global_markets.stocks_short_interest
    WHERE settlement_date = (SELECT d FROM latest)
),
liquid AS (
    SELECT ticker, days_to_cover
    FROM reported
    WHERE avg_daily_volume >= 5000000
      AND days_to_cover IS NOT NULL
      AND ticker NOT IN ('SPCX')
      AND ticker NOT IN ('KORU','SOXL','SOXS','TQQQ','SQQQ','NVDL','NVDS','NVD','TSLL','TSLQ','TSLZ','SPXL','SPXS','UPRO','SPXU','LABU','LABD','FAS','FAZ','TNA','TZA','YINN','YANG','UDOW','SDOW','BOIL','KOLD','UCO','SCO','USD','SSO','SDS','QLD','QID','ERX','ERY','DRN','DRV','CURE','SOXY','MUU','SNXX','UVXY','SVXY','UVIX','SVIX','BULZ','WEBL','WEBS','DPST','DRIP','GUSH','AGQ','ZSL','BITX','ETHU','MSTX','MSTU','CONL','DUST','JNUG','JDST','NUGT')
      AND ticker NOT IN (SELECT ticker FROM global_markets.stocks_splits
                         WHERE execution_date BETWEEN today() - 60 AND today())
),
crowded AS (
    SELECT ticker FROM liquid WHERE days_to_cover >= 5
),
tape AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS session,
           argMax(close, window_start) AS rth_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN (SELECT ticker FROM crowded)
      AND toDate(toTimeZone(window_start, 'America/New_York')) IN (SELECT session FROM sessions)
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60
        + toMinute(toTimeZone(window_start, 'America/New_York')) >= 570
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60
        + toMinute(toTimeZone(window_start, 'America/New_York')) < 960
    GROUP BY ticker, session
),
rising AS (
    SELECT ticker,
           round((argMax(rth_close, session) / argMin(rth_close, session) - 1) * 100, 1) AS return_5d_pct
    FROM tape
    GROUP BY ticker
    HAVING count() = 6 AND return_5d_pct > 0
),
tally AS (
    SELECT (SELECT count() FROM reported) AS all_names,
           (SELECT count() FROM liquid) AS liquid_names,
           (SELECT count() FROM crowded) AS crowded_names,
           (SELECT count() FROM rising) AS rising_names
)
SELECT step.1 AS label, step.2 AS names
FROM (
    SELECT arrayJoin([
        ('Every name in the settlement file', all_names),
        ('Liquid: five million shares a day, no leveraged funds', liquid_names),
        ('Crowded: five or more days to cover', crowded_names),
        ('Rising: a positive move over the price window', rising_names)]) AS step
    FROM tally
)