Spreadsheet testing OHLC.

Discussions about the testing and simulation of mechanical trading systems using historical data and other methods. Trading Blox Customers should post Trading Blox specific questions in the Customer Support forum.
Post Reply
Nathan
Roundtable Knight
Roundtable Knight
Posts: 127
Joined: Sun Jul 13, 2003 3:52 am

Spreadsheet testing OHLC.

Post by Nathan » Wed Dec 03, 2003 2:17 am

I have been Testing various daily patterns using OHLC data for stocks using spreadsheets. I am able to compute things like profit from open to close, or open to x days later, which perhaps can be used to find a market tendancy. So far ive looked at X # of day streaks, gaps, combinations of the two, activity after large price ranges, and what i call box days, which is where X # of days close fall within a prior price range. Im just getting started but its pretty fun.

I have also been able to simulate a breakout from open price after certain pattern has set up.

So far I don't have a grasp on simulating stops or trailing stops. If anyone has done this, I would appreciate any ideas.

Also, if anyone is interested in OHLC patterns and exchanging ideas, feel free to email me. At this point i was just interested in looking at market dynamics, I don't have the capability to do full system testing.

brendo
Senior Member
Senior Member
Posts: 41
Joined: Wed Dec 03, 2003 11:46 pm
Location: Melbourne AUSTRALIA

OHLC Spreadsheet Testing

Post by brendo » Wed Dec 10, 2003 12:04 am

Nathan

Have a read of this forum first : viewtopic.php?t=237&highlight=excel

I too have spent many, many hours on just this very endeavour. It is indeed a messy and time-consuming task in Excel and I am loathe to recommend anyone else try to do the same.

The approach I took was to look at the data one day at a time and to define each variable based on logical arguments. Each day's data would give a fresh opportunity to move a stop-loss if my position had moved enough in my favour. If so, the stop was reset, if not it was left alone. Once you get the hang of the Excel function IF(Condition,True,False) this all becomes straight-forward in small doses.

The biggest problem I encountered was when I tried to add in additional units (ie pyramiding). The logic part was sound, but in Excel the system became a monster and far too complex. I have put it aside in frustration.

I have started playing around with Veritrader and I am very impressed with what I've seen. Nothing else that I have seen comes close to supporting a trading system with a solid Money Management component.

However, for several reasons I prefer to test/trade Forex and that market is not currently supported by Veritrader. So, I have taken up the task of learning VBA to fill the gaps, and I will be looking very closely at Veritrader Forex developments in the future.

Hope this helps

BRENDO

Nathan
Roundtable Knight
Roundtable Knight
Posts: 127
Joined: Sun Jul 13, 2003 3:52 am

excel

Post by Nathan » Wed Dec 10, 2003 3:11 am

thanks for the feedback. I will examine the link you provided.

"Once you get the hang of the Excel function IF(Condition,True,False) this all becomes straight-forward in small doses."

My daily pattern testing method is based amost entirely on "IF" and "and" statements. So far, the only exits i have used has been based on exiting the close or open today or x number of days out. I then set up a grid of 0 and 1 columns that refer to eachother, basically to keep things organized so that individual parts can be easily tinkered with and built upon. i have also simulated a OR breakout, which i then add a slippage factor too. Obviously, this is kind of ruff, so i consider it as giving me a general idea rather than anything close too stone cold results.

Thanks for the advice about trailing stops in excel. I wanted to overlay that logic onto a few of the patterns i am playing with. However, i have found that going "x" days out suits my needs fairly well, cause at this point i am not doing complete system testing but rather looking to discover market tendancies.

Nathan

Post Reply