Our Big Story in blportfolio dated March 3, 2024, explained how statistical measures can be used to navigate the stock market and make informed investment decisions.

Following up on that, of course, calculating statistical measures such as standard deviation and correlation manually can be cumbersome. Instead, you can harness the power of sophisticated yet user-friendly software — spreadsheets.

Microsoft Excel, the widely used spreadsheet software, can be helpful for statistical analysis, with its built-in functions and free add-in tools.

While using formulas is straightforward, the “Data Analysis” add-in goes beyond, enabling you to perform various analytical tasks — from computing basic descriptive measures such as mean and standard deviation to finding correlation between various asset classes or sectors, without requiring you to install any external software.

Here’s a lowdown on how to use the data analysis tool to compute the statistical measures for investing.

Descriptive measures

Before we begin, it is essential to activate the analysis tool pack, which can be done by following the steps below:

“File” > “Options” > “Add-Ins” > “Excel Add-ins” > “Go” and then checking the “Analysis ToolPak” box

Let us keep things simple by using the examples mentioned in the Big Story. Consider plotting the three-sigma bands for the monthly Price-to-Earnings (P/E) ratio of Infosys from January 2004 to December 2023.

The three-sigma rule can inform investment strategies, such as contrarian or momentum investing. For contrarian investors, buying stocks when they are trading at prices below two/three standard deviations from the mean may present buying opportunities, assuming that the market has overreacted to negative news. Conversely, momentum investors may look to capitalise on stocks trading at prices above one/two standard deviations from the mean, expecting continued price appreciation.

So, before you dive into calculating the average and standard deviation to set up the three-sigma bands, make sure your data is neatly organised in Excel. Each data point should be clearly labelled in either rows or columns.

Moving on, head over to the “Data” tab on your Excel toolbar, and you’ll spot a button labelled “Data Analysis.” Give it a click, and a “Data Analysis” dialog box pops up (see screenshot 1). Scroll through the options until you find “Descriptive Statistics,” then hit “OK.”

In the dialog box (see screenshot 2), simply specify the range of data, in our case the Infosys P/E, and make sure to check the box for “Summary statistics”. Once you have got everything set up, just click “OK,” and Excel will quickly crunch the numbers, giving the mean (24.4), standard deviation (6.9), and other useful statistical metrics for our dataset (see screenshot 3).

Correlation

In a similar manner, correlation can also be computed from the Data Analysis tool. Alternatively, CORREL function can be a quick way to calculate the correlation coefficient. However, the function can only handle two data sets at a time whereas the correlation function in the Data Analysis tool pack can be used to find pairwise correlation for more than two data sets.

Let us consider the yearly prices of two popular asset classes, namely MCX gold future for gold and Nifty 50 for equities from 2004 to 2023. Gold Exchange Traded Funds can be considered as proxy in case the gold futures prices aren’t available. When analysing the relationship between gold and equity, it’s often more insightful to compute the correlation between their returns rather than their prices because returns allow for a comparison of the relative performance of gold and equity regardless of their absolute price levels.

Once the annual return of gold and Nifty 50 is calculated, select “Correlation” from the Analysis Tools menu, and the correlation dialog box will pop up (see screenshot 4). In the “Input Range” field, select the annual returns of both gold and Nifty 50 from the past 20 years, then hit “OK”. This will return a correlation matrix, displaying correlation coefficients for all possible pairs of datasets, in our case Gold and Nifty 50 returns with a correlation of -0.08.

By including low or negatively correlated assets in a portfolio, investors can achieve a more balanced risk-return profile. While one asset class may underperform in a particular market scenario, another may thrive, contributing positively to the overall portfolio returns. This diversification allows investors to capture upside potential while reducing the impact of downside risk.

Related Topics
comment COMMENT NOW