Pandas Backtester Data Alignment

Article Quant Q&A · Author: Eka

Summary

The document presents a simple moving-average backtester for shares, with signals based on a fast and slow average, position sizing, cash accounting, and portfolio equity. The reported output contains missing values, and the answer points to a likely cause in the portfolio calculation: multiplying a positions DataFrame by a price Series can trigger pandas label alignment and broadcasting across columns rather than the intended matching of each position with its corresponding price.

The suggested correction is to use the position column explicitly and multiply it element by element by the price series; the position difference should likewise be calculated from that column. This highlights how pandas aligns data by labels and why selecting the intended series matters in portfolio calculations. The response is brief and its proposed code fragment is incomplete and appears to contain typographical errors, so it does not establish a fully corrected backtest. It also does not assess other possible accounting or timing issues in the implementation.

Key ideas

Pandas aligns objects by their labels, which can produce unintended results when a DataFrame is multiplied by a Series.
Select the position series explicitly when calculating position value against a price series.
Calculate trades from changes in the position series.
The displayed fix is incomplete, so the document does not provide a complete validated backtest.
The example uses moving-average signals and share positions to illustrate portfolio accounting.

Tags

Backtesting Equities Technical indicators Execution

Full text

# What is wrong in this python backtester?


# What is wrong in this python backtester?












I am trying to write a simple backtester using python and pandas. I have reused the code of Michael halls moore's pandas backtester. I rewrote the programme because first I want to understand how backtesting works and secondly I found (for me) the original code hard to understand.

This is the rewrote code

```
import datetime
import pandas as pd
import numpy as np
from pandas.io.data import DataReader

#download data

start=datetime.datetime(2000,1,1)
end=datetime.datetime(2012,1,1)

ibm = DataReader('IBM',  'yahoo', start ,end )

#technical analysis library
#https://www.quantopian.com/posts/technical-analysis-indicators-without-talib-code

def MA(df, n):  
    MA = pd.Series(pd.rolling_mean(df, n), name = 'MA_' + str(n))  
    df =MA
    return df

def gen_signals(df,fast=10,slow=30):
    signals=pd.DataFrame(index=df.index)
    signals['signal']=0
    signals['price']=df

    #fast slow moving average
    signals['fast']=MA(signals['price'],fast)
    signals['slow']=MA(signals['price'],slow)

    #if fast sma is greater than slow sma then 1 else 0
    signals['signal']=np.where(signals['fast']>signals['slow'],1,0)

    #taking difference to genrate actual trading order
    signals['positions']=signals['signal'].diff()
    return signals

def gen_positions(self):
    positions=pd.DataFrame(index=self.index).fillna(0.0)
    positions['position']=10*self['signal'] #10 shares/signal
    return positions

def backtest(positions,price,initial_capital=10000):
    #creating protfolio
    portfolio =positions*price['price']    
    pos_diff=positions.diff()

    #creating holidings
    portfolio['holidings']=(positions*price['price']).sum(axis=1)
    portfolio['cash']=initial_capital-(pos_diff*price['price']).sum(axis=1).cumsum()

    #full account equity
    portfolio['total']=portfolio['cash']+ portfolio['holidings']
    portfolio['return']=portfolio['total'].pct_change()
    return portfolio

#signal generation
sig=gen_signals(ibm['Close'],fast=5,slow=25)
sig.head(3)

#positon genration
pos=gen_positions(sig)
pos.tail(3)

#backtesting
re=backtest(pos,sig)
re.head()
```

But the out put result is this

```
            2011-12-30 00:00:00  position  holidings  cash  total  return  
Date                                                                       
2000-01-03                  NaN       NaN        NaN   NaN    NaN     NaN  
2000-01-04                  NaN       NaN        NaN   NaN    NaN     NaN  
2000-01-05                  NaN       NaN        NaN   NaN    NaN     NaN  
2000-01-06                  NaN       NaN        NaN   NaN    NaN     NaN  
2000-01-07                  NaN       NaN        NaN   NaN    NaN     NaN
```

Any idea why its happening?

## Answer by mde (score 1)

https://quant.stackexchange.com/a/29884

You are getting cross-product instead of value by value multiplication in this line:

> portfolio = positions*price['price']

I suspect you need something like that (use .position filed from your dataframe) :

```
portfolio = positions['position']*price['price']    
pos_diff = positions['position'].diff()

#creating holidings
portfolio['holidings'] = (positions['position']*price['price']).sum()
portfolio['cash'] = initial_capitalpos_diff*price['price']).sum().cumsum()
....
```

Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)

This summary was written by Stratmill's research agent from the original; it is not a copy of the source.

Aligning Positions and Prices in a Pandas Backtester

Summary

Key ideas

Tags