Financial data

Instruction:

Part I: Download financial data.
1.    Download 3-month Treasury bill rates in 2014 at monthly frequency (12 observations).1
2.    Download monthly stock price for Apple Inc. in 2014 (12 observations). Compute monthly returns (11 observations). 2
3.    Download non-seasonally adjusted CPI from 2013-04-01 to 2014-04-01. What is the inflation rate over the past year from April 2014 to April 2014? 2
4.    Obtain information for Treasury securities: 3
a.    What is the CUSIP of the latest 10-year TIPS? What is its coupon rate (interest rate), yield to maturity, and maturity date?
b.    Find the 10-year Treasury note with the closest maturity date with the above 10-year TIPS. What is the CUSIP of the matching T-Note? What is its coupon rate, yield to maturity, and maturity date?
c.    What is the expected inflation rate over next 10 year? (hints: the difference between the T-Note yield and TIPS yield)

Part II: Statistical analysis in Excel.
5.    Download the Excel file “Project1_Data.xls”. These are the monthly return data for the period January 1996 through December 2002 for the following assets: S&P 500 index, IBM, Microsoft, Caterpillar Inc. Chevron Texaco, Bethlehem Steel, and General Motors (7 return series in total).
Compute their (1) mean value, (2) standard deviation, and (3) covariance matrix. 4
6.    Run a simple regression with the data from the “Sheet 2” of “Project1_Data.xls”. Using the T-bill rate, S&P 500 market returns, and stock returns of Disney, compute alpha (intercept of the regression) and beta (slope of the regression) of Disney based on Capital Asset Pricing Model:

where   is the stock return of Disney,   is the risk-free rate (T-bill rate),   is the market return (S&P 500 return). After you regress    on  , you get the intercept ( ) and the slope ( ). You can ignore the error term ( ) for now. 3

Appendix: step-by-step guidance
1.    Download 3-month T-bill rate:
(1)    Google “FRED”
(2)    Click on “Federal Reserve Economic Data” (http://research.stlouisfed.org/fred2/)
(3)    Click on “Releases”
(4)    Click on “H.15 Selected Interest Rates” (following the alphabetical order, you’ll find it)
(5)    Click on “3-Month Treasury Bill: Secondary Market Rate” with Freq. = “M”(Monthly)
(6)    Click on the figure
(7)    Input Observation Date Range: 2014-01-01 to 2014-12-31
(8)    Click on “Download Data in Graph”
(9)    Open the popup excel and find the data you request (note these data are in percentages).

2.    Download Apple historical stock prices:
(1)    Go to http://finance.yahoo.com/
(2)    Search “Apple”, click on the “GET QUOTES” button
(3)    Click on “Historical Prices” on the left panel
(4)    Input date from 1/1/2014 to 12/31/2014; Check “Monthly”; Click “Get Prices”
(5)    Click on the link “Download to Spreadsheet” below the table.
(6)    Open the “table.csv” file. Keep only “Date” and “Adj Close” prices. Delete all other columns (“Adj close” are close prices adjusted for stock splits and dividends)
(7)    Highlight “Date” column, and then “Sort Oldest to Newest” (Don’t forget to “Expand the selection”)
(8)    Calculate monthly return by inputting formula: “=(B3-B2)/B2” in the C2 cell; apply the same formula to rest of rows; delete the last row (cell C13) with the wrong return “-1”.

3.    Download CPI:
(1)    Google “FRED”
(2)    Click on “Federal Reserve Economic Data” (http://research.stlouisfed.org/fred2/)
(3)    Click on “Consumer Price Indexes (384)”
(4)    Click on “Consumer Price Index for All Urban Consumers: All Items” with “NSA” (Non-Seasonally Adjusted)
(5)    Click on the figure
(6)    Input Observation Date Range:  2013-04-01 to 2014-04-01
(7)    Click “Download Data in Graph”
(8)    Open the popup excel and find the data you need

4.    Obtain information about Treasury securities:
(1)    Go to http://treasurydirect.gov
(2)    Click on “Auction Results”
(3)    Select “Announcements, Data & Results”
(4)    Pick the most recent TIPS with security term of “10-YEAR”
(5)    Pick the most recent Treasury notes with security term of “10-YEAR”
(6)    Find the matching Treasury note which has the closest maturity date with the above TIPS. The security term has to be “10-YEAR”.

5.    To compute summary statistics, you need to install Data Analysis package.
5.1. Install the Data Analysis Add-In:
Follow the instructions from http://www.youtube.com/watch?v=-ubtpQJ1smI to install Data analysis add-in:
(1)    Click on the “File” (Excel 2010) or icon  (Excel 2007) on the left-top corner
(2)    Choose “Options”
(3)    Click on the page “Add-Ins”
(4)    Choose “ Analysis ToolPak”
(5)    Click on “Go…” next to “Manager: Excel Add-ins” at the bottom of the page
(6)    Check “Analysis Toolpak” and click “OK”
.
5.2. Calculate the mean and standard deviation for many time series at once
(1)    Click at “Data” menu
(2)    Choose “Data Analysis” in the Analysis panel
(3)    Choose “Descriptive Statistics” and click “OK”
(4)    Click the “Input Range:” cell, and then highlight all your time series (including the column name)
(5)    Check “Label in first row” and “Summary statistics”
(6)    Check “Output Range:” and click the Output Range cell, and then highlight a cell in your spreadsheet where you want to output your summary statistics to. Click “OK”.
(7)    Now you can remove unnecessary summaries and keep “Mean” and “Standard Deviation” only.

5.3. Calculate covariance for many time series at once (rather than call “COVAR” function for too many times).
(1)    Click at “Data” menu
(2)    Choose “Data Analysis” in the Analysis panel
(3)    Choose “Covariance” and click “OK”
(4)    Click the “Input Range:” cell, and then highlight all your time series (including the column name)
(5)    Check “Label in first row”
(6)    Check “Output Range:” and click the Output Range cell, and then highlight a cell in your spreadsheet where you want to output your covariance matrix to. Click “OK”.
(7)    Now you only have lower-triangle half of the matrix (because covariance matrix is symmetric). To get another half of data all at once (rather than manually copy & paste one by one)
a.    Copy the matrix and “Paste Special…” with “Transpose” option to another blank area
b.    Copy the transposed matrix you just created, “Paste Special…” with “Skip blanks” option back to overlap with the original matrix.

6.    Run a simple regression based on CAPM:
(1)    Use the data from the “Sheet 2” of “Project1_Data.xls”.
(2)     Create a new column and compute Disney excess return
(3)     Create another new column and compute market excess return
(4)    Click at “Data” menu
(5)    Choose “Data Analysis” in the Analysis panel
(6)    Choose “Regression” and click “OK”
(7)     For Input Y Range, select the column of dependent variable
(8)     For Input X Range, select the column of independent variable
(9)    Check “label” if you select the column titles in (7) and (8)
(10)     Check “Line Fit Plots” if you want to see the plot about the regression fitting line.
(11)     For Output Range: highlight a cell in a blank area.
(12)     Find the intercept (alpha) and the coefficient on the   term (beta).