How to Calculate Correlation Between Stocks
- 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.
Source...