Pandas for Finance: Rolling Windows: Moving Avg, Bollinger, Volatility, Drawdown

df["Close"].rolling(50).mean() — 50-day moving average. .rolling(20).std() — 20-day rolling std. Bollinger Bands are mean ± 2σ. .expanding().max() for running maximum (drawdown calc). The technical-analysis toolbox in pandas.

A rolling window walks N rows at a time, applying a function. It’s how every moving average, Bollinger band, and volatility metric in finance gets computed.

Moving averages

import pandas as pd

df = pd.read_parquet("data/prices.parquet")
aapl = df[df["Ticker"] == "AAPL"].copy().sort_values("Date").set_index("Date")

aapl["MA50"] = aapl["Close"].rolling(50).mean()
aapl["MA200"] = aapl["Close"].rolling(200).mean()

print(aapl[["Close", "MA50", "MA200"]].tail())

              Close      MA50     MA200
Date
2024-12-26   258.20    241.03    222.56
2024-12-27   255.59    241.55    222.91
2024-12-30   252.20    242.04    223.27
2024-12-31   250.42    242.51    223.62

rolling(N).mean() produces NaN for the first N-1 rows (not enough data), then the rolling average from there.

For the famous golden cross (50-day above 200-day):

aapl["GoldenCross"] = aapl["MA50"] > aapl["MA200"]
crosses = aapl["GoldenCross"].diff()
print(aapl[crosses].head())  # days the cross flipped

Bollinger Bands

window = 20
mean = aapl["Close"].rolling(window).mean()
std = aapl["Close"].rolling(window).std()

aapl["BBUpper"] = mean + 2 * std
aapl["BBLower"] = mean - 2 * std
aapl["BBMid"] = mean

print(aapl[["Close", "BBLower", "BBMid", "BBUpper"]].tail())

The 20-day moving average ± 2 standard deviations. Most action stays inside; touches and breakouts flag turning points (depending on who you ask).

Rolling volatility

aapl["DailyReturn"] = aapl["Close"].pct_change()

# 30-day rolling std, annualized
aapl["Vol30"] = aapl["DailyReturn"].rolling(30).std() * (252 ** 0.5)

print(aapl["Vol30"].tail())

Date
2024-12-26    0.1721
2024-12-27    0.1724
2024-12-30    0.1750
2024-12-31    0.1791

17.91% annualized vol over the trailing 30 days. The standard “is the market quiet or stressed?” gauge.

Rolling Sharpe

window = 60
roll_return = aapl["DailyReturn"].rolling(window).mean() * 252
roll_vol = aapl["DailyReturn"].rolling(window).std() * (252 ** 0.5)
aapl["Sharpe60"] = (roll_return - 0.04) / roll_vol

print(aapl["Sharpe60"].tail())

A rolling 60-day Sharpe (assuming 4% rf). Fairer view than one number for the whole period.

Expanding windows

expanding() is “all data up to here,” not just the last N. Used for cumulative max/min:

aapl["AllTimeHigh"] = aapl["Close"].expanding().max()
print(aapl[["Close", "AllTimeHigh"]].tail())

Each row holds the max value seen up to that point. Combined with a wealth curve, this is the drawdown calculation:

aapl["DailyReturn"] = aapl["Close"].pct_change().fillna(0)
aapl["Wealth"] = (1 + aapl["DailyReturn"]).cumprod()
aapl["Peak"] = aapl["Wealth"].expanding().max()
aapl["Drawdown"] = aapl["Wealth"] / aapl["Peak"] - 1

print(f"Max drawdown: {aapl['Drawdown'].min():.2%}")
print(f"Date: {aapl['Drawdown'].idxmin().date()}")

Max drawdown: -31.43%
Date: 2022-12-28

The 2022 tech selloff in one number.

Custom rolling functions

For arbitrary window math, .apply():

def annualized_sharpe(returns, rf=0.04):
  if returns.std() == 0:
    return float("nan")
  return (returns.mean() * 252 - rf) / (returns.std() * (252 ** 0.5))

aapl["Sharpe60"] = aapl["DailyReturn"].rolling(60).apply(annualized_sharpe)

