How to Calculate Correlation between Two Stocks in Excel (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

Excel incorporates pretty much every calculation aspect from numerous branches of science. As a result, it’s easier 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.

Dataset-Calculate Correlation between Two Stocks in Excel

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.


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.


How to Calculate Correlation between Two Stocks in Excel: 3 Easy Methods

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.

CORREL Function-Calculate Correlation between Two Stocks in Excel

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

CORREL Outcome

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 Do Correlation and Regression Analysis in Excel


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.

Toolpak add-in-Calculate Correlation between Two Stocks in Excel

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.

Excel Add-in

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

Add-ins

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

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.

Analysis Tools

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.

Correlation window- Calculate Correlation between Two Stocks in Excel

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

Toolpak add-in Outcome-Calculate Correlation between Two Stocks in Excel

Read More: How to Calculate Cross Correlation in Excel


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 consists 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.

Data Setting-Calculate Correlation between Two Stocks in Excel

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 ∑P2*∑Q2. Execute the formula

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.

Statistics Formula

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

Statistics Formula-Calculate Correlation between Two Stocks in Excel

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.


Download Excel Workbook


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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo