Maruf Islam
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.
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 ∑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.
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.
