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 »