pandas for time-indexed financial data

Why pandas dominates quant finance

NumPy is fast at arithmetic on arrays. But financial data is labeled — each price has a date, each row has a ticker, each column has a meaning. Pandas adds:

• DatetimeIndex — time-aware row labels. This unlocks resampling (converting a series between time frequencies, e.g. daily rows into monthly rows), slicing by date, and rolling windows (a statistic over the last N rows, recomputed at every row).
• DataFrame — labeled 2D structure (think SQL table with row index).
• Series — labeled 1D structure.
• Vectorized operations that respect index alignment: when you combine two Series, pandas matches rows by label (here, by date) rather than by position.

For research and exploratory work, pandas is faster to write than raw NumPy. For production execution, you may move to Polars (faster) or duckdb (SQL-native) — but every quant team I've seen starts in pandas.

The basic shape

OHLCV stands for Open, High, Low, Close, Volume — the first, highest, lowest, and last price of each trading day, plus the number of shares traded. A standard OHLCV DataFrame from yfinance (a free Python library that downloads Yahoo Finance market data) looks like:

                  Open       High        Low      Close   Adj Close       Volume
Date
2024-01-02  187.149994 188.440002 183.889999 185.640007 185.640007  82488700
2024-01-03  184.220001 185.880005 183.430000 184.250000 184.250000  58414500
2024-01-04  182.149994 183.089996 180.880005 181.910004 181.910004  71983600
...

• Row index is a DatetimeIndex. Each row is a trading day.
• Columns hold the OHLCV plus Adj Close — the close adjusted for splits and dividends. A split is a company dividing each share into several (the price halves, the share count doubles — no real change in value); a dividend is a cash payout to shareholders. Both make the raw price jump for reasons that aren't real gains or losses, and the adjusted series backs those jumps out. This is the column you'll use 95% of the time.
• Only trading days appear — most market-data APIs simply omit weekends and holidays from the index. NaN ("not a number" — pandas' marker for missing data) gaps show up when you combine series with mismatched calendars (e.g., US equities joined with crypto's 7-day weeks, or two exchanges with different holidays) — that's when index alignment fills the holes with NaN.

The five operations you'll do constantly

1. Loading

import yfinance as yf

# Single ticker
spy = yf.download("SPY", start="2010-01-01", auto_adjust=False)

# Multiple tickers — wide format
tickers = yf.download(["SPY", "AAPL", "GOOG"], start="2010-01-01")

2. Computing returns

spy["returns"] = spy["Adj Close"].pct_change()           # simple returns
spy["log_returns"] = np.log(spy["Adj Close"]).diff()     # log returns

These are the two return conventions from C1-01: simple returns (today's price over yesterday's, minus 1) via pct_change, and log returns (the log of that price ratio) via a difference of logs.

Always use Adj Close for return calculations. Raw Close is wrong on dividend days and split days — the price jumps, but you didn't actually gain or lose anything.

3. Slicing by time

spy.loc["2020"]                       # all of 2020
spy.loc["2020-03"]                    # March 2020 only
spy.loc["2020-03-15":"2020-03-30"]   # specific range (inclusive both ends!)

The slicing semantics for DatetimeIndex differ from regular Python — both endpoints are inclusive. Easy to misread.

4. Rolling windows

spy["vol_20d"] = spy["returns"].rolling(20).std()
spy["sma_50"]  = spy["Adj Close"].rolling(50).mean()

vol_20d is 20-day rolling volatility — the standard deviation (σ, the "spread" measure from the math primer) of the last 20 daily returns, recomputed every day. sma_50 is the 50-day simple moving average — the plain mean of the last 50 closes, a classic trend gauge.

The first 19 values of vol_20d will be NaN — there aren't 20 returns to look back on yet. Pandas does this for you correctly, but the NaN can propagate if you don't handle it.

5. Resampling to longer horizons

# Daily to monthly
monthly_returns = spy["returns"].resample("M").apply(lambda x: (1 + x).prod() - 1)

# Daily to weekly OHLC bars
weekly_bars = spy["Adj Close"].resample("W").ohlc()

Two things to unpack. First, resample("M") buckets the daily rows by month; the lambda then compounds the daily returns inside each bucket — multiplies the (1 + return) growth factors together and subtracts 1 — because a month's return is not the plain sum of its daily returns. Second, .ohlc() builds Open/High/Low/Close summary bars at the new frequency.

resample is one of pandas' best features. It handles all the date arithmetic — weekday vs. calendar, month-end, etc.

The three pandas pitfalls that bite everyone

1. Look-ahead bias from .shift()

Some vocabulary first. A signal is any number your strategy computes to decide its position (e.g. "buy when the 20-day average turns up"). A backtest is a simulation of the strategy on historical data. Look-ahead bias is the cardinal backtesting sin: letting the simulation act on information that didn't exist yet at trading time. When you compute "yesterday's signal predicts today's return," you must .shift(1) correctly:

# WRONG — uses today's signal to predict today's return
df["pnl"] = df["signal"] * df["returns"]

# RIGHT — yesterday's signal predicts today's return
df["pnl"] = df["signal"].shift(1) * df["returns"]

.shift(1) moves every value down one row, so each date's row pairs yesterday's signal with today's return. (pnl is profit and loss — the return the strategy captures.) A correct backtest always shifts the signal back relative to the return. Forgetting this single line is the most common source of inflated backtest results.

2. NaN propagation

NaN poisons most computations. If you compute df["pnl"].sum(), NaN values are silently skipped, which sounds nice but means an unexpected NaN can swing your reported numbers. Default to:

df["pnl"].sum()                        # skips NaN silently — be aware
df["pnl"].dropna().sum()              # explicit about ignoring NaN
df["pnl"].fillna(0).sum()              # explicit about treating NaN as zero

Pick one convention per project and document it.

3. Mixing series with different indexes

If you do series_a + series_b and they have different indexes, pandas matches rows by label, keeps every label found in either series (the union), and fills the missing spots with NaN. This can silently break a calculation. When in doubt:

a, b = a.align(b, join="inner")
result = a + b

A small but essential pattern: vectorize, don't loop

# WRONG — Python loop over 5000 rows
for i in range(1, len(df)):
    df.loc[i, "ma5"] = df["close"].iloc[i-5:i].mean()

# RIGHT — vectorized
df["ma5"] = df["close"].rolling(5).mean()

The vectorized version is 100–1000× faster. If you find yourself writing a loop over a DataFrame, stop and ask "is there a built-in?"

Predict before the next lesson

Tomorrow we cover the mean (A2-01) — what "average return" actually is, and why the arithmetic and geometric versions differ. Predict:

• A strategy returns +50% one year and −50% the next. Are you back to breakeven?
• Why or why not?