Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stock Stop Loss Formula
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(e3e2),"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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stock Stop Loss Formula
First, your IF statements are overly complicated. You don't need AND if there's
only one condition. =if(e3e2,"BUY","") is the same as =if(AND(e3e2),"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" wrote in message ... 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(e3e2),"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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Stock Stop Loss Formula
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(e3e2,"BUY","") is the same as =if(AND(e3e2),"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" wrote in message ... 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(e3e2),"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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
self-updating formula | Excel Discussion (Misc queries) | |||
Editing an existing formula | Excel Worksheet Functions | |||
How to stop excel from plotting a "blank" cell with formula as zer | Excel Discussion (Misc queries) | |||
How do I get my formula to stop rounding up | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |