Lesson 59 · Pandas Full Course
Stock Market Data Analysis in Pandas: Returns, Risk and Beta | Pandas Course #59
In this lesson you'll be able to: Read daily price bars: open, high, low, close and volume Load prices as a time series and turn them into returns Compare…
- CoursePandas Full Course
- Lesson59 of 65
- FormatJupyter notebook · 45 code cells
- Data2 datasets
What you'll learn
Datasets used in this lesson
Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.
- aapl.csv preview58.8 KB
- stocks.csv preview148.1 KB
📓 Full notebook
Download .ipynbPandas 59: Analysing Financial and Stock Market Data#
In this lesson you'll be able to:
- Read daily price bars: open, high, low, close and volume
- Load prices as a time series and turn them into returns
- Compare growth fairly with one dollar invested and yearly returns
- Measure risk with volatility and drawdowns
- Compare stocks with correlation and beta Data: Apple daily bars 2015-2017, and five price series 2007-2016
Warm-up: shares of answers#
Lesson 58 read survey answers as shares. Price days have answers too.
import pandas as pd
aapl = pd.read_csv("data/pandas/aapl.csv", parse_dates=["Date"],
index_col="Date")
print(aapl.shape)
aapl["direction"].value_counts(normalize=True).round(3)
Step 1: Read a day of trading#
Each row is one day: open, high, low, close and shares traded.
print(aapl.columns.tolist())
Common mistake: assuming the column is called Close
aapl["Close"].head()
aapl.columns = aapl.columns.str.replace("AAPL.", "", regex=False)
aapl = aapl[["Open", "High", "Low", "Close", "Volume"]]
aapl.head(3).round(2)
rng = (aapl["High"] - aapl["Low"]) / aapl["Open"] * 100
print(f"Typical range: {rng.median():.2f}%")
print(f"Widest: {rng.max():.2f}% on {rng.idxmax().date()}")
Your turn: on how many days did the range exceed 3%? What share is that?
wide = rng > 3
print(wide.sum(), round(wide.mean(), 3))
Quick check: what else was unusual on 24 August 2015?
aapl["gap"] = (aapl["Open"] / aapl["Close"].shift(1) - 1) * 100
cols = ["Open", "Close", "Volume", "gap"]
aapl.loc["2015-08-20":"2015-08-25", cols].round(2)
aapl["Volume"].nlargest(3)
Step 2: Load many prices as one time series#
Dates must be real dates, in the index, in order.
raw = pd.read_csv("data/pandas/stocks.csv")
raw.head(3)
Common mistake: looking up a date that isn't in the index
raw.loc["2008-09-15"]
prices = pd.read_csv("data/pandas/stocks.csv", parse_dates=["Date"],
index_col="Date")
print(prices.index.min().date(), prices.index.max().date())
prices.loc["2008-09-15"].round(2)
year_end = prices.groupby(prices.index.year).last()
year_end.round(2).head(3)
print(prices.index.is_monotonic_increasing)
print(prices.isna().sum().sum())
Quick check: how many trading days are in a year?
days = prices.index.year.value_counts().sort_index()
print(days.head(4))
prices.loc["2008-09-11":"2008-09-17"].round(2)
Step 3: Turn prices into returns#
A 1-dollar move is huge for a 10-dollar stock, tiny for a 1,000-dollar one.
import matplotlib.pyplot as plt
ax = prices.plot(figsize=(9, 4))
ax.set_title("Raw prices: hard to compare")
ax.set_xlabel("Date")
ax.set_ylabel("Price (USD or index points)")
plt.show()
returns = prices.pct_change().dropna()
returns.head(3).round(4)
stats = returns.describe().loc[["mean", "std", "min", "max"]]
stats.round(4)
ax = returns["GSPC"].mul(100).plot(kind="hist", bins=80,
figsize=(8, 4))
ax.set_title("S&P 500 daily returns, 2007 to 2016")
ax.set_xlabel("Daily return (%)")
ax.set_ylabel("Number of days")
plt.show()
best = pd.DataFrame({"return": returns.max().round(3),
"date": returns.idxmax().dt.date})
best
Your turn: build the same table for each series' worst day.
worst = pd.DataFrame({"return": returns.min().round(3),
"date": returns.idxmin().dt.date})
worst
(returns > 0).mean().round(3)
Step 4: Compare growth fairly#
Start every series at 1 dollar, then follow it.
growth = prices / prices.iloc[0]
growth.iloc[-1].round(2)
ax = growth.plot(figsize=(9, 4), logy=True)
ax.set_title("Growth of 1 dollar, 2007 to 2016")
ax.set_xlabel("Date")
ax.set_ylabel("Value of 1 dollar (log scale)")
plt.show()
span = prices.index[-1] - prices.index[0]
years = span.days / 365.25
cagr = growth.iloc[-1] ** (1 / years) - 1
print(round(years, 2))
cagr.round(3)
by_year = (1 + returns).groupby(returns.index.year).prod() - 1
by_year.round(3)
Quick check: in which year did every series fall?
all_down = (by_year < 0).all(axis=1)
by_year[all_down].round(3)
Your turn: find each series' best year, and its return.
top_year = pd.DataFrame({"return": by_year.max().round(3),
"year": by_year.idxmax()})
top_year
Step 5: Measure risk#
Volatility measures the bumps; drawdown measures the worst fall.
import numpy as np
vol = returns.std() * np.sqrt(252)
vol.sort_values().round(3)
crisis = returns.loc["2008"].std() * np.sqrt(252)
calm = returns.loc["2013"].std() * np.sqrt(252)
pd.DataFrame({"2008": crisis, "2013": calm}).round(3)
roll = returns["GSPC"].rolling(21).std() * np.sqrt(252)
print(round(roll.max(), 3), roll.idxmax().date())
print(round(roll.min(), 3), roll.idxmin().date())
ax = roll.plot(figsize=(9, 3))
ax.set_title("S&P 500: one-month volatility, annualised")
ax.set_xlabel("Date")
ax.set_ylabel("Volatility")
plt.show()
peak = prices.cummax()
drawdown = prices / peak - 1
drawdown.min().round(3)
Your turn: find the date each series hit its lowest point.
drawdown.idxmin().dt.date
ax = drawdown[["GSPC", "SBUX"]].plot(figsize=(9, 3))
ax.set_title("Drawdown from previous peak")
ax.set_xlabel("Date")
ax.set_ylabel("Fall from peak")
plt.show()
Step 6: Compare stocks with correlation and beta#
Do they move together, and how strongly do they follow the market?
returns.corr().round(2)
Common mistake: passing a column name to corr
returns.corr("GSPC")
market = returns["GSPC"]
returns.corrwith(market).round(2)
link = returns["AAPL"].rolling(252).corr(market)
print(round(link.min(), 2), link.idxmin().date())
print(round(link.max(), 2), link.idxmax().date())
beta = returns.cov()["GSPC"] / market.var()
beta.round(2)
Mini-project: which investment rewarded its risk best?#
Return, volatility, worst fall and recovery in one table.
summary = pd.DataFrame({"cagr": cagr, "volatility": vol,
"worst_drop": drawdown.min()})
summary["per_risk"] = summary["cagr"] / summary["volatility"]
summary.sort_values("per_risk", ascending=False).round(3)
recovered = {}
for name in prices.columns:
after = drawdown.loc[drawdown[name].idxmin():, name]
back = after[after >= 0]
recovered[name] = back.index[0].date()
summary["recovered"] = pd.Series(recovered)
summary[["worst_drop", "recovered"]].round(3)
ranked = summary["per_risk"].sort_values()
ax = ranked.plot(kind="barh", figsize=(8, 3))
ax.set_title("Yearly return per unit of risk, 2007 to 2016")
ax.set_xlabel("Growth rate / volatility")
ax.set_ylabel("Series")
plt.show()
import os
os.makedirs("output/pandas", exist_ok=True)
summary.to_csv("output/pandas/l59_risk_summary.csv")
back = pd.read_csv("output/pandas/l59_risk_summary.csv", index_col=0)
back[["cagr", "per_risk", "recovered"]].round(3)
top = summary["per_risk"].idxmax()
mkt = summary.loc["GSPC", "per_risk"]
print(f"Best reward for risk: {top}, "
f"{summary.loc[top, 'per_risk']:.2f} against {mkt:.2f} "
"for the S&P 500")
Recap#
- Clean column names, and parse dates into the index first
- pct_change turns prices into returns on one scale
- Growth of 1 dollar, CAGR and yearly returns compare performance
- Volatility and drawdown (cummax) measure risk
- corr, corrwith and beta show how stocks move with the market
Challenges#
- Repeat the summary table for 2010 onwards only. Who wins now?
- Find Apple's best and worst calendar month in aapl.csv.
- Build an equal-weight portfolio of the four stocks. Beat the S&P?
What's next#
Lesson 60: Hands-on Project - Analysing Stock Prices and Trends Moving averages, trend signals and volume, on real prices.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