Slower than vectorized rolling, but works for anything.

Rolling beta vs SPY

prices = pd.read_parquet("data/prices.parquet")
wide = prices.pivot(index="Date", columns="Ticker", values="Adj Close")
returns = wide.pct_change()

# Rolling 60-day beta of AAPL vs SPY
window = 60
cov = returns["AAPL"].rolling(window).cov(returns["SPY"])
var = returns["SPY"].rolling(window).var()
beta = cov / var

print(beta.tail())

Beta = correlation × volatility ratio. cov / var is the regression coefficient — exactly that.

EWMA — exponentially weighted

rolling() weights every data point in the window equally. ewm() weights recent points more:

aapl["EMA20"] = aapl["Close"].ewm(span=20, adjust=False).mean()
aapl["EMAVol"] = aapl["DailyReturn"].ewm(span=30).std() * (252 ** 0.5)

span=N is roughly equivalent to a window of N. Used in MACD, GARCH-lite models, and many exponential decay schemes.

Try it

rolling.py:

import pandas as pd

pd.set_option("display.float_format", lambda x: f"{x:,.4f}")

df = pd.read_parquet("data/prices.parquet")
aapl = df[df["Ticker"] == "AAPL"].copy().set_index("Date").sort_index()
aapl["DailyReturn"] = aapl["Close"].pct_change()

print("=== Moving averages ===")
aapl["MA50"] = aapl["Close"].rolling(50).mean()
aapl["MA200"] = aapl["Close"].rolling(200).mean()
print(aapl[["Close", "MA50", "MA200"]].tail())

print("\n=== Bollinger Bands (20-day, 2σ) ===")
m = aapl["Close"].rolling(20).mean()
s = aapl["Close"].rolling(20).std()
aapl["BBUpper"] = m + 2 * s
aapl["BBLower"] = m - 2 * s
print(aapl[["Close", "BBLower", "BBUpper"]].tail())

print("\n=== Rolling 30-day volatility (annualized) ===")
aapl["Vol30"] = aapl["DailyReturn"].rolling(30).std() * (252 ** 0.5)
print(aapl["Vol30"].tail().map(lambda x: f"{x:.2%}"))

print("\n=== Drawdown ===")
aapl["Wealth"] = (1 + aapl["DailyReturn"].fillna(0)).cumprod()
aapl["Peak"] = aapl["Wealth"].expanding().max()
aapl["DD"] = aapl["Wealth"] / aapl["Peak"] - 1
print(f"Max drawdown: {aapl['DD'].min():.2%}")
print(f"Date: {aapl['DD'].idxmin().date()}")

Common stumbles

Rolling without sort. Rolling is order-sensitive. Sort by Date first.

min_periods confusion. Default for rolling(N) is min_periods=N — first N-1 are NaN. Set min_periods=1 to start computing immediately (with smaller window).

std() on a single value. Returns NaN. Rolling 1-day std is meaningless; use ≥ 2.

Annualizing vol. Use * sqrt(252), not * 252. Vol scales with sqrt of time, not linearly.

EWMA adjust=True vs False. Default is True (adjusts initial bias). False matches the “exponential moving average” you’d see in trading platforms.

Drawdown signs. wealth / peak - 1 is negative when below peak. peak / wealth - 1 is positive — use whichever convention your audience expects.

Cross-ticker rolling without groupby. df["Close"].rolling(50).mean() rolls across the boundary between tickers. Always df.groupby("Ticker")["Close"].rolling(50).mean() for long format.

What’s next

Ep 12: cleaning real market data — splits, dividends, missing days, outliers.

Recap

series.rolling(N).func() for moving stats; .expanding() for cumulative. Bollinger = mean ± 2*std over a window. Annualized vol = daily_std * sqrt(252). Drawdown = wealth / wealth.expanding().max() - 1. Rolling Sharpe and rolling beta are simple combinations. EWMA (.ewm(span=N).mean()) for exponentially-weighted recent emphasis. For multi-ticker, always groupby("Ticker") first.

Next episode: cleaning real market data.