Here is a quick one following on from this post about using excel as a start to back testing. Today the SPI gaped open from yesterdays close by more than 1%, in fact it was 1.4% from my calculations. So as a start it is not a bad Idea to have access to where the edge lies in terms of how similar days have played out. Here is a quick spread sheet I banged together in about 5 minutes.

Results are for a day when the SPI opened greater than 1% from previous cash close from 24th of July 07 up to yesterday. There has been 106 opens greater than 1%. Out of those 106 times the close on the day has been lower than the open on 62 occasions, 58%. Average day move is -11 points. So a mild negative bias. The SPI is now 25 odd points above the open. lets see how this one plays out.

Ever noticed the lack of Gaps on the ASX Indexes? Have a look at the three charts below of the S&P 500, the HSI and the All Ordinaries (XAO).

Notice in the first two, S&P 500 & HSI, that there is all sorts of gap games going on. Gaps and continual moves and gaps and rollovers to fill on the same day etc. They don't look all that dissimilar to the average stock chart. Now have a look at the third one. A lot of the bars actually start where they left off the day before. Most have the tiniest of gaps. Out of 276 days since the start of Jan 09 there has been 176 days (63%) with an overnight gap of 10 points or less and 108 days (39%) with an overnight gap of 5 points or less. That means the index data suggest that it is a two out of three chance to open 0.215% within yesterdays close and a one in three chance of opening within 0.1% of yesterdays close! BS!!

Compared to the S&P 500 taking similar % moves you get 26% and 14%. Half the chance?

Whats going on? Well it isn't that the ASX is less volatile or sluggish on the open. They both move about the same range per day. The problem with the ASX indexes are two fold. Firstly because of the staggered open from 10:00 to 10:08 the opening tick of XJO/XAO is not the true value of "opening price" of all index constituents, this is unique to ASX. In fact the data from 10:00 to 10:08 is really twilight zone stuff.

Secondly and even more strange is sometimes the first tick that the ASX provides for indexes is magically exactly the same as yesterday close, to two decimal points?? Now that would only be possible if all 400 or so stocks, particularly the big caps, that open in the first round at 10:00 am (A to B) traded at their closing price. It doesn't happen! Where this comes from is that the opening auction is at 10:00 am +/- 15 secs BUT if its say 10:00 am +12 seconds the ASX will still take the index quote from 10:00. If you have a look at the data post February 08 virtually every open tick for the ASX is exactly the same as yesterdays close. In fact only 5 of the opens were not exactly the same as the previous close of all of 07's and to two decimal places. It seems that the ASX from February 08 has changed slightly their method for quoting the first tick for indexes but it still doesn't get around the problem of the staggered open.

If you think gaps are at all significant you cannot us the ASX Index charts for a reference. There is simply 100s more gaps in the index than what shows up on the Dodgy charts. The better reference to find gaps for the ASX is the ASX200 SPI chart without the overnight trading. Here is an XJO chart and a SPI cash hours only chart. Notice all the twilight data (circled) in the ASX XJO chart that never actually happened.

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, 1234 .

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?