pandas - Portfolio rebalancing with bandwidth method in python

Question

We need to calculate a continuously rebalanced portfolio of 2 stocks. Lets call them A and B. They shall both have an equal part of the portfolio. So if I have 100$ in my portfolio 50$ get invested in A and 50$ in B. As both stocks perform very differently they will not keep their equal weights (after 3 month already A may be worth 70$ while B dropped to 45$). The problem is that they have to keep their share of the portfolio within a certain bandwidth of tolerance. This bandwidth is 5%. So I need a function that does: If A > B*1.05 or A*1.05 < B then rebalance.

This first part serves only to get the fastest way some data to have a common basis of discussion and to make results comparable, so you can just copy and paste this whole code and it works for you..

import pandas as pd
from datetime import datetime
import numpy as np


df1 = pd.io.data.get_data_yahoo("IBM", 
                                start=datetime(1970, 1, 1), 
                                end=datetime.today())
df1.rename(columns={'Adj Close': 'ibm'}, inplace=True)

df2 = pd.io.data.get_data_yahoo("F", 
                                start=datetime(1970, 1, 1), 
                                end=datetime.today())
df2.rename(columns={'Adj Close': 'ford'}, inplace=True)

df = df1.join(df2.ford, how='inner')
del df["Open"]
del df["High"]
del df["Low"]
del df["Close"]
del df["Volume"]

Nowe start to calculate the relative performance of each stock with the formula: df.ibm/df.ibm[0]. The problem is that as soon as we break the first bandwidth, we need to reset the 0 in our formula: df.ibm/df.ibm[0], since we rebalance and need to start calculating from that point on. So we use df.d for this placeholder function and set it equal to df.t as soon as a bandwidth gets broken df.t basically just counts the length of the dataframe and can tell us therefore always “where we are”. So here the actual calculation starts:

tol = 0.05 #settintg the bandwidth tolerance
df["d"]= 0 # 
df["t"]= np.arange(len(df))
tol = 0.3

def flex_relative(x):
    if df.ibm/df.ibm.iloc[df.d].values < df.ford/df.ford.iloc[df.d].values * (1+tol):
        return  df.iloc[df.index.get_loc(x.name) - 1]['d'] == df.t
    elif df.ibm/df.ibm.iloc[df.d].values > df.ford/df.ford.iloc[df.d].values * (1+tol):
        return df.iloc[df.index.get_loc(x.name) - 1]['d'] == df.t
    else:
        return df.ibm/df.ibm.iloc[df.d].values, df.ford/df.ford.iloc[df.d].values



df["ibm_performance"], df["ford_performance"], = df.apply(flex_relative, axis =1)

The problem is, that I am getting this error form the last line of code, where I try to apply the function with df.apply(flex_relative, axis =1)

ValueError: ('The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().', u'occurred at index 1972-06-01 00:00:00') The problem is that none of the given options of the error statement solves my problem, so I really don't know what to do...

The only thing I found so far was the link below, but calling a R function won't work for me because I need to apply that to quite big datasets and I may also implement an optimization in this function, so it definitely needs to be built in python. Here is the link anyway: Finance Lib with portfolio optimization method in python

Manually (what is not a good way to handle big data), I calculated that the first date for a rebalancing would be: 03.11.1972 00:00:00

The output of the dataframe at the first rebalancing should look like this:

                     ibm        ford        d   t   ibm_performance ford_performance
1972-11-01 00:00:00 6,505655    0,387415    0   107 1,021009107 0,959552418
1972-11-02 00:00:00 6,530709    0,398136    0   108 1,017092172 0,933713605
1972-11-03 00:00:00 6,478513    0,411718    0   109 1,025286667 0,902911702 # this is the day, the rebalancing was detected
1972-11-06 00:00:00 6,363683    0,416007    109 110 1,043787536 0,893602752 # this is the day the day the rebalancing is implemented, therefore df.d gets set = df.t = 109
1972-11-08 00:00:00 6,310883    0,413861    109 111 1,052520384 0,898236364
1972-11-09 00:00:00 6,227073    0,422439    109 112 1,066686226 0,879996875

Thanks a lot for your support!

