Lecture 3: Working with Financial Data
Computational Finance
1
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 the table out to the given file
Importing and exporting data
Working with Tables
3
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
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
4
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 8% annual coupon and the
zero rates from our file using annual compounding
In groups: start from the vectorized bond price code from Week1 and
use 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
5
Exercise: Pricing a Bond with Imported Data
Working
with Dates
6
7
Working with Dates: Challenge
Most financial data has a date dimension
• Stock prices 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) = “2019-12-31” (ISO8601)
• 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)
Why would we care about the number of weekdays?
8
Working with Dates: Examples
Creating dates
• date_var = datetime(2019,12,31)
• date_var = datetime(‘Dec 31 2019’, ‘InputFormat’, ‘MMM dd yyyy’)
Formatting dates: date_var.Format = ‘MM/dd/yyyy’
Doing math with dates: vectors allowed
• next_day = date_var + 1
o Returns 1/1/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
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!
Data
Sources
9
10
Where to Get Financial Data?
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 U.S. Financial Data (for free)?
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
Alpha Vantage for Stock Prices
12
Get your free API key here:
https://www.alphavantage.co/sup
port/#api-key
Download from
• https://www.alphavantage.co/query?fu
nction=DATATYPE&symbol=TICKER&apikey
=YOURKEY&datatype=csv
Replace
• DATATYPE with the data you need
o TIME_SERIES_DAILY_ADJUSTED for
daily returns
o TIME_SERIES_MONTHLY_ADJUSTED
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
https://www.alphavantage.co/support/#api-key
https://www.alphavantage.co/query?function=DATATYPE&symbol=TICKER&apikey=YOURKEY&datatype=csv
https://www.alphavantage.co/documentation/#dailyadj
https://www.alphavantage.co/documentation/#monthlyadj
https://www.alphavantage.co/documentation/
Downloading data from Ken French’s Website:
Stock portfolio and risk-free rate data
1
3
https://mba.tuck.dartmouth.edu/p
ages/faculty/ken.french/data_libra
ry.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
https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data_library.html
Working with
Financial Data:
a demo
14
Additional
References
15
Goal of the slides in this section
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
you may need to look them up! )
• 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. Vadim Elenev and me
• Feel free to refer to it to complete your homework and in future courses
16
Some MATLAB Table functions
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
17
Plotting
Data
18
Plotting in MATLAB
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
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
19
More MATLAB Plot Functions/Commands
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
20
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)
21
Table and Plotting Examples
Some more examples of readtable(), writetable() and
plotting:
• Run_readtable_AMZN.m
• RunIBM_AMZN_analysis.m
22
Reading Data from Yahoo! Finance
Yahoo! Finance used to allow downloading via API (application
programming interface)
This was shut down in 2017, but a helpful MATLAB programmer
posted a file on Mathworks that “scrapes” from the Yahoo! Finance
site
• getMarketDataViaYahoo.m
• You don’t need to understand this function at all – just how to use it
See lecture code: RunAppleDataFromYahoo.m
23
Working
with Dates
and Times
Dealing with dates and times
can be a tricky and important
issue with financial data
24
Importance of dates, times and durations
In the RunAppleDataFromYahoo.m code that we just ran, we
calculated the number of days of data using:
• num_days = length(stock_data.AdjClose);
• What is the value of num_days? Type the variable name on the
command line.
Let’s try it a different way. Try this on the command line after
running that script file:
• num_days2 = daysdif(stock_data.Date(1),stock_data.Date(end))
• This is the number of days between the first and last date in the data
• Why are they so different?
Trading Days ≠ Calendar Days!
25
Some date/time considerations
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
26
Creating datetimes in matlab
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’)
27
Datetime Input formats
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’)
Image from: https://www.mathworks.com/help/matlab/ref/datetime.html#d117e275257
28
https://www.mathworks.com/help/matlab/ref/datetime.html#d117e275257
Converting datetimes to strings
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’) 29
Datetime vs. datestring formats
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’
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
30
https://www.mathworks.com/help/matlab/ref/datestr.html?s_tid=doc_ta
Datetimes and readtable: an example
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
Snip from DateTest.csv file
31
https://www.mathworks.com/help/matlab/matlab_prog/set-display-format-of-date-and-time-arrays.html#buhb8v6-1
Datetime examples
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
32
https://www.mathworks.com/help/matlab/live-scripts-and-functions.html
Back to our apple problem
Remember the problem with our Apple data and calculating the
number of days of data we had?
• Trading days = 500, calendar days = 725
Let’s revisit this using what we’ve learned and something new to
account for business days
• New MATLAB function: days252bus(start_date, end_date)
• See live script: RunAppleDates.mlx
33
Lecture 3: Working with Financial 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
Reading Data from Yahoo! Finance
Working with Dates and Times
Importance of dates, times and durations
Some date/time considerations
Creating datetimes in matlab
Datetime Input formats
Converting datetimes to strings
Datetime vs. datestring formats
Datetimes and readtable: an example
Datetime examples
Back to our apple problem