Using excel as a first stop to back testing

I have posted plenty of times about how I use excel to find out what is to be expected. A good example of  the way markets moves is here.  To do these types of charts, which are really more helpful than a static candle chart, I thought I would run through an example.

I mostly trade the HSI futures on the Hong Kong stock exchange. There is another index future that moves very similar, the HHI, Hang Seng China Enterprises Index. Its currently at 11540 while the HSI is almost double the value at 20300, both futures are $50 HKD per tick. I have noticed that recently the daily range has almost matched that of the HSI in points, which would mean double the % move. Now that's not a huge relevance as the fut is the same per tick but still its of interest, not to mention that the brokerage is cheaper on the HHI so more bang for the buck.

Anyway how to go about finding the range  and % range to compare in excel? These file are the day OHLCV for the Dec & Jan futs combined HSI and HHI. Download and save each to file then open each in excel. When you open select all files to fine the text data file you saved. See these steps if your not sure how to get it into excel, 1 2 3 4 .

This is what you end up with for the HSI, note I have added a heading line to make it clear what each column is.

 
 

So do the same for the HHI and then paste them into the same worksheet. Making sure the dates align and you leave some room to work on the data.

Ok. First step is to calculate the daily range: High - Low. So in cell H2 type =C2-D2 and press enter. You get the daily range.

Then "grab" the little box on the right had corner and drag down to the bottom of your data. This is a vid of the next few steps of finding the range and the daily range.

 

 

Now we have to have a way of comparing them. I like to do this by charting the averages to see how the data changes over time. Since we are only working with 41 days of data I will plot the 5 day averages of the % to see what type of relationship there is between the two. Here is the graph once you have the data.

As you can see the HHI has larger moves as a percentage and seems to be gaining in % range over January. You can also compare the average range with that data.

So yes the HSI has a larger range expressed in points but there is only a small difference and then maybe the cheaper brokerage or margin may make it worth while having a look at. Either way we now know how they move in hard stats thanks to 10 minute spent with excel.

Here is the spreedsheet that I used if you need to play around with it.

There is of course a hell of a lot more to do with simple OHLCV data and excel. Brett Steenbarger's book "The daily trading Coach" has a whole chapter on ways to use this technique to find edges in the market. I will show some more examples soon, Any suggestions?

  

 

Comments

[DT]

MacroMan has a nice little study on the recent outperformance of Mondays as compared to the other days of the week. He also notes that this is a recent phenomenon.

Look here: http://macro-man.blogspot.com/2010/02/monday-monday.html

[Factory]

For backtesting I reckon you really need to be proficient in programming to do test with sufficient speed/throughness. Spreadsheets are too limited in the things they can do, beyond simple stuff as this. (VBA helps a bit, but for more complex stuff it's more of a limitation)

OTOH I've developed my own systems for analysis of eod data, and it's alot of work before you can get a system up and producing useful analysis. (especially if you are a fiddler like me)

[Sam Collins]

excellent Jason, this is the stuff :) just one thing, do you import the data from NT? or get it from Yahoo or something?

Cheers

[Sam Collins]

nevermind, found it in NT :)