# Some (Interesting/Useless) stats on the SPI



## Trembling Hand (16 January 2009)

Thought I might post some stats that I play around with for fun (Whoopee!! )


----------



## jersey10 (16 January 2009)

Trembling Hand said:


> Thought I might post some stats that I play around with for fun (Whoopee!! )




so does this mean you are more likely to get your outlier moves early in the day and late in the day?

Empirically speaking, do you find that you take more points out of the spi during the times when the range is greater?


----------



## Trembling Hand (16 January 2009)

No great surprise in these stats. But you need to know what to expect. Both  for possible profit and for possible losses and therefore MM/position sizing.

No point having a 30 point profit target if you hold for 10 min. the Averages are not in your fav. Especially outside the open & close.


----------



## AzzaB80 (17 January 2009)

Thanks very much for this TH. 

Do you have any stats on how often one extreme of the day is set in the first hour?

Also, is it possible to export historical data out of IB and into Excel & do you know how?

Cheers
AB


----------



## Trembling Hand (17 January 2009)

AzzaB80 said:


> Thanks very much for this TH.
> 
> Do you have any stats on how often one extreme of the day is set in the first hour?
> 
> ...




Down load Ninja trader connect it to IB then use the "Export Historical Data" function.

Can probably do a test on that but not sure what you would do with such info.


----------



## Trembling Hand (15 March 2010)

These stats are for the XJO but interesting and applicable to trading the SPI in any case,

Chances of the daily High or Low also being the weekly High or Low,






Bit more about it on my last blog post.


----------



## brty (15 March 2010)

Thanks for the stats TH, they are indeed important...

They mean....






























Start lunch early and have a long one...

brty


----------



## Trembling Hand (15 March 2010)

brty said:


> Start lunch early and have a long one...
> 
> brty




Yeah, especially if your Monday trade has not been stopped out yet. lunch can go right up to Friday arvo.


----------



## brty (15 March 2010)

Sorry, didn't look at the last stats.

Let's just add a mid week break, to recover from Mondays long lunch..

brty


----------



## ThingyMajiggy (19 March 2010)

Nice stats TH  

I have been trying to do some for the HSI. Got the 10 Minute Range here from todays morning and afternoon sessions, probably doesn't mean much, is there some easy fast way of doing this over the whole month or something? 

For this I just exported todays minute data and did it all manually and went down each column, so column D1-D10, D11-D21 etc for both sessions on the high and low, could I do the drag down thing after entering it into 1 column? That way I could import the whole months minute data and find the X minute range way easier, would take ages doing it this way! 

Also, to actually use stats like this, you would say that for eg. you would be pushin it hoping for a 50-60 point target in the middle of the session if you held for 10 mins?? Targets of 20 points are much more realistic? 

Cheers


----------



## ThingyMajiggy (19 March 2010)

Oh also, does anyone know how to calculate if a cell is less than the 2 cells above it? I want to see how many times we get X lower closes in a row, but not sure of formula eg. I want to know if cell E3 is less than cell E2 AND E1. 

I've tried:

=E3<E2,E1
=E3<E2+E1
=E3<E2ANDE1

No success yet


----------



## Trembling Hand (19 March 2010)

lol Sam. You have to learn how to use the drag down auto fill in thingo. For god sake!!! 

Here is the formula you are looking for,

=IF( E2<E1,IF(E3<E2, TRUE, FALSE))


----------



## skyQuake (19 March 2010)

Or you can also use the 'and' function, it'll save you some effort; not the mention the logical nightmare when there are too many 'ifs' in a more complex code.

=if(and(e3<e2,e2<e1),"True","False")


----------



## ThingyMajiggy (19 March 2010)

Trembling Hand said:


> lol Sam. You have to learn how to use the drag down auto fill in thingo. For god sake!!!
> 
> Here is the formula you are looking for,
> 
> =IF( E2<E1,IF(E3<E2, TRUE, FALSE))




Yeah I know, how would I use the drag feature for working out the 10 minute range over the whole month then? If I get just 1 days minute data and put in =max(C1:C10) thats going to give me the highest high in the first 10 minutes of trading. But if I drag that down thats not right is it? Because I want to start the next measurement from C11, I tried just copying and paste that formula into cell 11, but it yelled REF! at me, calling for the ref, it's obviously against the rules  

Thanks for the formula  So....if I'm getting this right, from all the minute data in March, there is an 80% chance that we _WON'T_ get 3 lower closes, this is on a 1 minute basis. 

