1. Correlation analysis
So far, you have seen how to compare the performance of a security with a benchmark. You can make a step further in the analysis and look at how the stock and its benchmark moved together in the past.
2. Correlation analysis
What happened to stock ABC when the US market increased? Did it increase as well, did it decrease, or can you not associate any pattern in their co-movements?
Even if these questions may sound a bit tricky to answer, there is an indicator that measures this type of relationship precisely: the correlation coefficient.
3. Correlation coefficient
The correlation coefficient is a statistical measure that quantifies the degree to which two variables move together.
In our case, the variables are the return of stock ABC and the return of the market index.
4. Correlation coefficient
The value of the coefficient ranges from -1 to 1. When two securities always moved in the same direction in the past, they are perfectly correlated, and the coefficient is one.
5. Correlation coefficient
On the contrary, when the two assets always moved in opposite directions, the coefficient measure is minus one.
6. Correlation coefficient
Finally, when there is no relationship between the return movements, the coefficient is zero.
7. Function CORREL()
Computing the correlation coefficient using spreadsheets is straightforward.
Given two series of historical returns, you can use the function CORREL() to compute the coefficient and check how strong the relationship between the two securities is.
8. Function CORREL()
This function receives two arguments, represented by the two series of returns being analyzed.
9. Function PEARSON()
Note that the function PEARSON() works the same way and also computes the correlation coefficient.
10. Correlation changes over time
The degree of dependence between the stock and the index can change over time. For instance, the correlation increases during crisis periods.
In these specific time frames, people tend to sell the assets they own.
11. Correlation changes over time
This leads to an excess of supply compared to the demand for those assets,
12. Correlation changes over time
which results in a generalized decline of prices,
13. Correlation changes over time
and an increase in correlation between the securities in the market.
14. Rolling-window correlation analysis
Tracking the evolution of correlation can be done using a rolling-window correlation analysis.
In this case, instead of looking at the correlation over the whole period, you consider a set of overlapping subperiods, one-year windows for instance, and you compute the correlation on the various subperiods.
15. Rolling-window correlation with Google Sheets
Rolling correlation is easy to perform with spreadsheets. First, compute the correlation for the first window using CORREL() and input cells B3:B14, and C3:C14.
16. Rolling-window correlation with Google Sheets
Then expand the correlation formula with relative cell values of the input cells, corresponding to the sub-windows of interest. For the second window, this will create the formula with CORREL() and the input cells B4:B15 and C4:C15.
17. Rolling-window correlation with Google Sheets
Just proceed further for the subsequent windows.
18. It's time to practice!
In the next exercises, you'll compute the correlation and rolling-window correlations between ABC stock and the benchmark. Time to practice!