# Summary: performance issue with for-loop, vectorization possible?

I am processing a time series of securities price data, stored in a pandas dataframe df_buy_sell. First, an initial function defines buy (+1) and sell (-1) signals df_buy_sell["Action"].

Date        Open    Adj_Close   Action  Signal  Slippage    Spread  Sell_for    Buy_for
8/21/2014   14.96   13.964128   1       1       0.002992    0.0374  14.919608   15.000392
8/29/2014   14.52   13.495533  -1       0       0.002904    0.0363  14.480796   14.559204
11/11/2014  14.8    14.307712   1       1       0.00296     0.037   14.76004    14.83996
8/17/2015   16.98   16.233931  -1       0       0.003396    0.04245 16.934154   17.025846
8/17/2016   12.82   11.867902   1       1       0.002564    0.03205 12.785386   12.854614
8/18/2016   11.8    11.290437  -1       0       0.00236     0.0295  11.76814    11.83186
2/22/2017   11.38   11.3        1       1       0.002276    0.02845 11.349274   11.410726
4/20/2017   10.8    10.8       -1       0       0.00216     0.027   10.77084    10.82916
4/27/2017   11.2    11.38       1       1       0.00224     0.028   11.16976    11.23024
5/12/2017   10.98   10.9       -1       0       0.002196    0.02745 10.950354   11.009646
5/23/2017   11.58   11.48       1       1       0.002316    0.02895 11.548734   11.611266


Based on the buy and sell signals in df_buy_sell["Action"], the trade() function below buys and sells the maximum number of stocks possible at that time/in that row, given a specific amount of cash. As a result, the remaining amount of cash changes, so does the price of the stock in scope for the next buy/sell signal/row.

Therefore, I put a for index, row in df.iterrows() loop to the core of this function. However, this loop is very slow and I have not been able to vectorize it so far.

Is it possible to vectorize this for-loop?

Function

FX_rate = 9.22703405
initial_capital = 100000*FX_rate
remaining_cash = initial_capital
# Create columns for row-wise operations
## begin row-wise operations
# GO LONG
for index, row in df_buy_sell.iterrows():
if row["Action"] > 0:
start_positions = int(remaining_cash / row["Buy_for"]) # THIS GOES TO NEXT ROW
df_buy_sell.loc[index, "Positions"] = start_positions
spent = start_positions * row["Buy_for"]
# Comission
comission = (4.90*FX_rate) + (0.0025 * spent)
if comission < (9.9*FX_rate):
comission = (9.9*FX_rate)
elif comission > (59.9*FX_rate):
comission = (59.9*FX_rate)
df_buy_sell.loc[index, "Comission"] = comission
# Exchange fees
exchange_fee = spent * 0.000025
if exchange_fee < (2.5*FX_rate):
exchange_fee = (2.5*FX_rate)
df_buy_sell.loc[index, "SExchange"] = exchange_fee
# Cash left after GO LONG
remaining_cash = remaining_cash - spent - exchange_fee - comission
df_buy_sell.loc[index, "Cash"] = remaining_cash
# GO SHORT
if row["Action"] < 0:
row["Positions"] = 0
row["Holdings"] = 0
earned = start_positions * row["Sell_for"]
# Comission
comission = (4.90*FX_rate) + (0.0025 * earned)
if comission < (9.9*FX_rate):
comission = (9.9*FX_rate)
elif comission > (59.9*FX_rate):
comission = (59.9*FX_rate)
df_buy_sell.loc[index, "Comission"] = comission
# Exchange fees
exchange_fee = earned * 0.000025
if exchange_fee < (2.5*FX_rate):
exchange_fee = (2.5*FX_rate)
df_buy_sell.loc[index, "SExchange"] = exchange_fee
# Cash left after GO LONG
remaining_cash = remaining_cash + earned - exchange_fee - comission # THIS GOES TO NEXT ROW
df_buy_sell.loc[index, "Cash"] = remaining_cash
## end row-wise operations


Date        Open    Adj_Close   Action  Signal  Slippage    Spread  Sell_for    Buy_for  Positions  Comission   SExchange   Holdings    Cash
8/21/2014   14.96   13.964128   1       1       0.002992    0.0374  14.919608   15.000392   61511   552.6993396 23.06758513 858947.4774 -561.4742367
8/29/2014   14.52   13.495533  -1       0       0.002904    0.0363  14.480796   14.559204   0       552.6993396 23.06758513 0            889591.0016
11/11/2014  14.8    14.307712   1       1       0.00296     0.037   14.76004    14.83996    59945   552.6993396 23.06758513 857675.7958 -566.1675302
8/17/2015   16.98   16.233931  -1       0       0.003396    0.04245 16.934154   17.025846   0       552.6993396 25.37794654 0            1013973.617
8/17/2016   12.82   11.867902   1       1       0.002564    0.03205 12.785386   12.854614   78880   552.6993396 25.34929881 936140.1098 -576.3842447
8/18/2016   11.8    11.290437  -1       0       0.00236     0.0295  11.76814    11.83186    0       552.6993396 23.20677208 0            927118.5928
2/22/2017   11.38   11.3        1       1       0.002276    0.02845 11.349274   11.410726   81249   552.6993396 23.17775192 918113.7    -567.3610219
4/20/2017   10.8    10.8       -1       0       0.00216     0.027   10.77084    10.82916    0       552.6993396 23.06758513 0            873976.8512
4/27/2017   11.2    11.38       1       1       0.00224     0.028   11.16976    11.23024    77823   552.6993396 23.06758513 885625.74   -569.8832313
5/12/2017   10.98   10.9       -1       0       0.002196    0.02745 10.950354   11.009646   0       552.6993396 23.06758513 0            851043.7492
5/23/2017   11.58   11.48       1       1       0.002316    0.02895 11.548734   11.611266   73294   552.6993396 23.06758513 841415.12   -568.1479428


df_buy_sell[condition]


lets you select all rows in the dataframe that matches your condition. You could then apply your entire function block to all the rows at once. For example,

df_buy = df_by_sell[df_by_sell.Action >0]
start_positions = remaining_cash / df_buy["Buy_for"] # THIS GOES TO NEXT ROW
spent = start_positions * df_by["Buy_for"]

...

exchange_fee[exchange_fee < (2.5*FX_rate)] = (2.5*FX_rate)

...


Once you are selecting from the dataframe with masks, you can apply your mathematical functions to your masked dataframes just like any numpy array.

That being said, it looks like 90% of your functions for Action>0 and Action<0 are the same, so I would recommend writing these code blocks into functions for code maintainability, readability, and re-use.

If you had functions,

def calculate_commission(transaction_amount):
...

def calculate_exchange_fees(transaction_amount):
...

def calculate_remaining_cash():
...


You could update your dateframe with something like:

df_buy_sell.loc[df_buy.index, "Commission"] = calculate_commission(spent)