Excel statistics, trending, patterns, probabilities

[ctrlbrk]

I've been meaning to put this together for a while. Tonight, I finally took a stab. I wanted to post my work so far so in hopes the forum community can improve on this.

The idea was to have an Excel spreadsheet that can answer questions I had about certain patterns and probabilities, in a statistical fashion.

For example:
- Most & Average consecutive days trending up
- Most & Average consecutive days trending down
- Most & Average consecutive days with no trend

- Probability tomorrow is an up day/down day

I created a simple Ninja script to dump data to a text file containing basic information:
Date, Instrument, Open price, Close Price

Then I imported that into Excel and built formulas around it.

Going forward, I would enhance the Ninja script to use a MySQL database instead, and same with Excel - I would attach it to a DB so I don't have to constantly pull new figures into the spreadsheet.

I just want to load the spreadsheet, and it has the most recent info ready to go. This would still require running the Ninja strategy once per day or whenever I want to update the values, but I can live with that.

The ultimate goal of this project was for me to see if there were any useful probabilities that I could apply to my strategies. Such as using weighted expressions if the odds say today was going to be a down day, etc.

This is sort of "step 1" in my ultimate longer-term and much bigger project of building a self-adapting, self-learning strategy that determines what works and what doesn't, and adapts in real time to accommodate.

The next post will contain the files.

Mike

[ctrlbrk]

Ok:

ninja.txt is the output file from the bardata.cs strategy. I run it against 1 minute data. I only went back to 1/1/2008, but once I have time I will go back as far as I can. This is hard with Zen Fire, so if someone else could provide this file with a long, long history, that would be great!


bardata.cs (zipped) is just a simple script using StreamWriter to write a plain text file, and GetBar to find the Open[] and Close[] values for the day (session open/close). You'll want to modify the code for your local time zone.


ninja spreadsheet.xlsx (zipped) is the Excel spreadsheet containing the imported ninja.txt data and the formulas I've created or thought of so far (lots of work to be done here). If you do not have Office 2007, download the free XLSX converter which works on earlier versions of Office (http://www.microsoft.com/downloads/d...displaylang=en)


Mike

[ctrlbrk]

Due to another ridiculously low forum limit, I am having to upload the last file on this third post. Here it is.

Thanks all!

Mike

[heech]

Hey there,

I don't think you and I have discussed this in the past. In a past life, during graduate school (I majored in computer science), I did some coursework with artificial intelligence and pattern recognition. You're basically putting together Bayesian network, and there's quite a bit of information available out there.

I think there's definitely potential out there. I'll play with the spreadsheets when I get a chance.

[ctrlbrk]

Thanks heech. I unfortunately was forced to delete the private message from whoever it was that shared this common interest, before Ray raised the PM cap. I can't remember who it was!

Your input is appreciated when you have time. Judging by the "off-topic" thread "anyone making money?" you are way smarter than me, lol!

Mike

[heech]

Thanks heech. I unfortunately was forced to delete the private message from whoever it was that shared this common interest, before Ray raised the PM cap. I can't remember who it was!

Your input is appreciated when you have time. Judging by the "off-topic" thread "anyone making money?" you are way smarter than me, lol!

Mike

How about way more delusional? This month is pushing me way, way back to the drawing board! Hopefully next month will be more promising, as I'm spending all my time figuring out how to screen out the "bad" stuff.

[TraderGuy]

This is hard with Zen Fire, so if someone else could provide this file with a long, long history, that would be great!

Hi Mike,

Tell me what time period you are looking for and how far back you would like and I'll see if I can help. Also PM me your email address so I can send you the zipped file.

Guy

[ctrlbrk]

Hi Mike,

Tell me what time period you are looking for and how far back you would like and I'll see if I can help. Also PM me your email address so I can send you the zipped file.

Guy

Hi Guy,

Thanks! For this project, just 1 minute data is all that is required because the script just needs the opening bell and closing bell prices. If you'll modify the script so the logfile output is in a directory that exists and make sure timezone is correct for the ToTime parameter (needs to be closing bell time), then the script will do it.

I would like at least a few more years of data if you've got it, like back to 2004 or so if you have it.

PM sent.

Mike

[ctrlbrk]

How about way more delusional? This month is pushing me way, way back to the drawing board! Hopefully next month will be more promising, as I'm spending all my time figuring out how to screen out the "bad" stuff.

LOL! How about we just say you are much more creative than me Good luck!

Mike

[ctrlbrk]

Thanks to the generosity of others, here is the new Excel spreadsheet with 5 years of minute data inside of it, along with the rest of the formulas (have made no more progress on the formulas yet).



(sigh, well can't upload it due to small forum restrictions, so here is a link):

http://www.mgbi.net/ninja/ninja%20price%20spreadsheet%2004-05-2009.zip

Mike

[sefstrat]

Some books on a related topic that might be of interest to you =)

http://www.amazon.com/Computational-Intelligence-Time-Forecasting-Applications/dp/1852339489

http://www.amazon.com/Identification-Predictive-Control-Advances-Industrial/dp/1852338288/ref=sip_rech_dp_4

http://www.amazon.com/Intelligent-Systems-Pattern-Recognition-Computing/dp/3540241213/ref=sip_rech_dp_5

http://www.amazon.com/Modeling-Simulation-Nonlinear-Engineering-Dynamical/dp/1402087772/ref=sr_1_3?ie=UTF8&s=books&qid=1239002110&sr=1-3

I have been working on neural-fuzzy pattern recognition system for a month or so now. Have not traded real money with it yet but in realtime simulation the results are quite good.

One thing I will say though is don't expect to have the system identify patterns that you cannot identify. The ideal situation for this approach is when you can 'see' a recurring pattern but can't easily quantify it with exact numbers.

That is the distinction between 'Computational Intelligence' vs. 'Artificial Intelligence'. With CI or soft computing you are defining the patterns and mostly using the computer for data mining and recognition of complex patterns as they unfold, as opposed to AI where the machine can learn new patterns. Pure AI techniques are notorious for overfitting with complex nonlinear systems unless there is an extremely long training window, and even then the results may not be that great in systems with alot of noise.

In a neural fuzzy system for example you are only using the neural network to optimize the parameters associated with your fuzzy logic control system. Fuzzy logic, in a nutshell, implies that the logical states of the system are predefined but the transition conditions and/or the connections between states are up for interpretation.

[ctrlbrk]

Thank you. As I've been gathering data and tinkering with this, I have strayed into neural networks and even tried a few Excel add-in's like NeuroXL Predictor. I feed it some additional data like HMA's, %R's, etc.

Still playing.

Mike

[ctrlbrk]

As part of this project, I am now also building a tick database with various instruments so I can later query that info. I did this using a MySQL DB from this thread:

http://www.ninjatrader-support2.com/vb/showthread.php?p=87746#post87746

Mike

[maxima]

So... have you made any money on the AI? What about you sefstrat?

Hmmm.... both disappeared 2 yrs ago... went rich? I hope ;-)