Hi,
I am trying to use Excel solver for the stock market portfolio where I have past history of a trader's trades and can either take profit or hit a stop loss per trade.
What I'd like to do is analyse past trades where I have the following information.
- profit
- drawdown (lowest point the price got before profit was taken)
So the data looks like:
1: (5, -10)
2: (200, -30)
3: (-3, -15)
I would like to use the excel solver to run over the data that I have and to optimise a stop loss for me. Looking at the data above a stop loss that would have allowed -30 is optimal as that resulted in a $200 profit.
So the rules are:
maximise the profit
constrained by the smallest drawdown (IF the drawdown is greater than x, the stop loss is hit) then use the stop loss else use the profit).
total profit =SUM_OF_ALL_TRADES( IF(drawdown < profitLoss THEN profitLoss ELSE profit) )
So in the data points above with a stop loss of -20 the calculation would be:
PROFT = 5 + (-20) + (-3)
5 because no stop loss hit so profit was selected
(-20) because the stop loss of -20 was hit as the price was on it's way to -30
(-3) because the stop loss was not hit so profit was selected
I am trying to get an excel solver running over this and am having issues with the IF statements that are required. I can use Excel 2010's Evolutionary solver but after playing with it a bit I don't like it that much and it spits out crazy scientific numbers.
I'd like to convert the if's into binary numbers but don't know how.
Does anyone have any suggestions on how to do this?
Much Appreciated
Bookmarks