Keeping Track Of A Trading System

[kevtherev]

Hello,

I have been wrestling with a 'challenge' for a few days and would appreciate a second, or third, pair of eyes.

The attached sheet is an extract, which just contains sample trades, from a much larger dataset of stock prices. There is a count of trades, a flag indicating a trade open or a trade close and a running total of positions, i.e. how many trades are currently open. A lot of the trades are closed by the subsequent trade but other trades can remain open for a longer period. There is no limit the number of concurrent open trades (although, in reality, it would be <12).

The objective is to track the opening and closing price of each trade. By the way, these are hedging trades so they might appear to be losing money, because the closing price is invariably lower than the opening price, but that is by design.

The complication is that the most recent open trade must be closed first, the previous open trade closed next, etc. Trade #1 is closed by trade #2. #15 is closed by #18. #21 is closed by #24. The challenge is how to close #10 with #25, #7 with #26, etc.

Absolute row references can't be used because there are many other rows of price data between each trade.

Any suggestions greatly appreciated.

Thanks,
Kevin

[Bo_Ry]

Please try at G2

=IF(C2="O",INDEX(A3:A27,MATCH(D2-1,D3:D27,)),A2)

[kevtherev]

Thank you, Bo_Ry. That's an elegant solution.

Best Regards,
Kevin