By Is the Market a Random Walk? Real Returns
Is the market a random walk? Twenty years of daily SPY returns, measured: the statistics that cannot tell real from simulated, and the two that always can.
Is the stock market a random walk? Measured over two decades of daily closes in SPY, the S&P 500 tracker, most of the statistics people quote in that argument cannot separate the real index from a simulated path with matched drift and matched volatility. Two of them separate the two every time. The size distribution of daily moves holds far more extreme days than a random walk allows, and the correlation of absolute returns stays positive for weeks. Every figure below comes from one sample: 5217 trading days between January 4, 2006 and September 30, 2026.
This is a measurement, not a verdict on market efficiency. That argument lives in the efficient market hypothesis. What follows is the part you can recompute.
What a random walk claims about daily returns
A random walk makes two claims at once. Tomorrow's close is today's close plus one draw from a fixed distribution, and that draw is independent of every draw before it. Independence is the first claim. A distribution whose width never changes is the second. Nearly every popular test attacks independence, which is the claim the data defends best. The second claim is where real returns part company with simulation.
A few terms first. Drift is the average daily return over a window. Volatility is the standard deviation of those daily returns, quoted below as sigma. Autocorrelation is the correlation between a return series and a copy of itself shifted back by a few days, so lag one asks whether yesterday carries information about today.
Losing streaks look like coin flips
Down days make up 45.3% of this sample. For independent days with a down probability of p, the expected number of runs of exactly k consecutive down days in N days is N * p^k * (1 - p)^2. No simulation is needed for that. The panel sets the counted streaks beside the formula.
| down_run_length | spy_runs | coin_flip_runs | down_day_share_pct | sample_from | sample_to |
|---|---|---|---|---|---|
| 1 | 728 | 707.4 | 45.3 | January 4, 2006 | September 30, 2026 |
| 2 | 321 | 320.3 | 45.3 | January 4, 2006 | September 30, 2026 |
| 3 | 153 | 145 | 45.3 | January 4, 2006 | September 30, 2026 |
| 4 | 68 | 65.6 | 45.3 | January 4, 2006 | September 30, 2026 |
| 5 | 30 | 29.7 | 45.3 | January 4, 2006 | September 30, 2026 |
| 6 | 10 | 13.5 | 45.3 | January 4, 2006 | September 30, 2026 |
| 7 plus | 7 | 11.1 | 45.3 | January 4, 2006 | September 30, 2026 |
The exact SQL behind every number
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),
flags AS (SELECT date, if(ret < 0, 1, 0) AS down FROM rets),
islands AS (SELECT down, row_number() OVER (ORDER BY date) - sum(down) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS island FROM flags),
runs AS (SELECT island, count() AS run_length FROM islands WHERE down = 1 GROUP BY island),
sample AS (SELECT count() AS n, avg(down) AS p, concat(monthName(min(date)), ' ', toString(toDayOfMonth(min(date))), ', ', toString(toYear(min(date)))) AS sample_from, concat(monthName(max(date)), ' ', toString(toDayOfMonth(max(date))), ', ', toString(toYear(max(date)))) AS sample_to FROM flags)
SELECT
if(bucket = 7, '7 plus', toString(bucket)) AS down_run_length,
count() AS spy_runs,
round(if(bucket = 7, any(n) * pow(any(p), 7) * (1 - any(p)), any(n) * pow(any(p), bucket) * pow(1 - any(p), 2)), 1) AS coin_flip_runs,
round(100 * any(p), 1) AS down_day_share_pct,
any(sample_from) AS sample_from,
any(sample_to) AS sample_to
FROM (SELECT least(r.run_length, 7) AS bucket, s.n AS n, s.p AS p, s.sample_from AS sample_from, s.sample_to AS sample_to FROM runs AS r CROSS JOIN sample AS s)
GROUP BY bucket
ORDER BY bucketSingle down days come to 728 against 707.4 from the formula. At the long end, the 7 plus bucket holds 7 counted streaks against 11.1 predicted. A reader who stopped here would file the market as a fair coin with an upward tilt. Streak length is a weak instrument for this question, and how long a losing streak is normal takes that distribution apart in detail.
The worst drawdown sits inside the random walk's range
A drawdown is the fall from an equity peak to the lowest point that follows it, measured close to close here, with no intraday lows. Maximum drawdown takes the deepest one in a window.
The next panel simulates 400 paths. Each has the same number of days as the real sample, the same average daily return, and the same daily standard deviation, with every step a normal draw. The draws come from a hash of the path number and the step number run through the Box-Muller transform, which keeps the panel reproducible: run the SQL again and the same 400 paths come back.
| 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 |
The exact SQL behind every number
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)Across the 400 paths the deepest fall runs from 31.8% at the fifth percentile to 63.3% at the ninety-fifth, with a median of 45%. The real index gave up 56.5% at its worst over the same window, and 14% of the simulated paths fell at least that far. One worst-case number cannot tell the two apart. The shape of the fall can, which is the next two sections.
Does yesterday's move predict today's?
The panel below runs two autocorrelations side by side out to ten days. The first uses signed returns, which asks about direction. The second uses absolute returns, which drops the sign and asks about size.
| lag | return_autocorr | abs_return_autocorr |
|---|---|---|
| 01 | -0.103 | 0.314 |
| 02 | -0.014 | 0.401 |
| 03 | 0.004 | 0.34 |
| 04 | -0.035 | 0.354 |
| 05 | -0.013 | 0.357 |
| 06 | -0.032 | 0.342 |
| 07 | 0.045 | 0.326 |
| 08 | -0.039 | 0.315 |
| 09 | 0.038 | 0.314 |
| 10 | 0 | 0.304 |
The exact SQL behind every number
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),
series AS (SELECT arrayMap(x -> tupleElement(x, 2), arraySort(groupArray(tuple(date, ret)))) AS r FROM rets),
lagged AS (SELECT lags.k AS k, arraySlice(s.r, lags.k + 1) AS x, arraySlice(s.r, 1, length(s.r) - lags.k) AS y FROM series AS s CROSS JOIN (SELECT arrayJoin(range(1, 11)) AS k) AS lags),
sums AS
(
SELECT
k,
toFloat64(length(x)) AS n,
arraySum(x) AS sx,
arraySum(y) AS sy,
arraySum(arrayMap((a, b) -> a * b, x, y)) AS sxy,
arraySum(arrayMap(a -> a * a, x)) AS sxx,
arraySum(arrayMap(a -> a * a, y)) AS syy,
arraySum(arrayMap(a -> abs(a), x)) AS sax,
arraySum(arrayMap(a -> abs(a), y)) AS say,
arraySum(arrayMap((a, b) -> abs(a) * abs(b), x, y)) AS saxy
FROM lagged
)
SELECT
leftPad(toString(k), 2, '0') AS lag,
round((n * sxy - sx * sy) / sqrt((n * sxx - sx * sx) * (n * syy - sy * sy)), 3) AS return_autocorr,
round((n * saxy - sax * say) / sqrt((n * sxx - sax * sax) * (n * syy - say * say)), 3) AS abs_return_autocorr
FROM sums
ORDER BY kAt one day the signed figure is -0.103 and the absolute figure is 0.314. At 10 days the signed figure is 0 while the absolute figure is still 0.304. The absolute column sits well above the signed column at both ends of the panel. That gap is the first fingerprint: direction has almost no memory, size has a long one. Large days arrive near other large days and quiet days near quiet days, which is volatility clustering.
Fat tails: what a bell curve misses
The second fingerprint is in the size distribution itself. Sort every day by how many sigma it moved away from the average day, bucket those distances, then compare the counts with what a normal distribution of the same width predicts. The predicted counts come from the error function in SQL rather than from a simulation.
| sigma_band | spy_moves | normal_model_moves | sample_size |
|---|---|---|---|
| 0 to 1 sigma | 4214 | 3561.6 | 5217 |
| 1 to 2 sigma | 756 | 1418 | 5217 |
| 2 to 3 sigma | 163 | 223.3 | 5217 |
| 3 to 4 sigma | 45 | 13.8 | 5217 |
| 4 to 5 sigma | 20 | 0.3 | 5217 |
| 5 sigma plus | 19 | 0 | 5217 |
The exact SQL behind every number
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),
banded AS (SELECT least(toUInt8(floor(abs(r.ret - s.mu) / s.sd)), 5) AS band, s.n AS n FROM rets AS r CROSS JOIN stats AS s)
SELECT
if(band = 5, '5 sigma plus', concat(toString(band), ' to ', toString(band + 1), ' sigma')) AS sigma_band,
count() AS spy_moves,
round(any(n) * (erf((band + 1) / sqrt(2)) - erf(band / sqrt(2))), 1) AS normal_model_moves,
any(n) AS sample_size
FROM banded
GROUP BY band
ORDER BY bandThe 3 to 4 sigma bucket holds 45 days against 13.8 predicted. The 5 sigma plus bucket holds 19 days against 0. A bell curve of this width treats a five sigma day as a once in several thousand years event. This sample carries 19 of them.
Excess kurtosis is the one number that summarizes that table. It is the average fourth power of standardized returns, minus three, so a normal distribution scores zero and a fat-tailed one scores above it. This sample measures 14.95.
| year | excess_kurtosis | moves_over_2pct | full_window_excess_kurtosis |
|---|---|---|---|
| 2006 | 1.08 | 2 | 14.95 |
| 2007 | 1.69 | 14 | 14.95 |
| 2008 | 6.4 | 70 | 14.95 |
| 2009 | 2.02 | 50 | 14.95 |
| 2010 | 2 | 22 | 14.95 |
| 2011 | 2.54 | 33 | 14.95 |
| 2012 | 0.79 | 7 | 14.95 |
| 2013 | 1.35 | 4 | 14.95 |
| 2014 | 1.33 | 4 | 14.95 |
| 2015 | 2.04 | 11 | 14.95 |
| 2016 | 2.21 | 10 | 14.95 |
| 2017 | 2.55 | 0 | 14.95 |
| 2018 | 3.17 | 19 | 14.95 |
| 2019 | 3.06 | 7 | 14.95 |
| 2020 | 7.06 | 42 | 14.95 |
| 2021 | 0.59 | 8 | 14.95 |
| 2022 | 0.32 | 46 | 14.95 |
| 2023 | -0.19 | 2 | 14.95 |
| 2024 | 1.7 | 7 | 14.95 |
| 2025 | 23.14 | 14 | 14.95 |
The exact SQL behind every number
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),
full_window AS (SELECT avg(ret) AS mu, stddevPop(ret) AS sd FROM rets),
full_excess AS (SELECT round(avg(pow((r.ret - f.mu) / f.sd, 4)) - 3, 2) AS ek FROM rets AS r CROSS JOIN full_window AS f),
yearly AS (SELECT toYear(r.date) AS yr, r.ret AS ret, e.ek AS ek FROM rets AS r CROSS JOIN full_excess AS e),
year_stats AS (SELECT yr, avg(ret) AS mu, stddevPop(ret) AS sd FROM yearly GROUP BY yr)
SELECT
y.yr AS year,
round(avg(pow((y.ret - s.mu) / s.sd, 4)) - 3, 2) AS excess_kurtosis,
countIf(abs(y.ret) > 0.02) AS moves_over_2pct,
round(any(y.ek), 2) AS full_window_excess_kurtosis
FROM yearly AS y
INNER JOIN year_stats AS s ON y.yr = s.yr
GROUP BY y.yr
ORDER BY y.yrThe yearly column lets you see where the tails come from instead of taking one number on faith. Each year is standardized by its own mean and its own sigma, so the figure measures tail shape rather than how loud the year was. The count of moves beyond two percent in either direction sits at 2 in 2006 and 4 in 2026, and the panel shows which years hold the rest of them.
What clustering changes about a backtest
Here the measurement turns operational. A confidence interval on a backtest is often built as though days were independent: resample daily returns at random, rebuild the equity curve a few thousand times, read the spread. Independent resampling keeps the fat tails of the daily distribution and destroys the clustering measured above. The drawdowns that come back are shallower and more evenly spaced than the ones a strategy meets in sequence, and the interval reads too narrow. Block resampling, which draws contiguous stretches of days rather than single days, keeps part of the clustering, and the block length becomes a modelling choice you have to defend. Bootstrapping backtest confidence intervals walks through the mechanics.
Two further observations follow from the same panels. Drawdowns arrive bunched rather than spread evenly across a sample, so a strategy's worst stretch and its second worst stretch sit closer together in time than independence would place them. And a position sizing rule keyed to recent volatility has something real to key on, which is the premise behind volatility targeting. Neither observation needs direction to be forecastable. The signed column of the autocorrelation panel says it is not.
FAQ
Is the stock market a random walk?
Partly. In this sample, streak lengths and the depth of the worst drawdown both land inside the range a matched simulation produces, and one-day direction autocorrelation stays near zero. The size distribution fails: daily moves carry much fatter tails than a normal random walk, and the correlation of absolute returns stays positive out to ten days.
What is excess kurtosis in stock returns?
It is the average fourth power of standardized daily returns, minus three. A normal distribution scores zero. These 5217 trading days score 14.95, which says the extremes are far heavier than a bell curve of the same width.
What is volatility clustering?
It is the tendency of large moves to arrive near other large moves, and quiet days near quiet days. Measured as the autocorrelation of absolute returns, it reads 0.314 at one day and 0.304 at 10 days here.
Does volatility clustering make returns predictable?
It makes the size of the next move partly forecastable, not the direction. The one-day autocorrelation of signed returns in this sample is -0.103, against 0.314 for absolute returns. Volatility models rest on the second number. A directional forecast would need the first.
Why can't I resample daily returns independently in a backtest?
Independent resampling keeps the shape of a single day and throws away the order of days. The clustering measured here lives in that order, so the drawdown distribution that comes back is shallower than what a strategy meets, and the confidence interval reads too narrow.
Data notes
Every panel reads daily closes for one ticker, SPY, over the same window, January 4, 2006 through September 30, 2026, and returns are simple close-to-close changes on unadjusted closes. SPY carries no share split inside this window, which keeps the series continuous; for windows that do contain one, see split adjusted price history. Drawdowns use closing prices only, so they run shallower than intraday peak to trough figures. The simulated paths draw from a hash rather than a random number generator, which is what makes the percentiles reproducible. The final year in the kurtosis panel is partial, ending with the sample window.
Open the SQL under any panel, change the ticker or the window, and the same measurement runs again on the Strasmore terminal.