CODEWITHSUNDEEP
pythonpandasvectorization
Al_Iskander
Al_Iskander

Reputation: 1001

vectorized backtest creation of pandas DataFrame

This is my final try after posting several questions that might have been badly addressed/described. I want to achive this DataFrame as result:

        Signal   price  buy_units   sell_units   cashflow  balance
   Index
      0     0       40          0         0           0     100000
      1     1       50       2000         0      -100000         0
      2    -1      100          0     -2000      +200000    200000  
      3     1       50       4000         0      -200000         0
      4    -1       70          0     -4000      +280000    280000

It's a stock trading strategy backtest. When Signal is ==1 buy_units is equal to current balance(value from prior row) divided by price. balance then becomes reduced by cashflow (that is units x price*-1). The remainder should be self-explanatory. The problem: calculating buy_units without iteration but in vectorized fashion. Who has an idea how to solve this?

ultimately I would like to design this in a class with a "Calculate units" and "calculate balance" method but this is not necessary in first step.

Upvotes: 2

Views: 1291

Answers (2)

IanS
IanS

Reputation: 16251

If -1 and +1 signals always alternate as in the example, then the balance in step n+2 is equal to the balance in step n multiplied by the price return between n+1 and n+2.

I use the cumulated product to translate this into vectorized operations in pandas:

# initialize balance
df['balance'] = 0.0
df.balance.iloc[0] = 10000.0

# calculate returns
df['return'] = df.price / df.price.shift()

# calculate balance where signal is -1
df.loc[df.Signal == -1, 'balance'] = \
    df.balance.iloc[0] * df.loc[df.Signal == -1, 'return'].cumprod()

The cashflows and number of units can easily be computed from the balance.

Upvotes: 1

ysearka
ysearka

Reputation: 3855

You can create a temporary dataframe in order to do your computation:

temp = df[df.balance == 1]

Then perform computations on this dataframe:

df.ix[df.balance == 1,'buy_units'] = temp.balance/temp.price

And delete the temporary dataframe:

%reset selective -f temp

Upvotes: 0

Related Questions