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 with resampling, slicing, rolling.
• DataFrame — labeled 2D structure (think SQL table with row index).
• Series — labeled 1D structure.
• Vectorized operations that respect index alignment.

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

A standard "OHLCV" DataFrame from yfinance 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 (adjusted for splits/dividends — 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 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

Always use Adj Close for return calculations. Raw Close is wrong on dividend days and split days.

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()

The first 19 values of vol_20d will be NaN. 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()

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()

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"]

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 aligns on the union and fills missing values 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?