To use this interactive portfolio rebalancer, you can change the settings in the two cells below. Once you have your inputs set to your liking, go to the top menu bar and select 'Kernel -> Restart & Run All' then click the red "Restart and Run All Cells' button on the pop up dialog. The entire code will now re-reun with your updated inputs. After about 15-30 seconds it should complete and you will see your rebalanced portfolio at the bottom. If there are errors with the Tiingo API its likely because the API key I provided for this interactive demo has been overused by interested users like you! Its easy enough to register your own API key at Tiingo (its free). Once you have your own API key, just uncomment the line "tiingo_key = 'YOURKEYGOESHERE'" and replace your key inbetween the single quotes and re-run.
You can see the original post here: evgenypogorelov.com
And the git repo: https://github.com/pogoetic/rebalance
Last, but not least, a huge shoutout and thanks to the team behind Binder, its an amazing tool that lets me host this interactive Jupyter notebook for free, and enables you to play around with the code in a real environment. Have fun! Post questions or feedback in the comments.
#Settings
new_money_in = 10000
#Set our rebalance threshold
rebal_threshold = .05 #allowable allocation drift
rebal_timeframe = 180 #in days
#Define target and current allocations
#create our target allocation
columns_t = ['ticker','allocation_target','assetclass']
positions_t = [['VTSAX',0.5652,'ST'],
['VIGAX',0.0131,'ST'],
['VSMAX',0.0066,'ST'],
['VSEQX',0.0066,'ST'],
['VWIGX',0.0507,'ST'],
['VTRIX',0.0507,'ST'],
['VTIAX',0.1521,'ST'],
['VBTLX',0.035,'BD'],
['VTABX',0.015,'BD'],
['VGSLX',0.05,'RE'],
['VNQI',0.01,'RE'],
['VDE',0.03,'ST'],
['GLD',0.015,'CS']]
#set our current portfolio
columns_c = ['accounttype','accountid','lastrebaldate','ticker','assetclass','basisdate','costbasis','shares']
positions_c = [['RIRA','1111','2018-11-16','VBTLX','BD','2018-11-16',1,913.483],
['RIRA','1111','2018-11-16','VTIAX','ST','2018-11-16',1,514.298],
['RIRA','1111','2018-11-16','VTSAX','ST','2018-11-16',10,151.121],
['RIRA','2222','2018-11-16','VBTLX','BD','2018-11-16',1,772.407],
['RIRA','2222','2018-11-16','VTSAX','ST','2018-11-16',20,151.578],
['TAXB','3333','2018-11-16','AAPL','ST','2018-11-16',1,3.14],
['TAXB','3333','2018-11-16','VTSAX','ST','2018-11-16',10,549.871]]
#Lets import the necessary packages
import pandas as pd
from IPython.display import display
pd.set_option('display.max_columns', None)
import numpy as np
import datetime
import decimal
from pandas_datareader import data as pdr
from keys import tiingo_key
#define todays datetime
now = datetime.datetime.now()
#uncomment below to override tiingo_key with your own!
#tiingo_key = 'YOURKEYGOESHERE'
#lookup table for account type abbreviations
accounttypes = {'TAXB':'Taxable Brokerage', '401K':'401k', 'RIRA':'Roth-IRA', 'TIRA':'Traditional-IRA'}
assetclasses = {'ST':'Equity Stocks', 'BD':'Bonds Fixed-Income', 'CS':'Cash and Commodities', 'RE':'Real-Estate', 'ALT':'Alternatives'}
assettypes = {'SEC':'Individual Security', 'ETF':'Exchange Traded Fund', 'MF': 'Mutual Fund', 'IF':'Index Fund'}
assetregion = {'D':'Domestic','I':'International'}
#initialize target portfolio
targetalloc = pd.DataFrame(columns = columns_t, data = positions_t)
total=decimal.Decimal(targetalloc.allocation_target.sum())
#check that our target allocation indeed adds to 100%
assert round(total,4) == 1,'Target Allocation not 100% : {}'.format(int(total))
#initialize current portfolio
start_port = pd.DataFrame(columns = columns_c, data = positions_c)
start_port.lastrebaldate = pd.to_datetime(start_port.lastrebaldate)
start_port.basisdate = pd.to_datetime(start_port.basisdate)
#custom apply function
def f(x):
d = {}
d['lastrebaldate'] = x['lastrebaldate'].max()
d['assetclass'] = x['assetclass'].max()
d['basisdate'] = x['basisdate'].min()
d['costbasis'] = (x['costbasis'] * x['shares']).sum()/(x['shares'].sum() or 1) #weighted avg
d['shares'] = x['shares'].sum()
return pd.Series(d, index=['lastrebaldate', 'assetclass', 'basisdate', 'costbasis', 'shares'])
#aggregate by ticker to account for duplicate securities held in different accounts
agg_port = start_port.groupby(['ticker']).apply(f)
#Define list of distinct tickers we care about
tickers = set(targetalloc.ticker.unique().tolist()+start_port.ticker.unique().tolist())
#Next we pull the latest prices from Tiingo (YahooFinance is buggy, and IEX does not contain mutual fund data)
#Tiingo limits for free API: 500 unique tickers ever, 500 requests/hr, 20,000 requests/day
#https://pandas-datareader.readthedocs.io/en/latest/remote_data.html#tiingo
#Tiingo API key required: set 'tiingo_key' value in python file called 'keys.py' in same directory as this script
now = datetime.datetime.now()
yesterday = now - datetime.timedelta(3) #avoids weekends with no data - need better weekend detection
start = datetime.datetime(yesterday.year, yesterday.month, yesterday.day)
end = datetime.datetime(now.year, now.month, now.day)
bad_tickers = []
for i, t in enumerate(tickers):
try:
if i==0:
ohlc = pdr.get_data_tiingo(t, api_key=tiingo_key).tail(1).close
else:
ohlc = ohlc.append(pdr.get_data_tiingo(t, api_key=tiingo_key).tail(1).close)
except:
bad_tickers.append(t)
#print(bad_tickers)
ohlc = ohlc.to_frame(name='close')
#drop our date index since its only the latest data
ohlc2=ohlc.reset_index(level=1, drop=True)
#Manual fix for known bad_tickers which Tiingo can't find, adjust to suit your needs
if 'VMFXX' in bad_tickers:
ohlc2.loc['VMFXX'] = 1.0
#concatenate target allocation and latest prices with our portfolio
start_port_c = pd.merge(agg_port, targetalloc, on ='ticker', how ='outer')
final_port = pd.merge(start_port_c, ohlc2, left_on ='ticker', right_index = True, how = 'left')
#set target to zero for tickers held but not present in our target allocation, set initial basisdate and costbasis for new securities entering the portfolio
final_port.fillna(value = {'allocation_target':0.0,'shares':0.0,'basisdate':pd.to_datetime(now.strftime("%Y-%m-%d")),'costbasis':final_port.close,'assetclass_x':final_port.assetclass_y},inplace = True)
final_port.drop(['assetclass_y'],axis=1,inplace=True)
final_port.rename(columns={'assetclass_x':'assetclass'},inplace=True)
#calculate holding values and current allocation
final_port['value'] = final_port.close * final_port.shares #calculate value as price x shares
final_port.loc[final_port.value.isna() & final_port.shares.isna(),['value']]=0.0 #for securities not currently held but in our target (and close price failed to return), establish zero value
final_port['allocation'] = final_port.value / final_port.value.sum()
final_port['correction'] = final_port.allocation_target - final_port.allocation
final_port['new_money_in'] = new_money_in * final_port.allocation_target #Account for new money in
#create timedelta int column
final_port['timedelta'] = (final_port.lastrebaldate - pd.to_datetime(now.strftime("%Y-%m-%d"))).dt.days
final_port.timedelta.fillna(0,inplace=True)
#define rebalance flags to determine if we must rebalance
final_port['rebal_flag_thresh'] = np.where((abs(final_port.correction)<=rebal_threshold) & (final_port.allocation > 0),0,1)
final_port['rebal_flag_time'] = np.where(final_port.timedelta >= rebal_timeframe,1,0)
final_port['rebal_flag_exit'] = np.where((final_port.allocation > 0) & (final_port.allocation_target==0),1,0) #force rebal securities not present in our target portfolio
final_port['rebal_flag_newmoney'] = np.where(final_port.new_money_in>0,1,0)
final_port['rebal_flag'] = np.where(final_port.rebal_flag_thresh + final_port.rebal_flag_time + final_port.rebal_flag_exit + final_port.rebal_flag_newmoney >= 1,1,0)
#Subset of securities we need to rebalance, and those we need to leave alone
rebal_port = final_port[final_port.rebal_flag==1].copy()
stable_port = final_port[final_port.rebal_flag==0].copy()
#Calculate our current allocation, target, and the change we need to hit target
total_val = rebal_port.value.sum()
rebal_port['allocation'] = rebal_port.value/rebal_port.value.sum()
rebal_port['allocation_target'] = rebal_port.allocation_target/rebal_port.allocation_target.sum()
rebal_port['correction'] = rebal_port.allocation_target - rebal_port.allocation
#Factor in any new money entering the portfolio and determine necessary changes in value and shares
rebal_port['value_chg'] = (total_val * rebal_port.correction) + rebal_port.new_money_in
rebal_port['shares_chg'] = rebal_port.value_chg / rebal_port.close
rebal_port.loc[rebal_port.value_chg.isna() & rebal_port.shares > 0,['shares_chg']]=-rebal_port.shares #sell all shares of securities not in our target portfolio
#Round off shares to whole numbers, except when we are fully exiting a position
rebal_port['shares_chg_round'] = rebal_port.shares_chg
rebal_port = rebal_port.astype({'shares_chg_round': int})
rebal_port['final_shares_chg'] = rebal_port.shares_chg
rebal_port.loc[np.round(rebal_port.shares_chg+rebal_port.shares)!=0,['final_shares_chg']]=rebal_port.shares_chg_round*1.0
rebal_port.drop(['shares_chg_round'],axis=1,inplace=True)
#Calculate initial new shares and values
rebal_port['new_shares'] = np.round(rebal_port.shares + rebal_port.final_shares_chg,3)
rebal_port['new_value'] = rebal_port.new_shares * rebal_port.close #due to share rounding, there will be slight variance vs. portfolio starting value
rebal_port['new_value_chg'] = rebal_port.final_shares_chg * rebal_port.close
#Double check our work so far
#net of buying and selling should be zero
assert(np.round(rebal_port.value_chg.sum(),3)-new_money_in==0)
#make sure totals match (with rounding error + new money in) from original portfolio and rebalanced portfolio
assert(np.round(rebal_port.new_value.sum() - rebal_port.value.sum(),3)==np.round((rebal_port.new_value.sum() + stable_port.value.sum()) - final_port.value.sum(),3))
#Merge our rebalanced portfolio with our stable portfolio for our execution portfolio
stable_port['value_chg'] = 0
stable_port['shares_chg']=0
stable_port['final_shares_chg'] = 0
stable_port['new_value_chg'] = 0
stable_port['new_shares'] = stable_port.shares
stable_port['new_value'] = stable_port.value
exec_port = pd.concat([rebal_port,stable_port],sort=False)
exec_port.drop(columns=['timedelta','rebal_flag_thresh','rebal_flag_time','rebal_flag_exit','rebal_flag_newmoney','value_chg','shares_chg'],inplace=True)
#Reset allocations to be based on all securities
exec_port['allocation'] = exec_port.value/exec_port.value.sum()
exec_port['allocation_target'] = exec_port.allocation_target/exec_port.allocation_target.sum()
exec_port['correction'] = exec_port.allocation_target - exec_port.allocation
exec_port['final_allocation'] = exec_port.new_value / exec_port.new_value.sum()
#Lets look at all our work to get to our target portfolio
exec_port
ticker | lastrebaldate | assetclass | basisdate | costbasis | shares | allocation_target | close | value | allocation | correction | new_money_in | rebal_flag | final_shares_chg | new_shares | new_value | new_value_chg | final_allocation | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | AAPL | 2018-11-16 | ST | 2018-11-16 | 1.000000 | 3.140 | 0.0000 | 156.15 | 490.31100 | 0.005905 | -0.005905 | 0.0 | 1 | -3.14 | 0.000 | 0.00000 | -490.311 | 0.000000 |
1 | VBTLX | 2018-11-16 | BD | 2018-11-16 | 1.000000 | 1685.890 | 0.0350 | 10.39 | 17516.39710 | 0.210973 | -0.175973 | 350.0 | 1 | -1372.00 | 313.890 | 3261.31710 | -14255.080 | 0.035196 |
2 | VTIAX | 2018-11-16 | ST | 2018-11-16 | 1.000000 | 514.298 | 0.1521 | 25.17 | 12944.88066 | 0.155913 | -0.003813 | 1521.0 | 1 | 47.00 | 561.298 | 14127.87066 | 1182.990 | 0.152468 |
3 | VTSAX | 2018-11-16 | ST | 2018-11-16 | 11.777895 | 852.570 | 0.5652 | 61.08 | 52074.97560 | 0.627209 | -0.062009 | 5652.0 | 1 | 8.00 | 860.570 | 52563.61560 | 488.640 | 0.567266 |
4 | VIGAX | NaT | ST | 2018-12-27 | 68.020000 | 0.000 | 0.0131 | 68.02 | 0.00000 | 0.000000 | 0.013100 | 131.0 | 1 | 17.00 | 17.000 | 1156.34000 | 1156.340 | 0.012479 |
5 | VSMAX | NaT | ST | 2018-12-27 | 62.250000 | 0.000 | 0.0066 | 62.25 | 0.00000 | 0.000000 | 0.006600 | 66.0 | 1 | 9.00 | 9.000 | 560.25000 | 560.250 | 0.006046 |
6 | VSEQX | NaT | ST | 2018-12-27 | 26.770000 | 0.000 | 0.0066 | 26.77 | 0.00000 | 0.000000 | 0.006600 | 66.0 | 1 | 22.00 | 22.000 | 588.94000 | 588.940 | 0.006356 |
7 | VWIGX | NaT | ST | 2018-12-27 | 24.760000 | 0.000 | 0.0507 | 24.76 | 0.00000 | 0.000000 | 0.050700 | 507.0 | 1 | 190.00 | 190.000 | 4704.40000 | 4704.400 | 0.050770 |
8 | VTRIX | NaT | ST | 2018-12-27 | 31.910000 | 0.000 | 0.0507 | 31.91 | 0.00000 | 0.000000 | 0.050700 | 507.0 | 1 | 147.00 | 147.000 | 4690.77000 | 4690.770 | 0.050623 |
9 | VTABX | NaT | BD | 2018-12-27 | 21.660000 | 0.000 | 0.0150 | 21.66 | 0.00000 | 0.000000 | 0.015000 | 150.0 | 1 | 64.00 | 64.000 | 1386.24000 | 1386.240 | 0.014960 |
10 | VGSLX | NaT | RE | 2018-12-27 | 105.060000 | 0.000 | 0.0500 | 105.06 | 0.00000 | 0.000000 | 0.050000 | 500.0 | 1 | 44.00 | 44.000 | 4622.64000 | 4622.640 | 0.049887 |
11 | VNQI | NaT | RE | 2018-12-27 | 52.050000 | 0.000 | 0.0100 | 52.05 | 0.00000 | 0.000000 | 0.010000 | 100.0 | 1 | 17.00 | 17.000 | 884.85000 | 884.850 | 0.009549 |
12 | VDE | NaT | ST | 2018-12-27 | 77.440000 | 0.000 | 0.0300 | 77.44 | 0.00000 | 0.000000 | 0.030000 | 300.0 | 1 | 36.00 | 36.000 | 2787.84000 | 2787.840 | 0.030086 |
13 | GLD | NaT | CS | 2018-12-27 | 120.570000 | 0.000 | 0.0150 | 120.57 | 0.00000 | 0.000000 | 0.015000 | 150.0 | 1 | 11.00 | 11.000 | 1326.27000 | 1326.270 | 0.014313 |
#Lets add a bar chart here to show the new allocation vs. the target allocation and vs. the original portfolio
graph_port = exec_port[['ticker','allocation','allocation_target','final_allocation']].copy()
graph_port.plot.barh(x='ticker',figsize=(20,10))
<matplotlib.axes._subplots.AxesSubplot at 0x11df9c630>
#Next we turn our ticker-level strategy into account level actions
#Join in our rebalanced portfolio and determine how to split value across accounts for a given ticker
port = pd.merge(start_port[['accounttype','accountid','ticker','shares']],
exec_port[['ticker','assetclass','close','value','final_shares_chg','new_shares','new_value','new_value_chg','final_allocation']],
how = 'right',
left_on = 'ticker',
right_on = 'ticker')
port['value_orig'] = port.close * port.shares
#Calculate the value-weight of each ticker by account
port['tick_alloc'] = port.value_orig / port.value #What pct of each ticker is in a given account?
port['tick_alloc'].fillna(1.0,inplace=True)
#check our sub-allocations
assert(port.groupby('ticker').tick_alloc.sum().sum() == len(port.groupby('ticker').tick_alloc.sum()))
#Recalculate the values proportionately
port['final_shares_chg_n'] = port.final_shares_chg * port.tick_alloc
port['new_shares_n'] = port.new_shares * port.tick_alloc
port['new_value_n'] = port.new_value * port.tick_alloc
port['new_value_chg_n'] = port.new_value_chg * port.tick_alloc
port['final_allocation_n'] = port.final_allocation * port.tick_alloc
#double check our final_allocation is 100%
assert(np.round(port.final_allocation_n.sum(),4)==1.0)
#Now we must double check to ensure we are not allocating buys to accounts with no sells (we cannot just add funds to a Traditional IRA account, for example)
#accounts with single securities in them which also exist in other accounts can cause issues if we don't do this
acctsdf = port.groupby(['accountid','accounttype']).new_value_chg_n.sum()
acctsdf = acctsdf.reset_index().rename(columns={'new_value_chg_n':'new_value_chg_sum'})
errordf = acctsdf[acctsdf.new_value_chg_sum > 0].copy() #a value >0 at the account-level implies we have allocated buys to an account with insufficient sells
erroraccts = errordf.accountid.values
if len(errordf) > 0:
for t in port[port.accountid.isin(erroraccts)].ticker.unique(): #Loop by security (not by account)
print("Correcting distribution for single-security accounts edge case: {}".format(t))
index = (port.accountid.isin(erroraccts)) & (port.ticker == t)
display(port[port.ticker == t])
#adjust numerator and denominator for proper recalculation of asset distribution across accounts
port.loc[index,'new_shares_n'] = port.new_shares_n - port.final_shares_chg_n
port.loc[index,'new_value_n'] = port.new_value_n - port.new_value_chg_n
port.loc[index,'final_shares_chg_n'] = 0
port.loc[index,'new_value_chg_n'] = 0
#remove from denominator
port.loc[port.ticker == t,'value'] = port.loc[port.ticker == t,'value'] - port[index].value_orig.sum()
#recalculate values for this ticker
port.loc[port.ticker == t,'tick_alloc'] = port[port.ticker == t].value_orig / port[port.ticker == t].value
port.loc[index,'tick_alloc'] = 0 #set new money allocation to zero for funds with insufficient assets
port.loc[port.ticker == t,'final_shares_chg_n'] = port.final_shares_chg * port.tick_alloc
port.loc[port.ticker == t,'new_shares_n'] = port.shares + port.final_shares_chg_n
port.loc[port.ticker == t,'new_value_chg_n'] = port.new_value_chg * port.tick_alloc
port.loc[port.ticker == t,'new_value_n'] = port.value_orig + port.new_value_chg_n
port.loc[port.ticker == t,'final_allocation_n'] = (port.new_value_n / port.new_value) * port.final_allocation
display(port[port.ticker == t])
#Cleanup
port['value'] = port.value_orig
port['final_shares_chg'] = port.final_shares_chg_n
port['new_shares'] = port.new_shares_n
port['new_value'] = port.new_value_n
port['new_value_chg'] = port.new_value_chg_n
port['final_allocation'] = port.final_allocation_n
port.drop(['value_orig','tick_alloc','final_shares_chg_n','new_shares_n','new_value_n','new_value_chg_n','final_allocation_n'],axis=1,inplace=True)
port.fillna({'value':0.0},inplace=True)
#Check our work
assert(np.round(port.final_allocation.sum(),4)==1.0)
assert(np.round(np.sum((port.shares+port.final_shares_chg)-port.new_shares))==0)
assert(np.round(np.sum(port.new_value-(port.new_shares*port.close)))==0)
assert(np.round(np.sum(port.new_value_chg-(port.final_shares_chg*port.close)))==0)
#Lets look at our final portfolio at the account level
display(port)
accounttype | accountid | ticker | shares | assetclass | close | value | final_shares_chg | new_shares | new_value | new_value_chg | final_allocation | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | RIRA | 1111 | VBTLX | 913.483 | BD | 10.39 | 9491.08837 | -743.404775 | 170.078225 | 1767.112759 | -7723.975611 | 0.019071 |
1 | RIRA | 2222 | VBTLX | 772.407 | BD | 10.39 | 8025.30873 | -628.595225 | 143.811775 | 1494.204341 | -6531.104389 | 0.016125 |
2 | RIRA | 1111 | VTIAX | 514.298 | ST | 25.17 | 12944.88066 | 47.000000 | 561.298000 | 14127.870660 | 1182.990000 | 0.152468 |
3 | RIRA | 1111 | VTSAX | 151.121 | ST | 61.08 | 9230.47068 | 1.418028 | 152.539028 | 9317.083821 | 86.613141 | 0.100550 |
4 | RIRA | 2222 | VTSAX | 151.578 | ST | 61.08 | 9258.38424 | 1.422316 | 153.000316 | 9345.259305 | 86.875065 | 0.100854 |
5 | TAXB | 3333 | VTSAX | 549.871 | ST | 61.08 | 33586.12068 | 5.159656 | 555.030656 | 33901.272475 | 315.151795 | 0.365862 |
6 | TAXB | 3333 | AAPL | 3.140 | ST | 156.15 | 490.31100 | -3.140000 | 0.000000 | 0.000000 | -490.311000 | 0.000000 |
7 | NaN | NaN | VIGAX | NaN | ST | 68.02 | 0.00000 | 17.000000 | 17.000000 | 1156.340000 | 1156.340000 | 0.012479 |
8 | NaN | NaN | VSMAX | NaN | ST | 62.25 | 0.00000 | 9.000000 | 9.000000 | 560.250000 | 560.250000 | 0.006046 |
9 | NaN | NaN | VSEQX | NaN | ST | 26.77 | 0.00000 | 22.000000 | 22.000000 | 588.940000 | 588.940000 | 0.006356 |
10 | NaN | NaN | VWIGX | NaN | ST | 24.76 | 0.00000 | 190.000000 | 190.000000 | 4704.400000 | 4704.400000 | 0.050770 |
11 | NaN | NaN | VTRIX | NaN | ST | 31.91 | 0.00000 | 147.000000 | 147.000000 | 4690.770000 | 4690.770000 | 0.050623 |
12 | NaN | NaN | VTABX | NaN | BD | 21.66 | 0.00000 | 64.000000 | 64.000000 | 1386.240000 | 1386.240000 | 0.014960 |
13 | NaN | NaN | VGSLX | NaN | RE | 105.06 | 0.00000 | 44.000000 | 44.000000 | 4622.640000 | 4622.640000 | 0.049887 |
14 | NaN | NaN | VNQI | NaN | RE | 52.05 | 0.00000 | 17.000000 | 17.000000 | 884.850000 | 884.850000 | 0.009549 |
15 | NaN | NaN | VDE | NaN | ST | 77.44 | 0.00000 | 36.000000 | 36.000000 | 2787.840000 | 2787.840000 | 0.030086 |
16 | NaN | NaN | GLD | NaN | CS | 120.57 | 0.00000 | 11.000000 | 11.000000 | 1326.270000 | 1326.270000 | 0.014313 |
#Finally, all new tickers need an account to land in
dport = None
acctsdf = None
if len(port[port.accounttype.isnull()])>0: #if we have none, skip this step
print('Distributing new securities to existing accounts . . .')
dport = port.copy()
#account-level fund surplus or deficit - must match these with our orphaned securities
acctsdf = port.groupby(['accountid','accounttype']).new_value_chg.sum()
acctsdf = acctsdf.reset_index().rename(columns={'new_value_chg':'new_value_chg_sum'})
#establish sort order so we can allocate tax-efficient account space first
actype_sortorder = pd.DataFrame(data=[['RIRA',1],['TIRA',2],['TAXB',3]],columns=['accounttype','order'])
acctsdf = pd.merge(acctsdf,actype_sortorder,how='left',left_on='accounttype',right_on='accounttype')
#We make a consequential assumption here that any new_money_in will be allocated 100% in one of the Taxable accounts (first in list).
#if you have a Roth-IRA which has not met its contribution limits for the year, it may be preferrential to distribute the funds there first.
#IF YOU HAVE NO TAXABLE ACCOUNT AND YOU WISH TO REBALANCE WITH new_money_in > 0 this will cause errors - so we assert here:
assert(new_money_in == 0 or (len(acctsdf[acctsdf.accounttype == 'TAXB'])>0 and new_money_in > 0))
min_idx = acctsdf[acctsdf.accounttype == 'TAXB'].index.min()
acctsdf.loc[min_idx,'new_value_chg_sum'] = acctsdf.loc[min_idx,'new_value_chg_sum'] - new_money_in
#only return accounts that have space
acctsdf = acctsdf[acctsdf.new_value_chg_sum<0].copy()
#establish sort order so we can allocate tax-inefficient assets first
aclass_sortorder = pd.DataFrame(data=[['ST',3],['BD',1],['CS',4],['RE',2],['ALT',5]],columns=['assetclass','order'])
dport = pd.merge(dport,aclass_sortorder,how='left',left_on='assetclass',right_on='assetclass')
#We loop twice, first to fit whole securities in accounts with tax location in mind, then again without tax location for anything leftover
loop = 0
while loop < 2:
loop+=1
#loop through orphaned tickers and place them in accounts until all assets are allocated or we are forced to split a security across accounts
# in the first loop we do not allow tax-inefficient assets to wind up in Taxable accounts, in the second loop we relax this constraint
for index, row in dport[dport.accounttype.isnull()].sort_values(['order','new_value_chg'],ascending=[True,False]).iterrows():
#loop through accounts and place the assets
for i, r in acctsdf.iterrows():
aid = r.accountid
atype = r.accounttype
bal = r.new_value_chg_sum
#print('Evaluating {}-{} with {} starting bal'.format(aid,atype,bal))
if loop == 0 and (row.assetclass in ('BD','RE') and atype == 'TAXB'):
continue #skip this case, since we don't want to place Bonds and Real-Estate assets in Taxable accounts
elif loop == 0 and (row.assetclass not in ('BD','RE') and atype != 'TAXB'):
continue #skip this case, since we don't want to place tax-efficient assets into tax sheltered accounts
if row.new_value_chg + bal <=0: #it fits
bal+=row.new_value_chg
print(' FITS {} in {}-{} with {} remaining'.format(row.ticker,aid,atype,bal))
#update our portfolio
dport.loc[index,'accountid'] = aid
dport.loc[index,'accounttype'] = atype
#update account bal for next loop
acctsdf.loc[i,'new_value_chg_sum'] = bal
break
else:
print(' {} {} does not fit in {}-{}'.format(row.ticker,row.new_value_chg,aid,atype))
print('\nLets see what remains in our accounts after 2 loops . . .')
display(acctsdf)
#Here we are forced to split a security across multiple accounts because no one account can fit it
# in this loop we allow tax-inefficient assets to wind up in Taxable accounts, but only as a last resort
if len(dport[dport.accounttype.isnull()])>0:
print('Splitting remaining securities across accounts . . .')
#loop through accounts and place portions of asset in each, create a new row in the df for each placement.
for index, row in dport[dport.accounttype.isnull()].sort_values(['order','new_value_chg'],ascending=[True,False]).iterrows():
final_shares_chg = row.final_shares_chg
asset_bal = row.new_value_chg
#if its a tax-inefficent asset, order the accounts by 'order'
if row.assetclass in ('BD','RE'):
acctsdf = acctsdf.sort_values('order',ascending=True)
else:
acctsdf = acctsdf.sort_values('order',ascending=False)
for i, r in acctsdf.iterrows():
bal = r.new_value_chg_sum
if asset_bal>-bal:
to_move = -bal
pct_move = -bal/row.new_value_chg
asset_bal+=bal
else:
to_move = asset_bal
pct_move = asset_bal/row.new_value_chg
asset_bal=0
print(' {} move {} or {}% into account {}-{}. {} bal remaining {}'.format(row.ticker,to_move,pct_move,r.accountid,r.accounttype,row.ticker,asset_bal))
#update our account to reflect this change
if asset_bal > 0:
acctsdf.loc[i,'new_value_chg_sum'] = 0.0
else:
acctsdf.loc[i,'new_value_chg_sum'] = to_move+bal
if (np.floor(pct_move*row.new_shares)*row.close)-row.value > 0:
#create new row in our portfolio for this asset in this account
dport.loc[max(dport.index)+1] = [r.accounttype,
r.accountid,
row.ticker,
row.shares,
row.assetclass,
row.close,
row.value,
np.floor(pct_move*row.final_shares_chg), #we round down to get back to whole shares
np.floor(pct_move*row.new_shares),
np.floor(pct_move*row.new_shares)*row.close,
(np.floor(pct_move*row.new_shares)*row.close)-row.value, #rounding can cause us to be short of our total allocatable funds
np.floor(pct_move*row.new_value)/dport.new_value.sum(),
row.order]
#finally delete the original row from the df
dport.drop(dport[dport.accounttype.isnull()].index,inplace=True)
#double check our work - we just care that distributed funds < total available funds for this ticker
assert(dport[dport.ticker==row.ticker].new_value_chg.sum() < row.new_value_chg)
Distributing new securities to existing accounts . . . FITS VTABX in 1111-RIRA with -5068.132470695083 remaining FITS VGSLX in 1111-RIRA with -445.4924706950824 remaining VNQI 884.8499999999999 does not fit in 1111-RIRA FITS VNQI in 2222-RIRA with -5559.379323808596 remaining VWIGX 4704.400000000001 does not fit in 1111-RIRA FITS VWIGX in 2222-RIRA with -854.9793238085958 remaining VTRIX 4690.77 does not fit in 1111-RIRA VTRIX 4690.77 does not fit in 2222-RIRA FITS VTRIX in 3333-TAXB with -5484.389205496322 remaining VDE 2787.84 does not fit in 1111-RIRA VDE 2787.84 does not fit in 2222-RIRA FITS VDE in 3333-TAXB with -2696.5492054963215 remaining VIGAX 1156.34 does not fit in 1111-RIRA VIGAX 1156.34 does not fit in 2222-RIRA FITS VIGAX in 3333-TAXB with -1540.2092054963216 remaining VSEQX 588.9399999999999 does not fit in 1111-RIRA FITS VSEQX in 2222-RIRA with -266.0393238085959 remaining VSMAX 560.25 does not fit in 1111-RIRA VSMAX 560.25 does not fit in 2222-RIRA FITS VSMAX in 3333-TAXB with -979.9592054963216 remaining GLD 1326.27 does not fit in 1111-RIRA GLD 1326.27 does not fit in 2222-RIRA GLD 1326.27 does not fit in 3333-TAXB GLD 1326.27 does not fit in 1111-RIRA GLD 1326.27 does not fit in 2222-RIRA GLD 1326.27 does not fit in 3333-TAXB Lets see what remains in our accounts after 2 loops . . .
accountid | accounttype | new_value_chg_sum | order | |
---|---|---|---|---|
0 | 1111 | RIRA | -445.492471 | 1 |
1 | 2222 | RIRA | -266.039324 | 1 |
2 | 3333 | TAXB | -979.959205 | 3 |
Splitting remaining securities across accounts . . . GLD move 979.9592054963216 or 0.7388836402062338% into account 3333-TAXB. GLD bal remaining 346.3107945036784 GLD move 346.3107945036784 or 0.26111635979376624% into account 1111-RIRA. GLD bal remaining 0 GLD move 0 or 0.0% into account 2222-RIRA. GLD bal remaining 0
#Lets see whats left in our accounts, it should be very close to zero
if acctsdf is not None:
display(acctsdf)
accountid | accounttype | new_value_chg_sum | order | |
---|---|---|---|---|
2 | 3333 | TAXB | 0.000000 | 3 |
0 | 1111 | RIRA | -99.181676 | 1 |
1 | 2222 | RIRA | -266.039324 | 1 |
#Review our final portfolio with recommended buys/sells in 'final_shares_chg' column
if dport is not None:
#Cleanup
dport.drop(columns=['order'],inplace=True)
dport = dport[['accounttype','accountid','ticker','shares','assetclass','close','value','new_shares','final_shares_chg','new_value','new_value_chg','final_allocation']]
display(dport)
else:
port = port[['accounttype','accountid','ticker','shares','assetclass','close','value','new_shares','final_shares_chg','new_value','new_value_chg','final_allocation']]
display(port)
accounttype | accountid | ticker | shares | assetclass | close | value | new_shares | final_shares_chg | new_value | new_value_chg | final_allocation | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | RIRA | 1111 | VBTLX | 913.483 | BD | 10.39 | 9491.08837 | 170.078225 | -743.404775 | 1767.112759 | -7723.975611 | 0.019071 |
1 | RIRA | 2222 | VBTLX | 772.407 | BD | 10.39 | 8025.30873 | 143.811775 | -628.595225 | 1494.204341 | -6531.104389 | 0.016125 |
2 | RIRA | 1111 | VTIAX | 514.298 | ST | 25.17 | 12944.88066 | 561.298000 | 47.000000 | 14127.870660 | 1182.990000 | 0.152468 |
3 | RIRA | 1111 | VTSAX | 151.121 | ST | 61.08 | 9230.47068 | 152.539028 | 1.418028 | 9317.083821 | 86.613141 | 0.100550 |
4 | RIRA | 2222 | VTSAX | 151.578 | ST | 61.08 | 9258.38424 | 153.000316 | 1.422316 | 9345.259305 | 86.875065 | 0.100854 |
5 | TAXB | 3333 | VTSAX | 549.871 | ST | 61.08 | 33586.12068 | 555.030656 | 5.159656 | 33901.272475 | 315.151795 | 0.365862 |
6 | TAXB | 3333 | AAPL | 3.140 | ST | 156.15 | 490.31100 | 0.000000 | -3.140000 | 0.000000 | -490.311000 | 0.000000 |
7 | TAXB | 3333 | VIGAX | NaN | ST | 68.02 | 0.00000 | 17.000000 | 17.000000 | 1156.340000 | 1156.340000 | 0.012479 |
8 | TAXB | 3333 | VSMAX | NaN | ST | 62.25 | 0.00000 | 9.000000 | 9.000000 | 560.250000 | 560.250000 | 0.006046 |
9 | RIRA | 2222 | VSEQX | NaN | ST | 26.77 | 0.00000 | 22.000000 | 22.000000 | 588.940000 | 588.940000 | 0.006356 |
10 | RIRA | 2222 | VWIGX | NaN | ST | 24.76 | 0.00000 | 190.000000 | 190.000000 | 4704.400000 | 4704.400000 | 0.050770 |
11 | TAXB | 3333 | VTRIX | NaN | ST | 31.91 | 0.00000 | 147.000000 | 147.000000 | 4690.770000 | 4690.770000 | 0.050623 |
12 | RIRA | 1111 | VTABX | NaN | BD | 21.66 | 0.00000 | 64.000000 | 64.000000 | 1386.240000 | 1386.240000 | 0.014960 |
13 | RIRA | 1111 | VGSLX | NaN | RE | 105.06 | 0.00000 | 44.000000 | 44.000000 | 4622.640000 | 4622.640000 | 0.049887 |
14 | RIRA | 2222 | VNQI | NaN | RE | 52.05 | 0.00000 | 17.000000 | 17.000000 | 884.850000 | 884.850000 | 0.009549 |
15 | TAXB | 3333 | VDE | NaN | ST | 77.44 | 0.00000 | 36.000000 | 36.000000 | 2787.840000 | 2787.840000 | 0.030086 |
17 | TAXB | 3333 | GLD | NaN | CS | 120.57 | 0.00000 | 8.000000 | 8.000000 | 964.560000 | 964.560000 | 0.010565 |
18 | RIRA | 1111 | GLD | NaN | CS | 120.57 | 0.00000 | 2.000000 | 2.000000 | 241.140000 | 241.140000 | 0.003749 |
Through this portfolio rebalancing demonstration using Pandas you can see we have achieved a rebalanced portfolio very closely approximating our desired target allocation. We can see how GLD was added as a new security, and AAPL was removed from the portfolio. The remaining securities were iteratively bought or sold as required by our target allocation. We accounted for whole-share rounding because most of our assets in this sample are index funds. This simple rebalancer can be adapted to your needs and I urge you grab the code and see if you can improve upon it. I welcome your thoughts or feedback in the comments.