@Alexander: Yes, the rebalancing will take place the following day.

@maxymoo: If you implement this code after yours, you get the portfolio weights of each stock and they don't rest between 45 and 55%. It's rather between 75% and 25%:

df["ford_weight"] = df.ford_prop*df.ford/(df.ford_prop*df.ford+df.ibm_prop*df.ibm) #calculating the actual portfolio weights
df["ibm_weight"] = df.ibm_prop*df.ibm/(df.ford_prop*df.ford+df.ibm_prop*df.ibm)

print df
print df.ibm_weight.min()
print df.ibm_weight.max()
print df.ford_weight.min()
print df.ford_weight.max()

I tried no for an hour or so to fix, but didn't find it.

Can I do anything to make this question clearer?

Answer 1

The main idea here is to work in terms of dollars instead of ratios. If you keep track of the number of shares and the relative dollar values of the ibm and ford shares, then you can express the criterion for rebalancing as

mask = (df['ratio'] >= 1+tol) | (df['ratio'] <= 1-tol)

where the ratio equals

    df['ratio'] = df['ibm value'] / df['ford value']

and df['ibm value'], and df['ford value'] represent actual dollar values.

---

import datetime as DT
import numpy as np
import pandas as pd
import pandas.io.data as PID

def setup_df():
    df1 = PID.get_data_yahoo("IBM", 
                             start=DT.datetime(1970, 1, 1), 
                             end=DT.datetime.today())
    df1.rename(columns={'Adj Close': 'ibm'}, inplace=True)

    df2 = PID.get_data_yahoo("F", 
                             start=DT.datetime(1970, 1, 1), 
                             end=DT.datetime.today())
    df2.rename(columns={'Adj Close': 'ford'}, inplace=True)

    df = df1.join(df2.ford, how='inner')
    df = df[['ibm', 'ford']]
    df['sh ibm'] = 0
    df['sh ford'] = 0
    df['ibm value'] = 0
    df['ford value'] = 0
    df['ratio'] = 0
    return df

def invest(df, i, amount):
    """
    Invest amount dollars evenly between ibm and ford
    starting at ordinal index i.
    This modifies df.
    """
    c = dict([(col, j) for j, col in enumerate(df.columns)])
    halfvalue = amount/2
    df.iloc[i:, c['sh ibm']] = halfvalue / df.iloc[i, c['ibm']]
    df.iloc[i:, c['sh ford']] = halfvalue / df.iloc[i, c['ford']]

    df.iloc[i:, c['ibm value']] = (
        df.iloc[i:, c['ibm']] * df.iloc[i:, c['sh ibm']])
    df.iloc[i:, c['ford value']] = (
        df.iloc[i:, c['ford']] * df.iloc[i:, c['sh ford']])
    df.iloc[i:, c['ratio']] = (
        df.iloc[i:, c['ibm value']] / df.iloc[i:, c['ford value']])

def rebalance(df, tol, i=0):
    """
    Rebalance df whenever the ratio falls outside the tolerance range.
    This modifies df.
    """
    c = dict([(col, j) for j, col in enumerate(df.columns)])
    while True:
        mask = (df['ratio'] >= 1+tol) | (df['ratio'] <= 1-tol)
        # ignore prior locations where the ratio falls outside tol range
        mask[:i] = False
        try:
            # Move i one index past the first index where mask is True
            # Note that this means the ratio at i will remain outside tol range
            i = np.where(mask)[0][0] + 1
        except IndexError:
            break
        amount = (df.iloc[i, c['ibm value']] + df.iloc[i, c['ford value']])
        invest(df, i, amount)
    return df

df = setup_df()
tol = 0.05
invest(df, i=0, amount=100)
rebalance(df, tol)

df['portfolio value'] = df['ibm value'] + df['ford value']
df['ibm weight'] = df['ibm value'] / df['portfolio value']
df['ford weight'] = df['ford value'] / df['portfolio value']

print df['ibm weight'].min()
print df['ibm weight'].max()
print df['ford weight'].min()
print df['ford weight'].max()

# This shows the rows which trigger rebalancing
mask = (df['ratio'] >= 1+tol) | (df['ratio'] <= 1-tol)
print(df.loc[mask])