Stock Stop Loss Formula

[zmr325]

Help,

I am going nuts. I need help finding a way to backtest stops for stocks. I
used an =if(and()) statement to come up with good entry points on the buy and
sell side. Now I just need to find a way to write stops. The way I wrote
the if(and()) statements is that I have the logical statement and then for
the True it would be either "buy" or "sell". How to write a stop loss would
get me up and running very quickly. I appreciate anybodies time. Kind of
new so might be over my head.

Thank you

 

[zmr325]

here is my spreadsheet:
for example
A B C D E F
G
1 Date Open High Low Close

if(and(e3<e2),"Sell"," ")
2 11/20 5 15 5 12
3 11/21 11 20 8 18 =if(and(e3>e2),"BUY","
")
4 11/22 17 17 10 10 " "
5 11/23 10 12 6 8 " "
6 11/24 7 10 4 4 " "


So in Column's F and G the word BUY and SELL would appear if it were true.
Now once I got the buy-sell word how can I take that data represented in E
and test a stop price. For example F3 would read buy, the value at that
moment is 18. HOw can I say stop me out if the price goes to 21 before i get
a SELL signal. Thank you for anyone who can help.

 

[Fred Smith]

First, your IF statements are overly complicated. You don't need AND if there's
only one condition. =if(e3>e2,"BUY","") is the same as =if(AND(e3>e2),"BUY,"")

Secondly, what happens if e3=e2? Your if statements don't provide for this.

Thirdly, what conditions determine the stop loss price? In your example, your
buy price is $18, and your stop loss price is $21, but how do you calculate the
$21? Is it price + $3? or price + 16%?

Depending on how you calculate the stop loss price, the formula could be as
simple as:

=if(f3="BUY",e2*1.16,"")

 

[zmr325]

Thank you fred,

I am going to give that a try tonight. I know in my explanation that I only
need an if statement but I actually have 6 logical statements that need to be
met. I can't give away all of my secrets. If you have any more ideas I
would greatly appreciate them and I will let you know how this works out.

Noel