Hi this is my 1st post here and while i would say i have a reasonable grasp of excell i have struggled with this so if anyone can help i would apritiate it.
I have a trading strategy (commodities gold, silver , wheat etc , forex) that i wish to back test using excell. My strategy produces buy or sell signals periodically and my data is ordered in data order so for eg my spread sheet might look like this
gold
A B C D E
date open , close, signal, close position
3/4/07 ,674.00 ,682.00 ,buy,
4/4/07 682.00 685.00,
5/4/07 685.00 683.00,
6/4/07 683.00 688.04,
7/4/07 688.04 687.34 sell
8/4/07 687.43 683.22
9/4/07 683.22 679.45 buy
10/4/07 679.45 680.21
etc
what i would like to be able to do is work out the p/l of trading the system so for eg
on 4/4/07 i buy at the open on 4/4/07 (signal generated on 3/4/07) at 682.00and close by sell at 687.43 on 8/4/07 after sell signal on the 7/4/07 for 5.43 profit , open new sell order at the same time on the 8/4/07 at 687.43 and close buy buying on 10/4/07 at 679.45 for profit of 7.89 and open new buy order on 10/4/07 at 679.45.
Also i wish to test other ways to close the order which may not be just the opposite signal ie buy 4/4/07 and sell 8/4/07 from a seperate signal in another column called close position (E) which may be activated earlier than the reverse signal buy/sell which would say close if true and blank if not true
This needs to be linked to the opening order ie in my eg above 4/4/07 buy
and if not activated will be cancelled if the reverse order is activated ie if a the close position is not indicated between 4/4/07 and the 7/4/07 (when the sell signal is shown) then the close parameters will be cancelled and a new close set of parameters set ie when buy open order 4/4/7 is given then the close poition paramaeters are set for that order, once a sell order is given a new set of close position parameters are set. If a position is clossed due to a close position signal then the p/l is calculated from this point rather than in the eg from the buy - sell diff
hope this makes sense and hope someone can help
Bookmarks