Pair Trading is a market neutral strategy where two highly co-related instruments are bought and sold together when there is a certain degree of deviation in their co-relation. Usually the stock or commodities selected for Pair Trading are from the same sector and moves together during most of the market events. For example: Banking stocks are always highly co-related since there long term movement depends on the same economic or news based factors. Pair Traders watch the co-related stocks over time and take action when they see any weakness in the co-relation. They go long in one of the stock and go short in another. The basic assumption is that the long position would profit more than the short position when the co-relation is strong again, or vice versa. In this post, we are giving away a Pair Trading Excel sheet which would help you automate this strategy. Also, there is a backtesting feature in the Excel sheet through which you can check the performance in different pair of Stocks. Although this is a low risk strategy, yet it should be practiced with caution. A proper Risk Management is mandatory for Pair Trading.
Pair Trading Excel Sheet
In this Excel sheet, we are going to identify Pair Trading candidates by comparing their current Price Ratio with 50 days average Price Ratio. A pre-defined divergence of the price ration from the 50 days average would signal a trade. When that happens, one has to go long in one Stock and short the other. This is a very basic Pair Trading strategy, but very effective. You can also check the past performance of the selected pair for last 1000 Trading days in the Excel sheet itself.
| Parameter | Description | |
| Worksheet Name | Pair Trading | |
| Inputs | Stock 1, Stock 2 (Cell E1,E2) | Selected Pair Trading instruments |
| Buy (Cell G1) | The Stock which should be bought when Pair Trading opportunity is signaled. | |
| Divergence% (Cell G2) | The divergence from of price ratio from 50 days average ratio which would signal a Pair Trading opportunity. | |
| Investment per Stock (Cell G3) | The Investment amount per stock for Pair Trading | |
| Outputs | Total Profit (Cell K1) | Total profit for the selected pair in 1000 day backtesting period |
| Profit % (Cell K2) | Profit in % terms | |
| Cell A5:O1000 | The complete trade log with divergence indicator | |
Screenshot
Below is the screenshot of Pair Trading Excel sheet. It indicates Pair Trades for HDFC Bank and Hindalco with divergence factor of 5%. The total profit of 31.07% is quite impressive. It would have been even better if a stop loss is maintained for losing trades.
How to use Pair Trading Excel Sheet?
Step 1: Identify two co-related stocks as a pair Trading candidates. Mostly we recommend to use the stocks with similar Beta Values. Find Beta values for NSE stocks here.
Step 2: Copy the Stock names in the designated cells in Excel Sheet. The format should be NSE:<Stock Name>. The prices would automatically populate in the sheet.
Step 3: Select from the drop-down(cell G1) that which stock you want to Buy when Pair trade signal is indicated. For ex: If you enter Stock 1 here, then you would go Long in Stock 1 and go short in Stock 2.
Step 4: Enter the Divergence % and Investment per Stock.
Step 5: Check out the Pair Trading performance (Profit %). Adjust the above values to see the changes.
Step 6: Scroll down at the bottom of the sheet to see if Pair Trading signal is generated today.(Yes in Divergence column indicates Pair Trade Opportunity)
No comments:
Post a Comment