Calculation of 'Stop Loss'; Buy/Sell price differences

[babapusy]

Hello,

I would like to calculate the 'Stop Loss', Buy/Sell price differences from the last trade and to be more clear here is an example:

Buy
Hold_Buy
Hold_Buy
Hold_Buy
Hold_Buy
Hold_Buy
No_Trade
No_Trade
No_Trade
No_Trade
Sell
Hold_Sell
Hold_Sell
Hold_Sell
Hold_Sell
Hold_Sell
etc...

When Buy is hit, to exit if Stop Loss is hit (opposite for Sell);
To calculate the difference of the last Hold_Buy and the previous Buy (opposite for Sell);

If someone could help me out how to solve this problem, I would be very grateful.
Thank you.

[dilipandey]

Hi babapusy,


Welcome to the forum.

Sorry to say, I am unable to get your properly.. what is your query.. when Buy is getting hit ?
I believe a sample file would help Forum to understand better. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[Bob Phillips]

Put this array formula in row 2 and copy down


=IF(A2="Stop_Loss",INDEX($B$1:$B1,MAX(IF($A$1:$A1="Hold_Buy",ROW($A$1:$A1))))-INDEX($B$1:$B1,MAX(IF($A$1:$A1="Buy",ROW($A$1:$A1)))),"")

[babapusy]

Put this array formula in row 2 and copy down


=IF(A2="Stop_Loss",INDEX($B$1:$B1,MAX(IF($A$1:$A1="Hold_Buy",ROW($A$1:$A1))))-INDEX($B$1:$B1,MAX(IF($A$1:$A1="Buy",ROW($A$1:$A1)))),"")

Thanks, but I am getting #VALUE!. Tried also with MAXA, but same result. I have uploaded an example to see how it should be as shown in the green field. I don't know how to solve it.

[jolivanes]

Bob mentioned that it is an array formula.

Enter the formula, select the cell, press F2, and then simultaneously press Ctrl+Shift+Enter.

Copy down.

[babapusy]

Thank you for your help, it works now.

[Navin Agrawal]

Hi babapusy.... Its great your problem is solved....

I am also working on some automated trading system and I feel your efforts might be useful and I may want to incorporate the same (I will def let you know if I DO incorporate)..

Can you explain the significance of columns A & B and what exactly are they?

Thanks...

[babapusy]

....
Can you explain the significance of columns A & B and what exactly are they?...

Thanks...

The situation is self explanatory. Column A is the Buy/Sell signal and column B is representing one of the values of OHLC. The simulation is when Buy is hit and Stop_Loss applied, then to make Stop_Loss - Buy. Don't use this in your project as it is for testing purposes only and you have to edit it a bit to get what you want.

[babapusy]

I have another problem and tried with AND and OR conditions, but none of them made me to a success. I am seeing that the problem perhaps lies in the MAX and/or ROW function, but where exactly?
I uploaded another example where in column C19 should be the difference between A19 and A10. I need this because sometimes there are signals that overlap the stop_buy signal with sell signal and therefore the stop_buy is only at the sell signal.
Please look at the example.
Thanks.

[Bob Phillips]

Can you explain again why C19 should be A19-A10, your last explanation was so far over my head I barely saw it.

[babapusy]

Can you explain again why C19 should be A19-A10, your last explanation was so far over my head I barely saw it.

Sure. In this case only, when buy signal appears, means that the exit price has to be subtracted from the entry price, therefore A19 represents the exit price or STOP_BUY(in case of buying) and A10 is the entry price or BUY signal. In case of selling will be opposite - A10-A19.

I made it this way because if the trend is up in case of buying, then positive value should be presented(profiting), but in case of buying and the trend is down, then negative value should be presented(profit loss). Opposite for selling case.

[babapusy]

It's ok, I found solution to the problem. Thanks anyway. For further help, I will ask additional questions.

[jolivanes]

Would it be too much to ask how the problem was solved for people in the future who might have the same problem?

[babapusy]

Would it be too much to ask how the problem was solved for people in the future who might have the same problem?

Not at all. I thought that the essentials are already given by Bob Phillips, so I considered it would be of no concern to anybody, but if you ask for the solution here it is:

Assuming that you already know what was the question,

Quote Originally Posted by Bob Phillips View Post
Can you explain again why C19 should be A19-A10, your last explanation was so far over my head I barely saw it.

Sure. In this case only, when buy signal appears, means that the exit price has to be subtracted from the entry price, therefore A19 represents the exit price or STOP_BUY(in case of buying) and A10 is the entry price or BUY signal. In case of selling will be opposite - A10-A19.

I made it this way because if the trend is up in case of buying, then positive value should be presented(profiting), but in case of buying and the trend is down, then negative value should be presented(profit loss). Opposite for selling case.

Here is the solution:

B19, B28 and B37 are overlapping zones stop_buy/sell or stop_sell/buy, so the formula would be

=IF(AND(B2="sell",B1="hold_buy"),A2-INDEX($A$1:$A$54,MAX(IF($B$1:$B2="buy",ROW($B$1:$B2)))),"")

Look the attachment

What I want to do now (look in the 'Buy-Sell Signals' sheet) is to make formula to show at given conditions a BUY signal as well as to show the STOP_LOSS and TAKE_PROFIT. If someone knows how to do it and wants to help me out, this will be the complete task for calculating the feasibility of a given strategy testing. I will share this document with everyone here who needs it, made with explanations and steps to calculate the effectiveness of a given strategy (FOREX or Stock), in the future with a better look I guess.

[babapusy]

I need solution to two problems:

First,

sell 9
hold_sell
hold_sell
hold_sell
hold_sell
sell 8
hold_sell
hold_sell
hold_sell
take_profit 7

to calculate 9-7=2, and to ignore the second sell signal(fake signal, there can be many of them in between).

Second,

Depending on the generated BUY signal for example, to generate STOP_LOSS or TAKE_PROFIT signal in a given condition. For example to generate BUY signal at price 10 pips above the Upper Bollinger Band and depending on the generated buy signal to generate, let's say, STOP_LOSS if the difference between the last value and the BUY is -10 pips, or TAKE_PROFIT if the difference between the last value and the BUY signal is +10 pips.
To be more clear to you what I mean, is if the formulated condition generates BUY in B1 from A1, then to look for values A2-A1,A3-A1,A4-A1,A5-A1 etc until it meets the condition -10 or +10 pips(to generate STOP_LOSS or TAKE_PROFIT accordingly) and to ignore the in between BUY(fake) signals.

[babapusy]

Anyone to this question?