The covariance of two variables tells you how likely they are to increase or decrease simultaneously. A high, positive covariance between two stocks means that when the price of one goes up, that of the other usually does too. A high negative figure means that when one stock advances, the other generally retreats. If the figure is close to zero, with either a positive or negative sign, the two stocks act mostly independently.
Time Frame
Before you can begin to calculate the covariance, you have to decide which data set to use. In other words, which two stocks will you use and how far back will your data go? If calculating the figure by hand, the further back you go, the more tedious the calculation. However, a spreadsheet will have no problem handling even decades worth of data. Generally, data covering half a year will suffice to arrive at a realistic covariance, if you are working with daily stock prices. If using end-of-week or end-of-month stock prices, you should go back at least a year, since you will have fewer prices to work with per month.
Calculating Mean and Deviation
Once you decide which stocks to use and how far back you will go, begin by calculating the average price for each stock over the time frame. Say your data covers 200 business days for Stock A and Stock B. Add up all the prices of Stock A and divide the result by 200; repeat the procedure for Stock B. The results are your averages, or in statistical terms, the means. Now, subtract the stock price for each day from the average price for that stock to find the daily deviation. if, for example, the average price for Stock A was $11.50, and the prices for the first three days were $11, $11.20 and $11.40 respectively, the daily deviations are 0.5, 0.3 and 0.1, respectively. Repeat the process for Stock B.
Finding the Covariance
Now you will have two sets of data: the daily deviation of Stock A from the average price of Stock A; and the daily variation of Stock B from its average price. For each day in your data set, multiply the deviation of Stock A by the deviation of Stock B. You will end up with as many results as there are days in your data set; in this example, you will have 200 numbers. Add these numbers and divide them by the number of days in your set. In other words, calculate the average of the results you have found by multiplying the deviations. This is your covariance.
Calcuation in Spreadsheet
If you have access to a spreadsheet software, such as Microsoft Excel, enter the daily prices of the two stocks in adjacent columns. If using Microsoft Excel, in an empty cell type "=COVAR" and open a parenthesis. Type the address of the first cell, followed by a colon and the address of the last cell for Stock A price data. Place a comma and type the addresses of the first and last cells where Stock B's price data is located. So if the prices of Stock A are located in cells C5 through C204 and prices of Stock B are in cells D5 through D204, your formula will read: =COVAR(C5:C204, D5:D204). Hit "Enter" to display the covariance.
References
Writer Bio
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.