Lecture 3: Working with data
Computational Finance
1
Importing and
Exporting Data
2
Importing: readtable()
• ideal way when we have column-organized variables all with the same
number of rows
• T=readtable(‘filename.xlsx’) reads both text and numeric data
into a MATLAB table. Tables are like matrices, but better suited for working
with data
o Columns and rows can have names
o Columns can contain different types of data, not just numbers (e.g. text, dates)
• Also works with comma-separated values (CSV) text files
Exporting: writetable()
• writetable(T,’filename.xlsx’) writes out a table to the given
file
3
Importing and Exporting Data
Each column has a name
Columns have different variable
types
• 1st is a date, others numeric
Table_name.Column_name
extracts the data (without the
column name) into a vector
Use parentheses ( ) to index a
table and get back a table
Use curly braces { } to index a
table and get back an array
Working with Tables
4
Let’s use readtable()to read in historical yield curves that we saw in Week 1
• File name: GSW_yields.xls
We will use these interest rates to compute bond prices like in the first two weeks
• But now we will use the interest rates we read in instead of typing them in ourselves
First, let’s write a script file to read in the rates and plot the term structure of interest
rates
5
Example: Reading Treasury Zero Rates
0 5 10 15 20 25 30
Maturity (years)
0.5
1
1.5
2
2.5
3
3.5
In
te
re
st
R
at
e
(%
)
U.S. Treasury Yield Curve On 10-Nov-2015
Let’s price a 10-year, $1,000 bond with a 4% annual coupon and the
zero rates from our file using annual compounding
In groups: use the function and the imported zero rates to price the
bond
• On the most recent day in the data (first row). Use disp()to display the
price.
• On each of the most recent 10 days in the data. Plot the price.
We will reconvene in 15 minutes
6
Exercise: Pricing a Bond with Imported Data
Working
with Dates
7
Most financial data has a date dimension
• Stock prices, bond prices, interest rates on a given day
• Company’s accounting variables (assets, liabilities, EBITDA) in a given quarter
Lots of different ways to represent dates
• “Dec 31 2019” = “December 31, 2019” = “12/31/2019” (U.S.) = “31.12.2019” (Europe)
• Monthly dates: “Dec 2019” = “201912” = “Dec 31 2019”
The way you store a date is not necessarily the way you want to display it
• Can you add 15 to “Dec 31 2019”? You can, but computers can’t add numbers to strings
• Need to tell MATLAB that “Dec 31 2019” represents a date so that it knows what you mean
• Format it to display it however you want
• Perform desired operations on it (e.g. add days, add months, find number of weekdays
between 2 dates)
o Why would we care about the number of weekdays?
8
Working with Dates: Challenge
Creating dates
• date_var = datetime(2019,12,31)
• date_var = datetime(‘Dec 31 2019’, ‘InputFormat’, ‘MMM dd yyyy’)
Formatting dates: date_var = date_var.Format = ‘mm/dd/yyyy’
Doing math with dates: vectors allowed
• next_day = date_var + [1,2]
o Returns 1/1/2020 and 1/2/2020 – MATLAB understands that a new month and year start after 12/31
• next_month = date_var + calmonths([1,2])
o Returns 1/31/2020 and 2/28/2020 – MATLAB understands that different months have different number of
days
• Lots of other date commands e.g. number of business days btw 2 dates days252bus
Converting to text: date_text = datestr(date_var,’mmm dd yyyy’) returns ‘Dec 31 2019’
o Input formats are different from output formats! Happened for historical reasons, but still very annoying!
9
Working with Dates: Examples
Data
Sources
10
Early in the project: still exploring
• Goal: just take a look
• Something quick, easy to use, visually appealing, doesn’t require coding
• Example: Yahoo! Finance
As the project matures
• Goal: obtain and analyze a medium to large amount of data
• Needs to be in a text format easily readable by computers (e.g. CSV, XML)
• Will the data continue to change as you work on the project?
o No (e.g. historical stock price data to backtest investment strategies):
• bulk data download, then import into your program of choice (Excel, MATLAB, etc.)
o Yes (e.g. monitor ongoing prices for current investment opportunities):
• Obtain data directly from your code through an Application Programming Interface (API)
11
Where to Get Financial Data?
Name Type of Data Cost Method of Access Sample Period Historical Data
Quality
Yahoo! Finance Stock price Free View, Download Recent 5 years OK
CRSP through WRDS Stock price Free to Carey Download, API 95 years (!) Excellent
Compustat through
WRDS
Accounting Free to Carey Download, API 60 years (!) Excellent
Alpha Advantage Stock,
Accounting,
Currency
Free (Limited) API 20 years Good
FRED Economic Free View, Download, API All available Excellent
Ken French’s Website Factor Portfolios,
Risk-Free Rate
Free Download 95 years Excellent
Bloomberg All Free to Carey View, Download, API Varies Good
12
Where to get U.S. Financial Data (for free)?
Get your free API key here:
https://www.alphavantage.co/su
pport/#api-key
Download from
https://www.alphavantage.co/query?funct
ion=DATATYPE&symbol=TICKER&apikey=YOURK
EY&datatype=csv
Replace
• DATATYPE with the data you need
o TIME_SERIES_DAILY_ADJUSTED for
daily returns
o TIME_SERIES_MONTHLY_ADJUSTE
D for monthly returns
o Lots of others
• TICKER with the stock ticker of the
company
• YOURKEY with the API key got
when you signed up
Alpha Vantage for Stock Prices
13
https://www.alphavantage.co/documentation/#dailyadj
https://www.alphavantage.co/documentation/#monthlyadj
https://www.alphavantage.co/documentation/
https://mba.tuck.dartmouth.edu/pages
/faculty/ken.french/data_library.html
Market return, risk-free rate, and
Fama-French factors available in
Fama/French 3 Factors
Portfolios of stocks sorted on one or
more particular characteristics (size,
book value / market value ratio,
investment, profitability, etc.) available
too
CSV format easier to import into
MATLAB than TXT
Downloading data from Ken French’s Website:
Stock portfolio and risk-free rate data
14
https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data_library.html
Working with
Financial Data:
a demo
15
Additional
references
16
In class, we will work through a detailed example of importing,
manipulating, and plotting data.
• More practice for homework
• Even more practice in other courses, projects, etc.
Rest of these slides are for your reference
• We won’t go through them in class
• I won’t test your ability to memorize a bunch of specialized MATLAB functions
• But the remaining slides contain a lot of useful information that will help you
when writing your own code
Lots of example code prepared by Prof. Stuart Urban and me
• Feel free to refer to it on your homeworks and in future courses
17
Goal of the slides in this section
Function Call on Table variable
table_name
Description
readtable(file_name) Reads table structure from a spreadsheet,
text file or csv
table_name.Properties.VariableNames Displays the names of the column
variables
summary(table_name) Displays a summary of all the fields (mean,
min, max)
addvars(table_name,new_var) Adds a new column variable to the table
(lots of options on this function)
writetable(table_var,file_name) Writes table data into a file
18
Some MATLAB Table functions
Plotting
Data
19
MATLAB allows us to do one-,
two- or three-dimensional
plotting
The simplest method is using
the plot() function for either
one or two dimensions
We can embellish plots with
text, legends and axes labels
in addition to using various
markers
You can pack a lot of
information into a single
visualization or slide when
presenting
20
Plotting in MATLAB
01-Jan 2018 01-Jul 2018 01-Jan 2019 01-Jul 2019 01-Jan 2020
-0.1
-0.05
0
0.05
0.1
0.15
Daily returns (AMZN and IBM)
AMZN max return
IBM max return
AMZN
IBM
: 0.41548
Function/Command Description
plot(x,y) Plot the vector x on the x-axis and the
vector y on the y-axis (many options
on this function)
title(‘Some string’) Add a title to the plot window
xlabel(‘String’),ylabel(‘String’) Add axis labels
subplot(m,n,p) Break plot window into an mxn matrix
of windows, making window number
p active
yyaxis left; %[or right] Associate the next plot with the left-
or right-hand side of the y-axis
21
More MATLAB Plot Functions/Commands
Function/Command Description
ytickformat(format)
xtickformat(format)
Set the format for y- or x-tick
labels (example format: ‘usd’)
text(x,y,’Text String’) Add text at specified x-y point
annotation(type,pos) Add an annotation – See lecture
code examples (many options on
this function)
histogram(x,edges) Plots a histogram of the variable x
with bin edges defined by “edges”
(default is to use 10 bins if “edges”
not included)
22
More MATLAB Plot Functions/Commands
Some more examples of readtable(), writetable() and
plotting:
• Run_readtable_AMZN.m
• RunIBM_AMZN_analysis.m
23
Table and Plotting Examples
Working
with Dates
and Times
Dealing with dates and times
can be a tricky and important
issue with financial data
24
Business/trading days, or calendar days?: This can make a big difference
Which business calendar?: USA = 252 days, most other countries = 250
Check the date format! What date is ’07/01/2019’? USA = Jul 1st, Europe
= Jan 7th
Addition and subtraction: Are you adding/subtracting days, hours,
years, etc.?
Are your datetimes actually datetimes?
• Some functions, like readtable(), will read dates in as datetimes, but not always
• Check to make sure that they are datetimes, or convert them if they are strings
25
Some date/time considerations
Function Description
datetime(year, month, day,…
hour, minutes, seconds, ms)
Create date with specified values. You can pass in
three, six or seven scalars/vectors, a matrix with
three or six columns. Colon operator is allowed.
datetime(date_string,
‘InputFormat’, infmt)
The 2nd and 3rd parameters are optional, and you
specify the format fmt (see next slide for formats).
datetime(shortcut_string) You can pass in ‘now’, ‘yesterday’, ‘tomorrow’,
’today’ as the shortcut. Ex:
datetime(‘today’)
26
Creating datetime’s in matlab
Examples:
datetime(’01-Jan-2019′,‘InputFormat’,’dd-MMM-yyyy’)
Note: these next two read the same date string in
different ways (US vs. Europe)
datetime(’01/03/2019′,’InputFormat’,’MM/dd/yyyy’)
datetime(’01/03/2019′,’InputFormat’,’dd/MM/yyyy’)
Datetime Input formats
27
Image from: https://www.mathworks.com/help/matlab/ref/datetime.html#d117e275257
https://www.mathworks.com/help/matlab/ref/datetime.html#d117e275257
Typically, when we want to display a date, we can just do so without
worrying about converting it to a string
• When we want to change the displayed format, though, we can either change
the date’s format directly, or we can use the datestr() function
• To change the display format of date d directly, we can simply say something
like:
o d.Format = ‘MM/dd/yyyy’
• Or we can create as string using the datestr() function, so we can say
something like:
o d_string = datestr(d,’mm/dd/yyyy’)
• Notice anything strange about these two lines of code when they are on top
of each other like below, which each cause exactly the same format to be
displayed?
o d.Format = ‘MM/dd/yyyy’
o d_string = datestr(d,’mm/dd/yyyy’) 28
Converting datetimes to strings
datetime and datestr
formats are slightly
different
• For datetime formats,
months use capital
‘M’ and minutes use
lowercase ‘m’
• For datestr formats,
months use lowercase
‘m’ and minutes use
capital ‘M’
29
Datetime vs. datestring formats
Image from: https://www.mathworks.com/help/matlab/ref/datestr.html?s_tid=doc_ta
The formats in this
table apply to the
datestr() function
https://www.mathworks.com/help/matlab/ref/datestr.html?s_tid=doc_ta
The DateTest.csv file has dates provided in European format (dd/mm/yyyy)
• Represents July 4th through the 7th
When we use the readtable() function to read in the data, it will interpret it as
US-formatted (mm/dd/yyyy)
• This actually depends on your computer’s locale and global defaults:
https://www.mathworks.com/help/matlab/matlab_prog/set-display-format-of-date-and-
time-arrays.html#buhb8v6-1
Once we discover that it’s reading them incorrectly, we want a way to correct it
We’ll use a combination of datetime() and datestr() in a user-defined function to
do so
See lecture code: RunCorrectedUSEuroDates.m
30
Datetimes and readtable: an example
Snip from DateTest.csv file
https://www.mathworks.com/help/matlab/matlab_prog/set-display-format-of-date-and-time-arrays.html#buhb8v6-1
There are lots of ways to create datetimes, so let’s use a MATLAB live
script:
• RunDatetimeExamples.mlx
For durations and other date examples, see another live script:
• RunDateDurationsAndDiffs.mlx
See Attaway, Section 6.6 or McCarthy, Section A.1.2.4-5 for brief
discussions of the live editor and live scripts
• The best resource, though, is the Mathworks website:
• https://www.mathworks.com/help/matlab/live-scripts-and-functions.html
31
Datetime examples
https://www.mathworks.com/help/matlab/live-scripts-and-functions.html
Suppose we want to save some variables we’ve created for
later use or for use in another script file
Why? Examples
• Use variables we created in another script file
o Could re-define them
o Or not clear the workspace and hope they still exist…
o Much more reliable: read them in from a saved file
• Creating the variables took significant computational time
Don’t want to do it again every time we use them
o One script file conducts a simulation that takes 3 hours to run
o Other script file analyzes the results
o Don’t want to re-run the simulation every time you want to update and re-run the
analysis
• We want to store the unique values we used/when we generated some
data and/or results
o E.g. model parameters: means, volatilities, correlations, etc. 32
Saving Variables: WHY
Suppose we want to save some variables we’ve created for later use or for
use in another script file
Saving
o save(‘filename.mat’,’var1′,’var2′,’var3′) saves variables var1,
var2, and var3 to the file file_name.mat
o .mat indicates it’s a file with MATLAB data (different from .m which is MATLAB code)
o save(‘filename.mat’) saves all workspace variables to the file
o Replace “filename” with whatever you want the name of the file to be (as long as
there aren’t any spaces!) e.g. “hw4” or “monteCarloParameters”
Loading
o load(‘filename.mat’,’var1′,’var2′,’var3′) loads variables var1,
var2, and var3 from file_name.mat into the current workspace
o load(‘filename.mat’) loads all variables from filename.mat into the
workspace
See RunSimulationAndSave.m and RunVaR_using_saved_sTs.m
33
Saving Variables: HOW
Lecture 3: Working with data
Importing and Exporting Data
Importing and Exporting Data
Working with Tables
Example: Reading Treasury Zero Rates
Exercise: Pricing a Bond with Imported Data
Working with Dates
Working with Dates: Challenge
Working with Dates: Examples
Data Sources
Where to Get Financial Data?
Where to get U.S. Financial Data (for free)?
Alpha Vantage for Stock Prices
Downloading data from Ken French’s Website:�Stock portfolio and risk-free rate data
Working with Financial Data: a demo
Additional references
Goal of the slides in this section
Some MATLAB Table functions
Plotting Data
Plotting in MATLAB
More MATLAB Plot Functions/Commands
More MATLAB Plot Functions/Commands
Table and Plotting Examples
Working with Dates and Times
Some date/time considerations
Creating datetime’s in matlab
Datetime Input formats
Converting datetimes to strings
Datetime vs. datestring formats
Datetimes and readtable: an example
Datetime examples
Saving Variables: WHY
Saving Variables: HOW