Backtesting in Excel

Hello,

I was trolling the web and this site seemed like one to pose this question to.

I am backtesting a trading system in Excel. I am using mostly IF() functions.

Each trade has an exit stop point, and each trade has an profit target. Obviously, if the market trades past the profit target, it should be written up as a profit, and if it trades down to the stop out point it should be written as a loss. The market may also trade sideways and hit neither the profit target or the stop loss, here I'll assume this condition to be a loss.

If the highest high goes past the profit target over the following ten days after the buy signal, and the max low is greater than the stop point, obviously this trade should be chalked up as a profit.

However, the problem I am facing is that sometimes the market hits the stop out point after it hits the profit target, my current logic counts this as a loss, even though in reality it would have been a profit.

How do I program excel to recognize that if the market trades to the profit target and then trades to the stop loss, the trade was profitable?

Here is the logic I am using currently.

=(IF(("long signal"),((IF((AND(((MAX("high over next ten trading days"))>("Current Close"+"Risk per share")),(MIN("low over next ten trading days")>("current close"-"target profit per share")))),($1000),(-($1000))))),(0)))

"If there is a long signal, and if highest high over the next ten trading days is greater than profit target AND the min over the next ten trading days is greater than the stop out point, display a profit, if not, display a loss."

Here is a link to a sample file.


https://drive.google.com/file/d/0B6o0g-zB7-SnQWZGYUo3TE9wejA/edit?usp=sharing

I really appreciate any help.

 

There's no way to tell with OHLC data, you need increased granularity through intraday data. Roughly, you'll find it's a pretty even split between profit/stop assuming you're trading on the first touch of a level.

 

Thanks for you reply.

There are some days where the market hits both my profit target and my stop loss, but there are relatively few. I decided to just count them as losses.

What I am having a problem with is that there are some ten day periods where the market hits both my stop and the profit target.

 

This is an age-old problem with daily data: which came first, the high or the low ?
In your case, you need an indicator to tell you if the stop price was hit before the target price.
Solution: use 15 minute bars for backtesting.