Time series with pandas

Market data is a time series: values attached to timestamps, in order, with gaps for weekends and holidays. pandas is the standard Python tool for it, and most of a quant researcher's day is spent turning prices into returns, returns into rolling statistics, and statistics into signals. This lesson covers the operations that matter most and the one mistake that turns an honest backtest into fiction.

Series, index and returns

A pandas Series is an array with an index. For market data the index is dates, and pandas uses it to align everything: adding two series lines them up by date, not by position. That one feature removes a whole class of off-by-one bugs, as long as every series carries its dates.

Returns come from prices with pct_change(), which computes $P_t / P_{t-1} - 1$. The first value is NaN because there is no previous price. Leave it as NaN; filling it with 0 invents a day with no move.

Most statistics are computed on returns rather than prices. Prices trend and wander, so their mean and variance depend on the period you pick. Returns are much closer to stable from one period to the next, which is what statistics need.

Rolling windows

Volatility changes over time, so a single number for the whole history is not enough. The standard estimate is a rolling standard deviation of daily returns over the last $w$ days, annualised by multiplying by $\sqrt{252}$ (random walks explains why):

rolling_vol = returns.rolling(21).std() * np.sqrt(252)

The value on each date uses that date and the $w - 1$ days before it, never later data. Choosing $w$ is a real trade-off. Below, the true volatility jumps from 15% to 40% on day 300 and falls back on day 450:

With a 5-day window the estimate catches the jump within a few days but swings wildly in calm periods. With 120 days it is smooth, but it takes around two months to get halfway to the new level. No window is best for everything: risk limits want fast reactions; position sizing often prefers stability.

EWMA (exponentially weighted) volatility is the usual compromise. Instead of a hard cut-off, each older day's squared return gets a weight smaller by a constant factor. Switch the chart to EWMA: for the same nominal length it reacts faster than a rolling window and never has the rolling window's sudden drop when one large day falls out of the window.

Key idea. Every rolling estimate trades reaction speed against noise. Pick the window for the job it does, and look at how it behaves around a jump before trusting it.

Resampling

Daily data is often converted to weekly or monthly. resample("W-FRI").last() takes the last price in each week ending Friday; returns are then computed on those weekly prices. Resample prices and recompute returns rather than averaging daily returns, which gives a different and usually meaningless number.

Look-ahead bias

A trading signal computed at today's close can only be traded from tomorrow. In pandas the fix is one call, shift(1), which moves each value one row later. Forget it, and each day's position is decided using that day's own return. The backtest then looks spectacular, and none of it is real.

This is the most common serious bug in backtesting, and it is easy to commit without noticing: a rolling statistic that includes today, a signal aligned with the wrong date after a merge, or data that was only published days after the date it is stamped with. The look-ahead bias lesson covers the subtler versions.

Key idea. Before multiplying a signal by returns, ask: "could I have known this signal before this return happened?" If you can't answer yes for every row, shift it.

In code

import numpy as np
import pandas as pd

rng = np.random.default_rng(4)
days = pd.bdate_range("2024-01-01", periods=500)  # business days only
vol = np.where(np.arange(500) < 250, 0.15, 0.40) / np.sqrt(252)  # volatility jumps on day 250
prices = pd.Series(100 * np.exp(np.cumsum(rng.standard_normal(500) * vol)), index=days)

returns = prices.pct_change()  # first value is NaN: there is no previous price
print(returns.head(3).round(4))

# Trailing 21-day volatility, annualised; label each value with the last day it uses.
rolling_vol = returns.rolling(21).std() * np.sqrt(252)
ewm_vol = np.sqrt((returns**2).ewm(span=21).mean() * 252)
jump = days[250]
print(f"21 days after the jump: rolling {rolling_vol.iloc[271]:.1%}, EWMA {ewm_vol.iloc[271]:.1%}")

# Weekly prices and returns: take the last price of each week (Friday-ending weeks).
weekly = prices.resample("W-FRI").last().pct_change().dropna()
print(f"{len(returns.dropna())} daily returns -> {len(weekly)} weekly returns")

# A signal must only use information available before the return it trades.
signal = np.sign(returns.rolling(5).mean())  # 5-day momentum, known at today's close
strategy = signal.shift(1) * returns  # trade it from tomorrow
cheating = signal * returns  # uses today's return to decide today's position
print(f"honest mean daily return {strategy.mean():.5f}, look-ahead version {cheating.mean():.5f}")

The first return is NaN, as expected. Twenty-one days after the jump both estimates have caught up (41.5% rolling, 40.8% EWMA, against a true 40%). Resampling turns 499 daily returns into 99 weekly ones.

The last line is the one to remember. The honest strategy averages 0.00115 a day, and on random data that is luck. The look-ahead version averages 0.00612 a day, more than five times as much and about 150% a year, from a strategy with no edge at all. The only difference is the missing shift(1).

Where this shows up in quant work

• Risk. Rolling and EWMA volatility feed position sizes and risk limits every day. RiskMetrics popularised EWMA variance for exactly this.
• Signal research. Momentum, mean reversion and most technical signals are rolling statistics, each with a window to choose and a shift to get right.
• Data alignment. Joining prices, fundamentals and alternative data by date, with each value stamped at the time it became available, is a large part of real research work.

Exercises

• Using the chart, find a rolling window that catches half of the jump within 10 days. How noisy is it in the calm period compared with a 60-day window?
• Compute monthly returns from daily prices with resample, then check that compounding the daily returns within each month ((1 + r).prod() - 1) gives the same numbers.
• In the code, compute returns.rolling(5).mean() without shift, then with shift(1), for the first 10 rows. Write down, row by row, which returns each value uses.

Key takeaways

• A pandas index aligns data by date; keep dates attached to everything.
• Compute statistics on returns, annualise volatility with $\sqrt{252}$, and leave missing values missing.
• Rolling estimates trade speed against noise; EWMA is a common compromise.
• Shift signals so each position uses only information available before the return it earns. Forgetting to is the most common way a backtest lies.