How to create your own Pairs Trading XLS spreadsheet template

Introduction

You can tweak and fine tune this template to show exactly what you want, but for the purpose of this demo I have used the following: EOD (end of day) closing price data for the last 240 days for the two different shares (companies) that I want to compare. I use these templates over a number of JSE sectors, to compare the relative over and under performance of like-for-like companies. You could use different time frames and instruments to suit your needs.

XLS PAIRS CHART

I have included a sample spreadsheet above. If you are Excel savvy then there is no need to read through the description below, unless you get stuck. Enjoy.

First, Grab the data

This could be automated. In the sample spreadsheet below you’ll see the data comes from Bloomberg, so I automatically get the last x-number of days as specified. You should also be able to get this data from your trading platform, price info terminal or the internet – try google finance (I haven’t used it, but see it is available, at the time of writing) as on https://support.google.com/finance/answer/71913?hl=en

Set up your Data columns

Label your columns as follow, Number, Date, Share#1, Share#2 (can use ticker codes), Correl, Ratio, SMA, Stdev, Min3, Min2, Min1, Plus1, Plus2, Plus3. The Number, Date and Share prices are obvious – the EOD share price data comes adjacent to each other in the Share#1 & Share#2 columns. I find it easier to have the most recent data entries at the top and this template is set up that way.

Use these Formulae

Correl shows the correlation between the two shares and if you can’t remember the ‘sum of squares’ routine from stats class, don’t worry – the Excel formula ‘=correl(x,y)’ will do all the number crunching for you. We are only interested in the last rolling (most recent) 20 periods (in this case days), so your formula should read something like this

=CORREL(D44:D63,E44:E63)

where the ‘D’ range is the 20 most recent EOD prices for Share#1 and the ‘E’ range is the same, but for Share#2.

Ratio is simply the price of the one share divided into the price of the other share. It doesn’t matter which is divided into which and I simply used Share#1 div Share#2.

SMA is the simple moving average of the Ratio column, also taken over just the 20 most recent entries. Your formula is no more complicated than ‘=average(x)’, where x is the last 20 entries from the Ratio column. This is integral to your pairs chart, as it forms the mean line around which everything will be based.

Stdev is also taken over the last 20 entries. We use Excel formula ‘=stdev(x)’, where x is the last 20 SMA entries. This Standard Deviation is used to plot the lines above and below the mean, so we can see how far ‘out-of-line’ our pairs ratio is. Simply take the Stdev and add and subtract 1 to 3 of each to/from the SMA to create the Min3, Min2, Min1, Plus1, Plus2, Plus3 columns.

That’s all! Now plot the chart

I’m not going to tell you how to plot an Excel chart here, other than this: because the most recent entries are at the top, you’ll have to double-click the horizontal (Y) axis and tick the box that says ‘Categories in reverse order” to get the most recent data entries on the right hand side.

You could also add a couple of things to show a little summary of your work, including the individual share prices for Share#1 and Share#2, the Correlation between them and more. Most importantly, what is the resulting pairs chart telling you? Is there a potential trade there? See our next entry on how to interpret the pairs chart.

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll Up