Excel incorporates pretty much every calculation aspect from numerous branches of science. As a result, it’s easy to calculate the correlation between two stocks in Excel than in other mediums. Users can use the **CORREL** function, **Toolpak Add-in**, or conventional **Statistics Correlation Formula**.

Let’s say we have the price data of two stocks stretching a time period couple of weeks. And we want to calculate the correlation between them.

In this article, we demonstrate the use of the **CORREL** function, **Toolpak Add-in** as well as conventional **Statistics Correlation Formula** to calculate correlation between two stocks in Excel.

**Table of Contents**hide

**Download Excel Workbook**

**What Is Correlation?**

In statistics, **Correlation** or **Correlation Coefficient** is the parameter to show coherence between two variables in response to the continuous fluctuating quantity of another. Its value ranges from** -1** to **1**. Therefore, it has three states of defining variable relationships. They are,

** ****(i) -1 indicates a Negative Correlation:** The relationship between the assigned variables is negative. That means the variables changes in the opposite direction.

**(ii) +1 indicates a Positive Correlation:** When variables change in the same direction, it’s called they have a positive correlation.

**(iii) 0 indicates No Correlation:** No apparent movements in any direction of a variable upon changing other variable’s values is known as no correlation.

**3 Easy Ways to Calculate Correlation between Two Stocks in Excel**

Follow any below-described method to calculate the correlation between two stocks in Excel.

**Method 1: Using CORREL Function to Calculate Correlation between Two Stocks**

Excel offers the **CORREL** function which takes arrays or ranges as its arguments. As a result, users can assign arrays or ranges to calculate correlations between them.

**Step 1:** Insert the following formula in any blank cell (i.e., **F5**).

`=CORREL(C5:C14,D5:D14)`

The formula takes **C5:C14** as **array1** and **D5:D14** **array2** argument.

**Step 2:** Press** ENTER** to display the correlation amount between the stock prices.

For convenience, the **stock prices** are in a currency amount, you can use percentages to depict changes in them. Irrespective of data types, the** CORREL** function calculates the correlation between them.

**Read More:** **How to Add Stock Data Type in Excel (2 Effective Methods)**

**Similar Readings**

**How to Download Historical Stock Data into Excel (with Easy Steps)****How to Track Stock Prices in Excel (2 Simple Methods)****How Do You Automatically Update Stock Prices in Excel (3 Easy Methods)**

**Method 2: Calculating Correlation between Two Stocks Using Toolpak Add-in**

Excel’s **Toolpak Add-in** provides multiple **Analysis Tools Correlation** is one of them. Users can perform a **Correlation Analysis** using the **Toolpak Add-in**. But users need to activate the **Toolpak Add-in** first.

**Step 1:** Go to Worksheet’s **File** > **Options**.

**Step 2:** Excel brings the **Options** window. From the window, Select **Add-ins** (right side of the window) > Choose **Excel Add-ins** in the **Manage** command box then click on **Go**.

**Step 3:** The **Add-ins** dialog box opens up. Tick the **Analysis Toolpak** under **Available Add-ins**. At last, click on **OK**.

**Step 4:** Now, return to the worksheet, then move to the **Data** tab > Select **Data Analysis**.

**Step 5:** Instantly, Excel opens up the **Data Analysis** dialog box. In the dialog box, Select **Correlation** as data **Analysis Tools**. After that, click on **OK**.

**Step 6:** The **Correlation** command box appears. Assign respective data in the boxes such as the entire range (i.e., **C4:D14**) as** Input Range**, **Columns** as **Grouped By**, and **F5** as **Output Range**. Finally, click on **OK**.

🔼 The **Toolpak Add-in** calculates the correlation among the assigned range and returns the value as shown in the below image.

**Read More:**** Get Stock Quote with Excel Add-in (With Easy Steps)**

**Method 3: Using Conventional Statistics Formula to Calculate Correlation between Two Stocks**

The **Correlation** or **Correlation Coefficient** has its own generic formula in **Statistics**. The formula is consist of two portions one is **Each Value** (**X** or** Y**) (here it is **P**) **– Mean Value** (**X** or** Y**) (here it is **Q**) the other one is **Square Root** of **Squared Summation** of **Each Value** (**X** or** Y**) (here it is **P**)** – Mean Value** (**X** or** Y**). So, the formula becomes,

`Correlation or Correlation Coefficient=∑(X(i)- Mean(X))*(Y(i)-Mean(Y))/√ ∑(X(i)-Mean(X))2* ∑(Y(i)-Mean(Y))2`

**🔄 Setting Up Data**

Before commencing any calculation, you have to generate the required entities from the given data.

**Geeting the Means: **

`Mean Calculation= Sum of the X or Y's/Number of the Data Rows`

**Subtract Values: **

`P=X(i)- Mean(X)= X values- Mean(X)`

` Q=Y(i)- Mean(Y)= Y values- Mean(Y)`

**Product of the Subtraction: **

`P*Q=X(i)- Mean(X)*Y(i)- Mean(Y)`

**Sum of the Product: **

`∑(P*Q)=∑(X(i)- Mean(X)* Y(i)- Mean(Y))`

**Square of the Subtraction: **

`P2 or Q2=(X(i)-Mean(X))2 or (Y(i)-Mean(Y))2`

**Product of the Squared Subtraction Sum: **

`∑P2*∑Q2=∑(X(i)-Mean(X))2* ∑(Y(i)-Mean(Y))2`

Conduct the above operation to prepare the required entities mentioned in the formula. After wrapping up the data settings, the dataset may look like the below depiction.

^{}

**Step 1:** As mentioned in the formula,** Correlation** is the ratio between the **Sum of the Products of the Subtraction Value ∑(P*Q) **and the

**Square Root Product of the Squared Summed the Subtraction Value**. Execute the formula

*∑P2*∑Q2*`Correlation or Correlation Coefficient=∑(P*Q)/√∑P2*∑Q2`

`=G15/SQRT(H16)`

In the formula, **G15** is the **Sum of the Products of the Subtraction Value** and the **SQRT** function square root of the **Product of the Squared Summed the Subtraction Value**.

**Step 2:** Hit** ENTER** to display the **Correlation** value that appears as depicted in the below screenshot.

⧭ Notice that the **Correlation** value is the same throughout the iterations. This happens because Excel does the same calculation in backends making functions like **CORREL** or **Add-ins** convenient.

**Conclusion**

In this article, we demonstrate the **CORREL** function, **Toolpak Add-in**, and conventional **Statistics Formula** to calculate **Correlation** between two stocks in Excel. Hope you find these above-mentioned methods compatible with your instances. Comment, if you have further inquiries or have anything to add.

## Related Articles

**How to Get Historical Data of NSE Stocks in Excel (2 Effective Ways)****How to Get Live Stock Prices in Excel (4 Easy Ways)****Stock return analysis using histograms & 4 skewness of histograms****How to Get Stock Quotes in Excel (2 Easy Ways)****How to Import Stock Prices into Excel from Google Finance (3 Methods)**