The correlation between any two variables tells you how closely their ups and downs mimic each other and is measured by a figure known as the correlation coefficient. A high positive correlation coefficient means the variables move up and down together. A high negative coefficient means that when one variable advances, the other tends to decline. Correlation is particularly important in investing since it tells you whether the stocks in your portfolio are likely to move in lockstep, which would amplify both gains and losses.
Select a Time Period
Begin by selecting a time period over which you will calculate the correlation between the two stocks. Keep in mind that the correlation will change over time. The stocks of two companies that are both selling ice cream may no longer be closely correlated after one company sells its ice cream factory and gets into the cookie business, for example. Especially if you will use the resulting figure for future investment purposes, select a past period that mimics the present. When working with daily stock prices, which is the most common practice, you can cover a time span anywhere from a few months to several years.
Calculate Mean and Deviation
Calculate the average price for each stock by adding up daily prices and dividing the sum by the number of days. Do this for both stocks. Next, subtract daily prices from the average. Assume the average price for Stock A is $3.5 and that of Stock B is $7.2, while the prices for the first two days are $3.40 and $3.55 for Stock A and $7.40 and $7.45 for Stock B. The deviation between daily prices and the average for Stock A for the first two days are $0.10, -$0.05; for Stock B they are -$0.20 and -$0.25.
Calculate the Coefficient
Take the square of daily deviations. For Stock A, square 0.1 and -0.05, and so on. Next, multiply the deviation you calculated for the first stock by the deviation for the second stock for each day. For the first day, you will multiply 0.1 by -0.2, and for the second day -0.05 by -0.25. You will now have three columns of data: square of deviations for the first stock in column 1, for the second stock in column 2 and the product of the deviations in column 3. Sum up the values in each column. Divide the sum of column 3 by the square root of [(sum of column 1) × (sum of columns 2)].
Use a Spreadsheet
Alternatively, you can use a spreadsheet program, such as Microsoft Excel, to calculate the correlation coefficient between two stocks. In Excel, enter the daily prices of the stocks into two adjacent columns. Now go to an empty cell and type =correl(address of first cell in column 1:address of last cell in column 1, address of first cell in column 2:address of last cell in column 2). The formula may look like =correl(C2:C25, D2:D25). Hit "Enter," and the cell will display the correlation coefficient between the two stocks.
Hunkar Ozyasar is the former high-yield bond strategist for Deutsche Bank. He has been quoted in publications including "Financial Times" and the "Wall Street Journal." His book, "When Time Management Fails," is published in 12 countries while Ozyasar’s finance articles are featured on Nikkei, Japan’s premier financial news service. He holds a Master of Business Administration from Kellogg Graduate School.