The best score climbs with the size of the search: best and average Sharpe by trials run

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 month_end AS (
    SELECT ticker,
           toStartOfMonth(toDate(toTimeZone(window_start, 'America/New_York'))) AS month_start,
           argMax(toFloat64(close), window_start) AS close_px
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('AAPL','ADBE','AMZN','BA','CAT','COST','CRM','CSCO','CVX','DE',
                     'DUK','GE','GOOGL','HD','HON','IBM','INTC','JNJ','JPM','KO',
                     'LMT','MCD','MMM','MRK','MSFT','NKE','NVDA','ORCL','PEP','PFE',
                     'PG','QCOM','SO','T','TGT','TXN','UNP','VZ','WMT','XOM')
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2015-12-01')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2021-06-30')
      AND toDayOfMonth(toTimeZone(window_start, 'America/New_York')) >= 22
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker, month_start
),
lagged AS (
    SELECT ticker,
           month_start,
           close_px,
           lagInFrame(close_px) OVER (PARTITION BY ticker ORDER BY month_start
                                      ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_px
    FROM month_end
),
monthly_return AS (
    SELECT ticker, month_start, close_px / prev_px - 1 AS ret
    FROM lagged
    WHERE prev_px > 0
      AND month_start >= toDate('2016-01-01')
),
trial AS (
    SELECT arrayJoin(range(1, 241)) AS n
),
factor_month AS (
    SELECT t.n AS trial_id,
           m.month_start AS month_start,
           avgIf(m.ret, bitAnd(cityHash64(m.ticker, toString(m.month_start), t.n), 1) = 1)
         - avgIf(m.ret, bitAnd(cityHash64(m.ticker, toString(m.month_start), t.n), 1) = 0) AS long_short_ret
    FROM monthly_return AS m
    CROSS JOIN trial AS t
    GROUP BY trial_id, month_start
    HAVING countIf(bitAnd(cityHash64(m.ticker, toString(m.month_start), t.n), 1) = 1) > 0
       AND countIf(bitAnd(cityHash64(m.ticker, toString(m.month_start), t.n), 1) = 0) > 0
),
scored AS (
    SELECT trial_id,
           avg(long_short_ret) / stddevSamp(long_short_ret) * sqrt(12) AS sharpe
    FROM factor_month
    GROUP BY trial_id
    HAVING stddevSamp(long_short_ret) > 0
),
ladder AS (
    SELECT arrayJoin([1, 2, 5, 10, 25, 50, 100, 160, 240]) AS n
)
SELECT l.n AS factors_tried,
       round(max(s.sharpe), 2) AS best_sharpe,
       round(avg(s.sharpe), 2) AS average_sharpe
FROM ladder AS l
CROSS JOIN scored AS s
WHERE s.trial_id <= l.n
GROUP BY factors_tried
ORDER BY factors_tried