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](http://stackoverflow.com/questions/4119054/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: 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])
    

