Correlation Calculator

Quick Start Guide

This Stock Correlation Calculator will calculate the stock price correlation coefficients and annual price standard deviation for five stock symbols using weekly closing prices from Yahoo Finance. The stock price correlation coefficients are calculated for each pair of stocks and presented in a five by five table for one, two and three year time periods. The annual price standard deviation is calculated for each stock for each of the same time periods.

Correlation coefficients between pairs of stocks range from -1 to +1. For our purposes here, a value of +1 implies that when one stock price increases, the other will increase an equal percent. A value of -1 implies that when one stock price increases, the other will decrease an equal percent. A value of zero implies no relationship between the two stocks.

A few tips: You may only make entries in the yellow boxes below. If you get a blank calculator or screen, then try clicking your browser’s refresh icon. (This seems to be an issue with Google.)  To get rid of the gray cross hatch shading on the protected cells, click on “View” in the upper left corner of the calculator and then click on “Protected Ranges. This calculator uses functions unique only to Google Docs and is designed to operate reliably only on the Constructing Diversified Investment Portfolios blog.

Step 1: 98% of the time you will only need to go to the yellow box here and enter up to five stock symbols separated by commas with no spaces. Just click once in the box and start typing. Acceptable stock symbols are those that are used on the Yahoo Finance website and have historical prices available. Always give the calculator a few seconds to do its job. It needs to load and process about 7,000 data items.

You should see results now. For more information, continue reading starting with ”Step 2” below the calculator.

Your comments, questions and suggestions for improvements are welcome. Please click on the “Post a Comment” link at the bottom of this page.

More Information

Step 2: Nothing to do here except note that this step provides a time stamp for hard copy printouts.

Step 3: Most of the time there is nothing to do here. If you see “N/A” instead of numerical results in the tables below, it is most likely that the associated stock symbol does not have one, two or three years of data available from Yahoo Finance (i.e. it is a relatively new stock issue).

However, it is critical that complete and consecutive weekly closing price data is available for every week for each of the stock symbols entered in step 1. By default, historical price data for symbol SPY (SPDR S&P 500 ETF) is used to check that every week of data is present for every other symbol. If you suspect a large number of cells with “N/A” are caused by a problem with the SPY data, try symbols QQQ, EEM or MSFT in the yellow box here.

Step 4: Typically on Mondays, in the evening, Yahoo updates their weekly price data and during that time the latest week’s closing prices may not be simultaneously available for all of the stock symbols entered in step 1. If this step displays a “NO” then enter “YES” in the yellow box in step 5.

Step 5: Most of the time there is nothing to do here. However, if step 4 displays “YES”, then enter “YES” in the yellow box here and stock data for the latest week will not be included in the calculations.

Details of the calculations

1.     After the stock symbols are entered in Step 1, the calculator requests three years of weekly price data for each stock from the Yahoo Finance web site.

2.     For each stock, the date for each week’s data is compared to the date provided by Yahoo Finance for the stock identified in Step 3. Errors are identified if any of the stocks have incomplete data or erroneous dates.

3.     Basic validity checks are done on the data supplied for each week. While not perfect, these basic checks should suffice to prevent obviously bad data from being used in calculations.

4.     Next the calculator counts the number of weeks of error-free data for each stock symbol entered in Step1 and determines, for each stock, if calculations can be done for one year (52 weeks), two years (104 weeks), or three years (156 weeks).

5.     The percent change in price for each week from the previous week is calculated for all five stocks. This calculation is done using the closing prices that are adjusted for dividends and splits so the resulting correlation coefficients are the result of price action only.

6.     The weekly percent changes in price are the basis for the calculation of Pearson’s correlation coefficient and standard deviation. The calculator uses the standard Google Docs functions for these two calculations. Since the standard deviations are based on weekly percent price changes, these results are multiplied by the square root of 52 to annualize the numbers.

7.     Finally, the results are displayed in the tables for one, two and three years with “N/A” displayed in cases where there is insufficient error-free data. As stated previously, if you see “N/A” instead of numerical results, it is most likely that the associated stock symbol does not have one, two or three years of data available from Yahoo Finance (i.e. it is a relatively new stock issue).

Frequently asked Questions

How can I verify how much stock price data is available for a particular stock?

Go to the Yahoo Finance web site and look at the historical prices for the stock. The dates for available historical prices are shown as defaults in the “Set Date Range” box towards the top of the page. For example, Facebook (Symbol = FB) has less than one year of data (as of the date of this writing).

Are the results of this Correlation Calculator comparable to others found on the internet?

It is always best to work with and compare correlations that were computed in exactly the same way. The results given here should be comparable to others found on the internet if the other calculations were done in the same manner. As stated above, the calculations done here use weekly closing prices adjusted for dividends and stock splits. These closing prices are used to calculate percent price changes per week and then the Pearson’s correlation coefficients and weekly standard deviations are calculated. Weekly standard deviation is converted to annual standard deviation by multiplying by the square root of 52. Results are presented for one, two and three years.

These steps are repeated here to emphasize that if one or more steps are skipped, done differently, or calculated over a different time period, then the results could be somewhat different or very different.  The relative relationship between correlations may be maintained i.e. larger or smaller, positive or negative. But in the end, it is always best to know how all the numbers were calculated.

Unfortunately, in a brief survey of other free stock correlation coefficient calculators on the internet, there was almost a total lack of description of how the correlations were calculated and in some cases results were produced that were baffling and defied logical explanation. So, users beware!

I see the calculator flash red error messages after I enter stock symbols and at other times. What is happening?

The calculator does a lot of work when it initially becomes visible on the web page and when it calculates results for your input stock symbols. It needs to load and process about 7,000 data items and this does not happen immediately. Sometimes, when work is started and there is a short delay before completion, there can be false error indications. Always give the calculator a few seconds to do its job.