Create columns based on a rolling condition without looping

Question

Background

I have a dataframe of stock prices with close price, day high, day low prices and am trying to backtest potential short sell entries.

The logic is if there has been any 30% downwards movement in price in the last 7 days ((Min Low - Max High) / Max High) a signal will be triggered and enter position (pos == 1) on the day after trigger (since you would theoretically would only know the trigger after market close).

If a position exists I also want to implement a stop loss of +15% to exit the trade if there has been an increase of 15% in price using the close price of Entry date (trigger date + 1) and each days close price to compare.

Note Entry can only happen if there is no current position (meaning either first entry or previous entry has been exited) and exit should only occur if a position exists.

Summary: Enter using High and Low columns, Exit using Close Price columns.

Please see example dataframe:

  Date      Price    High   Low     Entry  Exit
2024-01-15  3100.0  3230.0  2685.0
2024-01-16  3595.0  3635.0  3155.0
2024-01-17  4295.0  4295.0  4040.0
2024-01-18  3595.0  4550.0  3595.0 
2024-01-19  3280.0  3880.0  2929.0 (entry trigger)
2024-01-22  3505.0  3575.0  3185.0  Entry
2024-01-23  3945.0  3970.0  3565.0
2024-01-24  4075.0  4300.0  4020.0 (exit trigger)
2024-01-25  3990.0  4190.0  3910.0          Exit
2024-01-26  3855.0  4020.0  3765.0
2024-01-29  3740.0  2920.0  2775.0 (entry trigger)
2024-02-01  3810.0  3880.0  3460.0  Entry
2024-02-02  4150.0  4150.0  3830.0
2024-02-05  3915.0  4195.0  3915.0
2024-02-06  3855.0  4040.0  2805.0
2024-02-07  3785.0  3945.0  3755.0
2024-02-08  3790.0  3885.0  3720.

Problem:

I am using a for loop to loop through each row with a position tracker but I know given this is a dataframe there must be a faster better way without using a for loop.

df['Max7'] = df['High'].rolling(window=7, min_periods=1).max()
df['Min7'] = df['Low'].rolling(window=7, min_periods=1).min()
df['Min PctChange7'] = (df['High'] - df['Min7']) / df['Min7'] * 100
df["Entry"] = np.nan
df["Exit"] = np.nan

pos = 0
entry_date = np.nan

pos = 0
entry_date = np.nan

for i in range(len(df)):
    if pos == 0:
        if df["Min PctChange7"].iloc[[i]][0] < -30:
            pos = 1
            entry_date = i+1
            df["Entry"].iloc[[entry_date]] = "Entry"  
        
    if i > entry_date and pos == 1:
        if (df.iloc[[i]]["Price"][0]/df.iloc[[entry_date]]["Price"][0]) > 1.15:
            df["Exit"].iloc[[i]] = "Exit"  
            pos = 0

Updated with relevant columns

Anonymous
– Anonymous

2024-06-18 04:45:01 +00:00
Commented Jun 18, 2024 at 4:45 — Anonymous
– Anonymous, Commented Jun 18, 2024 at 4:45

PRASOON SINGH PATEL · Accepted Answer · 2024-06-17 12:44:12Z

import numpy as np

import pandas as pd
 
# Sample DataFrame structure

df = pd.DataFrame({

    'High': np.random.randn(100) * 10 + 100,

    'Low': np.random.randn(100) * 10 + 90,

    'Close': np.random.randn(100) * 10 + 95,

})
 
# Calculate Rolling Maximum and Minimum

df['Max7'] = df['High'].rolling(window=7, min_periods=1).max()

df['Min7'] = df['Low'].rolling(window=7, min_periods=1).min()
 
# Calculate Percentage Change from Min7

df['Min PctChange7'] = (df['High'] - df['Min7']) / df['Min7'] * 100
 
# Initialize Entry and Exit Columns

df["Entry"] = np.nan

df["Exit"] = np.nan
 
# Vectorized Calculation of Entry Points

entry_points = (df['Min PctChange7'] < -15).astype(int)

entry_points = entry_points.diff().fillna(0)

entry_dates = entry_points[entry_points == 1].index

df.loc[entry_dates + 1, "Entry"] = "Entry"
 
# Vectorized Calculation of Exit Points

df['Position'] = df['Entry'].notna().astype(int).cumsum()

df['Entry_Price'] = df.groupby('Position')['Close'].transform('first')

exit_condition = (df['Close'] / df['Entry_Price']) > 1.15

exit_dates = df[exit_condition & df['Entry_Price'].notna()].index

df.loc[exit_dates, "Exit"] = "Exit"
 
# Clean Up

df.drop(columns=['Position', 'Entry_Price'], inplace=True)
 
print(df)

try using this code

Collectives™ on Stack Overflow

Create columns based on a rolling condition without looping

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related