Mathew K Analytics

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…

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.

📓 Full notebook

Download .ipynb

Pandas 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)
(506, 10)
direction
Increasing    0.549
Decreasing    0.451
Name: proportion, dtype: float64

Step 1: Read a day of trading#

Each row is one day: open, high, low, close and shares traded.

print(aapl.columns.tolist())
['AAPL.Open', 'AAPL.High', 'AAPL.Low', 'AAPL.Close', 'AAPL.Volume', 'AAPL.Adjusted', 'dn', 'mavg', 'up', 'direction']

Common mistake: assuming the column is called Close

aapl["Close"].head()
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\base.py:3641, in Index.get_loc(self, key)
   3640 try:
-> 3641     return self._engine.get_loc(casted_key)
   3642 except KeyError as err:

File pandas/_libs/index.pyx:168, in pandas._libs.index.IndexEngine.get_loc()

File pandas/_libs/index.pyx:197, in pandas._libs.index.IndexEngine.get_loc()

File pandas/_libs/hashtable_class_helper.pxi:7668, in pandas._libs.hashtable.PyObjectHashTable.get_item()

File pandas/_libs/hashtable_class_helper.pxi:7676, in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: 'Close'

The above exception was the direct cause of the following exception:

KeyError                                  Traceback (most recent call last)
Cell In[4], line 1
----> 1 aapl["Close"].head()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py:4378, in DataFrame.__getitem__(self, key)
   4376 if self.columns.nlevels > 1:
   4377     return self._getitem_multilevel(key)
-> 4378 indexer = self.columns.get_loc(key)
   4379 if is_integer(indexer):
   4380     indexer = [indexer]

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\base.py:3648, in Index.get_loc(self, key)
   3643     if isinstance(casted_key, slice) or (
   3644         isinstance(casted_key, abc.Iterable)
   3645         and any(isinstance(x, slice) for x in casted_key)
   3646     ):
   3647         raise InvalidIndexError(key) from err
-> 3648     raise KeyError(key) from err
   3649 except TypeError:
   3650     # If we have a listlike key, _check_indexing_error will raise
   3651     #  InvalidIndexError. Otherwise we fall through and re-raise
   3652     #  the TypeError.
   3653     self._check_indexing_error(key)

KeyError: 'Close'
aapl.columns = aapl.columns.str.replace("AAPL.", "", regex=False)
aapl = aapl[["Open", "High", "Low", "Close", "Volume"]]
aapl.head(3).round(2)
Open High Low Close Volume
Date
2015-02-17 127.49 128.88 126.92 127.83 63152400
2015-02-18 127.63 128.78 127.45 128.72 44891700
2015-02-19 128.48 129.03 128.33 128.45 37362400
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()}")
Typical range: 1.55%
Widest: 17.71% on 2015-08-24

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))
47 0.093

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)
Open Close Volume gap
Date
2015-08-20 114.08 112.65 68501600 -0.81
2015-08-21 110.43 105.76 128275500 -1.97
2015-08-24 94.87 103.12 162206300 -10.30
2015-08-25 111.11 103.74 103601600 7.75
aapl["Volume"].nlargest(3)
Date
2015-08-24    162206300
2016-01-27    133369700
2015-08-21    128275500
Name: Volume, dtype: int64

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)
MSFT IBM SBUX AAPL GSPC Date
0 23.950705 80.517962 16.149666 11.086612 1416.599976 2007-01-03
1 23.910599 81.378851 16.167992 11.332687 1418.339966 2007-01-04
2 23.774242 80.642129 16.099269 11.251984 1409.709961 2007-01-05

Common mistake: looking up a date that isn't in the index

raw.loc["2008-09-15"]
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
Cell In[11], line 1
----> 1 raw.loc["2008-09-15"]

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexing.py:1207, in _LocationIndexer.__getitem__(self, key)
   1205 maybe_callable = com.apply_if_callable(key, self.obj)
   1206 maybe_callable = self._raise_callable_usage(key, maybe_callable)
-> 1207 return self._getitem_axis(maybe_callable, axis=axis)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexing.py:1449, in _LocIndexer._getitem_axis(self, key, axis)
   1447 # fall thru to straight lookup
   1448 self._validate_key(key, axis)
-> 1449 return self._get_label(key, axis=axis)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexing.py:1399, in _LocIndexer._get_label(self, label, axis)
   1397 def _get_label(self, label, axis: AxisInt):
   1398     # GH#5567 this will fail if the label is not present in the axis.
-> 1399     return self.obj.xs(label, axis=axis)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\generic.py:4241, in NDFrame.xs(self, key, axis, level, drop_level)
   4239             new_index = index[loc]
   4240 else:
