simple trailing stop in excel

[Bowball]

hi - i'm building a very basic mechanical trading system in excel but need to create a basic trailing stop. i'm sure it can be done using simple IF functions, but i'm feeling a bit slow today. can anybody help

thanks in advance

 

[Rhody Trader]

Depending on how the stop is determined, you could either do it through a straight formula or by using one of the conditional functions (or, if). Without more information, though, I can't really be very helpful.

 

[Bowball]

thanks John. my spreadsheet looks, broadly, like this:

buy/sell price

sell 1.145
1.1406
1.1382
1.1382
1.1319
1.1359
1.1309

in the left column i have a new 'sell' signal, in the right i have price. i'm comfortable enough using a conditional formula to establish the cashflow when i receive the signal (i.e. a positive amount mulitiplied by the fx rate), but how do i use excel to apply a trailing stop

thanks

 

[Rhody Trader]

What you've posted here doesn't make any sense to me at all. I don't know what it's supposed to be telling me. How is your trailing stop determined?

 

[Bowball]

sorry, that didnt paste too well. on the left column are buy/sell signals, on the right is price of the underlying. so in the above example i have a sel signal when the price hit 1.1450.

lets say i wanted a stop at 200 pips, then easy: if ( current price 200 pips > sell price , buy, 0 ). but how would i write a conditional function in excel to general a trailing stop if the price rose, say, 200 pips from the low after the sell signal then i would want a stop signal generated

 

[Rhody Trader]

Are you saying in your example, that you want the stop to be 200 pips above the lowest point reached by price while the trade is on? So Stop=200+Lowest Low?

 

[new_trader]

If position = "sell" then STOP = tradeprice + stop size
If position = "buy" then STOP = tradeprice - stop size

To Trail:

If position = "buy" then
If (lastprice - STOP) > (tradeprice - STOP) then
STOP = STOP + (lastprice - tradeprice)
End if

This could be a start, I haven't formatted the code properly but it may give you an idea of how you could go about it. There is more needed to prevent the STOP being reduced on a retrace. I would use a cell to store the current value of the stop, so you might need another conditional statement to check the value to ensure the STOP is not reduced on a long position during a retrace.