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 More

## Instructions

- 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
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
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
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.