Downloading Historical Prices and Converting to Returns<\/h6>\n

Many types of investment analysis require historical returns.\u00a0 For example, if we want to calculate the\u00a0ex-post\u00a0Sharpe ratio, CAPM beta, or Fama-French factor loadings of a fund, we need the fund’s historical returns (including dividends!).<\/p>\n

One source of data which\u00a0includes dividend adjustments is Yahoo! Finance<\/a>.\u00a0 You can click on the “Historical Prices” option after looking up the quote for a particular ticker to see a table of past prices.<\/p>\n

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

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\u00a0import the prices for a specified stock or fund and convert the price\u00a0data\u00a0into monthly returns.<\/p>\n

Example Return Download<\/h6>\n

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

Here is an embedded capture of the “Total Returns” sheet.<\/p>\n