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.
The spreadsheet creates a URL using the values entered on the “Total Returns” sheet. This URL is used to import a Yahoo! Finance generated CSV file with the historical prices into the “RawData” sheet. The Google Docs “importdata” function is used for this CSV file import. The prices are then reordered, converted to returns, and displayed on the “Total Returns” sheet.
The starting date of the monthly returns is either the date specified, or the earliest date with data available, whichever is later.
Use this spreadsheet at your own risk! The Yahoo! Finance data is known to contain errors and there could be undiscovered math errors in the spreadsheet itself. If you find any let me know and I will fix them.