Create columns based on a rolling condition

Question

I have a dataframe of 2 asset prices and the Z score of their ratio (A/B) and am trying to do a simple backtest with signal generation of a pairs ratio trade of entering when Z score < -2 (long ratio) or Z score > +2 (short ratio) and unwinding when Z score >= -0.1 or Z score <= 0.1 respectively.

(index)     Asset A Asset B  Z Score  Column I need
2023-01-05  33400.0 45.6781 -2.002516  Entry (long)
2023-01-06  34050.0 46.5457 -1.952896
2023-01-09  34800.0 48.0222 -2.157506
2023-01-10  35150.0 48.2774 -1.988247
2023-01-11  35600.0 48.6792 -1.856716
...
2023-02-21  37900.0 50.8524 -0.728916
2023-02-22  36600.0 49.7823 -1.021323
2023-02-23  37850.0 51.6347 -1.071015
2023-02-24  39300.0 50.7727 0.171235   Exit (long)
2023-02-27  38700.0 50.2548 0.050697
2023-02-28  38350.0 49.9594 -0.023634

It must differentiate whether the entry is triggered due to >2 (short ratio entry) or whether it is triggered to -2 (long ratio entry).

For the exit condition, If there has been an entry due to Z score > 2 (short ratio entry) then exit when Z score is <= 0.1 and if the entry was triggered by Z score < -2 (long ratio entry) then exit when Z score is >= -0.1 It is important to distinguish which type entry and exit the trade is.

Additionally, there will be only one trade at a time meaning if there has been an entry made previously due to being triggered by the conditions mentioned above, there cannot be a new entry until the position is closed or exited.

I'm not sure how to vectorize this as I am currently using a for loop with a position variable to keep track whilst iterating each row. Ideally the column I need should specify just the long / short ratio entry and exit

mozway · Accepted Answer · 2024-08-13 07:34:23Z

This problem typically cannot be vectorized since the state of your flag depends on the previous state. For example, a position n you could have a "long" flag, but it will depend on whether or not the position n-5 could also have an ongoing trade, which itself could depend on the position n-12, etc.

A loop is the best way to handle this, use numba to compile it and have efficient processing:

from numba import jit

@jit(nopython=True)
def flag(a):
    ratio = None
    out = []
    for x in a:
        if ratio is None:
            if x > 2:
                ratio = 'long'
                out.append('Entry (long)')
            elif x < -2:
                ratio = 'short'
                out.append('Entry (short)')
            else:
                out.append(None)
        else:
            if ratio == 'long' and x <= 0.01:
                out.append('Exit (long)')
                ratio = None
            elif ratio == 'short' and x >= -0.01:
                out.append('Exit (short)')
                ratio = None
            else:
                out.append(None)
    return out
        
df['output'] = flag(df['Z Score'].to_numpy())

Output:

            Asset A  Asset B   Z Score         output
2023-01-05  33400.0  45.6781 -2.002516  Entry (short)
2023-01-06  34050.0  46.5457 -1.952896           None
2023-01-09  34800.0  48.0222 -2.157506           None
2023-01-10  35150.0  48.2774 -1.988247           None
2023-01-11  35600.0  48.6792 -1.856716           None
2023-02-21  37900.0  50.8524 -0.728916           None
2023-02-22  36600.0  49.7823 -1.021323           None
2023-02-23  37850.0  51.6347 -1.071015           None
2023-02-24  39300.0  50.7727  0.171235   Exit (short)
2023-02-27  38700.0  50.2548  0.050697           None
2023-02-28  38350.0  49.9594 -0.023634           None

Collectives™ on Stack Overflow

Create columns based on a rolling condition

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