Lecture 3: Working with data
Computational Finance
Importing and Exporting Data
Copyright By PowCoder代写 加微信 powcoder
Importing and Exporting Data
Importing: readtable()
• ideal way when we have column-organized variables all with the same
• 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
number of rows
Working with Tables
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
Example: Reading Treasury Zero Rates
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
U.S. Treasury Yield Curve On 10-Nov-2015
0 5 10 15 20 25 30
Maturity (years)
Interest Rate (%)
Exercise: Pricing a Bond with Imported Data
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
Working with Dates
Working with Dates: Challenge
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
• “Dec312019″=”December31,2019″=”12/31/2019″(U.S.)=”31.12.2019″(Europe) • Monthlydates:”Dec2019″=”201912″=”Dec312019″
The way you store a date is not necessarily the way you want to display it
• Canyouadd15to”Dec312019”?Youcan,butcomputerscan’taddnumberstostrings
• 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?
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 = 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
• 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!
Data Sources
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)
Where to get U.S. Financial Data (for free)?
Type of Data
Method of Access
Sample Period
Historical Data Quality
Yahoo! Finance
Stock price
View, Download
Recent 5 years
CRSP through WRDS
Stock price
Free to , API
95 years (!)
Compustat through WRDS
Accounting
Free to , API
60 years (!)
Alpha Advantage
Stock, Accounting, Currency
Free (Limited)
View, Download, API
All available
‘s Website
Factor Portfolios, Risk-Free Rate
Free to , Download, API
Alpha Vantage for Stock Prices
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
• DATATYPEwiththedatayouneed
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
• YOURKEY with the API key got when you signed up
Downloading data from ‘s Website: Stock portfolio and risk-free rate data
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
Working with Financial Data: a demo
Additional references
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 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. and me • Feel free to refer to it on your homeworks and in future courses
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
Plotting Data
Plotting in MATLAB
MATLAB allows us to do one-, two- or three-dimensional 0.15 plotting
The simplest method is using the plot() function for either one or two dimensions
Daily returns (AMZN and IBM)
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
AMZN max return
IBM max return
More MATLAB Plot Functions/Commands
Function/Command
Description
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
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)
Table and Plotting Examples
Some more examples of readtable(), writetable() and plotting:
• Run_readtable_AMZN.m
• RunIBM_AMZN_analysis.m
Dealing with dates and times can be a tricky and important
issue with financial data
Working with Dates and Times
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
Creating datetime’s in matlab
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’)
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
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’)
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’
The formats in this table apply to the datestr() function
Image from: https://www.mathworks.com/help/matlab/ref/datestr.html?s_tid=doc_ta 29
Datetimes and readtable: an example
Snip from DateTest.csv file
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
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
Saving Variables: WHY
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
• Wewanttostoretheuniquevaluesweused/whenwegeneratedsome data and/or results
o E.g. model parameters: means, volatilities, correlations, etc. 32
Saving Variables: HOW
Suppose we want to save some variables we’ve created for later use or for use in another script file
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”
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
See RunSimulationAndSave.m and RunVaR_using_saved_sTs.m
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com