Normally, correlation measures use stock returns but in Excel you don’t have to use returns, as this article reveals.
The lower the correlations of your stocks with one another and with the market, the better diversified your portfolio – this does not mean the higher your returns though. Correlations range from 0 to 1 and a measure of 0.5 or higher indicates a high correlation.
When you know the correlations of your stocks, you are able to make objective buy or sell decisions.
For example, say, two of yours stocks, A and B, are highly correlated and you want to sell one of them to rebalance or reallocate your portfolio. Say stock A is also highly correlated with the S&P 500 while stock B is not. To improve the diversification of your portfolio, your best move would be to sell stock A - even if you're in love with it - because it correlates more with the market.
While you can get a stock’s correlation with the S&P on good financial information sites like Yahoo!Finance, you still need to know your stocks’ correlations with one another.
If you don’t have software that can give you these statistics at the press of a button, you can easily get them in Excel by using the “CORREL” function.
The trick is to use a system of letters or numbers to indicate up and down movements, and then apply the CORREL function to the two data sets for the two stocks in question, to get the correlation between them.
In the picture illustration below, you see that I use 1 (green shade) for upside movement and 2 (red shade) for downside movement.
To format your cells to flag high correlations – indicated by the yellow cells in the picture – you use the “Conditional Formatting” feature in Excel.
The good thing about the layout in the picture above is that you can see all your correlations in one window and make quick comparisons.
Of course, the downside to this whole exercise is that you have to manually record the daily movements of your stocks. Otherwise, you may not get accurate measurements.
