Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in /home/calcul9/public_html/wp-content/themes/suffusion/functions/media.php on line 580

Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in /home/calcul9/public_html/wp-content/themes/suffusion/functions/media.php on line 583

Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in /home/calcul9/public_html/wp-content/themes/suffusion/functions/media.php on line 586

Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in /home/calcul9/public_html/wp-content/themes/suffusion/functions/media.php on line 589

Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in /home/calcul9/public_html/wp-content/themes/suffusion/functions/media.php on line 592
2012 August » The Calculating Investor

Downloading Monthly Returns into Google Docs

Aug 242012
Downloading Historical Prices and Converting to Returns

Many types of investment analysis require historical returns.  For example, if we want to calculate the ex-post Sharpe ratio, CAPM beta, or Fama-French factor loadings of a fund, we need the fund’s historical returns (including dividends!).

One source of data which includes dividend adjustments is Yahoo! Finance.  You can click on the “Historical Prices” option after looking up the quote for a particular ticker to see a table of past prices.

For example, Yahoo! Finance provides daily, weekly, or monthly prices for SPY, an S&P500 ETF, going back to 1993.  These quoted prices include the “Adj* Close” column which gives the historical closing prices adjusted for past splits and dividends.

The Yahoo! Finance adjusted closing prices can be downloaded to a spreadsheet and the daily, weekly, or monthly total returns can be calculated from these prices. However, this can be a tedious process if it needs to be repeated for multiple stocks/funds, so I have created a Google Docs spreadsheet which can automatically import the prices for a specified stock or fund and convert the price data into monthly returns.

Example Return Download

My Google Docs return download spreadsheet is read only, so it isn’t possible to directly edit after opening.  However, if you are logged into Google Docs, you can create a copy (under the “File” pulldown) to your personal account and the copy will have full edit privileges.

Here is an embedded capture of the “Total Returns” sheet.

The values in blue are the values which should be edited by the user.  In this example, I download the monthly returns for SPY starting in January of 2000.  Updating the blue values (and waiting for the updates to propagate through the rest of the sheet) will give you the historical returns for the fund or stock of your choice. Continue reading »

Loading Yield Curve Data into a Google Docs Spreadsheet

Aug 222012
Yield Curves and Break-even Inflation

In a previous post, I showed how to calculate the break-even rate of inflation using the real and nominal yield curve data available from the U.S. Treasury website.

In this post, I will show how to automatically import the most up-to-date yield curve data into a Google Docs Spreadsheet using the “ImportXML” function. This yield curve data is useful for many financial calculations, but in this post I will again use the yield curve data to do a rough estimate of the break-even inflation rate.

Read the “Notes on the Break-even Inflation Calculation” section for an explanation of the break-even rate and a clarification on why the method shown only gives an approximation of the break-even rate.

ImportXML Function in Google Docs

The ImportXML function can be used to import data from XML files. The syntax of the command is:

=importXml(<url>,<xpath expression>)

The Treasury website has XML format files showing real yields for the current month and nominal yields for the current month.

The ImportXML example shown here will import the real 5-year yields into the spreadsheet.

=importXml(“http://www.treasury.gov/resource-center/data-chart-center/interest-rates/Datasets/real_yield.xml”, “//TC_5YEAR”)

The “//TC5_YEAR” is an xpath expression which selects all the nodes named “TC_5YEAR” in the target document.   Other maturities in the document can be selected by changing the node name.  For example, the 30-year real yields are selected by changing the xpath expression to “//TC_30YEAR”.

Example Google Docs Spreadsheet

I have created an example Google Docs spreadsheet which uses the ImportXML data to download the real and nominal yields at several maturities.  The example sheet is read-only, but if you would like to customize you can select “make a copy” from the “File” pull-down and edit the copy of the spreadsheet.  You must be logged into your Google Docs account to make a copy.

The spreadsheet automatically imports the yield information for the current month to the sheet labeled “Yields”.   The “Summary” sheet extracts the most recent set of daily yield curve data from the “Yields” sheet and uses it to estimate the break-even inflation rate.  The yield curves and the breakeven inflation curves are plotted in the “Summary” sheet as shown here:

Continue reading »