-> 4241     loc = index.get_loc(key)
   4243     if isinstance(loc, np.ndarray):
   4244         if loc.dtype == np.bool_:

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\range.py:535, in RangeIndex.get_loc(self, key)
    533         raise KeyError(key) from err
    534 if isinstance(key, Hashable):
--> 535     raise KeyError(key)
    536 self._check_indexing_error(key)
    537 raise KeyError(key)

KeyError: '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)
2007-01-03 2016-03-01
MSFT      22.06
IBM       97.84
SBUX       6.91
AAPL      18.57
GSPC    1192.70
Name: 2008-09-15 00:00:00, dtype: float64
year_end = prices.groupby(prices.index.year).last()
year_end.round(2).head(3)
MSFT IBM SBUX AAPL GSPC
Date
2007 28.94 90.74 9.38 26.21 1468.36
2008 16.10 71.88 4.33 11.29 903.25
2009 25.83 114.04 10.56 27.88 1115.10
print(prices.index.is_monotonic_increasing)
print(prices.isna().sum().sum())
True
0

Quick check: how many trading days are in a year?

days = prices.index.year.value_counts().sort_index()
print(days.head(4))
Date
2007    251
2008    253
2009    252
2010    252
Name: count, dtype: int64
prices.loc["2008-09-11":"2008-09-17"].round(2)
MSFT IBM SBUX AAPL GSPC
Date
2008-09-11 22.49 101.24 7.16 20.20 1249.05
2008-09-12 22.72 101.05 7.02 19.70 1251.70
2008-09-15 22.06 97.84 6.91 18.57 1192.70
2008-09-16 21.37 98.57 7.34 18.51 1213.60
2008-09-17 20.21 94.68 7.14 16.91 1156.39

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()
No description has been provided for this image
returns = prices.pct_change().dropna()
returns.head(3).round(4)
MSFT IBM SBUX AAPL GSPC
Date
2007-01-04 -0.0017 0.0107 0.0011 0.0222 0.0012
2007-01-05 -0.0057 -0.0091 -0.0043 -0.0071 -0.0061
2007-01-08 0.0098 0.0152 -0.0037 0.0049 0.0022
stats = returns.describe().loc[["mean", "std", "min", "max"]]
stats.round(4)
MSFT IBM SBUX AAPL GSPC
mean 0.0005 0.0003 0.0008 0.0012 0.0002
std 0.0183 0.0144 0.0213 0.0213 0.0136
min -0.1171 -0.0828 -0.1097 -0.1792 -0.0903
max 0.1860 0.1151 0.1838 0.1390 0.1158
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()
No description has been provided for this image
best = pd.DataFrame({"return": returns.max().round(3),
                     "date": returns.idxmax().dt.date})
best
return date
MSFT 0.186 2008-10-13
IBM 0.115 2009-01-21
SBUX 0.184 2009-07-22
AAPL 0.139 2008-10-13
GSPC 0.116 2008-10-13

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
return date
MSFT -0.117 2009-01-22
IBM -0.083 2013-04-19
SBUX -0.110 2008-12-01
AAPL -0.179 2008-09-29
GSPC -0.090 2008-10-15
(returns > 0).mean().round(3)
MSFT    0.498
IBM     0.508
SBUX    0.507
AAPL    0.526
GSPC    0.541
dtype: float64

Step 4: Compare growth fairly#

Start every series at 1 dollar, then follow it.

growth = prices / prices.iloc[0]
growth.iloc[-1].round(2)
MSFT    2.20
IBM     1.67
SBUX    3.72
AAPL    9.07
GSPC    1.40
Name: 2016-03-01 00:00:00, dtype: float64
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()
No description has been provided for this image
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)
9.16
MSFT    0.090
IBM     0.058
SBUX    0.154
AAPL    0.272
GSPC    0.037
Name: 2016-03-01 00:00:00, dtype: float64
by_year = (1 + returns).groupby(returns.index.year).prod() - 1
by_year.round(3)
MSFT IBM SBUX AAPL GSPC
Date
2007 0.208 0.127 -0.419 1.364 0.037
2008 -0.444 -0.208 -0.538 -0.569 -0.385
2009 0.605 0.586 1.438 1.469 0.235
2010 -0.065 0.143 0.412 0.531 0.128
2011 -0.045 0.274 0.454 0.256 -0.000
2012 0.058 0.059 0.183 0.326 0.134
2013 0.443 -0.002 0.481 0.081 0.296
2014 0.276 -0.124 0.062 0.406 0.114
2015 0.227 -0.114 0.482 -0.030 -0.007
2016 -0.045 -0.014 0.003 -0.040 -0.032

