How to calculate correlations using Excel?


Correlation is a measure of linear dependence of two variables. By measuring and mapping the variance of each variable, the correlation gives an idea of the strength of the relationship. Or to put it another way, correlation answers the question: How variable (the independent variable) explain variable B (the dependent variable)?

The formula for correlation

Correlation combines several important and related statistical concepts, namely, variance, and standard deviation. The variance is the dispersion of the variable around the mean and the standard deviation is the square root of the variance.


Since correlation wants to evaluate the linear dependence of two variables that is really needed is to see what sum the covariance of these two variables, and to what extent that the covariance reflects the standard deviation of each variable separately.


Typical errors in the correlation

The most common mistake is assuming correlation approaching +/- 1 is statistically significant. This value is approaching +/- 1, of course, increases the chances of actual statistical significance, but without additional testing it is impossible to know. Statistical tests of correlation, it can be difficult for several reasons; it is not easy. A critical assumption of correlation is that the variables are independent, and that the relationship between them is linear. In theory, you might want to check these allegations to determine if a correlation calculation is appropriate.

The second most common mistake is forgetting the normalization of data into a single unit. If the correlation calculation on the two beta versions, then they are already normalized: the beta unit. However, if you want to correlate the inventory, it is important to normalize their percentage yield, not share price changes. This happens too often, even among professionals in the field of investment.

For stocks, the correlation of prices, You are essentially asking two questions: what is the yield for a certain number of periods, and how to relate to return to return to another security over the same period? Therefore, a comparison of stock prices is difficult: two securities can have high correlation if the return is the daily percentage changes for the last 52 weeks, but low correlation if the return of monthly changes for the last 52 weeks. Which one is “better”? There really is no perfect answer, and it depends on the purpose of the test.

Finding correlations in Excel

There are several ways to calculate correlation in Excel.

The easiest way is to make the two data sets and to use built-in formula correlation:


This is a convenient way to calculate correlations between two datasets. But what if you want to create a correlation matrix across data sets? For this you need to use the data analysis module of Excel. The plugin can be found on the data tab, under Analysis.

Select the table of returns. In this case, our columns entitled, so we want to check the “Labels in first row” so that Excel knows to treat them as headings. Then you can choose to output on the same sheet or on a new sheet.

As soon as You press Enter, the data is automatically. You can add text and conditional formatting to clean up the result.

Investing stocks online advice #investingstocksonline