Stock Stop Loss Formula

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

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.





"zmr325" wrote:

> 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
>

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,"")

--
Regards,
Fred


"zmr325" <zmr325@discussions.microsoft.com> wrote in message
news:FD83F0A9-6B14-42AB-A26F-B4C21160B357@microsoft.com...
> 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.
>
>
>
>
>
> "zmr325" wrote:
>
>> 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
>>

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

"Fred Smith" wrote:

> 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,"")
>
> --
> Regards,
> Fred
>
>
> "zmr325" <zmr325@discussions.microsoft.com> wrote in message
> news:FD83F0A9-6B14-42AB-A26F-B4C21160B357@microsoft.com...
> > 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.
> >
> >
> >
> >
> >
> > "zmr325" wrote:
> >
> >> 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
> >>
>
>
>