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
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?