Also, the average 1 minute range is currently around 13 ticks, based on March 's 1minute data...I think  

This is interesting this stuff


----------



## Trembling Hand (19 March 2010)

Sam when I need to make 1 min data into 10 min for example I use Access. Thats a whole different game again. You can do 1000 mill more things with a database.

But of course 10000000 mill more things to learn to be able to do it. 

Maybe SkyQ has an excel solution?


----------



## Trembling Hand (19 March 2010)

ThingyMajiggy said:


> Yeah I know, how would I use the drag feature for working out the 10 minute range over the whole month then? If I get just 1 days minute data and put in =max(C1:C10) thats going to give me the highest high in the first 10 minutes of trading. But if I drag that down thats not right is it? Because I want to start the next measurement from C11, I tried just copying and paste that formula into cell 11, but it yelled REF! at me, calling for the ref, it's obviously against the rules




On second thoughts you can do it but its messy. If you start at cell  I10, type in your =max(C1:C10) then select I1 to I10 and drag you get the max formula for every 10 lines, see pic.


----------



## ThingyMajiggy (19 March 2010)

Trembling Hand said:


> On second thoughts you can do it but its messy. If you start at cell  I10, type in your =max(C1:C10) then select I1 to I10 and drag you get the max formula for every 10 lines, see pic.




Alright, cheers for that. 

What are some important stats one should be looking for?


----------



## AzzaB80 (19 March 2010)

I've got some Excel macros that I've made which will work out and graph:

Average Volume per 30 Minute Period
Range Between Preset Times
Average Daily Range 
Average Range per 30 Minute Period

All you need to is paste the data in from Ninja and hit run. Oh and you will need to change the times as these are all based on european market hours. I'm thinking of puting dialog box prompt in so it can be adjusted on the fly.

Happy to give anyone a copy as long as they don't pick on my code ;-)


----------



## sleepy (19 March 2010)

Nice work T/H. For those interested heres some general stats on the SPI and Hang Seng that get updated daily.

http://www.mypivots.com/dn/?symbol=254
http://www.mypivots.com/dn/?symbol=299

Most of the main US/UK futures contracts can also be found here. 

http://www.mypivots.com/DailyNotes

sleepy


----------



## skyQuake (19 March 2010)

Trembling Hand said:


> On second thoughts you can do it but its messy. If you start at cell  I10, type in your =max(C1:C10) then select I1 to I10 and drag you get the max formula for every 10 lines, see pic.




its certainly the easiest  Messy? But it works! If you want to display it all nice (without the 10line gaps) then thats gonna be a bit of work.
--------------------------------------------------------
Just thinking now if it would be actually _better_ to drag it down instead of for every 10lines. That way you get a more 'representative' 10min range rather than for the arbitary time scales... you smooth the range a bit and it also misses end of period bar painting..


----------



## Trembling Hand (19 March 2010)

skyQuake said:


> its certainly the easiest  Messy? But it works! If you want to display it all nice (without the 10line gaps) then thats gonna be a bit of work.
> --------------------------------------------------------
> Just thinking now if it would be actually _better_ to drag it down instead of for every 10lines. That way you get a more 'representative' 10min range rather than for the arbitary time scales... you smooth the range a bit and it also misses end of period bar painting..




Yep on third thoughts its not really the way to go. If it doesn't trade for 1 min you are going to have bars 11 min or greater and then everything is going to be out of whack. Only real way to go is via running queries in a database I reckon.


----------



## skyQuake (19 March 2010)

Trembling Hand said:


> Yep on third thoughts its not really the way to go. If it doesn't trade for 1 min you are going to have bars 11 min or greater and then everything is going to be out of whack. Only real way to go is via running queries in a database I reckon.




There are a few ways even if minute bars are missing, mostly vlookup and stuff.
Havent used ninjatrader for a while, but surely you can export 10min data?


----------



## ThingyMajiggy (19 March 2010)

skyQuake said:


> =if(and(e3<e2,e2<e1),"True","False")




This formula would say if there were 2 lower closes in a row wouldn't it? if e3 is less than e2 and e2 is less than e1, so 2 lower closes? 

So for 3 consecutive lower closes, it would be =if(and(e4<e3,e3<e2,e2<e1),TRUE,FALSE) yes? 

So, if I'm correct, then looking at the 1 minute data from March so far, there is only a 9% chance that we will get 3 lower closes in a row on a 1m chart? I must have this wrong? Doesn't sound right. 

