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 normalized 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. (Improve your excel skills by taking Investopedia Academy's excel training course.)

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 I calculate correlation between market indicators and specific stocks?

    Discover how to calculate the correlation coefficient between market indicators and stock prices, a critical skill in technical ... Read Answer >>
  2. How do I find positive correlation in the stock market?

    Learn how positive correlation is found in the stock market, how correlation is calculated and how positive correlation is ... Read Answer >>
  3. Does a negative correlation between two stocks mean anything?

    Learn what the concept of negative correlation means, understand how it is generally calculated and see how it is used in ... Read Answer >>
  4. How do fund managers use correlation to create portfolio diversity?

    Read about how contemporary investment fund managers use the concept of correlation to add diversification among assets in ... Read Answer >>
  5. What is the difference between positive correlation and inverse correlation?

    Learn the difference between a positive correlation and a negative, or inverse, correlation and the way they apply to the ... Read Answer >>
  6. How is correlation used differently in finance and economics?

    Take a look at the similarities and differences between how statistical correlation is applied in economics as opposed to ... Read Answer >>
Related Articles
  1. Investing

    Correlation

    In the world of finance, correlation is a statistical measure of how two securities move in relation to each other.
  2. Financial Advisor

    4 Reasons Why Market Correlation Matters

    Learn about how correlation can be used to measure how broader markets move in relation to each other. See how correlation is used to manage risk.
  3. Investing

    Is the Stock Correlation Strategy Effective?

    The synchronized movement among stocks and markets in recent years is challenging diversification.
  4. Investing

    Understanding the Oil & Gas Price Correlation

    Learn how the correlation between the commodity prices for natural gas and oil changed from 2004 to 2015 due to increased natural gas production.
  5. Trading

    Using Currency Correlations To Your Advantage

    Knowing the relationships between pairs can help control risk exposure and maximize profits.
  6. Insights

    Prices of Stocks and Bonds Move More in Tandem

    Correlation between stock and bond prices in the U.S. have reached a 10-year high, reversing a broader trend of negative correlation.
  7. 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 ...
  8. Investing

    Diversification: Protecting Portfolios From Mass Destruction

    This investing strategy retains its charm as a protection against random events in the market.
  9. Investing

    Explaining Autocorrelation

    Autocorrelation is the measure of an internal correlation with a given time series.
RELATED TERMS
  1. Correlation Coefficient

    A measure that determines the degree to which two variable's ...
  2. Positive Correlation

    A relationship between two variables in which both variables ...
  3. Inverse Correlation

    A contrary relationship between two variables such that they ...
  4. Serial Correlation

    The relationship between a given variable and itself over various ...
  5. Cluster Analysis

    An investment approach that places securities into groups based ...
  6. Correlation

    In the world of finance, a statistical measure of how two securities ...
Hot Definitions
  1. Leveraged Buyout - LBO

    The acquisition of another company using a significant amount of borrowed money (bonds or loans) to meet the cost of acquisition. ...
  2. Current Assets

    A balance sheet account that represents the value of all assets that can reasonably expected to be converted into cash within ...
  3. Tax Liability

    The total amount of tax that an entity is legally obligated to pay to an authority as the result of the occurrence of a taxable ...
  4. Preferred Stock

    A class of ownership in a corporation that has a higher claim on its assets and earnings than common stock. Preferred shares ...
  5. Net Profit Margin

    Net Margin is the ratio of net profits to revenues for a company or business segment - typically expressed as a percentage ...
  6. Gross Margin

    A company's total sales revenue minus its cost of goods sold, divided by the total sales revenue, expressed as a percentage. ...
Trading Center