Quick check: in which year did every series fall?

all_down = (by_year < 0).all(axis=1)
by_year[all_down].round(3)
MSFT IBM SBUX AAPL GSPC
Date
2008 -0.444 -0.208 -0.538 -0.569 -0.385

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
return year
MSFT 0.605 2009
IBM 0.586 2009
SBUX 1.438 2009
AAPL 1.469 2009
GSPC 0.296 2013

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)
GSPC    0.216
IBM     0.229
MSFT    0.290
SBUX    0.338
AAPL    0.338
dtype: float64
crisis = returns.loc["2008"].std() * np.sqrt(252)
calm = returns.loc["2013"].std() * np.sqrt(252)
pd.DataFrame({"2008": crisis, "2013": calm}).round(3)
2008 2013
MSFT 0.485 0.249
IBM 0.360 0.187
SBUX 0.578 0.195
AAPL 0.582 0.286
GSPC 0.410 0.111
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())
0.863 2008-10-28
0.048 2014-12-05
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()
No description has been provided for this image
peak = prices.cummax()
drawdown = prices / peak - 1
drawdown.min().round(3)
MSFT   -0.579
IBM    -0.443
SBUX   -0.802
AAPL   -0.609
GSPC   -0.568
dtype: float64

Your turn: find the date each series hit its lowest point.

drawdown.idxmin().dt.date
MSFT    2009-03-09
IBM     2008-11-20
SBUX    2008-11-20
AAPL    2009-01-20
GSPC    2009-03-09
dtype: object
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()
No description has been provided for this image

Step 6: Compare stocks with correlation and beta#

Do they move together, and how strongly do they follow the market?

returns.corr().round(2)
MSFT IBM SBUX AAPL GSPC
MSFT 1.00 0.55 0.48 0.47 0.71
IBM 0.55 1.00 0.49 0.49 0.71
SBUX 0.48 0.49 1.00 0.43 0.68
AAPL 0.47 0.49 0.43 1.00 0.61
GSPC 0.71 0.71 0.68 0.61 1.00

Common mistake: passing a column name to corr

returns.corr("GSPC")
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
~\AppData\Local\Temp\ipykernel_11704\2599212085.py in ?()
----> 1 returns.corr("GSPC")

~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py in ?(self, method, min_periods, numeric_only)
  13162                         c = corrf(ac, bc)
  13163                     correl[i, j] = c
  13164                     correl[j, i] = c
  13165         else:
> 13166             raise ValueError(
  13167                 "method must be either 'pearson', "
  13168                 "'spearman', 'kendall', or a callable, "
  13169                 f"'{method}' was supplied"

ValueError: method must be either 'pearson', 'spearman', 'kendall', or a callable, 'GSPC' was supplied
market = returns["GSPC"]
returns.corrwith(market).round(2)
MSFT    0.71
IBM     0.71
SBUX    0.68
AAPL    0.61
GSPC    1.00
dtype: float64
link = returns["AAPL"].rolling(252).corr(market)
print(round(link.min(), 2), link.idxmin().date())
print(round(link.max(), 2), link.idxmax().date())
0.18 2014-05-05
0.75 2009-11-02
beta = returns.cov()["GSPC"] / market.var()
beta.round(2)
MSFT    0.95
IBM     0.76
SBUX    1.06
AAPL    0.96
GSPC    1.00
Name: GSPC, dtype: float64

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)
cagr volatility worst_drop per_risk
AAPL 0.272 0.338 -0.609 0.805
SBUX 0.154 0.338 -0.802 0.456
MSFT 0.090 0.290 -0.579 0.309
IBM 0.058 0.229 -0.443 0.251
GSPC 0.037 0.216 -0.568 0.172
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)
worst_drop recovered
MSFT -0.579 2013-04-30
IBM -0.443 2009-10-12
SBUX -0.802 2011-03-10
AAPL -0.609 2009-10-21
GSPC -0.568 2013-03-28
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()
No description has been provided for this image
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)
cagr per_risk recovered
MSFT 0.090 0.309 2013-04-30
IBM 0.058 0.251 2009-10-12
SBUX 0.154 0.456 2011-03-10
AAPL 0.272 0.805 2009-10-21
GSPC 0.037 0.172 2013-03-28
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")
Best reward for risk: AAPL, 0.80 against 0.17 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#

  1. Repeat the summary table for 2010 onwards only. Who wins now?
  2. Find Apple's best and worst calendar month in aapl.csv.
  3. 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.