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:
Other People Are Reading
Things you need
- Excel spreadsheet
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.
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.
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
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.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for