I have a column called Entry signal that signals a Short Sell Entry but want to implement a backtest with stop loss at 15% that references the most recent last "Entry" from Entry Signal calling (if price rises more than 15% from Entry Price then exit trade, do nothing until next Entry and repeat).
I want to create below Stop Loss column
e.g.
Date Price Entry Signal Stop Loss
2024-01-18 3595.0 NaN NaN
2024-01-19 3280.0 Entry NaN
2024-01-22 3505.0 NaN NaN
2024-01-23 3945.0 NaN Exit (since first time price change triggers > +15%)
2024-01-24 4075.0 NaN NaN
2024-03-07 10270.0 Entry NaN
2024-03-08 8120.0 NaN NaN
2024-03-11 14020.0 NaN Exit
So far I can only think of for looping across each row but there must be a better way given I am working with a dataframe.
>Solution :
You could get the latest Entry with where+ffill, then filter the values above +15% and get the first with groupby.idxmax :
# identify entries
m = df['Entry Signal'].eq('Entry')
# identify prices with a 15% increase since last entry
s = df['Price'].div(df['Price'].where(m).ffill()).gt(1.15)
# get first one per group
df.loc[s[s].groupby(m.cumsum()).idxmax(), 'Stop Loss'] = 'Exit'
Output:
Date Price Entry Signal Stop Loss
0 2024-01-18 3595.0 NaN NaN
1 2024-01-19 3280.0 Entry NaN
2 2024-01-22 3505.0 NaN NaN
3 2024-01-23 3945.0 NaN Exit
4 2024-01-24 4075.0 NaN NaN
5 2024-03-07 10270.0 Entry NaN
6 2024-03-08 8120.0 NaN NaN
7 2024-03-11 14020.0 NaN Exit
Intermediates:
Date Price Entry Signal Stop Loss m where(m) ffill div gt(1.15)
0 2024-01-18 3595.0 NaN NaN False NaN NaN NaN False
1 2024-01-19 3280.0 Entry NaN True 3280.0 3280.0 1.000000 False
2 2024-01-22 3505.0 NaN NaN False NaN 3280.0 1.068598 False
3 2024-01-23 3945.0 NaN Exit False NaN 3280.0 1.202744 True
4 2024-01-24 4075.0 NaN NaN False NaN 3280.0 1.242378 True
5 2024-03-07 10270.0 Entry NaN True 10270.0 10270.0 1.000000 False
6 2024-03-08 8120.0 NaN NaN False NaN 10270.0 0.790652 False
7 2024-03-11 14020.0 NaN Exit False NaN 10270.0 1.365141 True