ab-logo

Code. Models. Analysis. Decisions.


Using Excel to Calculate a Correlation Matrix

Excel's Correlation Matrix

This tutorial demonstrates the correlation matrix tool found in Excel's data analysis toolpak. The tool can be used to calculate many correlations simultaneously. This example uses the correlation matrix to investigate correlations between the so-called FANG stocks, but a matrix can be calculated for samples of much larger size.

This example is relevant since it may be an important part of stock portfolio investment to include stocks that are not closely correlated. In this way business risk can be minimized.

Daily returns were calculated for Facebook, Amazon, NetFlix and Google for the previous one year period and a matrix was constructed to determine how closely these stocks follow one another. The video demonstrates using the correlation matrix tool and briefly discusses the output.

Link to Excel File