library(reshape2)11 Data used here
These three files drive the examples in these notes. EODHD supplies the daily US data. The S&P 500 index series runs from 1990 to 2025, while the total return series and the stock prices run from 2003 to 2022. The files are at https://www.financialriskforecasting.com/data.
- SP500, daily prices of the Standard & Poor’s 500 index (S&P 500), used later for single-series examples;
- SP500TR, total returns on the S&P 500;
- Stocks, the prices of different stocks, chosen to represent the various sectors of the US economy, and including both winners and losers. The file contains both unadjusted and adjusted prices.
The first three lines of sp500.csv are:
date,Close
19900102,359.69
19900103,358.76The last two:
20250627,6173.07
20250630,6204.95The start of stocks.csv looks like:
ticker,date,Close,Adjusted_close
MCD,20030102,16.55,9.6942
MCD,20030103,16.12,9.4423The end:
INTC,20221229,26.21,25.8945
INTC,20221230,26.43,26.1118We show the steps here because the logic matters. Later chapters call ProcessRawData(), which does the same work in one line but also attaches time-series columns and returns the index series alongside the stock matrices.
Log returns are calculated as:
\[ \CompoundReturns_t = \log \Price_t - \log \Price_{t-1}. \]
11.1 Libraries
Each language needs a little setup before the first file is read.
import pandas as pd
import numpy as npusing CSV
using DataFrames11.2 The index series
sp500.csv holds one price per trading day. Reading it, converting the date column and differencing the logged price gives the return series that most of the book uses.
We import the CSV file into R as a data.frame using read.csv().
sp500 = read.csv('data/sp500.csv')
class(sp500)
str(sp500)[1] "data.frame"
'data.frame': 8939 obs. of 2 variables:
$ date : int 19900102 19900103 19900104 19900105 19900108 19900109 19900110 19900111 19900112 19900115 ...
$ Close: num 360 359 356 352 354 ...head(sp500, 2)
tail(sp500, 2) date Close
1 19900102 359.69
2 19900103 358.76
date Close
8938 20250627 6173.07
8939 20250630 6204.95The name Close is not convenient, so we rename it.
names(sp500)[2] = "price"We add a new column with log returns. We will not have an observation for day 1, so we add NA for the first value and then remove it.
sp500$date = as.Date(as.character(sp500$date), format = "%Y%m%d")
sp500$y = c(NA, diff(log(sp500$price)))
sp500 = sp500[!is.na(sp500$y), ]
head(sp500, 2) date price y
2 1990-01-03 358.76 -0.002588908
3 1990-01-04 355.67 -0.008650307We import the CSV file using pd.read_csv().
sp500 = pd.read_csv('data/sp500.csv')
print(type(sp500))
print(sp500.dtypes)<class 'pandas.core.frame.DataFrame'>
date int64
Close float64
dtype: objectprint(sp500.head(2))
print(sp500.tail(2)) date Close
0 19900102 359.69
1 19900103 358.76
date Close
8937 20250627 6173.07
8938 20250630 6204.95We rename the Close column to price.
sp500 = sp500.rename(columns={'Close': 'price'})We add log returns and remove the first row which has NaN.
sp500['date'] = pd.to_datetime(sp500['date'], format='%Y%m%d')
sp500['y'] = np.log(sp500['price']).diff()
sp500 = sp500.dropna(subset=['y'])
print(sp500.head(2)) date price y
1 1990-01-03 358.76 -0.002589
2 1990-01-04 355.67 -0.008650We import the CSV file using CSV.read().
using CSV, DataFrames
sp500 = CSV.read("data/sp500.csv", DataFrame);
println(typeof(sp500))
println(names(sp500))DataFrame
["date", "Close"]using CSV, DataFrames
sp500 = CSV.read("data/sp500.csv", DataFrame);
println(first(sp500, 2))
println(last(sp500, 2))2×2 DataFrame
Row │ date Close
│ Int64 Float64
─────┼───────────────────
1 │ 19900102 359.69
2 │ 19900103 358.76
2×2 DataFrame
Row │ date Close
│ Int64 Float64
─────┼───────────────────
1 │ 20250627 6173.07
2 │ 20250630 6204.95We rename the Close column to price.
using CSV, DataFrames
sp500 = CSV.read("data/sp500.csv", DataFrame);
rename!(sp500, :Close => :price);We add log returns and remove the first row which has missing.
using CSV, DataFrames, Dates
sp500 = CSV.read("data/sp500.csv", DataFrame);
rename!(sp500, :Close => :price);
sp500.date = Date.(string.(sp500.date), DateFormat("yyyymmdd"));
sp500.y = [missing; diff(log.(sp500.price))];
sp500 = sp500[.!ismissing.(sp500.y), :];
println(first(sp500, 2))2×3 DataFrame
Row │ date price y
│ Date Float64 Float64?
─────┼──────────────────────────────────
1 │ 1990-01-03 358.76 -0.00258891
2 │ 1990-01-04 355.67 -0.0086503111.3 The total return series
sp500tr.csv has the same shape, so the same three operations apply. The difference is in what the series means, not in how it is handled.
The S&P 500 TR (Total Return) index includes reinvested dividends, unlike the price index above. We process it the same way.
sp500tr = read.csv('data/sp500tr.csv')
names(sp500tr)[2] = "price"
sp500tr$date = as.Date(as.character(sp500tr$date), format = "%Y%m%d")
sp500tr$y = c(NA, diff(log(sp500tr$price)))
sp500tr = sp500tr[!is.na(sp500tr$y), ]The S&P 500 TR (Total Return) index includes reinvested dividends, unlike the price index above. We process it the same way.
sp500tr = pd.read_csv('data/sp500tr.csv')
sp500tr = sp500tr.rename(columns={'Close': 'price'})
sp500tr['date'] = pd.to_datetime(sp500tr['date'], format='%Y%m%d')
sp500tr['y'] = np.log(sp500tr['price']).diff()
sp500tr = sp500tr.dropna(subset=['y'])The S&P 500 TR (Total Return) index includes reinvested dividends, unlike the price index above. We process it the same way.
using CSV, DataFrames, Dates
sp500tr = CSV.read("data/sp500tr.csv", DataFrame);
rename!(sp500tr, :Close => :price);
sp500tr.date = Date.(string.(sp500tr.date), DateFormat("yyyymmdd"));
sp500tr.y = [missing; diff(log.(sp500tr.price))];
sp500tr = sp500tr[.!ismissing.(sp500tr.y), :];11.4 The stock panel
stocks.csv is stacked long, one row per ticker per day, with unadjusted and adjusted prices side by side. We keep the adjusted column.
stocks = read.csv('data/stocks.csv')
str(stocks)'data.frame': 30210 obs. of 4 variables:
$ ticker : chr "MCD" "MCD" "MCD" "MCD" ...
$ date : int 20030102 20030103 20030106 20030107 20030108 20030109 20030110 20030113 20030114 20030115 ...
$ Close : num 16.6 16.1 16.6 16.7 16.8 ...
$ Adjusted_close: num 9.69 9.44 9.75 9.76 9.86 ...The data frame has four columns. There are columns for ticker symbols, dates and both unadjusted and adjusted stock prices.
dim(stocks)
colnames(stocks)
unique(stocks$ticker)[1] 30210 4
[1] "ticker" "date" "Close" "Adjusted_close"
[1] "MCD" "DIS" "AAPL" "GE" "JPM" "INTC"We rename the columns to distinguish the raw close from the adjusted price and parse date to a Date.
names(stocks)[3:4] = c("UnAdjustedPrice", "price")
stocks$date = as.Date(as.character(stocks$date), format = "%Y%m%d")stocks = pd.read_csv('data/stocks.csv')
print(stocks.dtypes)
print(stocks.shape)
print(stocks['ticker'].unique())ticker object
date int64
Close float64
Adjusted_close float64
dtype: object
(30210, 4)
['MCD' 'DIS' 'AAPL' 'GE' 'JPM' 'INTC']We rename the columns and parse date to a datetime.
stocks = stocks.rename(columns={'Close': 'UnAdjustedPrice', 'Adjusted_close': 'price'})
stocks['date'] = pd.to_datetime(stocks['date'], format='%Y%m%d')using CSV, DataFrames
stocks = CSV.read("data/stocks.csv", DataFrame);
println(names(stocks))
println(size(stocks))
println(unique(stocks.ticker))["ticker", "date", "Close", "Adjusted_close"]
(30210, 4)
String7["MCD", "DIS", "AAPL", "GE", "JPM", "INTC"]We rename the columns and parse date to a Date.
using CSV, DataFrames, Dates
stocks = CSV.read("data/stocks.csv", DataFrame);
rename!(stocks, :Close => :UnAdjustedPrice, :Adjusted_close => :price);
stocks.date = Date.(string.(stocks.date), DateFormat("yyyymmdd"));11.5 Reshaping the panel
The stacked layout is convenient to store and awkward to compute with. Every model in the book wants a matrix with one column per stock and one row per date, so the last step pivots the panel and takes returns column by column.
The analysis needs one column per stock, so we reshape stocks into one row per date and one column per stock. We use reshape2 for this.
Price = dcast(stocks, date ~ ticker, value.var = "price")
head(Price, 2)
UnAdjustedPrice = dcast(stocks, date ~ ticker, value.var = "UnAdjustedPrice") date AAPL DIS GE INTC JPM MCD
1 2003-01-02 0.2250 13.7547 88.5027 9.7865 14.5077 9.6942
2 2003-01-03 0.2265 13.8344 88.2248 9.6985 14.7929 9.4423We compute returns for each stock.
Return = Price
for (i in 2:dim(Price)[2]) Return[, i] = c(NA, diff(log(Price[, i])))Remove the NA values.
mask = complete.cases(Return[, -1])
Price = Price[mask, ]
UnAdjustedPrice = UnAdjustedPrice[mask, ]
Return = Return[mask, ]
head(Return, 2) date AAPL DIS GE INTC JPM
2 2003-01-03 0.006644543 0.00577766 -0.003144957 -0.009032651 0.01946779
3 2003-01-06 0.000000000 0.04999279 0.025268359 0.037966760 0.07570013
MCD
2 -0.02632817
3 0.03234458We store the result in one object. In R that object is a list, and in Python and Julia a dictionary.
x = list(Return = Return, Price = Price, UnAdjustedPrice = UnAdjustedPrice)We reshape using pivot() so each row is a date and each column is a stock.
Price = stocks.pivot(index='date', columns='ticker', values='price').reset_index()
Price = Price[['date'] + sorted(Price.columns.drop('date'))]
print(Price.head(2))
UnAdjustedPrice = stocks.pivot(index='date', columns='ticker', values='UnAdjustedPrice').reset_index()
UnAdjustedPrice = UnAdjustedPrice[['date'] + sorted(UnAdjustedPrice.columns.drop('date'))]ticker date AAPL DIS GE INTC JPM MCD
0 2003-01-02 0.2250 13.7547 88.5027 9.7865 14.5077 9.6942
1 2003-01-03 0.2265 13.8344 88.2248 9.6985 14.7929 9.4423We compute returns for each stock.
Return = Price.copy()
for col in Return.columns[1:]:
Return[col] = np.log(Price[col]).diff()Remove the NaN values.
mask = Return.iloc[:, 1:].notna().all(axis=1)
Price = Price[mask].reset_index(drop=True)
UnAdjustedPrice = UnAdjustedPrice[mask].reset_index(drop=True)
Return = Return[mask].reset_index(drop=True)
print(Return.head(2))ticker date AAPL DIS GE INTC JPM MCD
0 2003-01-03 0.006645 0.005778 -0.003145 -0.009033 0.019468 -0.026328
1 2003-01-06 0.000000 0.049993 0.025268 0.037967 0.075700 0.032345We can store everything in a dictionary.
x = {'Return': Return, 'Price': Price, 'UnAdjustedPrice': UnAdjustedPrice}We reshape using unstack() so each row is a date and each column is a stock.
using CSV, DataFrames, Dates
stocks = CSV.read("data/stocks.csv", DataFrame);
rename!(stocks, :Close => :UnAdjustedPrice, :Adjusted_close => :price);
stocks.date = Date.(string.(stocks.date), DateFormat("yyyymmdd"));
Price = unstack(stocks, :date, :ticker, :price);
Price = Price[:, ["date"; sort(names(Price)[2:end])]];
println(first(Price, 2))
UnAdjustedPrice = unstack(stocks, :date, :ticker, :UnAdjustedPrice);
UnAdjustedPrice = UnAdjustedPrice[:, ["date"; sort(names(UnAdjustedPrice)[2:end])]];2×7 DataFrame
Row │ date AAPL DIS GE INTC JPM MCD
│ Date Float64? Float64? Float64? Float64? Float64? Float64?
─────┼────────────────────────────────────────────────────────────────────────
1 │ 2003-01-02 0.225 13.7547 88.5027 9.7865 14.5077 9.6942
2 │ 2003-01-03 0.2265 13.8344 88.2248 9.6985 14.7929 9.4423We compute returns for each stock.
using CSV, DataFrames, Dates
stocks = CSV.read("data/stocks.csv", DataFrame);
rename!(stocks, :Close => :UnAdjustedPrice, :Adjusted_close => :price);
stocks.date = Date.(string.(stocks.date), DateFormat("yyyymmdd"));
Price = unstack(stocks, :date, :ticker, :price);
Price = Price[:, ["date"; sort(names(Price)[2:end])]];
UnAdjustedPrice = unstack(stocks, :date, :ticker, :UnAdjustedPrice);
UnAdjustedPrice = UnAdjustedPrice[:, ["date"; sort(names(UnAdjustedPrice)[2:end])]];
Return = copy(Price);
for col in names(Return)[2:end]
Return[!, col] = [missing; diff(log.(Price[!, col]))]
end;Remove the missing values.
using CSV, DataFrames, Dates
stocks = CSV.read("data/stocks.csv", DataFrame);
rename!(stocks, :Close => :UnAdjustedPrice, :Adjusted_close => :price);
stocks.date = Date.(string.(stocks.date), DateFormat("yyyymmdd"));
Price = unstack(stocks, :date, :ticker, :price);
Price = Price[:, ["date"; sort(names(Price)[2:end])]];
UnAdjustedPrice = unstack(stocks, :date, :ticker, :UnAdjustedPrice);
UnAdjustedPrice = UnAdjustedPrice[:, ["date"; sort(names(UnAdjustedPrice)[2:end])]];
Return = copy(Price);
for col in names(Return)[2:end]
Return[!, col] = [missing; diff(log.(Price[!, col]))]
end
mask = completecases(Return[:, 2:end]);
Price = Price[mask, :];
UnAdjustedPrice = UnAdjustedPrice[mask, :];
Return = Return[mask, :];
println(first(Return, 2))2×7 DataFrame
Row │ date AAPL DIS GE INTC JPM MCD
│ Date Float64? Float64? Float64? Float64? Float64? Float64?
─────┼─────────────────────────────────────────────────────────────────────────────────────
1 │ 2003-01-03 0.00664454 0.00577766 -0.00314496 -0.00903265 0.0194678 -0.0263282
2 │ 2003-01-06 0.0 0.0499928 0.0252684 0.0379668 0.0757001 0.0323446We can store everything in a dictionary.
using CSV, DataFrames, Dates
stocks = CSV.read("data/stocks.csv", DataFrame);
rename!(stocks, :Close => :UnAdjustedPrice, :Adjusted_close => :price);
stocks.date = Date.(string.(stocks.date), DateFormat("yyyymmdd"));
Price = unstack(stocks, :date, :ticker, :price);
Price = Price[:, ["date"; sort(names(Price)[2:end])]];
UnAdjustedPrice = unstack(stocks, :date, :ticker, :UnAdjustedPrice);
UnAdjustedPrice = UnAdjustedPrice[:, ["date"; sort(names(UnAdjustedPrice)[2:end])]];
Return = copy(Price);
for col in names(Return)[2:end]
Return[!, col] = [missing; diff(log.(Price[!, col]))]
end
mask = completecases(Return[:, 2:end]);
Price = Price[mask, :];
UnAdjustedPrice = UnAdjustedPrice[mask, :];
Return = Return[mask, :];
x = Dict("Return" => Return, "Price" => Price, "UnAdjustedPrice" => UnAdjustedPrice);11.6 Summary
ProcessRawData() performs every step above in one call and is what later chapters use. It returns the index series, the total return series and the stock matrices together, with the date columns already typed, so a chapter that needs data starts with one line rather than thirty. The volatility and risk chapters all begin that way.