{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Portfolio Rebalancer in Python"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Instructions\n",
"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](https://api.tiingo.com/) (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. \n",
"\n",
"You can see the original post here: [evgenypogorelov.com](https://evgenypogorelov.com/portfolio-rebalancing-python.html#portfolio-rebalancing-python) \n",
"And the git repo: https://github.com/pogoetic/rebalance \n",
"\n",
"Last, but not least, a huge shoutout and thanks to the team behind [Binder](https://mybinder.org/), 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. \n",
" "
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [],
"source": [
"#Settings\n",
"new_money_in = 10000\n",
"#Set our rebalance threshold\n",
"rebal_threshold = .05 #allowable allocation drift\n",
"rebal_timeframe = 180 #in days"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [],
"source": [
"#Define target and current allocations\n",
"#create our target allocation\n",
"columns_t = ['ticker','allocation_target','assetclass']\n",
"positions_t = [['VTSAX',0.5652,'ST'],\n",
" ['VIGAX',0.0131,'ST'],\n",
" ['VSMAX',0.0066,'ST'],\n",
" ['VSEQX',0.0066,'ST'],\n",
" ['VWIGX',0.0507,'ST'],\n",
" ['VTRIX',0.0507,'ST'],\n",
" ['VTIAX',0.1521,'ST'],\n",
" ['VBTLX',0.035,'BD'],\n",
" ['VTABX',0.015,'BD'],\n",
" ['VGSLX',0.05,'RE'],\n",
" ['VNQI',0.01,'RE'],\n",
" ['VDE',0.03,'ST'],\n",
" ['GLD',0.015,'CS']]\n",
"\n",
"#set our current portfolio\n",
"columns_c = ['accounttype','accountid','lastrebaldate','ticker','assetclass','basisdate','costbasis','shares']\n",
"positions_c = [['RIRA','1111','2018-11-16','VBTLX','BD','2018-11-16',1,913.483],\n",
" ['RIRA','1111','2018-11-16','VTIAX','ST','2018-11-16',1,514.298],\n",
" ['RIRA','1111','2018-11-16','VTSAX','ST','2018-11-16',10,151.121],\n",
" ['RIRA','2222','2018-11-16','VBTLX','BD','2018-11-16',1,772.407],\n",
" ['RIRA','2222','2018-11-16','VTSAX','ST','2018-11-16',20,151.578],\n",
" ['TAXB','3333','2018-11-16','AAPL','ST','2018-11-16',1,3.14],\n",
" ['TAXB','3333','2018-11-16','VTSAX','ST','2018-11-16',10,549.871]]"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### WARNING: Above this cell are settings you can change and test with. Below this cell is code you shouldn't touch unless you know Python"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [],
"source": [
"#Lets import the necessary packages\n",
"import pandas as pd\n",
"from IPython.display import display\n",
"pd.set_option('display.max_columns', None)\n",
"import numpy as np\n",
"import datetime\n",
"import decimal\n",
"from pandas_datareader import data as pdr\n",
"from keys import tiingo_key\n",
"#define todays datetime\n",
"now = datetime.datetime.now()\n",
"#uncomment below to override tiingo_key with your own! \n",
"#tiingo_key = 'YOURKEYGOESHERE'"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [],
"source": [
"#lookup table for account type abbreviations\n",
"accounttypes = {'TAXB':'Taxable Brokerage', '401K':'401k', 'RIRA':'Roth-IRA', 'TIRA':'Traditional-IRA'}\n",
"assetclasses = {'ST':'Equity Stocks', 'BD':'Bonds Fixed-Income', 'CS':'Cash and Commodities', 'RE':'Real-Estate', 'ALT':'Alternatives'}\n",
"assettypes = {'SEC':'Individual Security', 'ETF':'Exchange Traded Fund', 'MF': 'Mutual Fund', 'IF':'Index Fund'}\n",
"assetregion = {'D':'Domestic','I':'International'}\n",
"\n",
"#initialize target portfolio\n",
"targetalloc = pd.DataFrame(columns = columns_t, data = positions_t)\n",
"total=decimal.Decimal(targetalloc.allocation_target.sum())\n",
"#check that our target allocation indeed adds to 100%\n",
"assert round(total,4) == 1,'Target Allocation not 100% : {}'.format(int(total))\n",
"\n",
"#initialize current portfolio\n",
"start_port = pd.DataFrame(columns = columns_c, data = positions_c)\n",
"start_port.lastrebaldate = pd.to_datetime(start_port.lastrebaldate)\n",
"start_port.basisdate = pd.to_datetime(start_port.basisdate)\n",
"\n",
"#custom apply function\n",
"def f(x):\n",
" d = {}\n",
" d['lastrebaldate'] = x['lastrebaldate'].max()\n",
" d['assetclass'] = x['assetclass'].max()\n",
" d['basisdate'] = x['basisdate'].min()\n",
" d['costbasis'] = (x['costbasis'] * x['shares']).sum()/(x['shares'].sum() or 1) #weighted avg\n",
" d['shares'] = x['shares'].sum()\n",
" return pd.Series(d, index=['lastrebaldate', 'assetclass', 'basisdate', 'costbasis', 'shares'])\n",
"\n",
"#aggregate by ticker to account for duplicate securities held in different accounts\n",
"agg_port = start_port.groupby(['ticker']).apply(f)\n",
"\n",
"#Define list of distinct tickers we care about\n",
"tickers = set(targetalloc.ticker.unique().tolist()+start_port.ticker.unique().tolist())"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [],
"source": [
"#Next we pull the latest prices from Tiingo (YahooFinance is buggy, and IEX does not contain mutual fund data)\n",
"#Tiingo limits for free API: 500 unique tickers ever, 500 requests/hr, 20,000 requests/day\n",
"#https://pandas-datareader.readthedocs.io/en/latest/remote_data.html#tiingo\n",
"#Tiingo API key required: set 'tiingo_key' value in python file called 'keys.py' in same directory as this script\n",
"now = datetime.datetime.now()\n",
"yesterday = now - datetime.timedelta(3) #avoids weekends with no data - need better weekend detection\n",
"start = datetime.datetime(yesterday.year, yesterday.month, yesterday.day)\n",
"end = datetime.datetime(now.year, now.month, now.day)\n",
"\n",
"bad_tickers = []\n",
"for i, t in enumerate(tickers):\n",
" try:\n",
" if i==0:\n",
" ohlc = pdr.get_data_tiingo(t, api_key=tiingo_key).tail(1).close\n",
" else:\n",
" ohlc = ohlc.append(pdr.get_data_tiingo(t, api_key=tiingo_key).tail(1).close)\n",
" except:\n",
" bad_tickers.append(t)\n",
" \n",
"#print(bad_tickers)\n",
"ohlc = ohlc.to_frame(name='close')\n",
"\n",
"#drop our date index since its only the latest data\n",
"ohlc2=ohlc.reset_index(level=1, drop=True)\n",
"\n",
"#Manual fix for known bad_tickers which Tiingo can't find, adjust to suit your needs\n",
"if 'VMFXX' in bad_tickers:\n",
" ohlc2.loc['VMFXX'] = 1.0"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [],
"source": [
"#concatenate target allocation and latest prices with our portfolio\n",
"start_port_c = pd.merge(agg_port, targetalloc, on ='ticker', how ='outer')\n",
"final_port = pd.merge(start_port_c, ohlc2, left_on ='ticker', right_index = True, how = 'left')\n",
"\n",
"#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\n",
"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)\n",
"final_port.drop(['assetclass_y'],axis=1,inplace=True)\n",
"final_port.rename(columns={'assetclass_x':'assetclass'},inplace=True)\n",
"\n",
"#calculate holding values and current allocation\n",
"final_port['value'] = final_port.close * final_port.shares #calculate value as price x shares\n",
"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\n",
"final_port['allocation'] = final_port.value / final_port.value.sum()\n",
"final_port['correction'] = final_port.allocation_target - final_port.allocation\n",
"final_port['new_money_in'] = new_money_in * final_port.allocation_target #Account for new money in"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {},
"outputs": [],
"source": [
"#create timedelta int column\n",
"final_port['timedelta'] = (final_port.lastrebaldate - pd.to_datetime(now.strftime(\"%Y-%m-%d\"))).dt.days\n",
"final_port.timedelta.fillna(0,inplace=True)\n",
"\n",
"#define rebalance flags to determine if we must rebalance\n",
"final_port['rebal_flag_thresh'] = np.where((abs(final_port.correction)<=rebal_threshold) & (final_port.allocation > 0),0,1)\n",
"final_port['rebal_flag_time'] = np.where(final_port.timedelta >= rebal_timeframe,1,0)\n",
"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\n",
"final_port['rebal_flag_newmoney'] = np.where(final_port.new_money_in>0,1,0)\n",
"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)\n",
"\n",
"#Subset of securities we need to rebalance, and those we need to leave alone\n",
"rebal_port = final_port[final_port.rebal_flag==1].copy()\n",
"stable_port = final_port[final_port.rebal_flag==0].copy()"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {},
"outputs": [],
"source": [
"#Calculate our current allocation, target, and the change we need to hit target\n",
"total_val = rebal_port.value.sum()\n",
"rebal_port['allocation'] = rebal_port.value/rebal_port.value.sum()\n",
"rebal_port['allocation_target'] = rebal_port.allocation_target/rebal_port.allocation_target.sum()\n",
"rebal_port['correction'] = rebal_port.allocation_target - rebal_port.allocation\n",
"\n",
"#Factor in any new money entering the portfolio and determine necessary changes in value and shares\n",
"rebal_port['value_chg'] = (total_val * rebal_port.correction) + rebal_port.new_money_in\n",
"rebal_port['shares_chg'] = rebal_port.value_chg / rebal_port.close\n",
"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\n",
"\n",
"#Round off shares to whole numbers, except when we are fully exiting a position\n",
"rebal_port['shares_chg_round'] = rebal_port.shares_chg\n",
"rebal_port = rebal_port.astype({'shares_chg_round': int})\n",
"rebal_port['final_shares_chg'] = rebal_port.shares_chg\n",
"rebal_port.loc[np.round(rebal_port.shares_chg+rebal_port.shares)!=0,['final_shares_chg']]=rebal_port.shares_chg_round*1.0\n",
"rebal_port.drop(['shares_chg_round'],axis=1,inplace=True)\n",
"\n",
"#Calculate initial new shares and values\n",
"rebal_port['new_shares'] = np.round(rebal_port.shares + rebal_port.final_shares_chg,3)\n",
"rebal_port['new_value'] = rebal_port.new_shares * rebal_port.close #due to share rounding, there will be slight variance vs. portfolio starting value\n",
"rebal_port['new_value_chg'] = rebal_port.final_shares_chg * rebal_port.close"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {},
"outputs": [],
"source": [
"#Double check our work so far\n",
"#net of buying and selling should be zero\n",
"assert(np.round(rebal_port.value_chg.sum(),3)-new_money_in==0) \n",
"#make sure totals match (with rounding error + new money in) from original portfolio and rebalanced portfolio\n",
"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))"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {},
"outputs": [],
"source": [
"#Merge our rebalanced portfolio with our stable portfolio for our execution portfolio\n",
"stable_port['value_chg'] = 0\n",
"stable_port['shares_chg']=0\n",
"stable_port['final_shares_chg'] = 0\n",
"stable_port['new_value_chg'] = 0\n",
"stable_port['new_shares'] = stable_port.shares\n",
"stable_port['new_value'] = stable_port.value\n",
"exec_port = pd.concat([rebal_port,stable_port],sort=False)\n",
"exec_port.drop(columns=['timedelta','rebal_flag_thresh','rebal_flag_time','rebal_flag_exit','rebal_flag_newmoney','value_chg','shares_chg'],inplace=True)\n",
"\n",
"#Reset allocations to be based on all securities\n",
"exec_port['allocation'] = exec_port.value/exec_port.value.sum()\n",
"exec_port['allocation_target'] = exec_port.allocation_target/exec_port.allocation_target.sum()\n",
"exec_port['correction'] = exec_port.allocation_target - exec_port.allocation\n",
"exec_port['final_allocation'] = exec_port.new_value / exec_port.new_value.sum()"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"
"
]
},
"metadata": {
"needs_background": "light"
},
"output_type": "display_data"
}
],
"source": [
"#Lets add a bar chart here to show the new allocation vs. the target allocation and vs. the original portfolio\n",
"graph_port = exec_port[['ticker','allocation','allocation_target','final_allocation']].copy()\n",
"graph_port.plot.barh(x='ticker',figsize=(20,10))"
]
},
{
"cell_type": "code",
"execution_count": 24,
"metadata": {
"scrolled": false
},
"outputs": [
{
"data": {
"text/html": [
"
\n",
"\n",
"
\n",
" \n",
"
\n",
"
\n",
"
accounttype
\n",
"
accountid
\n",
"
ticker
\n",
"
shares
\n",
"
assetclass
\n",
"
close
\n",
"
value
\n",
"
final_shares_chg
\n",
"
new_shares
\n",
"
new_value
\n",
"
new_value_chg
\n",
"
final_allocation
\n",
"
\n",
" \n",
" \n",
"
\n",
"
0
\n",
"
RIRA
\n",
"
1111
\n",
"
VBTLX
\n",
"
913.483
\n",
"
BD
\n",
"
10.39
\n",
"
9491.08837
\n",
"
-743.404775
\n",
"
170.078225
\n",
"
1767.112759
\n",
"
-7723.975611
\n",
"
0.019071
\n",
"
\n",
"
\n",
"
1
\n",
"
RIRA
\n",
"
2222
\n",
"
VBTLX
\n",
"
772.407
\n",
"
BD
\n",
"
10.39
\n",
"
8025.30873
\n",
"
-628.595225
\n",
"
143.811775
\n",
"
1494.204341
\n",
"
-6531.104389
\n",
"
0.016125
\n",
"
\n",
"
\n",
"
2
\n",
"
RIRA
\n",
"
1111
\n",
"
VTIAX
\n",
"
514.298
\n",
"
ST
\n",
"
25.17
\n",
"
12944.88066
\n",
"
47.000000
\n",
"
561.298000
\n",
"
14127.870660
\n",
"
1182.990000
\n",
"
0.152468
\n",
"
\n",
"
\n",
"
3
\n",
"
RIRA
\n",
"
1111
\n",
"
VTSAX
\n",
"
151.121
\n",
"
ST
\n",
"
61.08
\n",
"
9230.47068
\n",
"
1.418028
\n",
"
152.539028
\n",
"
9317.083821
\n",
"
86.613141
\n",
"
0.100550
\n",
"
\n",
"
\n",
"
4
\n",
"
RIRA
\n",
"
2222
\n",
"
VTSAX
\n",
"
151.578
\n",
"
ST
\n",
"
61.08
\n",
"
9258.38424
\n",
"
1.422316
\n",
"
153.000316
\n",
"
9345.259305
\n",
"
86.875065
\n",
"
0.100854
\n",
"
\n",
"
\n",
"
5
\n",
"
TAXB
\n",
"
3333
\n",
"
VTSAX
\n",
"
549.871
\n",
"
ST
\n",
"
61.08
\n",
"
33586.12068
\n",
"
5.159656
\n",
"
555.030656
\n",
"
33901.272475
\n",
"
315.151795
\n",
"
0.365862
\n",
"
\n",
"
\n",
"
6
\n",
"
TAXB
\n",
"
3333
\n",
"
AAPL
\n",
"
3.140
\n",
"
ST
\n",
"
156.15
\n",
"
490.31100
\n",
"
-3.140000
\n",
"
0.000000
\n",
"
0.000000
\n",
"
-490.311000
\n",
"
0.000000
\n",
"
\n",
"
\n",
"
7
\n",
"
NaN
\n",
"
NaN
\n",
"
VIGAX
\n",
"
NaN
\n",
"
ST
\n",
"
68.02
\n",
"
0.00000
\n",
"
17.000000
\n",
"
17.000000
\n",
"
1156.340000
\n",
"
1156.340000
\n",
"
0.012479
\n",
"
\n",
"
\n",
"
8
\n",
"
NaN
\n",
"
NaN
\n",
"
VSMAX
\n",
"
NaN
\n",
"
ST
\n",
"
62.25
\n",
"
0.00000
\n",
"
9.000000
\n",
"
9.000000
\n",
"
560.250000
\n",
"
560.250000
\n",
"
0.006046
\n",
"
\n",
"
\n",
"
9
\n",
"
NaN
\n",
"
NaN
\n",
"
VSEQX
\n",
"
NaN
\n",
"
ST
\n",
"
26.77
\n",
"
0.00000
\n",
"
22.000000
\n",
"
22.000000
\n",
"
588.940000
\n",
"
588.940000
\n",
"
0.006356
\n",
"
\n",
"
\n",
"
10
\n",
"
NaN
\n",
"
NaN
\n",
"
VWIGX
\n",
"
NaN
\n",
"
ST
\n",
"
24.76
\n",
"
0.00000
\n",
"
190.000000
\n",
"
190.000000
\n",
"
4704.400000
\n",
"
4704.400000
\n",
"
0.050770
\n",
"
\n",
"
\n",
"
11
\n",
"
NaN
\n",
"
NaN
\n",
"
VTRIX
\n",
"
NaN
\n",
"
ST
\n",
"
31.91
\n",
"
0.00000
\n",
"
147.000000
\n",
"
147.000000
\n",
"
4690.770000
\n",
"
4690.770000
\n",
"
0.050623
\n",
"
\n",
"
\n",
"
12
\n",
"
NaN
\n",
"
NaN
\n",
"
VTABX
\n",
"
NaN
\n",
"
BD
\n",
"
21.66
\n",
"
0.00000
\n",
"
64.000000
\n",
"
64.000000
\n",
"
1386.240000
\n",
"
1386.240000
\n",
"
0.014960
\n",
"
\n",
"
\n",
"
13
\n",
"
NaN
\n",
"
NaN
\n",
"
VGSLX
\n",
"
NaN
\n",
"
RE
\n",
"
105.06
\n",
"
0.00000
\n",
"
44.000000
\n",
"
44.000000
\n",
"
4622.640000
\n",
"
4622.640000
\n",
"
0.049887
\n",
"
\n",
"
\n",
"
14
\n",
"
NaN
\n",
"
NaN
\n",
"
VNQI
\n",
"
NaN
\n",
"
RE
\n",
"
52.05
\n",
"
0.00000
\n",
"
17.000000
\n",
"
17.000000
\n",
"
884.850000
\n",
"
884.850000
\n",
"
0.009549
\n",
"
\n",
"
\n",
"
15
\n",
"
NaN
\n",
"
NaN
\n",
"
VDE
\n",
"
NaN
\n",
"
ST
\n",
"
77.44
\n",
"
0.00000
\n",
"
36.000000
\n",
"
36.000000
\n",
"
2787.840000
\n",
"
2787.840000
\n",
"
0.030086
\n",
"
\n",
"
\n",
"
16
\n",
"
NaN
\n",
"
NaN
\n",
"
GLD
\n",
"
NaN
\n",
"
CS
\n",
"
120.57
\n",
"
0.00000
\n",
"
11.000000
\n",
"
11.000000
\n",
"
1326.270000
\n",
"
1326.270000
\n",
"
0.014313
\n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" accounttype accountid ticker shares assetclass close value \\\n",
"0 RIRA 1111 VBTLX 913.483 BD 10.39 9491.08837 \n",
"1 RIRA 2222 VBTLX 772.407 BD 10.39 8025.30873 \n",
"2 RIRA 1111 VTIAX 514.298 ST 25.17 12944.88066 \n",
"3 RIRA 1111 VTSAX 151.121 ST 61.08 9230.47068 \n",
"4 RIRA 2222 VTSAX 151.578 ST 61.08 9258.38424 \n",
"5 TAXB 3333 VTSAX 549.871 ST 61.08 33586.12068 \n",
"6 TAXB 3333 AAPL 3.140 ST 156.15 490.31100 \n",
"7 NaN NaN VIGAX NaN ST 68.02 0.00000 \n",
"8 NaN NaN VSMAX NaN ST 62.25 0.00000 \n",
"9 NaN NaN VSEQX NaN ST 26.77 0.00000 \n",
"10 NaN NaN VWIGX NaN ST 24.76 0.00000 \n",
"11 NaN NaN VTRIX NaN ST 31.91 0.00000 \n",
"12 NaN NaN VTABX NaN BD 21.66 0.00000 \n",
"13 NaN NaN VGSLX NaN RE 105.06 0.00000 \n",
"14 NaN NaN VNQI NaN RE 52.05 0.00000 \n",
"15 NaN NaN VDE NaN ST 77.44 0.00000 \n",
"16 NaN NaN GLD NaN CS 120.57 0.00000 \n",
"\n",
" final_shares_chg new_shares new_value new_value_chg \\\n",
"0 -743.404775 170.078225 1767.112759 -7723.975611 \n",
"1 -628.595225 143.811775 1494.204341 -6531.104389 \n",
"2 47.000000 561.298000 14127.870660 1182.990000 \n",
"3 1.418028 152.539028 9317.083821 86.613141 \n",
"4 1.422316 153.000316 9345.259305 86.875065 \n",
"5 5.159656 555.030656 33901.272475 315.151795 \n",
"6 -3.140000 0.000000 0.000000 -490.311000 \n",
"7 17.000000 17.000000 1156.340000 1156.340000 \n",
"8 9.000000 9.000000 560.250000 560.250000 \n",
"9 22.000000 22.000000 588.940000 588.940000 \n",
"10 190.000000 190.000000 4704.400000 4704.400000 \n",
"11 147.000000 147.000000 4690.770000 4690.770000 \n",
"12 64.000000 64.000000 1386.240000 1386.240000 \n",
"13 44.000000 44.000000 4622.640000 4622.640000 \n",
"14 17.000000 17.000000 884.850000 884.850000 \n",
"15 36.000000 36.000000 2787.840000 2787.840000 \n",
"16 11.000000 11.000000 1326.270000 1326.270000 \n",
"\n",
" final_allocation \n",
"0 0.019071 \n",
"1 0.016125 \n",
"2 0.152468 \n",
"3 0.100550 \n",
"4 0.100854 \n",
"5 0.365862 \n",
"6 0.000000 \n",
"7 0.012479 \n",
"8 0.006046 \n",
"9 0.006356 \n",
"10 0.050770 \n",
"11 0.050623 \n",
"12 0.014960 \n",
"13 0.049887 \n",
"14 0.009549 \n",
"15 0.030086 \n",
"16 0.014313 "
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"#Next we turn our ticker-level strategy into account level actions\n",
"#Join in our rebalanced portfolio and determine how to split value across accounts for a given ticker\n",
"port = pd.merge(start_port[['accounttype','accountid','ticker','shares']], \n",
" exec_port[['ticker','assetclass','close','value','final_shares_chg','new_shares','new_value','new_value_chg','final_allocation']], \n",
" how = 'right', \n",
" left_on = 'ticker', \n",
" right_on = 'ticker')\n",
"port['value_orig'] = port.close * port.shares\n",
"#Calculate the value-weight of each ticker by account\n",
"port['tick_alloc'] = port.value_orig / port.value #What pct of each ticker is in a given account?\n",
"port['tick_alloc'].fillna(1.0,inplace=True)\n",
"\n",
"#check our sub-allocations\n",
"assert(port.groupby('ticker').tick_alloc.sum().sum() == len(port.groupby('ticker').tick_alloc.sum()))\n",
"\n",
"#Recalculate the values proportionately\n",
"port['final_shares_chg_n'] = port.final_shares_chg * port.tick_alloc\n",
"port['new_shares_n'] = port.new_shares * port.tick_alloc\n",
"port['new_value_n'] = port.new_value * port.tick_alloc\n",
"port['new_value_chg_n'] = port.new_value_chg * port.tick_alloc\n",
"port['final_allocation_n'] = port.final_allocation * port.tick_alloc\n",
"\n",
"#double check our final_allocation is 100%\n",
"assert(np.round(port.final_allocation_n.sum(),4)==1.0)\n",
"\n",
"#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)\n",
"#accounts with single securities in them which also exist in other accounts can cause issues if we don't do this\n",
"acctsdf = port.groupby(['accountid','accounttype']).new_value_chg_n.sum()\n",
"acctsdf = acctsdf.reset_index().rename(columns={'new_value_chg_n':'new_value_chg_sum'})\n",
"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\n",
"erroraccts = errordf.accountid.values\n",
"if len(errordf) > 0: \n",
" for t in port[port.accountid.isin(erroraccts)].ticker.unique(): #Loop by security (not by account)\n",
" print(\"Correcting distribution for single-security accounts edge case: {}\".format(t))\n",
" index = (port.accountid.isin(erroraccts)) & (port.ticker == t)\n",
" display(port[port.ticker == t])\n",
" #adjust numerator and denominator for proper recalculation of asset distribution across accounts\n",
" port.loc[index,'new_shares_n'] = port.new_shares_n - port.final_shares_chg_n\n",
" port.loc[index,'new_value_n'] = port.new_value_n - port.new_value_chg_n\n",
" port.loc[index,'final_shares_chg_n'] = 0\n",
" port.loc[index,'new_value_chg_n'] = 0\n",
"\n",
" #remove from denominator\n",
" port.loc[port.ticker == t,'value'] = port.loc[port.ticker == t,'value'] - port[index].value_orig.sum()\n",
" \n",
" #recalculate values for this ticker\n",
" port.loc[port.ticker == t,'tick_alloc'] = port[port.ticker == t].value_orig / port[port.ticker == t].value\n",
" port.loc[index,'tick_alloc'] = 0 #set new money allocation to zero for funds with insufficient assets\n",
" port.loc[port.ticker == t,'final_shares_chg_n'] = port.final_shares_chg * port.tick_alloc\n",
" port.loc[port.ticker == t,'new_shares_n'] = port.shares + port.final_shares_chg_n\n",
" port.loc[port.ticker == t,'new_value_chg_n'] = port.new_value_chg * port.tick_alloc\n",
" port.loc[port.ticker == t,'new_value_n'] = port.value_orig + port.new_value_chg_n\n",
" port.loc[port.ticker == t,'final_allocation_n'] = (port.new_value_n / port.new_value) * port.final_allocation\n",
" \n",
" display(port[port.ticker == t])\n",
"\n",
"\n",
"#Cleanup\n",
"port['value'] = port.value_orig\n",
"port['final_shares_chg'] = port.final_shares_chg_n\n",
"port['new_shares'] = port.new_shares_n\n",
"port['new_value'] = port.new_value_n\n",
"port['new_value_chg'] = port.new_value_chg_n\n",
"port['final_allocation'] = port.final_allocation_n\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)\n",
"port.fillna({'value':0.0},inplace=True)\n",
"\n",
"#Check our work\n",
"assert(np.round(port.final_allocation.sum(),4)==1.0)\n",
"assert(np.round(np.sum((port.shares+port.final_shares_chg)-port.new_shares))==0)\n",
"assert(np.round(np.sum(port.new_value-(port.new_shares*port.close)))==0)\n",
"assert(np.round(np.sum(port.new_value_chg-(port.final_shares_chg*port.close)))==0)\n",
"\n",
"#Lets look at our final portfolio at the account level\n",
"display(port)"
]
},
{
"cell_type": "code",
"execution_count": 30,
"metadata": {
"scrolled": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Distributing new securities to existing accounts . . .\n",
" FITS VTABX in 1111-RIRA with -5068.132470695083 remaining\n",
" FITS VGSLX in 1111-RIRA with -445.4924706950824 remaining\n",
" VNQI 884.8499999999999 does not fit in 1111-RIRA\n",
" FITS VNQI in 2222-RIRA with -5559.379323808596 remaining\n",
" VWIGX 4704.400000000001 does not fit in 1111-RIRA\n",
" FITS VWIGX in 2222-RIRA with -854.9793238085958 remaining\n",
" VTRIX 4690.77 does not fit in 1111-RIRA\n",
" VTRIX 4690.77 does not fit in 2222-RIRA\n",
" FITS VTRIX in 3333-TAXB with -5484.389205496322 remaining\n",
" VDE 2787.84 does not fit in 1111-RIRA\n",
" VDE 2787.84 does not fit in 2222-RIRA\n",
" FITS VDE in 3333-TAXB with -2696.5492054963215 remaining\n",
" VIGAX 1156.34 does not fit in 1111-RIRA\n",
" VIGAX 1156.34 does not fit in 2222-RIRA\n",
" FITS VIGAX in 3333-TAXB with -1540.2092054963216 remaining\n",
" VSEQX 588.9399999999999 does not fit in 1111-RIRA\n",
" FITS VSEQX in 2222-RIRA with -266.0393238085959 remaining\n",
" VSMAX 560.25 does not fit in 1111-RIRA\n",
" VSMAX 560.25 does not fit in 2222-RIRA\n",
" FITS VSMAX in 3333-TAXB with -979.9592054963216 remaining\n",
" GLD 1326.27 does not fit in 1111-RIRA\n",
" GLD 1326.27 does not fit in 2222-RIRA\n",
" GLD 1326.27 does not fit in 3333-TAXB\n",
" GLD 1326.27 does not fit in 1111-RIRA\n",
" GLD 1326.27 does not fit in 2222-RIRA\n",
" GLD 1326.27 does not fit in 3333-TAXB\n",
"\n",
"Lets see what remains in our accounts after 2 loops . . .\n"
]
},
{
"data": {
"text/html": [
"
\n",
"\n",
"
\n",
" \n",
"
\n",
"
\n",
"
accountid
\n",
"
accounttype
\n",
"
new_value_chg_sum
\n",
"
order
\n",
"
\n",
" \n",
" \n",
"
\n",
"
0
\n",
"
1111
\n",
"
RIRA
\n",
"
-445.492471
\n",
"
1
\n",
"
\n",
"
\n",
"
1
\n",
"
2222
\n",
"
RIRA
\n",
"
-266.039324
\n",
"
1
\n",
"
\n",
"
\n",
"
2
\n",
"
3333
\n",
"
TAXB
\n",
"
-979.959205
\n",
"
3
\n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" accountid accounttype new_value_chg_sum order\n",
"0 1111 RIRA -445.492471 1\n",
"1 2222 RIRA -266.039324 1\n",
"2 3333 TAXB -979.959205 3"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"Splitting remaining securities across accounts . . .\n",
" GLD move 979.9592054963216 or 0.7388836402062338% into account 3333-TAXB. GLD bal remaining 346.3107945036784\n",
" GLD move 346.3107945036784 or 0.26111635979376624% into account 1111-RIRA. GLD bal remaining 0\n",
" GLD move 0 or 0.0% into account 2222-RIRA. GLD bal remaining 0\n"
]
}
],
"source": [
"#Finally, all new tickers need an account to land in\n",
"dport = None\n",
"acctsdf = None\n",
"if len(port[port.accounttype.isnull()])>0: #if we have none, skip this step\n",
" print('Distributing new securities to existing accounts . . .')\n",
" dport = port.copy()\n",
"\n",
" #account-level fund surplus or deficit - must match these with our orphaned securities\n",
" acctsdf = port.groupby(['accountid','accounttype']).new_value_chg.sum()\n",
" acctsdf = acctsdf.reset_index().rename(columns={'new_value_chg':'new_value_chg_sum'})\n",
" #establish sort order so we can allocate tax-efficient account space first\n",
" actype_sortorder = pd.DataFrame(data=[['RIRA',1],['TIRA',2],['TAXB',3]],columns=['accounttype','order'])\n",
" acctsdf = pd.merge(acctsdf,actype_sortorder,how='left',left_on='accounttype',right_on='accounttype')\n",
" #We make a consequential assumption here that any new_money_in will be allocated 100% in one of the Taxable accounts (first in list).\n",
" #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.\n",
" #IF YOU HAVE NO TAXABLE ACCOUNT AND YOU WISH TO REBALANCE WITH new_money_in > 0 this will cause errors - so we assert here:\n",
" assert(new_money_in == 0 or (len(acctsdf[acctsdf.accounttype == 'TAXB'])>0 and new_money_in > 0))\n",
" min_idx = acctsdf[acctsdf.accounttype == 'TAXB'].index.min()\n",
" acctsdf.loc[min_idx,'new_value_chg_sum'] = acctsdf.loc[min_idx,'new_value_chg_sum'] - new_money_in\n",
" #only return accounts that have space\n",
" acctsdf = acctsdf[acctsdf.new_value_chg_sum<0].copy()\n",
"\n",
" #establish sort order so we can allocate tax-inefficient assets first\n",
" aclass_sortorder = pd.DataFrame(data=[['ST',3],['BD',1],['CS',4],['RE',2],['ALT',5]],columns=['assetclass','order'])\n",
" dport = pd.merge(dport,aclass_sortorder,how='left',left_on='assetclass',right_on='assetclass')\n",
"\n",
" #We loop twice, first to fit whole securities in accounts with tax location in mind, then again without tax location for anything leftover\n",
" loop = 0\n",
" while loop < 2:\n",
" loop+=1\n",
" #loop through orphaned tickers and place them in accounts until all assets are allocated or we are forced to split a security across accounts\n",
" # 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\n",
" for index, row in dport[dport.accounttype.isnull()].sort_values(['order','new_value_chg'],ascending=[True,False]).iterrows():\n",
" #loop through accounts and place the assets\n",
" for i, r in acctsdf.iterrows():\n",
" aid = r.accountid\n",
" atype = r.accounttype\n",
" bal = r.new_value_chg_sum\n",
" #print('Evaluating {}-{} with {} starting bal'.format(aid,atype,bal))\n",
" if loop == 0 and (row.assetclass in ('BD','RE') and atype == 'TAXB'):\n",
" continue #skip this case, since we don't want to place Bonds and Real-Estate assets in Taxable accounts\n",
" elif loop == 0 and (row.assetclass not in ('BD','RE') and atype != 'TAXB'):\n",
" continue #skip this case, since we don't want to place tax-efficient assets into tax sheltered accounts \n",
"\n",
" if row.new_value_chg + bal <=0: #it fits\n",
" bal+=row.new_value_chg\n",
" print(' FITS {} in {}-{} with {} remaining'.format(row.ticker,aid,atype,bal))\n",
" #update our portfolio\n",
" dport.loc[index,'accountid'] = aid\n",
" dport.loc[index,'accounttype'] = atype\n",
" #update account bal for next loop\n",
" acctsdf.loc[i,'new_value_chg_sum'] = bal\n",
" break\n",
" else:\n",
" print(' {} {} does not fit in {}-{}'.format(row.ticker,row.new_value_chg,aid,atype))\n",
" \n",
" print('\\nLets see what remains in our accounts after 2 loops . . .')\n",
" display(acctsdf)\n",
" \n",
" #Here we are forced to split a security across multiple accounts because no one account can fit it\n",
" # in this loop we allow tax-inefficient assets to wind up in Taxable accounts, but only as a last resort\n",
" if len(dport[dport.accounttype.isnull()])>0:\n",
" print('Splitting remaining securities across accounts . . .')\n",
" #loop through accounts and place portions of asset in each, create a new row in the df for each placement.\n",
" for index, row in dport[dport.accounttype.isnull()].sort_values(['order','new_value_chg'],ascending=[True,False]).iterrows():\n",
" final_shares_chg = row.final_shares_chg\n",
" asset_bal = row.new_value_chg\n",
" #if its a tax-inefficent asset, order the accounts by 'order'\n",
" if row.assetclass in ('BD','RE'):\n",
" acctsdf = acctsdf.sort_values('order',ascending=True)\n",
" else:\n",
" acctsdf = acctsdf.sort_values('order',ascending=False)\n",
" \n",
" for i, r in acctsdf.iterrows():\n",
" bal = r.new_value_chg_sum\n",
" if asset_bal>-bal:\n",
" to_move = -bal\n",
" pct_move = -bal/row.new_value_chg\n",
" asset_bal+=bal\n",
" else:\n",
" to_move = asset_bal\n",
" pct_move = asset_bal/row.new_value_chg\n",
" asset_bal=0\n",
" print(' {} move {} or {}% into account {}-{}. {} bal remaining {}'.format(row.ticker,to_move,pct_move,r.accountid,r.accounttype,row.ticker,asset_bal))\n",
" \n",
" #update our account to reflect this change\n",
" if asset_bal > 0:\n",
" acctsdf.loc[i,'new_value_chg_sum'] = 0.0\n",
" else:\n",
" acctsdf.loc[i,'new_value_chg_sum'] = to_move+bal\n",
" \n",
" if (np.floor(pct_move*row.new_shares)*row.close)-row.value > 0:\n",
" #create new row in our portfolio for this asset in this account\n",
" dport.loc[max(dport.index)+1] = [r.accounttype,\n",
" r.accountid,\n",
" row.ticker,\n",
" row.shares,\n",
" row.assetclass,\n",
" row.close,\n",
" row.value,\n",
" np.floor(pct_move*row.final_shares_chg), #we round down to get back to whole shares\n",
" np.floor(pct_move*row.new_shares),\n",
" np.floor(pct_move*row.new_shares)*row.close,\n",
" (np.floor(pct_move*row.new_shares)*row.close)-row.value, #rounding can cause us to be short of our total allocatable funds\n",
" np.floor(pct_move*row.new_value)/dport.new_value.sum(),\n",
" row.order]\n",
" \n",
" #finally delete the original row from the df\n",
" dport.drop(dport[dport.accounttype.isnull()].index,inplace=True)\n",
" \n",
" #double check our work - we just care that distributed funds < total available funds for this ticker\n",
" assert(dport[dport.ticker==row.ticker].new_value_chg.sum() < row.new_value_chg)"
]
},
{
"cell_type": "code",
"execution_count": 31,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"
\n",
"\n",
"
\n",
" \n",
"
\n",
"
\n",
"
accountid
\n",
"
accounttype
\n",
"
new_value_chg_sum
\n",
"
order
\n",
"
\n",
" \n",
" \n",
"
\n",
"
2
\n",
"
3333
\n",
"
TAXB
\n",
"
0.000000
\n",
"
3
\n",
"
\n",
"
\n",
"
0
\n",
"
1111
\n",
"
RIRA
\n",
"
-99.181676
\n",
"
1
\n",
"
\n",
"
\n",
"
1
\n",
"
2222
\n",
"
RIRA
\n",
"
-266.039324
\n",
"
1
\n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" accountid accounttype new_value_chg_sum order\n",
"2 3333 TAXB 0.000000 3\n",
"0 1111 RIRA -99.181676 1\n",
"1 2222 RIRA -266.039324 1"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"#Lets see whats left in our accounts, it should be very close to zero\n",
"if acctsdf is not None:\n",
" display(acctsdf)"
]
},
{
"cell_type": "code",
"execution_count": 32,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"
\n",
"\n",
"
\n",
" \n",
"
\n",
"
\n",
"
accounttype
\n",
"
accountid
\n",
"
ticker
\n",
"
shares
\n",
"
assetclass
\n",
"
close
\n",
"
value
\n",
"
new_shares
\n",
"
final_shares_chg
\n",
"
new_value
\n",
"
new_value_chg
\n",
"
final_allocation
\n",
"
\n",
" \n",
" \n",
"
\n",
"
0
\n",
"
RIRA
\n",
"
1111
\n",
"
VBTLX
\n",
"
913.483
\n",
"
BD
\n",
"
10.39
\n",
"
9491.08837
\n",
"
170.078225
\n",
"
-743.404775
\n",
"
1767.112759
\n",
"
-7723.975611
\n",
"
0.019071
\n",
"
\n",
"
\n",
"
1
\n",
"
RIRA
\n",
"
2222
\n",
"
VBTLX
\n",
"
772.407
\n",
"
BD
\n",
"
10.39
\n",
"
8025.30873
\n",
"
143.811775
\n",
"
-628.595225
\n",
"
1494.204341
\n",
"
-6531.104389
\n",
"
0.016125
\n",
"
\n",
"
\n",
"
2
\n",
"
RIRA
\n",
"
1111
\n",
"
VTIAX
\n",
"
514.298
\n",
"
ST
\n",
"
25.17
\n",
"
12944.88066
\n",
"
561.298000
\n",
"
47.000000
\n",
"
14127.870660
\n",
"
1182.990000
\n",
"
0.152468
\n",
"
\n",
"
\n",
"
3
\n",
"
RIRA
\n",
"
1111
\n",
"
VTSAX
\n",
"
151.121
\n",
"
ST
\n",
"
61.08
\n",
"
9230.47068
\n",
"
152.539028
\n",
"
1.418028
\n",
"
9317.083821
\n",
"
86.613141
\n",
"
0.100550
\n",
"
\n",
"
\n",
"
4
\n",
"
RIRA
\n",
"
2222
\n",
"
VTSAX
\n",
"
151.578
\n",
"
ST
\n",
"
61.08
\n",
"
9258.38424
\n",
"
153.000316
\n",
"
1.422316
\n",
"
9345.259305
\n",
"
86.875065
\n",
"
0.100854
\n",
"
\n",
"
\n",
"
5
\n",
"
TAXB
\n",
"
3333
\n",
"
VTSAX
\n",
"
549.871
\n",
"
ST
\n",
"
61.08
\n",
"
33586.12068
\n",
"
555.030656
\n",
"
5.159656
\n",
"
33901.272475
\n",
"
315.151795
\n",
"
0.365862
\n",
"
\n",
"
\n",
"
6
\n",
"
TAXB
\n",
"
3333
\n",
"
AAPL
\n",
"
3.140
\n",
"
ST
\n",
"
156.15
\n",
"
490.31100
\n",
"
0.000000
\n",
"
-3.140000
\n",
"
0.000000
\n",
"
-490.311000
\n",
"
0.000000
\n",
"
\n",
"
\n",
"
7
\n",
"
TAXB
\n",
"
3333
\n",
"
VIGAX
\n",
"
NaN
\n",
"
ST
\n",
"
68.02
\n",
"
0.00000
\n",
"
17.000000
\n",
"
17.000000
\n",
"
1156.340000
\n",
"
1156.340000
\n",
"
0.012479
\n",
"
\n",
"
\n",
"
8
\n",
"
TAXB
\n",
"
3333
\n",
"
VSMAX
\n",
"
NaN
\n",
"
ST
\n",
"
62.25
\n",
"
0.00000
\n",
"
9.000000
\n",
"
9.000000
\n",
"
560.250000
\n",
"
560.250000
\n",
"
0.006046
\n",
"
\n",
"
\n",
"
9
\n",
"
RIRA
\n",
"
2222
\n",
"
VSEQX
\n",
"
NaN
\n",
"
ST
\n",
"
26.77
\n",
"
0.00000
\n",
"
22.000000
\n",
"
22.000000
\n",
"
588.940000
\n",
"
588.940000
\n",
"
0.006356
\n",
"
\n",
"
\n",
"
10
\n",
"
RIRA
\n",
"
2222
\n",
"
VWIGX
\n",
"
NaN
\n",
"
ST
\n",
"
24.76
\n",
"
0.00000
\n",
"
190.000000
\n",
"
190.000000
\n",
"
4704.400000
\n",
"
4704.400000
\n",
"
0.050770
\n",
"
\n",
"
\n",
"
11
\n",
"
TAXB
\n",
"
3333
\n",
"
VTRIX
\n",
"
NaN
\n",
"
ST
\n",
"
31.91
\n",
"
0.00000
\n",
"
147.000000
\n",
"
147.000000
\n",
"
4690.770000
\n",
"
4690.770000
\n",
"
0.050623
\n",
"
\n",
"
\n",
"
12
\n",
"
RIRA
\n",
"
1111
\n",
"
VTABX
\n",
"
NaN
\n",
"
BD
\n",
"
21.66
\n",
"
0.00000
\n",
"
64.000000
\n",
"
64.000000
\n",
"
1386.240000
\n",
"
1386.240000
\n",
"
0.014960
\n",
"
\n",
"
\n",
"
13
\n",
"
RIRA
\n",
"
1111
\n",
"
VGSLX
\n",
"
NaN
\n",
"
RE
\n",
"
105.06
\n",
"
0.00000
\n",
"
44.000000
\n",
"
44.000000
\n",
"
4622.640000
\n",
"
4622.640000
\n",
"
0.049887
\n",
"
\n",
"
\n",
"
14
\n",
"
RIRA
\n",
"
2222
\n",
"
VNQI
\n",
"
NaN
\n",
"
RE
\n",
"
52.05
\n",
"
0.00000
\n",
"
17.000000
\n",
"
17.000000
\n",
"
884.850000
\n",
"
884.850000
\n",
"
0.009549
\n",
"
\n",
"
\n",
"
15
\n",
"
TAXB
\n",
"
3333
\n",
"
VDE
\n",
"
NaN
\n",
"
ST
\n",
"
77.44
\n",
"
0.00000
\n",
"
36.000000
\n",
"
36.000000
\n",
"
2787.840000
\n",
"
2787.840000
\n",
"
0.030086
\n",
"
\n",
"
\n",
"
17
\n",
"
TAXB
\n",
"
3333
\n",
"
GLD
\n",
"
NaN
\n",
"
CS
\n",
"
120.57
\n",
"
0.00000
\n",
"
8.000000
\n",
"
8.000000
\n",
"
964.560000
\n",
"
964.560000
\n",
"
0.010565
\n",
"
\n",
"
\n",
"
18
\n",
"
RIRA
\n",
"
1111
\n",
"
GLD
\n",
"
NaN
\n",
"
CS
\n",
"
120.57
\n",
"
0.00000
\n",
"
2.000000
\n",
"
2.000000
\n",
"
241.140000
\n",
"
241.140000
\n",
"
0.003749
\n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" accounttype accountid ticker shares assetclass close value \\\n",
"0 RIRA 1111 VBTLX 913.483 BD 10.39 9491.08837 \n",
"1 RIRA 2222 VBTLX 772.407 BD 10.39 8025.30873 \n",
"2 RIRA 1111 VTIAX 514.298 ST 25.17 12944.88066 \n",
"3 RIRA 1111 VTSAX 151.121 ST 61.08 9230.47068 \n",
"4 RIRA 2222 VTSAX 151.578 ST 61.08 9258.38424 \n",
"5 TAXB 3333 VTSAX 549.871 ST 61.08 33586.12068 \n",
"6 TAXB 3333 AAPL 3.140 ST 156.15 490.31100 \n",
"7 TAXB 3333 VIGAX NaN ST 68.02 0.00000 \n",
"8 TAXB 3333 VSMAX NaN ST 62.25 0.00000 \n",
"9 RIRA 2222 VSEQX NaN ST 26.77 0.00000 \n",
"10 RIRA 2222 VWIGX NaN ST 24.76 0.00000 \n",
"11 TAXB 3333 VTRIX NaN ST 31.91 0.00000 \n",
"12 RIRA 1111 VTABX NaN BD 21.66 0.00000 \n",
"13 RIRA 1111 VGSLX NaN RE 105.06 0.00000 \n",
"14 RIRA 2222 VNQI NaN RE 52.05 0.00000 \n",
"15 TAXB 3333 VDE NaN ST 77.44 0.00000 \n",
"17 TAXB 3333 GLD NaN CS 120.57 0.00000 \n",
"18 RIRA 1111 GLD NaN CS 120.57 0.00000 \n",
"\n",
" new_shares final_shares_chg new_value new_value_chg \\\n",
"0 170.078225 -743.404775 1767.112759 -7723.975611 \n",
"1 143.811775 -628.595225 1494.204341 -6531.104389 \n",
"2 561.298000 47.000000 14127.870660 1182.990000 \n",
"3 152.539028 1.418028 9317.083821 86.613141 \n",
"4 153.000316 1.422316 9345.259305 86.875065 \n",
"5 555.030656 5.159656 33901.272475 315.151795 \n",
"6 0.000000 -3.140000 0.000000 -490.311000 \n",
"7 17.000000 17.000000 1156.340000 1156.340000 \n",
"8 9.000000 9.000000 560.250000 560.250000 \n",
"9 22.000000 22.000000 588.940000 588.940000 \n",
"10 190.000000 190.000000 4704.400000 4704.400000 \n",
"11 147.000000 147.000000 4690.770000 4690.770000 \n",
"12 64.000000 64.000000 1386.240000 1386.240000 \n",
"13 44.000000 44.000000 4622.640000 4622.640000 \n",
"14 17.000000 17.000000 884.850000 884.850000 \n",
"15 36.000000 36.000000 2787.840000 2787.840000 \n",
"17 8.000000 8.000000 964.560000 964.560000 \n",
"18 2.000000 2.000000 241.140000 241.140000 \n",
"\n",
" final_allocation \n",
"0 0.019071 \n",
"1 0.016125 \n",
"2 0.152468 \n",
"3 0.100550 \n",
"4 0.100854 \n",
"5 0.365862 \n",
"6 0.000000 \n",
"7 0.012479 \n",
"8 0.006046 \n",
"9 0.006356 \n",
"10 0.050770 \n",
"11 0.050623 \n",
"12 0.014960 \n",
"13 0.049887 \n",
"14 0.009549 \n",
"15 0.030086 \n",
"17 0.010565 \n",
"18 0.003749 "
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"#Review our final portfolio with recommended buys/sells in 'final_shares_chg' column\n",
"if dport is not None:\n",
" #Cleanup\n",
" dport.drop(columns=['order'],inplace=True)\n",
" dport = dport[['accounttype','accountid','ticker','shares','assetclass','close','value','new_shares','final_shares_chg','new_value','new_value_chg','final_allocation']]\n",
" display(dport)\n",
"else:\n",
" port = port[['accounttype','accountid','ticker','shares','assetclass','close','value','new_shares','final_shares_chg','new_value','new_value_chg','final_allocation']]\n",
" display(port)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Conclusion\n",
"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."
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.6.5"
}
},
"nbformat": 4,
"nbformat_minor": 2
}