# Backtesting short-selling strategy using pandas dataframe

I would like to make a simple backtest for one of my short-selling strategies. I am using pandas dataframes. So I have a dataframe like the following, that indicates how many positions to open/close every day.

                position_change position_total
2018-01-03      1               1
2018-01-04      0               1
2018-01-05      0               1
2018-01-08      0               1
2018-01-09      0               1
2018-01-10      1               2
2018-01-11      0               2
2018-01-12      0               2
2018-01-16      0               2


I also have a dataframe with the prices of the asset:

            price       short_sell_change       accum_change
2018-01-03  10          1                       1
2018-01-04  9           1,1111111111            1,1111111111
2018-01-05  8           1,125                   1,25
2018-01-08  7           1,1428571429            1,4285714286
2018-01-09  6           1,1666666667            1,6666666667
2018-01-10  5           1,2                     2
2018-01-11  4           1,25                    2,5
2018-01-12  3           1,3333333333            3,3333333333
2018-01-16  2,5         1,2                     4


The final (net amount) dataframe should be:

            net_amount
2018-01-03  10
2018-01-04  11,1111111111
2018-01-05  12,5
2018-01-08  14,2857142857
2018-01-09  16,6666666667
2018-01-10  25
2018-01-11  31,25
2018-01-12  41,6666666667
2018-01-16  50


This is easy to do with Excel accumulating the previous net_amount with a reference to the above cell and adding the position_chage info:

How can this be done in a pandas-way? (Unfortunately I guess the only possible way is iterating over the rows)

• There are a few ways this could be done. Could you show your excel formulas that you want to replicate in the net_amount dataframe? – amdopt Jul 23 '20 at 13:24

Assuming your dataframe looks something like this and the name of the dataframe is your_dataframe (I left out one of your columns that wasn't needed for this):

   position  pos_total  price  ss_change
0         1          1   10.0   1.000000
1         0          1    9.0   1.111111
2         0          1    8.0   1.125000
3         0          1    7.0   1.142857
4         0          1    6.0   1.166667
5         1          2    5.0   1.200000
6         0          2    4.0   1.250000
7         0          2    3.0   1.333333
8         0          2    2.5   1.200000
9         0          2    1.0   2.500000


Next, to replicate your Excel formula I defined a function that takes a dataframe and your initial value as inputs and returns the dataframe with a new column and the values you are looking for:

def net_amount(df, initial_value):
df['net_amount'] = float(initial_value)
for row in range(1, len(df)):
df['net_amount'][row] = (df['net_amount'][row-1] *
df['ss_change'][row] +
df['position'][row] *
df['price'][row])
return df


This is an iterative way of doing it and with large amounts of data it will take a long time. If you have a ton of data there are better ways.

Next just call the function:

net_amount(your_dataframe, 10)


This is what is returned:

   position  pos_total  price  ss_change  net_amount
0         1          1   10.0   1.000000   10.000000
1         0          1    9.0   1.111111   11.111111
2         0          1    8.0   1.125000   12.500000
3         0          1    7.0   1.142857   14.285714
4         0          1    6.0   1.166667   16.666667
5         1          2    5.0   1.200000   25.000000
6         0          2    4.0   1.250000   31.250000
7         0          2    3.0   1.333333   41.666667
8         0          2    2.5   1.200000   50.000000
9         0          2    1.0   2.500000  125.000000


There are plenty of ways to improve upon this and to make it more dynamic if needed. The way it sits relies upon the columns being named a specific way but you can use it as a template and make changes.

Edit:

For additional speed you can incorporate the use numba like this:

import pandas as pd
from numba import jit

@jit
def numba_calc(net, ss, pos, price):
for i in range(1, len(net)):
net[i] = (net[i-1] * ss[i] + pos[i] * price[i])
return net

def net_amount_numba(df, initial_value):
df['net_amount'] = float(initial_value)
net = df['net_amount'].to_numpy()
ss = df['ss_change'].to_numpy()
pos = df['position'].to_numpy()
price = df['price'].to_numpy()

df['net_amount'] = numba_calc(net, ss, pos, price)

return df


On my machine, this improves performance by ~8x from ~2.4 milliseconds down to ~300 microseconds. Pandas/Numba documentation

• Thanks for this. I guess there is not any option to get this without iterate over the rows :(. Any example of these "better ways" you mentioned (because yes, my example df is a really simplified version of my real df with multiple colums and tons of rows)? – Alfonso_MA Jul 23 '20 at 16:46
• Well, being that you need the prior value to be updated before you can compute the next value, it is an iterative process. When I said better ways, I was referring to having a lot of data. To deal with a data set that is too large for your memory, you may opt for a generator that you can loop over on an 'as needed' basis. – amdopt Jul 23 '20 at 17:16
• You could also move it out of pandas into numpy and use numba. Then consolidate it back into a dataframe after you are done with the compuatations. What I showed is just a pandas way of doing it because that is what you asked for. – amdopt Jul 23 '20 at 17:41