Key Takeaways
- This tutorial explains how to backtest a dual moving average crossover strategy with KDB-X Python.
- It converts eight days of minute-level AAPL and MSFT data into 15-minute OHLCV bars.
- It uses fast and slow moving averages to generate buy and sell signals for each symbol.
- A KDB-X as-of join matches each sell signal with the most recent preceding buy signal.
- The backtest calculates PnL, win rate, holding periods, maximum drawdown, and the effect of changing the moving average parameters.
You can backtest a moving average crossover strategy with KDB-X Python by converting historical market data into time-based bars, calculating fast and slow moving averages, and measuring the resulting trades.
This KDB-X Python tutorial uses eight days of minute-level AAPL and MSFT data from Yahoo Finance to build 15-minute bars and calculate profit and loss (PnL), win rate, cumulative PnL, and maximum drawdown.
The strategy: moving average crossover
The notebook builds a classic dual moving average crossover, which is a trend-following approach traders have used for decades:
- Fast MA: a 10-period moving average, responsive to recent price action
- Slow MA: a 30-period moving average, tracking the longer-term trend
- Buy signal: fast MA crosses above slow MA — bullish momentum
- Sell signal: fast MA crosses below slow MA — bearish momentum
The description sounds simple, but turning that into disciplined repeatable code is where KDB-X Python steps up.
Step 1: Pull real data
The notebook doesn’t use generated or synthetic prices. It pulls 8 days of minute-level data for AAPL and MSFT straight from Yahoo Finance via yfinance, then converts the resulting pandas DataFrame into a KDB-X Python table with one powerful call:
quotes = kx.toq(data_long)This is really the main advantage KDB-X Python: your data crosses from the pandas world into q‘s high-performance analytics engine without friction, so every calculation that follows benefits from kdb+‘s speed.
Step 2: Aggregate into clean 15-minute bars
The tutorial then buckets the minute data into 15-minute OHLCV bars using xbar, rounding timestamps down to the nearest interval, then aggregating open, high, low, close, and volume per bucket and per symbol:
quotes = quotes.update(columns={'bucket': '15 xbar time'})
bars = quotes.select(
columns={
'open': 'first Open',
'high': 'max High',
'low': 'min Low',
'close': 'last Close',
'volume': 'sum Volume'
},
by=[
kx.Column('sym'),
kx.Column('date'),
kx.Column('bucket')
]
)This is standard OHLC bar construction, but notice the simplicity of the code: there are no loops and no manual grouping logic. by handles the per-symbol split natively.
Step 3: Calculate moving averages and detect crossovers
With the data neatly in bars, the notebook computes both moving averages using mavg, then uses prev to compare each bar against the one before it: The mechanism that actually catches a crossover the moment it happens:
bars = bars.update(
columns={
'ma_fast': kx.Column('close').mavg(10),
'ma_slow': kx.Column('close').mavg(30)
},
by=kx.Column('sym')
)A bullish signal is created when the fast MA was below or equal to the slow MA and is now above it; bearish is the mirror image. Running this by sym ensures calculations are performed separately for each ticker, meaning no data leakage between symbols.
Step 4: Pair entries with exits using an asof join
To turn signals into trades, you need to match every buy with the sell that follows it. The notebook does this with aj: an “asof” join that finds “the most recent match as of a time,” which is exactly what pairing a trade entry with its exit requires:
positions = kx.q(
'{[s;b] aj[`sym`exit_dt; s; update exit_dt:entry_dt from b]}',
sells, # First argument: s (sells)
buys # Second argument: b (buys)
)This is the step where time-series databases separate themselves from general-purpose tools. Asof joins are a first-class operation in KDB-X, not a workaround.
Step 5: Measure what actually happened
From there, the tutorial builds out the full performance picture:
- PnL per position: dollar and percentage returns, plus holding period
- Win rate and per-symbol breakdown: which ticker the strategy actually worked on
- Cumulative PnL: an equity curve built with
sums, a running total - Maximum drawdown: the largest peak-to-trough decline, calculated with
maxsagainst the cumulative curve
And it doesn’t stop at one parameter set. The final section swaps in a faster 5/20 MA combination and compares signal frequency against the original 10/30. This is a quick, practical introduction to parameter sensitivity analysis, with a challenge left for you to calculate the PnL yourself.
Why this matters at scale
The dataset here is small on purpose: 8 days, two tickers, easy to follow line by line. But the same code runs unchanged against years of tick data and thousands of instruments.
xbar, mavg, aj, by — none of that logic changes when you move from 10,000 rows to 10 billion. That’s the practicality of KDB-X Python: you learn the syntax once, on a small notebook, and it holds up in production.
Try it yourself
If you want to go further than the tutorial, the notebook itself points the way: add transaction costs, real position sizing, stop losses, and walk-forward testing before you trust a strategy with real capital.
