FIGURE 1: EXCEL SPREADSHEET. The spreadsheet needs to have all the necessary data entered to further analyze your pairs.
AT THE CLOSE
In this, the second part of this series, find out how to construct a spreadsheet to help reveal profitable opportunities in pair trades.
In the first part of this series, I explained that pair trading is classified as a market-neutral strategy, where you are not concerned with market direction, up or down. The idea, I asserted, is to exploit temporary price discrepancies in two (or more) securities by simultaneously going long the outperforming security and going short the underperforming security. I looked at how to enter stock data and the formulas to calculate returns, cumulative returns, regression residuals, and the formulas to transform the calculations for use as chart inputs.
FIGURE 1: EXCEL SPREADSHEET. The spreadsheet needs to have all the necessary data entered to further analyze your pairs.
This time, we will complete the Excel spreadsheet for identifying the mean reversion (comovement) characteristic of a spread in a pair of stocks. Here’s how:
Step 1
Plot cumulative return residuals. The first chart to create is an XY scatter chart of the residual data in columns K and M. Figure 1 displays the spreadsheet, but please refer to my previous article for a detailed description of how to create this spreadsheet. Enter these ranges in an XY scatterplot B:
X values =Sheet1!$K$10:$K$118 Y values =Sheet1!$M$10:$M$118
You will need to add a trendline to the residual data points and select the “display R-squared value.” The finished chart will look like what you see in Figure 2.