Hello,

This is a similar question to the one I asked before, but, slightly different. I want to find the top loss trade and bottom win trade from the table below.

Top loss trade begins, there will be both a run-up and a period during which the profit made from the run-up is lost. Hence it is necessary that one run-up is followed by a period during which the profits made from that run-up are then lost before another run-up can start.

In this case, a winning trade is immediately met by a losing trade after the next trade and for Bottom Win trade begins, in this case, when the next winning trade begins after a loss or period of no gain expressed as zero.

What I would like to calculate is, the maximum top loss trade, which precedes a negative or losing trade. And, what the maximum bottom win trade is immediately after a negative trade and/or trade with a value of zero.

As you can see in the table, the maximum top loss trade before a loss is USD 1,126,399.31 on 6-Jan-08, the maximum bottom win trade is USD 1,115,023.81 on 11-Jan-08. These are the two answers, maximum top loss trade = USD 1,126,399.31, maximum bottom win trade = USD 1,115,023.81.

Without using macro’s, how can I get excel to calculate these two answers automatically once I have inputted the data into the return (%) column? Lastly, is it possible to include into the formula, the date on which the maximum trades occurred i.e. USD 1,126,399.31 on 6-Jan-08, USD 1,115,023.81 on 11-Jan-08?


Date Capital Return ($) Return (%) Profit ($)

1-Jan-08 USD 1,020,605.00 1.00% USD 10,206.05
2-Jan-08 USD 1,030,811.05 1.00% USD 10,308.11
3-Jan-08 USD 1,041,119.16 1.00% USD 10,411.19
4-Jan-08 USD 1,051,530.35 1.00% USD 10,515.30
5-Jan-08 USD 1,083,076.26 3.00% USD 32,492.29
6-Jan-08 USD 1,126,399.31 4.00% USD 45,055.97 <--------max top loss before loss
7-Jan-08 USD 1,115,135.32 -1.00% (USD 11,151.35)
8-Jan-08 USD 1,103,983.97 -1.00% (USD 11,039.84)
9-Jan-08 USD 1,103,983.97 0.00% USD 0.00
10-Jan-08 USD 1,103,983.97 0.00% USD 0.00
11-Jan-08 USD 1,115,023.81 1.00% USD 11,150.24 <--------max top loss before loss


Thank you again, look forward to hearing from you.