It must be because I'm dragging it down over each row, so each 1 minute, so is e4<e3,<e3<e2,e2<e1 that is right, but then I'm just dragging it down over all of them so then it would do that same calculation on the 2nd row, so e5<e4,e4<3,e3<e2, where as it should be from 4th row to be accurate? 

Fark, my head hurts


----------



## ThingyMajiggy (19 March 2010)

Trembling Hand said:


> Yep on third thoughts its not really the way to go. If it doesn't trade for 1 min you are going to have bars 11 min or greater and then everything is going to be out of whack. Only real way to go is via running queries in a database I reckon.




Yeah that's what was happening with me when I tried it, it got all skewed and offset. 



skyQuake said:


> There are a few ways even if minute bars are missing, mostly vlookup and stuff.
> Havent used ninjatrader for a while, but surely you can export 10min data?




As far as I know you can't? It's only got day, minute or tick. I most definitely would have chosen the 10 minute option if so, rather than trying to do it off 1 min data.


----------



## skyQuake (19 March 2010)

ThingyMajiggy said:


> This formula would say if there were 2 lower closes in a row wouldn't it? if e3 is less than e2 and e2 is less than e1, so 2 lower closes?
> 
> So for 3 consecutive lower closes, it would be =if(and(e4<e3,e3<e2,e2<e1),TRUE,FALSE) yes?
> 
> ...




Looks right to me; You may also be discounting the lower or equal closes. The 1min bar on spi spikes around a bit so it sounds about right.


----------



## ThingyMajiggy (19 March 2010)

skyQuake said:


> Looks right to me; You may also be discounting the lower or equal closes. The 1min bar on spi spikes around a bit so it sounds about right.




I'm talking HSI.


----------



## skyQuake (19 March 2010)

ThingyMajiggy said:


> I'm talking HSI.




lol probably more so for the HSI, the spread alone can be 2x range when it gets crazy 

Can you check with the HSI data (or spi), if theres no vol in that minute does it still print a bar in excel or skips it?


----------



## ThingyMajiggy (19 March 2010)

skyQuake said:


> lol probably more so for the HSI, the spread alone can be 2x range when it gets crazy
> 
> Can you check with the HSI data (or spi), if theres no vol in that minute does it still print a bar in excel or skips it?




As far as I can see, there are no bars with 0 volume, and it hasn't skipped any.


----------



## Trembling Hand (19 March 2010)

ThingyMajiggy said:


> What are some important stats one should be looking for?



As for this question.

Whatever helps you make sense of moves. For example.

Its an up week(SPI/XJO) , its Friday, we have just made the weekly high. Now where have I seen such info??


----------



## Naked shorts (21 March 2010)

you should have added a poll so we can vote whether these stats are useless/interesting


----------



## nomore4s (31 March 2010)

Some more useless SPI stats.

Not a huge data sample - only based on this month. While I try to keep larger samples like TH, I also like to break things down into weekly & monthly data to see if I can catch onto any patterns that may only last while the market is in a certain phase.

Unfortunately I've had computer issues and have lost some data hence the blacked out bit but you can still get the gist of how the market has been moving in March. I've also had to split the data where the contracts rolled over. All stats are based on the open of the SPI @ 9:50am to 4:30pm close Sydney time

Most of these stats are nothing new and TH in particular has covered a lot of this before but thought someone may find it interesting.

This month the XJO has put on around 280 points but on the SPI pretty much all of the gains have come overnight. 
Also the first hour of trading on the SPI price generally moves in the opposite direction to the overnight move.
The other interesting one is that it's not uncommon for the second half of the day to move in the opposite direction to the first half of the day.


----------



## Trembling Hand (31 March 2010)

The gain in overnight gaps is always an interesting move. Makes day traders look like idiots doesn't it.


----------



## nomore4s (31 March 2010)

Trembling Hand said:


> The gain in overnight gaps is always an interesting move. Makes day traders look like idiots doesn't it.




hahaha yeah it does. When the markets are moving up I've noticed that's haw they tend to make all their gains though.

Amazing how so few understand how the market actually moves.


----------



## sleepy (31 March 2010)

Reminds of a post you did a while back T/H about most gains in bull market when trading equities was made via overnight gaps (which funnily enough arent tradable). Although couldnt find it anywhere.

sleepy 

With respect to Futures do you have any stats looking at gap open reversals (i.e, market gaps open then reverses in first 10-15m ?


----------



## Trembling Hand (31 March 2010)

This one,

http://tremblinghandtrader.typepad.com/trembling_hand_trader/2007/07/spi.html


----------

