What Is Correlation?

Correlation measures the linear relationship between two variables. It gives an indication of the strength of the relationship by measuring and relating the variance of each. Correlation answers the question: How much does Variable A (the independent variablꦆe) explain Variable B (the dependent variable)?

Understanding Correlation

The correlation coefficient ranges from -1 to 1. Exactly 1 is considered perfect. It indicates that one security will move in the same direction when another security moves up or down. A negative correlation indicates that they'll move in opposite directions. A zero correlation means that there’s no linear relationship.

Investing in non-correlated assets can minimize risk but interpre✨ting correlation can be tricky.

The Formula for Correlation

Correlation combines two important and related statistical concepts: variance and 澳洲幸运5开奖号码历史查询:standard deviation. Variance is the dispersion of a variable around the mean. Standard deviation is thꦡe square root of variance. 

The formula is: 

Correlation wants to assess the linear relationship of two variables so what's really required is to see what amount of 澳洲幸运5开奖号码历史查询:covariance those two variables have. It's necessary to determine the extent to whꦡich that covariance is reflected by the standard deviations of each individual variable.

Common Mistakes With Correlation

The single most common mistake is assuming that a correlation approaching +/- 1 is statistically significant. A reading approaching +/- 1 definitely increases the chances of actual statistical significance but it's impossible to know without further testing.

The statistical testing of a correlation can be complicated for several reasons. It's not at all straightforward. A critical assumption is that the variables are independent and the relationship between them is linear. In theory, you would test these claims to determine if a correlation calculation is appropriate. 

The second most common mistake is neglecting to normalize the data into a common unit. The units are already normalized if you're calculating a correlation on two betas. Beta itself is the unit. It's critical that you normalize them into perꦺcent return and not share price changes, however, if you want to correlate stocks. This happ𒆙ens all too frequently even among investment professionals. 

You're essentially asking two questions for stock price correlation: What is the return over a certain number of periods and how does that return correlate to another security's return over the same period? 

Correlating stock prices is difficult. Two securities might have a high correlation if the return is daily percent changes over the past 52 weeks but they could have a low correlation if the return is monthly changes over the past 52 weeks. Which one is better? There's no perfect answer. It depends on the purpose of the test. 

Finding Correlation in Excel

You can use several methods to calculate correlation in Excel. The simplest is to get two data sets side-by-sid▨e and use the built-in correlation formula.

This is a convenient way to calculate a correlation between just two data sets. You must use Excel's Data Analysis plugin, however, if you want to create a correlation matrix across a range of data sets. The plugin can be found in the Data tab under Analyze. 

Select the table of returns. Our columns are titled in this example. We want to check the box "Labels in first row" so Excel knows to treat these as titles. You can then choose to output on the same sheet or a new sheet. 

The data is automatically made when you hit enter. You can add some text and co⭕nditional formatting to clean up the result.

The Bottom Line

Correlation is a statistical measurement of the variation between two variables. It’s based on variance and standard deviation and it can be a critical tool in analyzing the ꦫrisk of an investment.

Excel can make calculating correlation a breeze but be sure🎶 you understand the results before you take action. Check with an advisor to make sure you’re on the right page before you part with your money.