A backtest replays recorded market data through your strategy, simulates the fills and the
costs, and ends in numbers you can be wrong about cheaply. This page follows what a backtest needs: the data
and where it lives (a SQL database first, then kdb+ and the time-series databases, then plain Parquet files),
the clock the replay must run on, and finally a Parquet-backed backtester whose P&L is identical at every
replay speed until one call to time.time() breaks it.
A backtest answers one question: if this strategy had been running during that recording, what would it have done and what would it have earned? It never touches a market. It reads recorded events in the order they happened, hands each one to the strategy, turns the strategy's orders into simulated fills against the recorded book, charges the costs, keeps the books and, at the end, summarises the equity curve. Six stages, always the same ones, whether the tool is a 30-line script or a bank's research platform.
A backtest is the cheapest place to kill an idea: minutes of CPU instead of a Friday of real P&L. If a strategy loses on the tape after honest costs, it will not win live. The reverse is not true. A positive backtest is an estimate under a fill model, a cost model and one path of history; it is never a proof. Treat it like a lab result: reproducible, with its assumptions written down, and compared against a baseline (doing nothing, buying and holding).
on_tick lands on a different book 44 % of the time right after the AAPL print.The whole pipeline in a dozen lines, on four made-up ticks. The mid rises 0.6 % and the strategy still loses: it buys the ask one tick late, sells the bid one tick late, and pays 0.30 % on both legs.
# data -> replay -> strategy -> fills & fees -> accounting -> metrics
tape = [(1, 100.0, 100.2), (2, 100.4, 100.6), (3, 101.0, 101.2), (4, 100.6, 100.8)] # (t, bid, ask)
fee, cash, pos, pending, curve = 0.003, 10_000.0, 0, [], []
for t, bid, ask in tape: # replay on the logical clock
for side, q in pending: # orders sent at t-1 fill now
px = ask if side == "buy" else bid # a buy lifts the ask
sgn = 1 if side == "buy" else -1
cash -= sgn * q * px + q * px * fee; pos += sgn * q
pending = [("buy", 10)] if t == 1 else [("sell", 10)] if t == 3 else [] # strategy
curve.append(cash + pos * (bid + ask) / 2) # accounting: mark to the mid
dd = max(max(curve[: i + 1]) - e for i, e in enumerate(curve))
print(f"P&L {curve[-1] - 10_000:+.2f} max drawdown {dd:.2f} ticks {len(curve)}")P&L -6.04 max drawdown 8.02 ticks 4
Ticks t390–t480 of the Session 2 tape, all ten equities, replayed by a
JavaScript port of the lab's backtest_parquet.py loop. The strategy is the lab's event_ride: when the
CALENDAR_EVENT "AAPL earnings +6.2 %" arrives at t400 it buys 100 AAPL, paying at most the pre-event mid
× (1 + 3.1 %), and sells back after hold ticks. Orders fill at the next tick against the first recorded
book, walking the depth, and pay the taker fee. With the defaults it reproduces the Python run exactly: +505.36,
4 fills, digest e4009208c4dc. Press Step and watch the stages light up.
The venue's recording is one JSON line per message: book snapshots, trades, acks, cancels, rejects, session events, leaderboards, all mixed together in one 265 MB file. To ask it anything you must parse all of it. The first instinct of most engineers is right: load it into a relational database, give each message type a table with typed columns, and let SQL do the searching.
import sqlite3 and you have a database.t (the tick), seq (order of lines), and ts if you need wall time. Part 6 explains why.Here is that schema in SQLite, with 200,000 synthetic quotes, and the one query every
backtest asks: this symbol, this window of time. EXPLAIN QUERY PLAN shows what the database
will do. Before the index it reads every row (SCAN); after a composite index on
(symbol, t) it walks the B-tree straight to the first AAPL row at t390 and reads only the matching
range (SEARCH). Order matters in a composite index: equality column first, range column second.
import random, sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE trades (seq INTEGER PRIMARY KEY, t INTEGER NOT NULL, symbol TEXT NOT NULL,
price REAL NOT NULL, qty INTEGER NOT NULL, aggressor TEXT CHECK (aggressor IN ('buy','sell')));
CREATE TABLE quotes (seq INTEGER PRIMARY KEY, t INTEGER NOT NULL, symbol TEXT NOT NULL,
bid REAL, bid_qty INTEGER, ask REAL, ask_qty INTEGER);
CREATE TABLE book_snapshots (seq INTEGER PRIMARY KEY, t INTEGER NOT NULL, symbol TEXT NOT NULL, mid REAL);
CREATE TABLE book_levels (seq INTEGER REFERENCES book_snapshots, side TEXT, level INTEGER,
px REAL, qty INTEGER, PRIMARY KEY (seq, side, level));
""")
rng, syms = random.Random(4), ["AAPL", "MSFT", "NVDA", "TSLA", "AMZN"]
db.executemany("INSERT INTO quotes VALUES (?,?,?,?,?,?,?)",
[(i, i // 50, rng.choice(syms), 100.0, 5, 100.1, 5) for i in range(200_000)])
q = "SELECT count(*) FROM quotes WHERE symbol = 'AAPL' AND t BETWEEN 390 AND 460"
print("before:", db.execute("EXPLAIN QUERY PLAN " + q).fetchall()[0][-1])
db.execute("CREATE INDEX quotes_sym_t ON quotes (symbol, t)")
print("after: ", db.execute("EXPLAIN QUERY PLAN " + q).fetchall()[0][-1])
print("rows: ", db.execute(q).fetchone()[0])before: SCAN quotes after: SEARCH quotes USING COVERING INDEX quotes_sym_t (symbol=? AND t>? AND t<?) rows: 686
Same query, a growing table. Each square is a page of rows on disk. A full scan reads every page; an index reads one page per level of the tree, then only the leaf pages that hold the answer. The tree is shallow because each page holds hundreds of keys: depth grows with logfanout(rows). Writing is the other side of the trade: every insert must also update every index.
The index fixed the lookup. It did not fix the shape of the work. A backtest and most research queries read a few columns of many rows in time order, and they arrive as a firehose of appends. A relational row store was built for the opposite: read or update a whole row, by key, many times a second, with transactions. Five places it hurts:
Each row's columns sit together on the page. "AAPL mid at every tick" needs 2 of the book's 19 columns, and the database still reads every byte of every matching row, plus the rows of the other symbols that share its pages.
Every insert writes the row, every index (one B-tree path each), and the log, inside a transaction. The arena tape has a median of 46 lines per tick and peaks at 269; a real feed is millions of messages a second. Bulk-load and drop indexes, or fall behind.
Research is "everything between t390 and t460", across all symbols, then aggregate. A B-tree finds the start fast; the scan itself is still row by row through pages full of columns you did not ask for.
"The quote in force when each trade happened" has no
operator in standard SQL. You write a correlated subquery or a LATERAL join: one index probe per
trade at best, a nested loop at worst. It works, it reads badly, and it is easy to get subtly wrong.
No column compression, a per-row header, and a depth list exploded into twenty child rows per snapshot. The database is 50,081,792 bytes; the same tape in Parquet is 6,740,873.
Limit 4 in code: the as-of join in SQLite on seven real AAPL trades from t400, each
matched to the last quote with seq at or before its own. Notice trade 21026: the quote in force has a
bid of 198.17 and no ask at all. The book was one-sided in the middle of the ramp; any fill model that
assumes "there is always an ask" is wrong right where the money is.
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE quotes (seq INTEGER PRIMARY KEY, symbol TEXT, bid REAL, ask REAL)")
db.execute("CREATE TABLE trades (seq INTEGER PRIMARY KEY, symbol TEXT, price REAL, qty INTEGER)")
db.executemany("INSERT INTO quotes VALUES (?,?,?,?)", [ # AAPL, Session 2 tape, t399-401
(20889, "AAPL", 193.94, 194.13), (20913, "AAPL", 193.94, 195.14), (20932, "AAPL", 193.94, 196.06),
(20943, "AAPL", 193.94, 196.21), (20958, "AAPL", 196.06, 196.21), (20992, "AAPL", 198.17, None),
(21131, "AAPL", 196.06, 197.18), (21184, "AAPL", 196.20, None)])
db.executemany("INSERT INTO trades VALUES (?,?,?,?)", [
(20912, "AAPL", 194.13, 3), (20931, "AAPL", 195.14, 4), (20942, "AAPL", 196.06, 2),
(20955, "AAPL", 196.20, 20), (20987, "AAPL", 196.21, 23), (21026, "AAPL", 196.92, 29),
(21179, "AAPL", 197.18, 36)])
asof = """SELECT tr.seq, tr.price, q.seq, q.bid, q.ask FROM trades tr
JOIN quotes q ON q.seq = (SELECT max(seq) FROM quotes -- the LAST quote
WHERE symbol = tr.symbol AND seq <= tr.seq)"""
for row in db.execute(asof):
print(row)(20912, 194.13, 20889, 193.94, 194.13) (20931, 195.14, 20913, 193.94, 195.14) (20942, 196.06, 20932, 193.94, 196.06) (20955, 196.2, 20943, 193.94, 196.21) (20987, 196.21, 20958, 196.06, 196.21) (21026, 196.92, 20992, 198.17, None) (21179, 197.18, 21131, 196.06, 197.18)
Six real AAPL and MSFT top-of-book rows from t399–t400. Pick a query and a layout. The strip shows the same 36 values as they sit on disk, in pages of six. A page is the unit of I/O: touch one value and you read the whole page.
Banks and trading firms met these limits in the 1990s and answered with a different machine: kdb+, a column store with its own language, q. It is still the reference for tick data on most desks. Three ideas carry over to every tool after it, including the Parquet files you will build in the lab.
A table is a set of equal-length vectors. "Average mid" reads the bid and ask vectors and nothing else, and a vector of floats compresses and streams through the CPU far better than mixed rows.
A tickerplant logs every message and publishes it to a real-time database (today, in memory). At end of day it is written to the historical database: one directory per date, each table splayed into one file per column. A query on one date never opens the others.
Data is stored sorted by time within symbol
(the `p# parted attribute on sym), so a window is a contiguous slice, and the
as-of join aj is a built-in operator rather than a subquery.
/hdb the historical database: one directory per date
sym the symbol enumeration, shared by every table
2026.10.08/
2026.10.09/ a partition
quote/ a splayed table: a directory ...
.d ... with the column order
time sym bid ask bsize asize ... and ONE FILE PER COLUMN
trade/
.d time sym price size side
\l /hdb / map the partitioned database (no load step) / AAPL quotes in one minute: the date picks a directory, `p#sym jumps to AAPL's rows, / and only the four columns named are read from disk select time, bid, ask, mid:0.5*bid+ask from quote where date=2026.10.09, sym=`AAPL, time within 15:46:00 15:47:00 / one-minute bars for every symbol: q-sql's "by" is GROUP BY, xbar buckets time select hi:max price, lo:min price, vwap:size wavg price by sym, 1 xbar time.minute from trade where date=2026.10.09 / the as-of join: each trade gets the quote in force at its time (last time <= trade time) t: select time, sym, price, size from trade where date=2026.10.09 q: select time, sym, bid, ask from quote where date=2026.10.09 aj[`sym`time; t; q]
q reads right to left, so 0.5*bid+ask is half of (bid + ask).
kdb+ is commercial; KX also offers KDB-X Community, free for personal and commercial use within limits (16 GB of RAM, one instance). The course does not require it. The q above is there so
you can read it on a desk, not so you can run it tonight.
| System | What it is | As-of join | Where you meet it |
|---|---|---|---|
| kdb+/q | In-memory + date-partitioned column store, its own language | aj, wj (window join) | Bank and HFT tick capture and research |
| TimescaleDB | A Postgres extension: tables split into time chunks, old chunks compressed to columnar form | No keyword; a LATERAL subquery | Teams that want SQL and Postgres tooling for time series |
| QuestDB | Open-source column store, partitioned by time, built for fast ingest | ASOF JOIN, LT JOIN, SAMPLE BY | Market-data capture with SQL |
| InfluxDB | Metrics store: measurements, tags, fields, a line protocol | Not its job | Monitoring your systems (latency, queue depth), not research |
| ArcticDB | Versioned, chunked pandas DataFrames on S3 or local disk, from Man Group | In pandas after the read | Python research teams storing dataframes by date |
| DuckDB | In-process analytical SQL engine; reads Parquet, CSV and JSON where they lie | ASOF JOIN | This course: the open-source analogue for the lab |
The same as-of join as Part 3, in pandas and in DuckDB. One line each, the same answer, and both of them sort-merge the two inputs instead of probing once per trade.
import duckdb, pandas as pd
quotes = pd.DataFrame({"seq": [20889, 20913, 20932, 20943, 20958, 20992, 21131, 21184],
"bid": [193.94, 193.94, 193.94, 193.94, 196.06, 198.17, 196.06, 196.20],
"ask": [194.13, 195.14, 196.06, 196.21, 196.21, None, 197.18, None]})
trades = pd.DataFrame({"seq": [20912, 20931, 20942, 20955, 20987, 21026, 21179],
"price": [194.13, 195.14, 196.06, 196.20, 196.21, 196.92, 197.18]})
quotes["symbol"] = trades["symbol"] = "AAPL"
pdj = pd.merge_asof(trades, quotes, on="seq", by="symbol", direction="backward")
ddj = duckdb.sql("""SELECT tr.seq, tr.price, q.bid, q.ask FROM trades tr
ASOF JOIN quotes q ON tr.symbol = q.symbol AND tr.seq >= q.seq
ORDER BY tr.seq""").df()
print(pdj[["seq", "price", "bid", "ask"]].to_string(index=False))
print("pandas == DuckDB:", pdj[["bid", "ask"]].equals(ddj[["bid", "ask"]]))seq price bid ask 20912 194.13 193.94 194.13 20931 195.14 193.94 195.14 20942 196.06 193.94 196.06 20955 196.20 193.94 196.21 20987 196.21 196.06 196.21 21026 196.92 198.17 NaN 21179 197.18 196.06 197.18 pandas == DuckDB: True
Seven real AAPL trades and nine quotes from t399–t401. For each trade the
join looks for the last quote at or before it on the key you choose. Join on seq (recording order) and
the trade prices line up with the ask in force when each buyer traded. Join on t (the tick) and every trade in tick 400 gets the book from
the end of tick 400, recorded after it traded: one-sided, no ask. Switch to forward to see the quote
that came next; that is what a markout uses after the fact, and a look-ahead bug inside a backtest.
| trade seq | t | price | matched quote | bid | ask | check |
|---|
book_eot view keeps the last snapshot of each (symbol, tick) by seq,
and every as-of join on t goes through it, so DuckDB, Polars and pandas return the same numbers. Without
it, AAPL's up to 17 snapshots inside one tick let each engine pick a different one, silently.Parquet is an open file format that stores a table by column, cut into row groups, with statistics in the footer: kdb+'s column idea, in a file. DuckDB queries the files in place, with no load step and no server. It is also what the backtester in Part 7 reads.
t BETWEEN 390 AND 460 is checked against each row group's min/max in the footer; groups that cannot match are never read. It only works because the file is sorted by tick.symbol (10 values) drops from 36,388 bytes to 170 with a dictionary.Converting the whole tape took 3.3 s and made it 39.3× smaller. Be honest about that ratio: 82 % of the JSONL is leaderboard messages, and the Parquet tape keeps ten fields per account rather than the full position maps. The fair comparison is the book, every depth level kept: 25.5 MB of JSON to 1.7 MB. Timings are best of several runs on one laptop; yours will differ, the ratio will not.
Row groups, footer statistics and dictionary encoding in a dozen lines of pyarrow, on synthetic data so it runs anywhere:
import os, tempfile
import pyarrow as pa, pyarrow.parquet as pq
n = 100_000 # 100k quotes, sorted by tick
tbl = pa.table({"t": [i // 35 for i in range(n)],
"symbol": [("AAPL", "MSFT", "NVDA", "TSLA", "AMZN")[i % 5] for i in range(n)],
"mid": [100 + (i % 997) / 100 for i in range(n)]})
path = os.path.join(tempfile.mkdtemp(), "quotes.parquet")
pq.write_table(tbl, path, row_group_size=10_000, compression="zstd")
md = pq.ParquetFile(path).metadata
stats = [md.row_group(g).column(0).statistics for g in range(md.num_row_groups)]
keep = [g for g, s in enumerate(stats) if not (s.max < 1000 or s.min > 1300)]
print(f"{md.num_row_groups} row groups; t in [1000, 1300] can only be in {keep}")
sym = md.row_group(0).column(1)
print(f"symbol column: {sym.encodings[-1]}, {sym.total_uncompressed_size / 10_000:.2f} bytes per value")
got = pq.read_table(path, columns=["t", "mid"], filters=[("t", ">=", 1000), ("t", "<=", 1300)])
print(f"read {got.num_rows:,} rows x {got.num_columns} of 3 columns")10 row groups; t in [1000, 1300] can only be in [3, 4] symbol column: RLE_DICTIONARY, 0.39 bytes per value read 10,535 rows x 2 of 3 columns
And the lab's Parquet tape queried in place with DuckDB: average spread of AAPL and MSFT in the fifteen ticks after the AAPL print. One SQL statement over a folder of files.
import duckdb
path = "/tmp/w4/tape.parquet/book_snapshot/**/*.parquet" # the lab's Parquet tape
q = """SELECT symbol, count(*) AS snaps, round(avg(1e4 * spread / mid), 1) AS spread_bp
FROM read_parquet('%s', hive_partitioning = true)
WHERE t BETWEEN 400 AND 414 AND symbol IN ('AAPL', 'MSFT')
GROUP BY symbol ORDER BY symbol""" % path
print(duckdb.sql(q))┌─────────┬───────┬───────────┐ │ symbol │ snaps │ spread_bp │ │ varchar │ int64 │ double │ ├─────────┼───────┼───────────┤ │ AAPL │ 52 │ 136.9 │ │ MSFT │ 29 │ 5.1 │ └─────────┴───────┴───────────┘
All 55,375 book snapshots of the block in one Parquet file, 14 row groups. The statistics below are the file's real footer. Move the window and watch which groups the reader must open. Then re-sort the same rows by symbol and try again: the tick filter stops pruning, and the symbol filter starts. Tick the columns your query needs: bytes read are the real compressed column-chunk sizes.
Every line of the tape carries two clocks. Event time is when something happened in the market's own order of events. Processing time is when some machine got around to writing it down. Streaming systems make the same distinction (Flink, Kafka Streams, Beam). A backtest that confuses them is not a backtest of your strategy; it is a benchmark of your laptop.
| Field | Clock | What it means | May a decision read it? |
|---|---|---|---|
tick / t | logical (event time) | The engine's step counter. t = tick − 824,202 on this tape, so "AAPL earnings at t400" is t == 400 in every table. | Yes: this is "now" |
seq | logical (order) | Recording order. Several book states share one tick (AAPL: median 2, up to 17); seq orders them. | Yes, for order within a tick |
ts | real (processing time) | The venue's wall clock when it wrote the line. | No. Only to pace a replay |
"Late" against a clock that assumes one tick per second: such a clock put the AAPL print at 15:46:40; the tape says 15:46:52. A bot that waited for "15:46:40" traded twelve seconds early, on the old price. On Friday, read the tick counter, not the clock.
Until 2026-10-09 the simulator resumed a halted symbol with a
wall-clock sleep. Same seed, two laptops: the fast one (0.2 ms a tick) stayed halted for 235 ticks and
made +49.79; the slow one (0.6 ms a tick) for 85 ticks and made +103.15. Counting the halt in ticks
(tick + 5): 5 ticks and +56.90 on both, every run. sleep(0.05) lasts 0.05 s divided by the
time per tick, so its length in market time depends on the machine. tick + 5 does not.
Give a live on_tick 400 ms to think. Over the whole block it lands
on the book it decided on: 98 % of decisions see the same best ask. In the fifteen ticks after the AAPL print,
44 % land on a different best ask and one in ten on a mid 81 bp away. A replay with no latency fills
on the decision book and invents that P&L. Hence the rule in Part 1's loop: an order decided at t
reaches the venue during t + 1, and fills against the first book of that tick.
The exit timer of the lab's strategy, both ways, on the real tick timestamps of
t400–t419. Counted in ticks the exit is t415 at every speed. Timed with time.time() it depends on how
fast you replay:
# wall-clock ts of ticks t400..t419 on the Session 2 tape, in seconds after t400
ts = [0.0, 1.0, 2.16, 3.35, 4.48, 5.48, 6.48, 7.58, 8.72, 9.87]
ts += [10.52, 11.68, 12.8, 13.86, 14.58, 15.78, 16.87, 17.97, 18.56, 19.58]
def exit_tick(speed, clock, t_in=400, hold=15):
for i, sec in enumerate(ts):
wall = sec / speed + 0.0003 * i # what time.time() reads during the replay
if clock == "logical" and i >= hold: # now - t_in >= 15 ticks
return f"t{t_in + i}"
if clock == "wall" and wall >= hold * 1.06: # time.time() - wall_in >= 15.9 s
return f"t{t_in + i}"
return "not before t420"
for speed in (float("inf"), 5, 1):
print(f"speed {speed:>3}: exit on ticks {exit_tick(speed, 'logical')}, on time.time() {exit_tick(speed, 'wall')}")speed inf: exit on ticks t415, on time.time() not before t420 speed 5: exit on ticks t415, on time.time() not before t420 speed 1: exit on ticks t415, on time.time() t416
The top axis is logical time: ticks t390–t480, evenly spaced. The bottom axis
is the wall clock while you replay them, using each tick's real ts divided by the speed. The position opens
at t400. Its exit is either "15 ticks later" or "15.9 s of time.time() later" (15 × the tape's 1.062 s
average). The P&L, fills and digest come from the same engine as Part 1, with time.time() simulated
exactly as the replay would see it; the measured column is the real Python run.
| speed | logical clock: P&L · fills · digest | time.time(): P&L · fills · digest |
|---|
Measured: python backtest_parquet.py --strategy event_ride --from-t 390 --to-t 480 --speed S [--realclock],
one run each, Python 3.13, polars 2.0.0. The logical-clock column is identical at every speed; the other one is not,
and the fastest replay, the one you would run a thousand times in a parameter search, looks best.
Put the pieces together. The lab's backtest_parquet.py reads the Parquet tape with Polars
(2,827 ticks and 50,751 book snapshots load in a few hundred milliseconds) and runs Part 1's loop once per tick
t, the logical clock: apply the tick's book snapshots in seq order; fill each order due at
t against the first book of its symbol; deliver the tick's events with on_event(ev, now=t);
collect decisions from on_tick(now=t, market, pf) as orders due at t + latency; mark to the
mid. The strategy is told the time. It never asks the machine.
Every fill is a taker fill at 0.30 %, the venue's taker rate on this tape, and walks the recorded depth. Read a result against the baselines, never alone (whole block, lab step 6):
| strategy | P&L | IS t<1400 | OOS | max DD | fills |
|---|---|---|---|---|---|
| event_ride | +1,201 | −255 | +1,455 | 1,245 | 62 |
| do_nothing | +198 | −1,257 | +1,455 | 1,931 | 0 |
| buy_hold | +1,203 | +792 | +411 | 3,308 | 56 |
| momentum | −16,501 | −8,708 | −7,793 | 16,858 | 1,187 |
event_ride earns $1,003 more than doing nothing with 38 % of buy-and-hold's drawdown. Its OOS equals do_nothing's: the second half has no events, so the edge lives only where the message is. Momentum pays 30 bp a trade, 1,187 times.
Choose momentum's lookback on the first half only (t < 1400), then run the second half once:
| lookback | IS | OOS (not looked at) |
|---|---|---|
| 5 ← chosen | −4,006 | −10,313 |
| 10 | −5,904 | −10,366 |
| 20 | −8,708 | −7,793 |
| 40 | −12,876 | −9,674 |
| 80 | −15,764 | −9,501 |
| 160 | −22,516 | −6,869 |
The best in-sample setting is among the worst out of sample. The split is chronological and you touch the second half once; Week 7 rolls it forward (walk-forward).
The classic bug, sig[t] * ret[t], decides at t with the close of t and is paid the
move into t. In clock terms: a decision at logical time t read data stamped after it.
In a vectorised backtest it is one missing shift(1):
import numpy as np, pandas as pd
rng = np.random.default_rng(7)
px = pd.Series(100 * np.cumprod(1 + rng.normal(0.0002, 0.01, 1500)))
ret, ma = px.pct_change(), px.rolling(40).mean()
sig = (px > ma).astype(float) # long above the 40-bar SMA
sharpe = lambda s: s.dropna().mean() / s.dropna().std() * 252 ** 0.5
print(f"BUG sig[t] * ret[t] Sharpe {sharpe(sig * ret):+.2f}")
print(f"FIX sig[t-1] * ret[t] Sharpe {sharpe(sig.shift(1) * ret):+.2f}")BUG sig[t] * ret[t] Sharpe +2.38 FIX sig[t-1] * ret[t] Sharpe -0.19
On the tape (lab step 6, all ten names pooled, a 40-tick mean, no costs): BUG +1.020 bp/tick, t-stat +10.4;
FIX −0.390 bp/tick, t-stat −4.2; holding every name +0.101 bp/tick. In the event loop the strategy is handed the
market as of t and there is no index to get wrong, so the lab has to plant the bug (--peek N: see
the book N ticks ahead). Without fees, peeking five ticks turns momentum's −$6,570 into +$8,614. The check for Phase 2:
shift your feature by one tick. A real edge degrades a little; a leak collapses.
Long AAPL when the mid is above its moving average, flat otherwise, over all 2,827 ticks of the block (end-of-tick mids, carried forward when AAPL did not update). The dashed line is t1400, the lab's in-sample/out-of-sample split. Toggle the lag and the cost per position change.
time.time(), three P&LsPart 6's table is the proof the backtester is deterministic: the slice t390–t480 replayed at ∞, 5× and 1× gives +505.36 and the same fill digest every time. Speed is a viewing choice. Then the lab changes one line, the exit timer, to read the wall clock, and the same three runs give +902.06, +861.70 and +456.60, with three different digests. Now find the same bug in your own bot:
grep -nE 'time\.time|monotonic|datetime\.now' team/*.py # or wherever your bot lives
Every hit inside a decision path (entry, exit, cooldown, "once a minute") is a backtest that depends on your CPU. Hits at the edges (socket timeouts, reconnect backoff, heartbeats, log lines, pacing a replay) are fine.
9 skills. Tick one only when you can say the sentence and then do it from an empty file, with no notes open. Ticks stay in this browser only (nothing is sent anywhere) and are shared with the skills dashboard, where the other weeks live.
On a desk the research loop is only as fast as the question you can ask of the history. If every question means parsing a quarter of a gigabyte of JSON, you ask three a day and stop exploring; if it costs ten milliseconds, you ask a thousand and actually test the idea. That is why tick data lives in column stores, kdb+ on most bank and HFT desks and Parquet files queried in place nearly everywhere else, while orders, positions and accounts stay in a relational database where transactions matter. Choosing the store by the shape of the work is a decision you will be asked to defend.
The as-of join is the most used and most abused operation in market-data research. Every fill analysis, every markout and every transaction-cost report matches events to the quote in force, and the key and direction decide whether the answer is honest. On the Session 2 tape, joining on the tick alone hands every AAPL trade in tick 400 a one-sided book recorded after it traded; joining forward is exactly right for a post-trade markout and exactly wrong inside a backtest. Write the definition down (the lab's book_eot) and make every engine use it.
Determinism is what turns a backtest into evidence. If the same tape and the same code can produce two P&Ls, a parameter search is measuring your machine, and the fastest replay, the one you run a thousand times, is the one that lies most: on the AAPL slice the wall-clock exit made +902 at full speed against +505 for the honest run. Desks enforce this by injecting the clock into the strategy, replaying recorded production days through the live code path, and comparing fill digests in CI, so that any difference between replay and live is a real difference and not an accident of timing.
Costs and latency are positions you hold whether you model them or not. At the arena's 0.30 % taker fee, momentum lost 16,501 over the block through 1,187 fills, and the in-sample tuning chose the lookback that lost most out of sample. A latency of one tick decided whether event_ride caught the 36 shares offered at 197.18 during the AAPL ramp or found an empty ask side. Report P&L net of fees against baselines, count the trials you ran, and treat latency as a number of ticks you measure, because your backtest will otherwise invent exactly the trades that pay.
course/systematic-trading/week4_demos/backtest_parquet.pythe lab's backtester. One loop per tick of the tape, the logical clock; the strategy is told the time (now=t) and never asks the machine. The wall clock appears once, to pace a replay, and never changes what is decided.
for i, t in enumerate(tape.ticks):
if math.isfinite(speed) and t in tape.ts_at: # pacing only
wait = (tape.ts_at[t] - t0_ts) / speed - (time.time() - t0_wall)
if wait > 0:
time.sleep(wait)
...
for ev in tape.events_at.get(t, []): # 3. events
strategy.on_event(ev, now=t)
for sig in strategy.on_tick(now=t, market=view, pf=pf) or []: # 4. decisions
pending[t + latency].append(sig) # fill at t + latency
8 questions a quant-developer interviewer could ask about this week: 2 warm-up, 4 core, 2 senior. Answer out loud first, then open the model answer.
It is a cheap way to falsify a strategy: replay recorded events in order, let the strategy see only the past, simulate fills and costs, and see whether it still makes money against a baseline. A loss after honest costs is strong evidence; a profit is only an estimate under the fill model, the cost model and one path of history. It cannot show your own market impact, because the recorded book never reacts to you, nor your queue position, a regime missing from the data, unmodelled latency, or the failures that only happen live, such as reconnects and rejects.
Related skill: The backtest pipeline and its goalA column store. The workload is append-only and reads a few columns of many rows in time order, such as the bid and ask of one symbol over a window. A column store reads only those columns, compresses each one well because the values are similar, and keeps data sorted by time within symbol so a window is a contiguous slice. A row store reads every column of every matching row and pays an index update on every insert. Orders, positions and accounts are the opposite workload, small and transactional, and belong in a relational row store such as Postgres.
Related skill: Column stores & time-series databasesOne table per message type with typed columns: trades (seq, t, symbol, price, qty, aggressor), quotes (seq, t, symbol, bid, bid_qty, ask, ask_qty), and book snapshots with the depth in a child table keyed by (snapshot, side, level). Keep the event clock, the recording order and, separately, the wall-clock timestamp. Index (symbol, t), equality column first and range second, and check with the query plan that it searches rather than scans. What hurts first is the write path, since every insert updates every index, then the size of the exploded depth table and the cost of as-of joins written as correlated subqueries.
Related skill: Tick data in a relational databaseFor each row on the left, take the last row on the right whose key is at or before it, usually by symbol: each trade gets the quote in force when it happened. In pandas it is merge_asof(trades, quotes, on='time', by='symbol', direction='backward'); in DuckDB, trades ASOF JOIN quotes ON trades.symbol = quotes.symbol AND trades.time >= quotes.time; in q, aj[`sym`time; trade; quote]. It goes wrong silently when the key is too coarse, so several quotes share a timestamp and an arbitrary one wins, when inputs are unsorted or the key dtypes differ, or when someone uses a forward match inside a backtest, which is look-ahead.
Related skill: The as-of joinFirst whether the files are sorted by the filter column: predicate pushdown skips a row group only if its min/max statistics exclude the range, and a file sorted by symbol has every row group spanning the whole day. Then whether the query names only the columns it needs, since projection is the other half of the saving. Then row-group size, since huge groups prune coarsely and tiny ones bloat the footer. Finally read the plan with EXPLAIN in DuckDB to see whether the filter and the projection were actually pushed into the Parquet scan.
Related skill: Parquet: columns, row groups & predicate pushdownFill at the next tick, not the decision tick, against the book recorded at that moment, walking the depth up to the limit price and dropping or resting the remainder by order type; charge the taker fee on the notional. Treat latency as an integer number of ticks and test several values, because on a thin book around an announcement the result is lumpy: on the AAPL earnings ramp an order arriving one tick after the decision filled 36 shares, while zero or two ticks found no ask under the cap. Expect one-sided books and empty levels exactly when the news hits, and never assume the mid.
Related skill: Fill simulation, costs & latencyMake the run reproducible enough to compare: record a digest of the fill list, rerun at two replay speeds and on two machines, and find the first fill that differs. The usual cause is a decision that reads the machine rather than the data: time.time(), datetime.now() or a sleep-based cooldown, so its length in market time depends on CPU speed. Others are unseeded randomness, iteration over an unordered container, threads racing on shared state, or parallel workers merging results in completion order. Fix it by handing the strategy the logical time and an injected clock, and keep the wall clock at the edges only.
Related skill: A deterministic replay on the logical clockLook for leaks first, because they are cheap to find: lag the signal by one tick and see whether the edge degrades a little or collapses, check every as-of join for its key and direction, and check that nothing reads restated or future reference data. Then the fill model: next-tick fills against recorded depth, the real fee schedule, latency in ticks, one-sided books. Then compare with baselines and count the trials behind the chosen parameters, and look at the out-of-sample block they never touched. Finally replay at two speeds and confirm the same fill digest, to rule out a wall-clock dependency.
Related skill: Look-ahead biasThe lab, the homework and the participation quizzes for this week are in the Week 4 module on Canvas. Every script on this page runs as shown: the snippets need only Python with pandas, pyarrow and DuckDB; the queries on the real tape need the Session 2 tape converted to Parquet, which the lab does in step 2.
make install.