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.

Spreadsheet Details

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.

Warning!

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.

6 Responses to “Downloading Monthly Returns into Google Docs”

  1. Sweet, this should save me some time. One thing to note – you might want to cut off the first month in cases where the start month is before the first month of returns – the reason being that it will generally be only a partial month, so the return for that month will add a slight error to whatever regression etc. you use the data for.

    So next, do you want to maintain google docs of the FF research factors, then have this spreadsheet pull those in automatically too? 😉

    • Hi Nathan,

      Thanks for the comments. The monthly returns are calculated from the end of month adjusted closing prices. For example, the return for April would use the March close and April close to compute the return. So, I always have closing price data for the month prior to the first monthly return reported. There shouldn’t be any partial months at the beginning of the series. At least I hope I did the calculation right! 🙂 The last month is a partial month though, it just shows the return to date….but if you are running a regression you probably don’t have factor data for that month, so it would be thrown out anyway.

      I actually tried to create an automated Fama-French regression sheet in Google Docs. The problem is that there is no built in regression function, so I had to use the “solve” tool to calculate the loadings and it is extremely slow. It is unusable if there are a meaningful number of data points. There may be a better way to do it, but I haven’t figured it out yet.

      -Chad

  2. There actually is a regression tool I believe. it is called linest.

    Can you explain why you would want adjusted prices for Fama-French calculations?

    • Calculating returns from the adjusted prices gives total returns (dividends + capital gain/loss). If I used the price series, the returns would not include dividends.

  3. I was just playing around with yahoo finance historical data
    It looks like there are different values for monthly adjusted and daily adjusted.
    Neither look like the unadjusted close, but they don’t match.
    How about another blog entry on why daily adjusted is different and maybe what is appropriate for what?
    If it matters I was using KYN from late 2012 to late 2013

    Dave

  4. Yahoo used to be real good about providing adjusted closing prices, but I just noticed today that they only have closing prices for a whole bunch of mutual funds. God knows how many. This is a big problem and there doesn’t appear to be a direct way to contact them.

Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)