A:

Correlation measures the linear relationship of two variables. By measuring and relating the variance of each variable, correlation gives an indication of the strength of the relationship. Or to put it another way, correlation answers the question: How much does variable A (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.  Variance is the dispersion of a variable around the mean, and standard deviation is the square root of variance.  

The formula is: 

Since correlation wants to assess the linear relationship of two variables, what's really required is to see what amount of covariance those two variables have, and to what extent that covariance is reflected by the standard deviations of each variable individually.

 

Common Mistakes with Correlation

The single most common mistake is assuming a correlation approaching +/- 1 is statistically significant.  A reading approaching +/- 1 definitely increases the chances of actual statistical significance, but without further testing it's impossible to know. The statistical testing of a correlation can get complicated for a number of reasons; it's not at all straightforward.  A critical assumption of correlation is that the variables are independent and that 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 forgetting to normalize the data into a common unit.  If calculating a correlation on two betas, then the units are already normalized: beta itself is the unit.  However, if you want to correlate stocks, it's critical you normalize them into percent return, and not share price changes.  This happens all too frequently, even among investment professionals.  

For stock price correlation, you are essentially asking two questions: 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?  This is also why 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 a low correlation if the return is monthly changes over the past 52 weeks.  Which one is "better"? There really is no perfect answer, and it depends on the purpose of the test. 

Finding Correlation in Excel

There are several methods to calculating correlation in Excel.

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

 

This is a convenient way to calculate a correlation between just two data sets. But what if you want to create a correlation matrix across a range of data sets? To do this, you need to use Excel's Data Analysis plugin. The plugin can be found in the Data tab, under Analyze.  

Select the table of returns. In this case, our columns are titled, so we want to check the box "Labels in first row," so Excel knows to treat these as titles.  Then you can choose to output on the same sheet or on a new sheet.  

Once you hit enter, the data is automatically made.  You can add some text and conditional formatting to clean up the result.

RELATED FAQS
  1. How do you calculate R-squared in Excel?

    Calculate R-squared in Microsoft Excel by creating two data ranges to correlate. Use the correlation formula to correlate ... Read Answer >>
  2. How is correlation used in modern portfolio theory?

    Discover how modern portfolio theory and the efficient frontier use correlation between investment assets to predict an optimal ... Read Answer >>
  3. How are negative correlations used in risk management?

    Learn about risk management and how negative correlations between assets are used to diversify and hedge risk associated ... Read Answer >>
  4. Is there a correlation between inflation and house prices?

    There is a correlation between inflation and house prices - in fact there are correlations between inflation and any good ... Read Answer >>
  5. According to the CAPM, the expected return on a stock, that is part of a portfolio, ...

    A. the covariance between the stock and the market. B. the variance of the market. C. the market risk premium. D. ... Read Answer >>
  6. How does the price of oil affect the stock market?

    Read about how the price of oil might impact the stock market and why economists have not been able to find a strong correlation ... Read Answer >>
Related Articles
  1. Trading

    Using Currency Correlations To Your Advantage

    Knowing the relationships between pairs can help control risk exposure and maximize profits.
  2. Investing

    What's the Correlation Coefficient?

    The correlation coefficient is a measure of how closely two variables move in relation to one another. If one variable goes up by a certain amount, the correlation coefficient indicates which ...
  3. Investing

    How to Diversify Your Portfolio Beyond Stocks

    Find out how to get diversified in asset classes beyond stocks to reduce portfolio risk. Learn how diversification can help you reach your financial goals.
  4. Managing Wealth

    Does International Investing Really Offer Diversification?

    Historically, international investing has worked out well for investors, but this may no longer be the case.
  5. Investing

    Do Oil and Natural Gas Prices Rise And Fall Together?

    Do the prices of crude oil and natural gas affect each other? Investopedia explores price patterns and provides analysis.
  6. Investing

    Portfolio Diversification Done Right

    Diversifying your portfolio by means of different securities and asset classes is an essential approach to lower the overall risk of a portfolio.
  7. Investing

    S&P 500 Vs. Dow Jones ETF: Which is a Safer Investment? (SPY,DIA)

    Learn about why the risks of investing in the ETFs that track the S&P 500 and the Dow Jones Industrial Average are very similar for investors.
  8. Investing

    Behind United Airline's 91.6% Rise in 10 Years (UAL)

    United Continental's stock has been impacted by oil prices and economic cycles, but its statistical correlation to the market has been very low.
  9. Financial Advisor

    Illiquid Real Estate: Correlation Pros and Cons

    Stock and bond markets are moving more closely in tandem with each other. Is illiquid real estate the vaccine for this correlation?
RELATED TERMS
  1. Benchmark For Correlation Values

    A benchmark for correlation values is a point of reference that ...
  2. Positive Correlation

    Positive correlation is a relationship between two variables ...
  3. Correlation Coefficient

    The correlation coefficient is a statistical measure that calculates ...
  4. Negative Correlation

    A perfect negative correlation is a relationship between two ...
  5. Serial Correlation

    Serial correlation is the relationship between a given variable ...
  6. Cross-Correlation

    Cross correlation is a measurement that tracks the movements ...
Trading Center