Last Updated:

How can I calculate correlation using Excel?

 

What is correlation?

Correlation measures the linear relationship between two variables. By measuring and correlating the variance of each variable, correlation gives an idea of the strength of the relationship.

 

In other words, correlation answers the question: How much does variable A (independent variable) explain variable B (dependent variable)?

 

Key findings

  • Correlation is the statistical linear correspondence of variation between two variables.
  • In finance, correlation is used in several aspects of analysis. including the calculation of the standard deviation of the portfolio.
  • Calculating the correlation can be time-consuming, but software like Excel makes the calculation easier.

Understanding Correlation

Correlation formula

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

 

Formula:

 
calculate correlation using Excel

Since correlation wants to estimate the linear relationship of two variables, it is really necessary to look at how much covariantity of these two variables and to what extent this covariance is reflected by the standard deviations of each variable separately.

 
 
 

Common errors with correlation

 

The most common mistake is assuming that a correlation approaching +/- 1 is statistically significant. An indication approaching +/- 1 definitely increases the chances of actual statistical significance, but without further testing, this is impossible to know.

 

Statistical correlation verification can become more difficult for a number of reasons; it's not that simple. A critical assumption about correlation is that variables are independent and that the relationship between them is linear. In theory, you should test these claims to determine if the correlation calculation is appropriate.

 

Remember, the correlation between the two variables does NOT imply that A caused B, or vice versa.

The second most common mistake is forgetting to normalize the data into a total unit. If a correlation is calculated for two beta versions, then the units are already normalized: the beta itself is a unit. However, if you want to correlate stocks, it is very important to normalize them as a percentage of return, and not in a change in the price of shares. This happens all too often, even among investment professionals.

 

As for the correlation of stock prices, you are essentially asking two questions: what is the return for a certain number of periods and how does that return relate to the return of another security. refund for the same period?

 

This is why it is so difficult to compare stock prices: two securities can have a high correlation if the yield is a daily percentage. changes in the last 52 weeks, but the low correlation if the yield is monthly, changes over the past 52 weeks. Which is better"? In fact, there is no perfect answer, and it depends on the purpose of the test.

 

Find correlation in Excel

 

There are several methods for calculating correlation in Excel. The simplest is to get two sets of data side by side and use the built-in correlation formula:

 
calculate correlation using Excel

This is a convenient way to calculate the correlation between two datasets. But what if you want to create a correlation matrix for a series of datasets? To do this, you need to use the Excel data analysis plugin. The plugin can be found on the "Data" tab in the "Analysis" section.

 

Select the yield table. In this case, our columns have headers, so we want to check the "Shortcuts in First Row" checkbox so that Excel handles them as headers. Then you can select the output in the same worksheet or in a new worksheet.

 
calculate correlation using Excel

As soon as you press Enter, the data will be created automatically. You can add text and conditional formatting to clear the result.

 
calculate correlation using Excel