Pair Trading Strategy [EXCEL MODEL]

Share on Facebook0Tweet about this on Twitter0Share on LinkedIn35Share on Google+0

Pair Trading Strategy

Pair Trading

Pair trading is a trading strategy that matches a long position in one stock/asset with an offsetting position in another stock/asset that is statistically related. Pair trading is a mean reversion strategy where we bet that the prices will revert to their historical trends.

Who can use this Excel Model?

People interested in algorithmic and Quant trading, those who want to learn about statistical arbitrage.

How it helps?

This excel model will help you to:

  • Learn the application of mean reversion
  • Understand of pair trading
  • Optimize trading parameters
  • Understand significant returns of statistical arbitrage

Why should you download the trading model?

As the trading logic is coded in the cells of the sheet, you can better the understanding by downloading and analyzing the files at your own convenience. Not just that, you can play around the numbers to obtain better results. You might find suitable parameters that provide higher profits than specified in the article.

Explanation of the model

In this example we consider the MSCI and Nifty pair as both of them are stock market indexes. We implement mean reversion strategy on this pair. Mean reversion is a property of stationary time series. Since we claim that the pair we have chosen is mean reverting we should test whether it follows stationarity. The following diagram shows the plotting of logarithmic ratio of Nifty to MSCI. At the outset this appears to be mean reverting with a mean value of 2.088 but we use Dicky Fuller Test to test whether it is stationary with a statistical significance. The results under Cointegration output table shows that the price series is stationary and hence mean reverting. Dicky Fuller Test statistic and a significantly low p-value (<0.05) confirms our assumption. Having determined that the mean reversion holds true for the chosen pair we proceed with specifying assumptions and input parameters.


  1. For simplification purpose, we ignore bid ask spreads.
  2. Prices are available at 5 minutes interval and we trade at the 5 minute closing price only.
  3. Since this is discrete data, squaring off of the position happens at the end of the candle i.e. at the price available at the end of 5 minutes.
  4. Only the regular session (T) is traded
  5. Transaction costs are $0.375 for Nifty and $1.10 for MSCI.
  6. Margin for each trade is $990 (approximated to $1000).

Input parameters

Please note that all the values for the input parameters mentioned below are configurable.

  • Average of 10 candles (one candle=every 5 minute price) is considered.
  • A “z” score of +2 is considered for buy and -2 for selling.
  • A stop loss of $100 and profit limit of $200 is set.
  • The order size for trading MSCI is 50 (1 lot) and for Nifty is 6 (3 lots).

The market data and trading parameters are included in the spread sheet from the 12th row onwards. So when the reference is made to column D, it should be obvious that the reference commences from D12 onwards.

Explanation of the columns in the Excel Model

Column C represents the price for MSCI.

Column D represents Nifty price.

Column E is the logarithmic ratio of Nifty to MSCI.

Column F calculates 10 candle average. Since 10 values are needed for average calculations, there are no values from F12 to F22. The formula =IF(A23>$C$3, AVERAGE(INDEX($E$13:$E$1358, A23-$C$3):E22), “”) means that the average should be calculated only if the data sample available is more than 10 (i.e. the value specified in cell C3), otherwise the cell should be blank. Consider cell F22. Its corresponding cell A22 has a value of 10. Since A22>$C$3 fails, the entry in that cell is blank. The next cell F23 has a value since A23>$C$3 is true. The next bit of the formula

AVERAGE(INDEX($E$13:$E$1358, A23-$C$3):E22) calculates the average value of last 10 (as mentioned in cell C3) candles of column E data. Similar logic holds for column G where standard deviation is calculated. The “z” score is calculated in the column H. Formula for calculating “z” score is z= (x-)/(σ). Here x is the sample (Column E), is the mean value (Column F) and σ is the standard deviation (Column G).

Column I represents trading signal. As mentioned in the input parameters, if “z” score goes below -2 we buy and if it goes above +2 we sell. When we say buy, we have a long position in 3 lots of Nifty and have short position in 1 lot of MSCI. Similarly when we say sell, we have long position in 1 lot of MSCI and have short position in 3 lots of Nifty thus squaring off the position. We have one open position all the time. To understand what this means, consider two trading signals “buy” and “sell”. For the “buy” signal, as explained before, we buy 3 lots of Nifty future and short 1 lot of MSCI future. Once the position is taken, we track the position using the Status column, i.e. column M. In each new row while the position is continuing, we check whether the stop loss (as mentioned in cell C6) or take profit (as mentioned in cell C7) is hit. The stop loss is given the value of USD -100, i.e. loss of USD 100 and take profit is given the value of USD 200 in the cells C6 and C7 respectively. While the position does not hit either stop loss or take profit, we continue with that trade and ignore all signals that are appearing in column I. Once the trade hits either the stop loss or take profit, we again start looking at the signals in column I and open a new trading position as soon as we’ve Buy or Sell signal in column I.

Column M represents the trading signals based on the input parameters specified. Column I already has trading signals and M tells us about the status of our trading position i.e. are we long or short or booked the profits or exited at the stop loss. If the trade is not exited, we carry forward the position to the next candle by repeating the value of the status column in the previous candle. If the price movement occurs in such a way that it breaches the given TP or SL then we square off our position thus denoting it by “TP” and “SL” respectively.

Column L represents Mark to Market. It specifies the portfolio position at the end of time period. As specified in the input parameters we trade 1 lot of MSCI and 3 lots of Nifty. So when we trade our position is the appropriate price difference (depending on whether we are bought or sold) multiplied by the number of lots.

Column N represents the profit/loss status of the trade. P/L is calculated only when we have squared off our position. Column O calculates the cumulative profit.


The output table has some performance metrics tabulated. Loss from all loss making trades is $3699 and profit from trades that hit TP is $9280. So the total P/L is $9280-$3699=$5581. Loss trades are the trades that resulted in losing money on the trading positions. Profitable trades are the successful trades ending in gaining cause. Average profit is the ratio of total profit to the total number of trades. Net average profit is calculated after subtracting the transaction costs which amounts to $91.77.

Now it is your turn!

  • First, download the model
  • Modify the parameters and study the backtesting results
  • Run the model for other historical prices
  • Modify the formula and strategy to add new parameters and indicators! Play with logic! Explore and study!
  • Comment below with your results and suggestions

Click to download the excel (If you have not logged in yet, first login/sign up!)

Login to access this file for FREE!

Existing Users Log In
 Remember Me  
New User Registration
*Required field