How to calculate correlation between stocks

Written by marsha hallet
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to calculate correlation between stocks
Calculating correlation coefficients manually takes time. (calculator image by gajatz from Fotolia.com)

The purpose of calculating a correlation coefficient between stocks, or between a stock and the market, is to find the strength of the linear association between two variables. The correlation coefficient will range from +1 to -1 to indicate whether the relationship is positive or negative. A correlation coefficient of 1 shows a perfect correlation and a correlation coefficient of 0 shows no correlation.This statistical measure can be very easily calculated using an Excel spreadsheet. Several internet sites offer online calculators. There are three steps involved in doing the calculation manually.

Skill level:
Easy

Other People Are Reading

Things you need

  • Excel spreadsheet
  • Calculator

Show MoreHide

Instructions

  1. 1

    Count how many values you want to use in your calculation. This would be a time value: for example, one year. If you downloaded the closing prices for the stocks you are comparing for the end of each month for one year, your value N would be 12. X would be the data for the first stock and Y would be the data for the second stock.

  2. 2

    Find the sums of X, Y, XY, X squared and Y squared. You are summing 12 numbers for each column, so XY is the sum of each data set of XY for each month.

  3. 3

    Put the sums into the formula and solve the equation. Correlation(r) =[ NΣXY - (ΣX)(ΣY) / Sqrt([NΣX2 - (ΣX)2][NΣY2 - (ΣY)2])] N = Number of values, or 12 X = First stock end-of-month values Y = Second stock end-of-month values ΣXY = Sum of the product of first and second stock values ΣX = Sum of data in X ΣY = Sum of data in Y ΣX2 = Sum of square for X ΣY2 = Sum of square for Y

  4. 4

    Solve this equation quickly using Excel. Click on the "Formulas" tab and scroll to the "More Functions" option. Select "Statistical" and "CORREL" from the drop-down menu. Complete array 1 with your X values and array 2 with your Y values. For example, put your 12 values for X in column A, for an array of A1:A12. Put your 12 values for Y in column B, for an array of B1:B12. Insert Array 1 and Array 2 into the open boxes and see the correlation coefficient between the two data sets.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.