Coding agents run most of the backtests in my research loop. On a busy afternoon they'll test 29 strategy variants across nine short windows and the full 24-month history.
That only works because a backtest costs minutes rather than hours. Runtime is effectively a budget on how many ideas I get to test: an hour-long run buys a handful of trials per day, while a run that finishes in minutes buys a real search.
Almost all of that speed lives in the data pipeline.
The raw material
This example uses 24 months of NQ futures data, covering every trade plus L1/L2 order-book updates, licensed and delivered as daily zip archives of semicolon-delimited CSV. Compressed, it's 38 GB. Unpacked, it's over 300 GB of raw CSV holding 8.4 billion records, split almost evenly between trade prints and order-book updates. Across all the datasets in my research, NQ and beyond, the total runs to roughly 100 billion datapoints; this post sticks to the NQ archive.
A raw row looks like this:
YYYYMMDD040000020187;1;2;20145.25;2;0;0
└─ timestamp ──────┘ │ │ price qty
YYYYMMDDhhmmss+µs │ └ type (2 = trade)
└── level (1 = top of book)Timestamps are 20-digit strings with microseconds and no separators. Order-book updates and trades share the same files. Bar construction needs only the trades (level=1, type=2). Parsing the raw CSV files for every backtest would be far too slow.
Rule #1: read the raw ticks once
I read the raw ticks during ingestion and never during a backtest.
A daily processing script unpacks each day's archive, filters trade prints, parses the timestamp strings, and aggregates into OHLCV bars at multiple resolutions, each written as its own per-day Parquet file:
markettick_archives/Future_NQ_T2_YYYYMMDD.zip ← 38 GB total, touched once
mt_ohlcv/NQ_5S_YYYYMMDD.parquet ← 5-second bars
mt_ohlcv/NQ_1M_YYYYMMDD.parquet ← 1-minute bars
mt_ohlcv/NQ_5M_YYYYMMDD.parquet ← 5-minute bars
mt_ohlcv/NQ_100T_YYYYMM.parquet ← 100-tick bars (for microstructure work)Each day produces one file per resolution, or about 2,200 files for this 24-month dataset. This makes ingestion incremental and idempotent. I can add a new day or rebuild a bad day without processing the full history. A --rebuild-all command combines the daily files into the datasets used by backtests:
| Working file | Rows | Size |
|---|---|---|
NQ_5S_all.parquet | 7.8M | 108 MB |
NQ_15S_all.parquet | 2.9M | 44 MB |
NQ_1M_all.parquet | 733K | 11 MB |
NQ_5M_all.parquet | 145K | 3 MB |
Over 300 GB of raw CSV becomes a 108 MB working dataset at 5-second resolution, roughly a 3,000-fold reduction, and that ratio is most of the trick: everything downstream is fast because the input got small. The file loads in a few seconds, so each experiment loads its own copy instead of sharing mutable state.
What Parquet buys, measured
First, the shape of the thing. A bar file is a typed, columnar table; these are real rows from the 5-second working set:
time open high low close volume tick_count
2024-03-01 06:30:00 18141.00 18141.00 18141.00 18141.00 6.0 5
2024-03-01 06:30:05 18141.25 18141.25 18141.00 18141.25 8.0 7
2024-03-01 06:30:10 18141.25 18142.00 18141.25 18141.50 11.0 10
7.8M rows · numeric columns stored as float64/int64 · snappy-compressed column chunksUnder the hood, the layout is the opposite of a CSV. A CSV is row-oriented text: every load walks every line and parses every field. Parquet stores each column separately, as binary column chunks bundled into row groups (this file has eight), with the schema and per-chunk statistics written into the file footer. Three useful properties fall out of that. Values are stored typed, so a close is eight bytes to copy rather than a string to parse. Similar values sit next to each other, so compression bites harder; two years of NQ closes hovering in the same few thousand points pack tightly. And because each column is its own chunk, a reader can pull two columns without touching the other five.
The backtest engine consumes this almost as-is. Prices come back as typed float64 arrays, so bars["close"].to_numpy() hands the event loop a NumPy array with no parsing step in between, and the timestamp column is converted once at load and never again. The format is close to the memory layout the backtest actually wants, which is most of why loading is measured in seconds.
A concrete comparison, run on my machine while writing this post. The same 7.8M-row table of 5-second bars, stored both ways:
| Format | On disk | Full load |
|---|---|---|
| CSV | 478 MB | 2.8 s, or 3.6 s parsing timestamps |
| Parquet | 108 MB | 1.4 s |
Compression is only half of it. Parquet stores each column separately, so a reader can skip what it doesn't need, and numeric columns come back as typed numbers rather than strings waiting to be parsed:
bars = pd.read_parquet("NQ_5S_all.parquet") # 1.4 s, all seven columns
scan = pd.read_parquet("NQ_5S_all.parquet",
columns=["time", "close"]) # 1.1 s, a fraction of the memoryThe per-day layout gives the same effect at the file level. A smoke test that needs one trading day reads a 243 KB file in 28 ms instead of touching the full history.
None of these numbers is dramatic alone. They matter because the load happens constantly: every variant in a sweep, every smoke test, every chart render starts by reading bars. Shaving seconds at the entry point is what keeps a twenty-variant sweep interactive instead of a coffee break.
Contract expiry needed its own fix
NQ is a quarterly contract, so a continuous feed switches contracts every three months. Around those switches the raw data gets noisy in two different ways, and both had to be fixed once, at ingestion, instead of patched inside every backtest.
The first problem was interleaving. During the Sunday reopen of one roll week, the feed mixed records from the expiring contract and the next one, which were trading about 200 points apart. Aggregated blindly, that produced 15-second bars spanning 200 to 250 points, and a backtest on those bars booked its worst day in two years, tens of thousands of dollars of loss that never happened. On cleaned data the same day came out slightly positive. A systematic scan found seven such windows across the dataset, all in overnight sessions near rolls, about 1,700 contaminated bars in total.
Detecting them was its own small lesson. A naive filter that flagged any oversized bar accused real news volatility 56 times out of 61. The detector that worked counts how many times the close series crosses the largest empty gap inside the bar's range: a genuine news move crosses once on its way through, while interleaved contracts bounce across the gap dozens of times.
The second problem is quieter. The continuous series isn't back-adjusted, so each roll leaves a silent price jump between contracts, 268 of them across the dataset, and a strategy holding a position through a roll books a phantom profit or loss of a few hundred dollars per contract. Ingestion now records every roll timestamp alongside the bar caches. Backtests go flat through the roll and re-enter, which measured out to roughly zero cost, and the chart tool marks the same timestamps so a human reviewing trades can see the seam.
Rule #2: store several useful resolutions
Finer data requires more work. The 24-month dataset has 145K bars at 5 minutes, 2.9M bars at 15 seconds, and 7.8M bars at 5 seconds. A loop that is fast on 5-minute data can be expensive on 5-second data.
Each experiment loads the coarsest resolution that can answer its question. The strategy's execution timeframe determines the file.
I don't store every higher timeframe. When a strategy needs 15-minute or 20-minute bars, I resample them from 1-minute bars in milliseconds. All bars use label='left', closed='left' and are stamped with their open time. My loaders assert this rule, and not as decoration. The bug that forced the assertion cost me two years of subtly wrong bars, and it gets its own post.
Parquet also supports column selection, preserves data types, and makes it easy to load one day while debugging. For example, a test can read only time and close instead of the full table.
Rule #3: use NumPy first, then Numba
After data loading, the main costs are indicator calculation and the event loop. I optimize them in this order:
Tier 1: don't compute it. Indicator frames are computed once per dataset and reused across every strategy variant in a sweep. The prepare step (resample, indicators, multi-timeframe alignment) might take 60 seconds; the 29 variants that follow share it. This is the highest-leverage "optimization" in the codebase and it's just caching.
Tier 2: NumPy vectorization. NumPy handles most rolling means, ATR calculations, efficiency ratios, band arithmetic, and boolean masks without a Python loop.
Tier 3: Numba for sequential loops. Some calculations depend on the previous bar. Examples include ratcheting stops, trend state machines, and rolling structural fits. These loops may process millions of bars, so I compile them with @numba.njit.
The measured speedups ranged from 8x to 789x:
- The 8x cases are simple sequential loops such as band ratchets and state machines. Their main cost is Python interpreter overhead.
- The 789x case is a rolling structural fit. The original code called
np.nanquantileandnp.polyfitfor every bar. The Numba version reuses a buffer and computes the fit from running sums. Same math, three orders of magnitude less per-call machinery.
Two habits keep this honest. Profile before reaching for Numba, because compilation adds cost and debugging compiled code is miserable. And every Numba kernel keeps a NumPy reference implementation beside it, which defines the expected output.
Tier 4: keep the event loop readable. My backtest engine uses a plain Python loop for fills, orders, positions, and equity. It processes about 5K to 6K bars per second. A full 15-second backtest finishes in under ten minutes; a 5-minute backtest takes seconds. I could make the loop faster, but fill timing, costs, and lookahead rules are exactly where backtests go quietly wrong, and I want that code readable in one sitting. Optimization stops where auditability starts.
The workflow this enables
A typical run looks like this:
load 24 months of 15s bars ........ ~2 s (44 MB Parquet)
indicator prep, all timeframes .... ~60 s (once per dataset)
one full-history backtest ......... ~2-9 min (resolution-dependent)
one 10-day window backtest ........ ~30-60 sAn agent can test a new idea on a short window in about a minute. It can then run 20 to 30 variants in the background and send the strongest results through the full history. I review the results that pass those initial checks.
A few habits keep this workflow reliable:
- Deterministic, rebuildable caches. One command can recreate every derived file from the raw data.
- The same code path for short and long tests. A date filter changes the window. The backtest logic stays the same.
- Fast verification runs. After a data or engine change, I rerun the main backtests and compare the results. A large change often reveals that a strategy is sensitive to implementation details.
Takeaways
- Convert ticks to bars once. Store several resolutions in Parquet. In this case, over 300 GB of raw CSV becomes 108 MB at 5-second resolution.
- Match backtest resolution to the strategy, not to the finest data you own.
- Cache indicator frames across variant sweeps to avoid repeated work.
- Vectorize with NumPy, and reserve Numba for the genuinely sequential hot loops, where speedups ranged from 8x on simple state machines to orders of magnitude on per-bar re-fits.
- Keep the execution engine readable so fill timing and costs stay easy to audit.
- Assert your timestamp conventions in code. A two-character bug made that lesson expensive for me.