simulated_drawdowns
Answered against 22 years of US equities and 12 years of US options data and published with the query that produced it. This result is stored as of 2026-10-01, from real-returns-vs-random-walks.
| percentile | sim_drawdown_depth_pct | spy_drawdown_depth_pct | paths_at_least_as_deep_pct |
|---|---|---|---|
| p05 | 31.8 | 56.5 | 14 |
| p25 | 37.9 | 56.5 | 14 |
| p50 | 45 | 56.5 | 14 |
| p75 | 51.5 | 56.5 | 14 |
| p95 | 63.3 | 56.5 | 14 |
- Rows × columns
- 5 × 4
- Computed
- Completeness
- No missing values
- Source
- US exchange, SIP and OPRA market data
- Licence
- Strasmore terms · free, no signup
What each column holds
| Column | Type | Range | Notes |
|---|---|---|---|
percentile |
text | 5 distinct values (p05, p25, p50…) | |
sim_drawdown_depth_pct |
number | 31.8 to 63.3 | percent |
spy_drawdown_depth_pct |
number | every row is 56.5 | percent |
paths_at_least_as_deep_pct |
number | every row is 14 | percent |
Computed from Strasmore's warehouse of US exchange, SIP and OPRA market data. Equity prices are delayed; options greeks and implied volatility are end-of-day. This result is stored, not recomputed on load — it is exactly the numbers that were returned on , and the query below is what returned them.
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 daily AS (SELECT date, argMax(toFloat64(close), _ingest_time) AS px FROM global_markets.stocks_daily_aggs WHERE ticker = 'SPY' AND date >= '2006-01-01' AND date <= '2026-09-30' GROUP BY date),
rets AS (SELECT date, px / prev_px - 1 AS ret FROM (SELECT date, px, lagInFrame(px) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_px FROM daily) WHERE prev_px > 0),
stats AS (SELECT avg(ret) AS mu, stddevPop(ret) AS sd, count() AS n FROM rets),
real_curve AS (SELECT date, sum(log(1 + ret)) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS log_equity FROM rets),
real_depth AS (SELECT 100 * (1 - exp(min(log_equity - greatest(peak, 0.0)))) AS depth_pct FROM (SELECT log_equity, max(log_equity) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS peak FROM real_curve)),
steps AS (SELECT pp.p AS path_id, arrayJoin(range(toUInt32(s.n))) AS t, s.mu AS mu, s.sd AS sd FROM stats AS s CROSS JOIN (SELECT arrayJoin(range(400)) AS p) AS pp),
draws AS (SELECT path_id, t, mu + sd * sqrt(-2 * log((cityHash64('strasmore-walk', path_id, t) % 1000000 + 0.5) / 1000000)) * cos(2 * pi() * ((cityHash64('strasmore-phase', t, path_id) % 1000000 + 0.5) / 1000000)) AS ret FROM steps),
sim_curve AS (SELECT path_id, t, sum(log(1 + ret)) OVER (PARTITION BY path_id ORDER BY t ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS log_equity FROM draws),
sim_depth AS (SELECT path_id, 100 * (1 - exp(min(log_equity - greatest(peak, 0.0)))) AS depth_pct FROM (SELECT path_id, log_equity, max(log_equity) OVER (PARTITION BY path_id ORDER BY t ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS peak FROM sim_curve) GROUP BY path_id),
summary AS (SELECT quantilesDeterministic(0.05, 0.25, 0.5, 0.75, 0.95)(d.depth_pct, d.path_id) AS qs, round(any(r.depth_pct), 1) AS spy_depth, round(100 * countIf(d.depth_pct >= r.depth_pct) / count(), 1) AS deeper_share FROM sim_depth AS d CROSS JOIN real_depth AS r)
SELECT
tupleElement(tagged, 1) AS percentile,
round(tupleElement(tagged, 2), 1) AS sim_drawdown_depth_pct,
spy_depth AS spy_drawdown_depth_pct,
deeper_share AS paths_at_least_as_deep_pct
FROM (SELECT arrayJoin(arrayZip(['p05', 'p25', 'p50', 'p75', 'p95'], qs)) AS tagged, spy_depth, deeper_share FROM summary)
Work with this data in your AI assistant
Opens ready to query, with this page's data. Free, no account.