CS计算机代考程序代写 matlab finance Excel Lecture 3: Working with data

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