portfolio_rebalance.ipynb

{
 "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": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ticker</th>\n",
       "      <th>lastrebaldate</th>\n",
       "      <th>assetclass</th>\n",
       "      <th>basisdate</th>\n",
       "      <th>costbasis</th>\n",
       "      <th>shares</th>\n",
       "      <th>allocation_target</th>\n",
       "      <th>close</th>\n",
       "      <th>value</th>\n",
       "      <th>allocation</th>\n",
       "      <th>correction</th>\n",
       "      <th>new_money_in</th>\n",
       "      <th>rebal_flag</th>\n",
       "      <th>final_shares_chg</th>\n",
       "      <th>new_shares</th>\n",
       "      <th>new_value</th>\n",
       "      <th>new_value_chg</th>\n",
       "      <th>final_allocation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>2018-11-16</td>\n",
       "      <td>ST</td>\n",
       "      <td>2018-11-16</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>3.140</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>156.15</td>\n",
       "      <td>490.31100</td>\n",
       "      <td>0.005905</td>\n",
       "      <td>-0.005905</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>-3.14</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>-490.311</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>VBTLX</td>\n",
       "      <td>2018-11-16</td>\n",
       "      <td>BD</td>\n",
       "      <td>2018-11-16</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1685.890</td>\n",
       "      <td>0.0350</td>\n",
       "      <td>10.39</td>\n",
       "      <td>17516.39710</td>\n",
       "      <td>0.210973</td>\n",
       "      <td>-0.175973</td>\n",
       "      <td>350.0</td>\n",
       "      <td>1</td>\n",
       "      <td>-1372.00</td>\n",
       "      <td>313.890</td>\n",
       "      <td>3261.31710</td>\n",
       "      <td>-14255.080</td>\n",
       "      <td>0.035196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>VTIAX</td>\n",
       "      <td>2018-11-16</td>\n",
       "      <td>ST</td>\n",
       "      <td>2018-11-16</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>514.298</td>\n",
       "      <td>0.1521</td>\n",
       "      <td>25.17</td>\n",
       "      <td>12944.88066</td>\n",
       "      <td>0.155913</td>\n",
       "      <td>-0.003813</td>\n",
       "      <td>1521.0</td>\n",
       "      <td>1</td>\n",
       "      <td>47.00</td>\n",
       "      <td>561.298</td>\n",
       "      <td>14127.87066</td>\n",
       "      <td>1182.990</td>\n",
       "      <td>0.152468</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>VTSAX</td>\n",
       "      <td>2018-11-16</td>\n",
       "      <td>ST</td>\n",
       "      <td>2018-11-16</td>\n",
       "      <td>11.777895</td>\n",
       "      <td>852.570</td>\n",
       "      <td>0.5652</td>\n",
       "      <td>61.08</td>\n",
       "      <td>52074.97560</td>\n",
       "      <td>0.627209</td>\n",
       "      <td>-0.062009</td>\n",
       "      <td>5652.0</td>\n",
       "      <td>1</td>\n",
       "      <td>8.00</td>\n",
       "      <td>860.570</td>\n",
       "      <td>52563.61560</td>\n",
       "      <td>488.640</td>\n",
       "      <td>0.567266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>VIGAX</td>\n",
       "      <td>NaT</td>\n",
       "      <td>ST</td>\n",
       "      <td>2018-12-27</td>\n",
       "      <td>68.020000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0131</td>\n",
       "      <td>68.02</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.013100</td>\n",
       "      <td>131.0</td>\n",
       "      <td>1</td>\n",
       "      <td>17.00</td>\n",
       "      <td>17.000</td>\n",
       "      <td>1156.34000</td>\n",
       "      <td>1156.340</td>\n",
       "      <td>0.012479</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>VSMAX</td>\n",
       "      <td>NaT</td>\n",
       "      <td>ST</td>\n",
       "      <td>2018-12-27</td>\n",
       "      <td>62.250000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0066</td>\n",
       "      <td>62.25</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.006600</td>\n",
       "      <td>66.0</td>\n",
       "      <td>1</td>\n",
       "      <td>9.00</td>\n",
       "      <td>9.000</td>\n",
       "      <td>560.25000</td>\n",
       "      <td>560.250</td>\n",
       "      <td>0.006046</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>VSEQX</td>\n",
       "      <td>NaT</td>\n",
       "      <td>ST</td>\n",
       "      <td>2018-12-27</td>\n",
       "      <td>26.770000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0066</td>\n",
       "      <td>26.77</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.006600</td>\n",
       "      <td>66.0</td>\n",
       "      <td>1</td>\n",
       "      <td>22.00</td>\n",
       "      <td>22.000</td>\n",
       "      <td>588.94000</td>\n",
       "      <td>588.940</td>\n",
       "      <td>0.006356</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>VWIGX</td>\n",
       "      <td>NaT</td>\n",
       "      <td>ST</td>\n",
       "      <td>2018-12-27</td>\n",
       "      <td>24.760000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0507</td>\n",
       "      <td>24.76</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.050700</td>\n",
       "      <td>507.0</td>\n",
       "      <td>1</td>\n",
       "      <td>190.00</td>\n",
       "      <td>190.000</td>\n",
       "      <td>4704.40000</td>\n",
       "      <td>4704.400</td>\n",
       "      <td>0.050770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>VTRIX</td>\n",
       "      <td>NaT</td>\n",
       "      <td>ST</td>\n",
       "      <td>2018-12-27</td>\n",
       "      <td>31.910000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0507</td>\n",
       "      <td>31.91</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.050700</td>\n",
       "      <td>507.0</td>\n",
       "      <td>1</td>\n",
       "      <td>147.00</td>\n",
       "      <td>147.000</td>\n",
       "      <td>4690.77000</td>\n",
       "      <td>4690.770</td>\n",
       "      <td>0.050623</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>VTABX</td>\n",
       "      <td>NaT</td>\n",
       "      <td>BD</td>\n",
       "      <td>2018-12-27</td>\n",
       "      <td>21.660000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0150</td>\n",
       "      <td>21.66</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.015000</td>\n",
       "      <td>150.0</td>\n",
       "      <td>1</td>\n",
       "      <td>64.00</td>\n",
       "      <td>64.000</td>\n",
       "      <td>1386.24000</td>\n",
       "      <td>1386.240</td>\n",
       "      <td>0.014960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>VGSLX</td>\n",
       "      <td>NaT</td>\n",
       "      <td>RE</td>\n",
       "      <td>2018-12-27</td>\n",
       "      <td>105.060000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0500</td>\n",
       "      <td>105.06</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.050000</td>\n",
       "      <td>500.0</td>\n",
       "      <td>1</td>\n",
       "      <td>44.00</td>\n",
       "      <td>44.000</td>\n",
       "      <td>4622.64000</td>\n",
       "      <td>4622.640</td>\n",
       "      <td>0.049887</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>VNQI</td>\n",
       "      <td>NaT</td>\n",
       "      <td>RE</td>\n",
       "      <td>2018-12-27</td>\n",
       "      <td>52.050000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0100</td>\n",
       "      <td>52.05</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>100.0</td>\n",
       "      <td>1</td>\n",
       "      <td>17.00</td>\n",
       "      <td>17.000</td>\n",
       "      <td>884.85000</td>\n",
       "      <td>884.850</td>\n",
       "      <td>0.009549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>VDE</td>\n",
       "      <td>NaT</td>\n",
       "      <td>ST</td>\n",
       "      <td>2018-12-27</td>\n",
       "      <td>77.440000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0300</td>\n",
       "      <td>77.44</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.030000</td>\n",
       "      <td>300.0</td>\n",
       "      <td>1</td>\n",
       "      <td>36.00</td>\n",
       "      <td>36.000</td>\n",
       "      <td>2787.84000</td>\n",
       "      <td>2787.840</td>\n",
       "      <td>0.030086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>GLD</td>\n",
       "      <td>NaT</td>\n",
       "      <td>CS</td>\n",
       "      <td>2018-12-27</td>\n",
       "      <td>120.570000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0150</td>\n",
       "      <td>120.57</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.015000</td>\n",
       "      <td>150.0</td>\n",
       "      <td>1</td>\n",
       "      <td>11.00</td>\n",
       "      <td>11.000</td>\n",
       "      <td>1326.27000</td>\n",
       "      <td>1326.270</td>\n",
       "      <td>0.014313</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ticker lastrebaldate assetclass  basisdate   costbasis    shares  \\\n",
       "0    AAPL    2018-11-16         ST 2018-11-16    1.000000     3.140   \n",
       "1   VBTLX    2018-11-16         BD 2018-11-16    1.000000  1685.890   \n",
       "2   VTIAX    2018-11-16         ST 2018-11-16    1.000000   514.298   \n",
       "3   VTSAX    2018-11-16         ST 2018-11-16   11.777895   852.570   \n",
       "4   VIGAX           NaT         ST 2018-12-27   68.020000     0.000   \n",
       "5   VSMAX           NaT         ST 2018-12-27   62.250000     0.000   \n",
       "6   VSEQX           NaT         ST 2018-12-27   26.770000     0.000   \n",
       "7   VWIGX           NaT         ST 2018-12-27   24.760000     0.000   \n",
       "8   VTRIX           NaT         ST 2018-12-27   31.910000     0.000   \n",
       "9   VTABX           NaT         BD 2018-12-27   21.660000     0.000   \n",
       "10  VGSLX           NaT         RE 2018-12-27  105.060000     0.000   \n",
       "11   VNQI           NaT         RE 2018-12-27   52.050000     0.000   \n",
       "12    VDE           NaT         ST 2018-12-27   77.440000     0.000   \n",
       "13    GLD           NaT         CS 2018-12-27  120.570000     0.000   \n",
       "\n",
       "    allocation_target   close        value  allocation  correction  \\\n",
       "0              0.0000  156.15    490.31100    0.005905   -0.005905   \n",
       "1              0.0350   10.39  17516.39710    0.210973   -0.175973   \n",
       "2              0.1521   25.17  12944.88066    0.155913   -0.003813   \n",
       "3              0.5652   61.08  52074.97560    0.627209   -0.062009   \n",
       "4              0.0131   68.02      0.00000    0.000000    0.013100   \n",
       "5              0.0066   62.25      0.00000    0.000000    0.006600   \n",
       "6              0.0066   26.77      0.00000    0.000000    0.006600   \n",
       "7              0.0507   24.76      0.00000    0.000000    0.050700   \n",
       "8              0.0507   31.91      0.00000    0.000000    0.050700   \n",
       "9              0.0150   21.66      0.00000    0.000000    0.015000   \n",
       "10             0.0500  105.06      0.00000    0.000000    0.050000   \n",
       "11             0.0100   52.05      0.00000    0.000000    0.010000   \n",
       "12             0.0300   77.44      0.00000    0.000000    0.030000   \n",
       "13             0.0150  120.57      0.00000    0.000000    0.015000   \n",
       "\n",
       "    new_money_in  rebal_flag  final_shares_chg  new_shares    new_value  \\\n",
       "0            0.0           1             -3.14       0.000      0.00000   \n",
       "1          350.0           1          -1372.00     313.890   3261.31710   \n",
       "2         1521.0           1             47.00     561.298  14127.87066   \n",
       "3         5652.0           1              8.00     860.570  52563.61560   \n",
       "4          131.0           1             17.00      17.000   1156.34000   \n",
       "5           66.0           1              9.00       9.000    560.25000   \n",
       "6           66.0           1             22.00      22.000    588.94000   \n",
       "7          507.0           1            190.00     190.000   4704.40000   \n",
       "8          507.0           1            147.00     147.000   4690.77000   \n",
       "9          150.0           1             64.00      64.000   1386.24000   \n",
       "10         500.0           1             44.00      44.000   4622.64000   \n",
       "11         100.0           1             17.00      17.000    884.85000   \n",
       "12         300.0           1             36.00      36.000   2787.84000   \n",
       "13         150.0           1             11.00      11.000   1326.27000   \n",
       "\n",
       "    new_value_chg  final_allocation  \n",
       "0        -490.311          0.000000  \n",
       "1      -14255.080          0.035196  \n",
       "2        1182.990          0.152468  \n",
       "3         488.640          0.567266  \n",
       "4        1156.340          0.012479  \n",
       "5         560.250          0.006046  \n",
       "6         588.940          0.006356  \n",
       "7        4704.400          0.050770  \n",
       "8        4690.770          0.050623  \n",
       "9        1386.240          0.014960  \n",
       "10       4622.640          0.049887  \n",
       "11        884.850          0.009549  \n",
       "12       2787.840          0.030086  \n",
       "13       1326.270          0.014313  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Lets look at all our work to get to our target portfolio\n",
    "exec_port"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x11df9c630>"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAABKYAAAJCCAYAAAD6G89IAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4wLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvqOYd8AAAIABJREFUeJzs3Xu0XlV9L/zvDxJIIhwFQWvUGtSDINm5sSkRK4KgpYoUUTTIWxM8QhHxUiuVWqs51IoCFcUiFBShlpODJy+kaIcHC5KDlItsMBACiCIppvJ6ICiCIVx0vn/sJ3EbcyV7Z8Xk8xljj/2sueac6zd3Rv75jrnmU621AAAAAMCmtk3XBQAAAACwdRJMAQAAANAJwRQAAAAAnRBMAQAAANAJwRQAAAAAnRBMAQAAANAJwRQAAAAAnRBMAQAAANAJwRQAAAAAnRjVdQFd22WXXdqECRO6LgMAAABgi3HzzTc/2FrbdV39tvpgasKECRkYGOi6DAAAAIAtRlX9x/r08yofAAAAAJ0QTAEAAADQCcEUAAAAAJ3Y6s+YAgAAAEbWk08+mSVLlmT58uVdl8IwGzNmTF7wghdk9OjRT2u8YAoAAAAYUUuWLMmOO+6YCRMmpKq6Lodh0lrL0qVLs2TJkuy2225Paw6v8gEAAAAjavny5Xn2s58tlNrCVFWe/exnb9ROOMEUAAAAMOKEUlumjf13FUwBAAAA0Imt/oypRUsXpe+ivhGbf+HMhSM2NwAAAPwumnDyvw7rfIs/9YanV8eECRkYGMguu+ySHXbYIY8++uiw1TRv3rzsvvvuefnLX54k+djHPpb9998/Bx988LA9Y0tgxxQAAADAMJs3b17uuOOOldennHKKUGo1BFMAAADAFu/www/P3nvvnb322ivnnXfeGvu11nLSSSdl4sSJ6evryyWXXLLy3mmnnZa+vr5Mnjw5J598cpLk/PPPzz777JPJkyfnzW9+c5YtW5brrrsul19+eU466aRMmTIl99xzT2bNmpW5c+cmSa666qpMnTo1fX19eec735nHH388yeAOro9//OOZNm1a+vr6ctddd43gX2TzIJgCAAAAtngXXHBBbr755gwMDOSss87K0qVLV9vv0ksvzYIFC3LrrbfmyiuvzEknnZT7778/3/jGNzJv3rzceOONufXWW/OXf/mXSZIjjjgiN910U2699dbsueee+dKXvpT99tsvhx12WE4//fQsWLAgL3nJS1bOv3z58syaNSuXXHJJFi5cmKeeeirnnHPOyvu77LJLbrnllrz73e/OGWecMbJ/lM2AYAoAAADY4p111lmZPHlypk+fnh/96Ef5/ve/v9p+1157bY466qhsu+22ee5zn5tXv/rVuemmm3LllVfmmGOOybhx45IkO++8c5Lk9ttvz6te9ar09fXl4osvzqJFi9Zax/e+973stttu2X333ZMkM2fOzDXXXLPy/hFHHJEk2XvvvbN48eKNXfZmb6s//BwAAADYss2fPz9XXnllrr/++owbNy4HHHBAli9fvtq+rbU1tlfVb7XPmjUr8+bNy+TJk3PhhRdm/vz5a61lTfOvsP322ydJtt122zz11FNr7bsl2CyDqap6bpIzk0xP8tMkTyQ5rff5Q621Q1fpPz/J85I8nmS7JFcm+Whr7WfretZejz+RgXvvG9b6f8PsZyazHx65+QEAAIC1evjhh7PTTjtl3Lhxueuuu3LDDTesse/++++ff/zHf8zMmTPz0EMP5Zprrsnpp5+e7bbbLqecckre/va3Z9y4cXnooYey884755FHHsnznve8PPnkk7n44ovz/Oc/P0my44475pFHHvmt+ffYY48sXrw4P/jBD/LSl740X/nKV/LqV796xNa+udvsgqkajB/nJbmotfb2XtuLkhyWwWBqTY5urQ1U1XZJTk3yL0m23n9ZAAAA2Ewt/tQbNunzDjnkkJx77rmZNGlSXvayl2X69Olr7PumN70p119/fSZPnpyqymmnnZbf+73fyyGHHJIFCxakv78/2223XV7/+tfnk5/8ZP72b/82++67b170ohelr69vZRg1Y8aMHHvssTnrrLNWHnqeJGPGjMmXv/zlHHnkkXnqqaeyzz775Pjjjx/xv8Hmqta1hWxTq6qDknystfZboVJVHZA175j6UGttoHe9bZIfJDm8tXbr2p7XP37bNnDcDsNU/RrYMQUAAMBW7M4778yee+7ZdRmMkNX9+1bVza21/nWN3RwPP98ryS0bM0Fr7ZdJbk2yx7BUBAAAAMCw2xyDqd9QVWdX1a1VddOGDl3LnMdV1UBVDTywbPPaMQYAAACwtdgcg6lFSaatuGitvSfJQUl2Xd8Jeq/y9SW5c3X3W2vntdb6W2v9u45bY34FAAAAwAjaHIOpbyUZU1XvHtI2bn0HV9XoDB5+/qPW2m3DXRwAAAAAw2Oz+1a+1lqrqsOTnFlVf5nkgSS/SPLhXpeDqmrJkCFH9n5fXFWPJ9k+yZVJ/mS9Hjh+ajJ7YFhqBwAAAGD9bXbBVJK01u5PMmMNt8eupu2AkasGAAAAgJGwWQZTAAAAwBZs9jOHeb6Hh3c+NpnN8YwpAAAAgBE3YcKEPPjgg0mSHXbYYVjnnjdvXu64446V1x/72Mdy5ZVXDtv88+fPz3XXXTds863NZz/72SxbtmxE5hZMAQAAAAyzVYOpU045JQcffPCwzf90gqmnnnrqaT1LMAUAAACwEQ4//PDsvffe2WuvvXLeeeetsV9rLSeddFImTpyYvr6+XHLJJSvvnXbaaenr68vkyZNz8sknJ0nOP//87LPPPpk8eXLe/OY3Z9myZbnuuuty+eWX56STTsqUKVNyzz33ZNasWZk7d26S5KqrrsrUqVPT19eXd77znXn88ceTDO7g+vjHP55p06alr68vd91112prXLx4cc4999yceeaZmTJlSr797W/na1/7Wvbdd99MnTo1Bx98cH7yk58kSWbPnp3jjjsur3vd6/KOd7wjy5Yty1vf+tZMmjQpb3vb27LvvvtmYGDwS+G++c1v5hWveEWmTZuWI488Mo8++mjOOuus/PjHP86BBx6YAw88cOP/IVbhjCkAAABgi3fBBRdk5513zmOPPZZ99tknb37zm1fb79JLL82CBQty66235sEHH8w+++yT/fffPwsWLMi8efNy4403Zty4cXnooYeSJEcccUSOPfbYJMlHP/rRfOlLX8p73/veHHbYYTn00EPzlre85TfmX758eWbNmpWrrroqu+++e97xjnfknHPOyQc+8IEkyS677JJbbrklX/jCF3LGGWfki1/84m/VOGHChBx//PHZYYcd8qEPfShJ8tOf/jQ33HBDqipf/OIXc9ppp+Xv//7vkyQ333xzrr322owdOzZnnHFGdtppp9x22225/fbbM2XKlCTJgw8+mE984hO58sor84xnPCOf/vSn85nPfCYf+9jH8pnPfCZXX311dtlll2H4l/hNgikAAABgi3fWWWflsssuS5L86Ec/yve///3V9rv22mtz1FFHZdttt81zn/vcvPrVr85NN92U//N//k+OOeaYjBs3Lkmy8847J0luv/32fPSjH83PfvazPProo/mjP/qjtdbxve99L7vttlt23333JMnMmTNz9tlnrwymjjjiiCTJ3nvvnUsvvXS917dkyZK87W1vy/33358nnngiu+2228p7hx12WMaOHbtyfe9///uTJBMnTsykSZOSJDfccEPuuOOOvPKVr0ySPPHEE3nFK16x3s9/ugRTAAAAwBZt/vz5ufLKK3P99ddn3LhxOeCAA7J8+fLV9m2trbG9qn6rfdasWZk3b14mT56cCy+8MPPnz19rLWuaf4Xtt98+SbLttttu0JlQ733ve/PBD34whx12WObPn5/Zs2evvPeMZzxjnc9vreW1r31t5syZs97PHA6CKQAAAGDTmv3wJn3cww8/nJ122injxo3LXXfdlRtuuGGNfffff//84z/+Y2bOnJmHHnoo11xzTU4//fRst912OeWUU/L2t7995at8O++8cx555JE873nPy5NPPpmLL744z3/+85MkO+64Yx555JHfmn+PPfbI4sWL84Mf/CAvfelL85WvfCWvfvWrN3hNO+64Y37+85//xhpXPPuiiy5a47g//MM/zFe/+tUceOCBueOOO7Jw4cIkyfTp0/Oe97xnZV3Lli3LkiVLsvvuu69cy0i8yufwcwAAAGCLdsghh+Spp57KpEmT8jd/8zeZPn36Gvu+6U1vyqRJkzJ58uS85jWvyWmnnZbf+73fyyGHHJLDDjss/f39mTJlSs4444wkyd/+7d9m3333zWtf+9rsscceK+eZMWNGTj/99EydOjX33HPPyvYxY8bky1/+co488sj09fVlm222yfHHH7/Ba3rjG9+Yyy67bOXh57Nnz86RRx6ZV73qVWsNkE444YQ88MADmTRpUj796U9n0qRJeeYzn5ldd901F154YY466qhMmjQp06dPX3n4+nHHHZc//uM/HpHDz2tdW8i2dP39/W3F6fMAAADA8Lvzzjuz5557dl0GSX75y1/mySefzJgxY3LPPffkoIMOyt13353tttvuac+5un/fqrq5tda/rrFe5QMAAADYSixbtiwHHnhgnnzyybTWcs4552xUKLWxBFMAAAAAm6kvf/nL+dznPvcbba985Stz9tlnP635dtxxx2xOb44JpgAAAAA2U8ccc0yOOeaYrssYMQ4/BwAAAKATgikAAAAAOiGYAgAAAKATzpgCAAAANqm+i/qGdb6FMxcO63xsOnZMAQAAAFu8s846K3vuuWd22mmnfOpTn3ra8+ywww4bNW7x4sWZOHHi037+6lx44YX58Y9/vPL6Xe96V+64445hfcZIsWMKAAAA2OJ94QtfyDe+8Y3stttuXZcy7C688MJMnDgx48ePT5J88Ytf7Lii9WfHFAAAALBFO/744/PDH/4whx12WM4888yceOKJSZJZs2blfe97X/bbb7+8+MUvzty5c5Mkjz76aA466KBMmzYtfX19+Zd/+Zf1es6Gjlu+fHmOOeaY9PX1ZerUqbn66quTJL/85S/zoQ99KH19fZk0aVI+//nPJ0lOOeWU7LPPPpk4cWKOO+64tNYyd+7cDAwM5Oijj86UKVPy2GOP5YADDsjAwECSZM6cOenr68vEiRPz4Q9/eOWzd9hhh/z1X/91Jk+enOnTp+cnP/nJhv1Rh4lgCgAAANiinXvuuRk/fnyuvvrq7LTTTr9x7/7778+1116br3/96zn55JOTJGPGjMlll12WW265JVdffXX+4i/+Iq21dT5nQ8edffbZSZKFCxdmzpw5mTlzZpYvX57zzjsv9957b7773e/mtttuy9FHH50kOfHEE3PTTTfl9ttvz2OPPZavf/3rectb3pL+/v5cfPHFWbBgQcaOHbty/h//+Mf58Ic/nG9961tZsGBBbrrppsybNy9J8otf/CLTp0/Prbfemv333z/nn3/+hv1Rh4lgCgAAANhqHX744dlmm23y8pe/fOWuodZaPvKRj2TSpEk5+OCD85//+Z/rtaNoQ8dde+21+dM//dMkyR577JEXvehFufvuu3PllVfm+OOPz6hRgycw7bzzzkmSq6++Ovvuu2/6+vryrW99K4sWLVprPTfddFMOOOCA7Lrrrhk1alSOPvroXHPNNUmS7bbbLoceemiSZO+9987ixYvXub6R4IwpAAAAYKu1/fbbr/y8YnfTxRdfnAceeCA333xzRo8enQkTJmT58uXrnGtDx61pN1VrLVX1G23Lly/PCSeckIGBgbzwhS/M7Nmz11nT2nZrjR49euUztt122zz11FNrnWukCKYAAACATWrhzIVdl7BWDz/8cJ7znOdk9OjRufrqq/Mf//EfIzJu//33z8UXX5zXvOY1ufvuu3PfffflZS97WV73utfl3HPPzQEHHJBRo0bloYceyjbbDL70tssuu+TRRx/N3Llz85a3vCVJsuOOO+aRRx75rfn33XffvP/978+DDz6YnXbaKXPmzMl73/veDfxrjKytPphatHRR+i7q67qMYbG5/8cGAACA3wVHH3103vjGN6a/vz9TpkzJHnvsMSLjTjjhhBx//PHp6+vLqFGjcuGFF2b77bfPu971rtx9992ZNGlSRo8enWOPPTYnnnhijj322PT19WXChAnZZ599Vs4za9asHH/88Rk7dmyuv/76le3Pe97zcuqpp+bAAw9May2vf/3r8yd/8idP748yQmp9Du/ako3dbWx76eyXdl3GsBBMAQAAsDm68847s+eee3ZdBiNkdf++VXVza61/XWMdfg4AAABAJ7b6V/kAAAAANsTChQtXfpveCttvv31uvPHGjir63SWYAgAAAEbc6r5p7ndVX19fFixY0HUZm4WNPSLKq3wAAADAiBozZkyWLl260SEGm5fWWpYuXZoxY8Y87Tk2ux1TVTU/yamttSuGtH0gyeuSHJjkriRjkjyS5OzW2kW9PrOSnJ7kP4dM9/bW2h1re95ejz+RgXvvG84lAAAAAEO84AUvyJIlS/LAAw90XQrDbMyYMXnBC17wtMdvdsFUkjlJZiS5YkjbjCQnJfn91trUJKmqFye5tKq2aa19udfvktbaiZu0WgAAAGCtRo8end12263rMtgMbY6v8s1NcmhVbZ8kVTUhyfgkS4Z2aq39MMkHk7xvE9cHAAAAwDDY7IKp1trSJN9JckivaUaSS5Ks7kXUW5LsMeT6bVW1YMjP2NU9o6qOq6qBqhp4YJn3WwEAAAC6sNkFUz0rXudL7/ecNfRb9Tj/S1prU4b8PLa6Qa2181pr/a21/l3HbRnfCAAAAADwu2ZzDabmJTmoqqYlGdtau2UN/aYmuXPTlQUAAADAcNksg6nW2qNJ5ie5IGvYLdU7e+qMJJ/fVHUBAAAAMHw2x2/lW2FOkkvz61f6kuQlVfXdJGOSPJLk80O+kS8ZPGPqD4dcn9Bau26tTxk/NZk9MEwlAwAAALC+NttgqrV2WYacIdVaW5xktYeZ9+5fmOTCka4LAAAAgOGxWb7KBwAAAMCWTzAFAAAAQCcEUwAAAAB0QjAFAAAAQCcEUwAAAAB0QjAFAAAAQCcEUwAAAAB0QjAFAAAAQCcEUwAAAAB0QjAFAAAAQCcEUwAAAAB0QjAFAAAAQCcEUwAAAAB0QjAFAAAAQCcEUwAAAAB0QjAFAAAAQCcEUwAAAAB0QjAFAAAAQCcEUwAAAAB0QjAFAAAAQCcEUwAAAAB0QjAFAAAAQCcEUwAAAAB0QjAFAAAAQCdGdV1A1xYtXZS+i/qe9viFMxcOYzUAAAAAWw87pgAAAADohGAKAAAAgE4IpgAAAADohGAKAAAAgE4IpgAAAADoROffyldV85Oc2lq7YkjbB5K8LskfJ3lfa+3zvfZ/SDLQWruwqirJXyeZmaQluT/Je1trt/X6Lk7S31p7cG3P3+vxJzJw731PfwGzn5nMfvjpjwcAAADYSm0OO6bmJJmxStuMJKcm+b9J3l9V261m3HuS7Jdkcmtt9yR/l+RrVfWMkSwWAAAAgOGxOQRTc5McWlXbJ0lVTUgyPsmSJA8kuSqDu6JW9eEM7pBaliSttW8muSbJ0SNfMgAAAAAbq/NgqrW2NMl3khzSa5qR5JIMvp6XJJ9K8hdVte2KMVX1X5I8o7V2zyrTDSR5+bqeWVXHVdVAVQ08sKytqzsAAAAAI6DzYKpn6Ot8M3rXSZLW2r0ZDK7evh7z1Po8rLV2Xmutv7XWv+u49RoCAAAAwDDbXIKpeUkOqqppSca21m5Z5f4nM/jq3jZJ0lr7eZJfVNWLV+k3LYO7pgAAAADYzG0WwVRr7dEk85NckCG7pYbcvyvJHUkOHdJ8epKzqmpsklTVwUn2yuCZVQAAAABs5kZ1XcAQc5Jcmt/+hr4V/i7Jd4dcfz7Js5LcVlWjk2yXZGJrbfkGPXX81GS2TVYAAAAAm1q19rt/+HdV7ZDksiQ3tdY+siFj+/v728CAYAoAAABguFTVza21/nX125x2TD1tvVcBX9t1HQAAAACsv83ijCkAAAAAtj6CKQAAAAA6IZgCAAAAoBOCKQAAAAA6IZgCAAAAoBOCKQAAAAA6IZgCAAAAoBOCKQAAAAA6IZgCAAAAoBOCKQAAAAA6IZgCAAAAoBOCKQAAAAA6IZgCAAAAoBOCKQAAAAA6IZgCAAAAoBOCKQAAAAA6IZgCAAAAoBOCKQAAAAA6IZgCAAAAoBOCKQAAAAA6IZgCAAAAoBOCKQAAAAA6MarrArq2aOmi9F3U13UZW4yFMxd2XQIAAADwO8KOKQAAAAA6IZgCAAAAoBOCKQAAAAA6IZgCAAAAoBOCKQAAAAA6IZgCAAAAoBOjRvoBVTU/yamttSuGtH0gye5Jzuz97JnkZ0l+nuTjrbVrquq5Sb6U5IVJRidZ3Fp7fVVNSPL11trEVZ5zeZL/1Vr7Su/6/CR3t9ZOX1t9ez3+RAbuvW84lgoAAADABtgUO6bmJJmxStuMXvu/JjmvtfaS1treSd6b5MW9Pqck+bfW2uTW2suTnLyO57wvySlV9ayq2i/Jvkk+O1yLAAAAAGB4jfiOqSRzk3yiqrZvrT3e2/E0PoM7pq5vrV2+omNr7fYkt/cun5fkm0Pu3ba2h7TWFlfVeUlOS/IHSU5srT05nAsBAAAAYPiM+I6p1trSJN9JckivaUaSS5LsleSWtQw9O8mXqurqqvrrqhq/Ho87o/ecRa21a9bUqaqOq6qBqhp4YFlbr3UAAAAAMLw21eHnQ1/nW/Ea32+oqsuq6vaqujRJemdSvTjJ+Un2SPLdqtp1Hc+ZlKSS7FFVa1xba+281lp/a61/13G14asBAAAAYKNtqmBqXpKDqmpakrGttVuSLEoybUWH1tqbksxKsvOQtodaa/+jtfanSW5Ksv+aHtALor6Q5E+TfD/Ju0dgHQAAAAAMk01xxlRaa4/2vp3vgvx6t9T/SPJXVXXYkHOmxq0YU1WvSXJDa21ZVe2Y5CVJ1vb1eX+W5PuttflVdXeS66vqq621B9Za3PipyeyBp7UuAAAAAJ6+TRJM9cxJcml6r/S11h6rqkOTfKaqPpvkJ0keSfKJXv+9k/xDVT2VwZ1dX2yt3dQ7PP1lVbVkyNx/keTDSab35v5xVX0ugwehHzPSCwMAAABgw1VrW/fh3/39/W1gwI4pAAAAgOFSVTe31vrX1W9TnTEFAAAAAL9BMAUAAABAJwRTAAAAAHRCMAUAAABAJwRTAAAAAHRCMAUAAABAJwRTAAAAAHRCMAUAAABAJwRTAAAAAHRCMAUAAABAJwRTAAAAAHRCMAUAAABAJwRTAAAAAHRCMAUAAABAJwRTAAAAAHRCMAUAAABAJwRTAAAAAHRCMAUAAABAJwRTAAAAAHRCMAUAAABAJwRTAAAAAHRCMAUAAABAJwRTAAAAAHRiVNcFdG3R0kXpu6hvRJ+xcObCEZ0fAAAA4HeRHVMAAAAAdEIwBQAAAEAnBFMAAAAAdEIwBQAAAEAnBFMAAAAAdGJEg6mqml9Vf7RK299U1R1VtaCqHqqqe3ufrxzS58+ranlVPXNI2wFV9XCv721VdWVVPad374NV9aUhfY+uqn8dybUBAAAAsHFGjfD8c5LMSHLFkLY3JPmz1tq3q+rCJF9vrc1dZdxRSW5K8qYkFw5p/3Zr7dAkqapTk7wnyceTnJVkoKpemWRRkk8kOWh9Ctzr8ScycO99G7gsAAAAADbWSL/KNzfJoVW1fZJU1YQk45Ncu6YBVfWSJDsk+WgGA6rV9akkOyb5aZK01p5KckKSs5OcluSC1toPh2sRAAAAAAy/Ed0x1VpbWlXfSXJIkn/J4O6pS1prbS3DjsrgTqtvJ3lZVT2ntfZ/e/deVVULkjw7yS+SfGTIs66rqjuTHJxkz+FfDQAAAADDaVMcfr7idb70fs9ZR/8ZSf5na+1XSS5NcuSQe99urU1prb0wyZczuDsqSVJVOyTpTzI6ya5re0BVHVdVA1U18MCytWVkAAAAAIyUTRFMzUtyUFVNSzK2tXbLmjpW1aQk/zXJv1XV4gyGVKt9nS/J5Un2H3L935P8c5K/S3Lm2gpqrZ3XWutvrfXvOq7WeyEAAAAADJ8RD6Zaa48mmZ/kgqx7t9RRSWa31ib0fsYneX5VvWg1ff8wyT1JUlV9GTxU/dNJzkvyoqp67TAtAQAAAIARMNLfyrfCnAy+ljdjHf1mJPnjVdou67XfmF+fMVVJHk7yrt5B6Ock+fPW2vIkqaoTkvxTVU1prT2x1ieOn5rMHtjA5QAAAACwsWrt55Bv+fr7+9vAgGAKAAAAYLhU1c2ttf519dsUZ0wBAAAAwG8RTAEAAADQCcEUAAAAAJ0QTAEAAADQCcEUAAAAAJ0QTAEAAADQCcEUAAAAAJ0QTAEAAADQCcEUAAAAAJ0QTAEAAADQCcEUAAAAAJ0QTAEAAADQCcEUAAAAAJ0QTAEAAADQCcEUAAAAAJ0QTAEAAADQCcEUAAAAAJ0QTAEAAADQCcEUAAAAAJ0QTAEAAADQCcEUAAAAAJ0QTAEAAADQCcEUAAAAAJ0QTAEAAADQiVFdF9C1RUsXpe+ivq7L2KIsnLmw6xIAAACA3wF2TAEAAADQCcEUAAAAAJ0QTAEAAADQCcEUAAAAAJ0QTAEAAADQiU0aTFXV/Kr6o1Xa/qaq7qiqBVX1UFXd2/t8ZVVNqKrHetd3VNU/VdXo3rgDqurrvc8frKovDZnz6Kr61025NgAAAAA2zKhN/Lw5SWYkuWJI2xuS/Flr7dtVdWGSr7fW5iZJVU1Ick9rbUpVbZvk35K8NcnFq8x7VpKBqnplkkVJPpHkoPUpaK/Hn8jAvfc97QWxGrOfmcx+uOsqAAAAgM3cpg6m5ib5RFVt31p7vBc8jU9y7boGttZ+WVXfSfL81dx7qqpOSPKFJN9JckFr7YfDWjkAAAAAw2qTvsrXWluaweDokF7TjCSXtNbausZW1Zgk+yb532uY+7okdyY5OMlpw1IwAAAAACOmi8PPV7zOl97vOevo/5KqWpBkaZL7Wmu3ra5TVe2QpD9I8kXlAAAgAElEQVTJ6CS7rm3CqjquqgaqauCBZevMxAAAAAAYAV0EU/OSHFRV05KMba3dso7+97TWpiR5aZLpVXXYGvr99yT/nOTvkpy5tglba+e11vpba/27jqsNLB8AAACA4bDJg6nW2qNJ5ie5IOveLTV03P1JTk7yV6veq6q+DB6i/ukk5yV5UVW9djjqBQAAAGBkbOrDz1eYk+TS/PqVvvU1L8nsqnrVioaqqiTnJPnz1tryXtsJSf6pqqa01p5Y64zjpyazBzawDAAAAAA2Vq3HueNbtP7+/jYwIJgCAAAAGC5VdXNrrX9d/bo4YwoAAAAABFMAAAAAdEMwBQAAAEAnBFMAAAAAdEIwBQAAAEAnBFMAAAAAdEIwBQAAAEAnBFMAAAAAdEIwBQAAAEAnBFMAAAAAdEIwBQAAAEAnBFMAAAAAdEIwBQAAAEAnBFMAAAAAdEIwBQAAAEAnBFMAAAAAdEIwBQAAAEAnBFMAAAAAdEIwBQAAAEAnBFMAAAAAdEIwBQAAAEAnBFMAAAAAdEIwBQAAAEAnBFMAAAAAdGJU1wV0bdHSRem7qK/rMrY4C2cu7LoEAAAAYDNnxxQAAAAAnRBMAQAAANAJwRQAAAAAnRBMAQAAANAJwRQAAAAAnRjRb+WrqvlJTm2tXTGk7QNJ/iTJ51pr83pt30vyldbaJ3rX/2+Si5M8lORDrbVDe+2HJDklyX9JsjzJ95Kc1Fq7r6ouT/K/Wmtf6fU9P8ndrbXT11bjXo8/kYF77xvGVQMAAACwPkZ6x9ScJDNWaZuR5FtJ9kuSqnp2kkeTvGJIn1ckuW7ooKqamOTzSWa21vZorU3JYHg1odflfUlOqapnVdV+SfZN8tlhXQ0AAAAAw2akg6m5SQ6tqu2TpKomJBmfIcFU7/fXk+xag3ZL8lhr7f9bZa4PJ/lka+3OFQ2ttctba9f0Pi9Ocl6S05J8IcmJrbUnR2hdAAAAAGykEQ2mWmtLk3wnySG9phlJLkkykGRiVW2XwWDq+gy+lrdn7/rfVzPdXkluWccjz+g9a9GKwAoAAACAzdOmOPx86Ot8M5LMaa09nmRRkmlJpie5MYPh1H69n+tWM89KVfXsqlpQVXdX1YeG3JqUpJLsUVVrXFtVHVdVA1U18MCy9nTXBQAAAMBG2BTB1LwkB1XVtCRjW2srdj1dl2T/JDu21n6a5Ib8Opha3Y6pFUFWWmtLe2dMnZdkhyTpBVFfSPKnSb6f5N1rKqi1dl5rrb+11r/ruBqGJQIAAACwoUb0W/mSpLX2aO/b+S7I4O6pFf49yd8nmd+7vi2Du6eem8EQalWnJbmsqm4Ycs7UuCH3/yzJ91tr86vq7iTXV9VXW2sPDNtiAAAAABg2Ix5M9cxJcml+8xv6rkvy4iSnJklr7amq+r9JftRa+9WqE7TWFlbV+5P8U1XtmGRpkvuSfLyqnpPBw9Gn9/r+uKo+l8Ew65i1VjZ+ajJ7YCOXBwAAAMCGqta27jOW+vv728CAYAoAAABguFTVza21/nX12xRnTAEAAADAbxFMAQAAANCJtQZTVbVNVe23qYoBAAAAYOux1mCqdwj532+iWgAAAADYiqzPq3zfrKo3V1WNeDUAAAAAbDVGrUefDyZ5RpJfVtVjSSpJa639lxGtDAAAAIAt2jqDqdbajpuiEAAAAAC2Lut8la8G/T9V9Te96xdW1R+MfGkAAAAAbMnW54ypLyR5RZK3964fTXL2iFUEAAAAwFZhfc6Y2re1Nq2qvpskrbWfVtV2I1wXAAAAAFu49dkx9WRVbZukJUlV7ZrkVyNaFQAAAABbvPUJps5KclmS51TV3yW5NsmpI1oVAAAAAFu89flWvour6uYkByWpJIe31u4c8coAAAAA2KKtM5iqqv/WWvtSkruGtH2qtXbyiFYGAAAAwBZtfQ4/f0tVLW+tXZwkVfWFJNuPbFkAAAAAbOnWJ5g6IsnlVfWrJH+c5KHW2gkjWxYAAAAAW7o1BlNVtfOQy3clmZfk35OcUlU7t9YeGuniAAAAANhyrW3H1M1JWgYPPF/x+w29n5bkxSNeHQAAAABbrDUGU6213TZlIQAAAABsXbZZV4eqek9VPWvI9U5V5YwpAAAAADbKOoOpJMe21n624qK19tMkx45cSQAAAABsDdYnmNqmqmrFRVVtm2S7kSsJAAAAgK3B2g4/X+GKJF+tqnMzeOj58Un+94hWBQAAAMAWb32CqQ8n+bMk787gN/N9M8kXR7IoAAAAALZ81VrruoZOjd1tbHvp7Jeus9/CmQs3QTUAAAAAv/uq6ubWWv+6+q1xx1RVfbW19taqWpjBV/h+Q2tt0kbWCAAAAMBWbG2v8r2/9/vOJCcNaa8kp41YRQAAAABsFdYYTLXW7u99fGlr7T+G3quqPUa0KgAAAAC2eGt7le/dSU5I8uKqum3IrR2T/PtIFwYAAADAlm1tr/L9jyTfSHJqkpOHtD/SWntoRKsCAAAAYIs3ot/KV1Xzk5zaWrtiSNsHkuye5Kkkr8ngwerLk7y1tXZvVS1O8kiSX/aGXNNae19VVZK/TjKzN+b+JO9trd1WVTsmWZDkkNba96tqdJJbkryrtXbj2mrsH79tGzhuh/Vb0OyH168fAAAAwFZso7+Vb5jMSTIjyRVD2mYk+dckk5NMaq39qqpekOQXQ/oc2Fp7cJW53pNkvySTW2vLqup1Sb5WVS9vrT1SVX+V5Owkr0vyoSTXrSuUAgAAAKA724zw/HOTHFpV2ydJVU1IMj7JsiT3t9Z+lSSttSWttZ+uY64PZ3CH1LLemG8muSbJ0b3rryb5VVX9ZZLjk/zVsK8GAAAAgGEzojumWmtLq+o7SQ5J8i8Z3C11Se/n2qp6VZKrkvxza+27Q4ZeXVUrXuW7KMmXkjyjtXbPKo8YSPLyIdcfSHJnkuPWdg5WVR2X5Lgk+f1n1tNdHgAAAAAbYaR3TCW/fp0vvd9zWmtLkrwsg7uafpXkqqo6aMiYA1trU3o/Z65l7lVTpUMyePbUxLUV1Fo7r7XW31rr33WcYAoAAACgC5simJqX5KCqmpZkbGvtliRprT3eWvtGa+2kJJ9McviaJmit/TzJL6rqxavcmpbBXVOpqvFJ3pfkD5K8vqomDf9SAAAAABguIx5MtdYeTTI/yQUZ3D2VqprWC5JSVdskmZTkP9Yx1elJzqqqsb1xByfZK4PnWCXJmUk+2duN9cEkZ/e+yQ8AAACAzdBIfyvfCnOSXJpfv9L3nCTnrzgUPcl3kvzDkP5Dz5i6rbX2jiSfT/KsJLdV1egk2yWZ2FpbXlWvTfL7GTyLKq21r1XVsUnekcEzqtZs/NRk9sDGrg8AAACADVStta5r2GBVtUOSy5Lc1Fr7yMbM1d/f3wYGBFMAAAAAw6Wqbm6t9a+r36baMTWseq8HvrbrOgAAAAB4+jbF4ecAAAAA8FsEUwAAAAB0QjAFAAAAQCcEUwAAAAB0QjAFAAAAQCcEUwAAAAB0QjAFAAAAQCcEUwAAAAB0QjAFAAAAQCcEUwAAAAB0QjAFAAAAQCcEUwAAAAB0QjAFAAAAQCcEUwAAAAB0QjAFAAAAQCcEUwAAAAB0QjAFAAAAQCcEUwAAAAB0QjAFAAAAQCcEUwAAAAB0QjAFAAAAQCcEUwAAAAB0QjAFAAAAQCdGdV1A1xYtXZS+i/rW2W/hzIWboBoAAACArYcdUwAAAAB0QjAFAAAAQCcEUwAAAAB0QjAFAAAAQCcEUwAAAAB0YkS+la+q5ic5tbV2xZC2DyTZPclTSV6TpCVZnuStrbV7q2pxkh+11l41ZMyCJKNaaxOHtH0uyVuSvLC19qte2weT7NVa+2+966OTvL219oZ11brX409k4N771r2o2c9MZj+87n4AAAAArJeR2jE1J8mMVdpmJLk/yfgkk1prfUnelORnQ/rsWFUvTJKq2nPVSatqm96YHyXZf8its5LsXVWvrKpnJflEkvcO01oAAAAAGAEjFUzNTXJoVW2fJFU1IYOB1LIk96/Y6dRaW9Ja++mQcV9N8rbe56MyGHANdWCS25Oc07uf3jxPJTkhydlJTktyQWvth8O7JAAAAACG04gEU621pUm+k+SQXtOMJJf0ft5YVQuq6u+rauoqQ+cmOaL3+Y1JvrbK/RVh1WUZDL5GD3nmdUnuTHJwBsOpNaqq46pqoKoGHljWNnh9AAAAAGy8kTz8fOjrfDOSzGmtLUnysiR/leRXSa6qqoOGjHkoyU+rakYGQ6ZlK25U1XZJXp9kXmvt50luTPK6Ifd3SNKfZHSSXddWWGvtvNZaf2utf9dxtXGrBAAAAOBpGZHDz3vmJflMVU1LMra1dkuStNYeT/KNJN+oqp8kOTzJVUPGXZLBV/JmrTLfIUmemWRhVSXJuAwGV//au//fk/xzkp8kOTPJkcO/JAAAAACGy4gFU621R3vfzndBemdF9UKq/6+19uPeQeaTkty2ytDLkjwvyRUZPJdqhaOSvKu1tmKuZyS5t6rGJXlJkjckmZLkiSTvrKrXttb+bZ2Fjp+azB542usEAAAA4OkZyVf5ksFAanKS/9m7fk6Sr1XV7RkMpJ5K8g9DB7TWHmmtfbq19sSKtl749Ef59e6otNZ+keTaDJ5FdU6SP2+tLe8drH5Cks/1Xv8DAAAAYDNUrW3dh3/39/e3gQE7pgAAAACGS1Xd3FrrX1e/kd4xBQAAAACrJZgCAAAAoBOCKQAAAAA6IZgCAAAAoBOCKQAAAAA6IZgCAAAAoBOCKQAAAAA6IZgCAAAAoBOCKQAAAAA6IZgCAAAAoBOCKQAAAAA6IZgCAAAAoBOCKQAAAAA6IZgCAAAAoBOCKQAAAAA6IZgCAAAAoBOCKQAAAAA6IZgCAAAAoBOCKQAAAAA6IZgCAAAAoBOCKQAAAAA6IZgCAAAAoBOCKQAAAAA6MarrArq2aOmi9F3UN6xzLpy5cFjnAwAAANgS2TEFAAAAQCcEUwAAAAB0QjAFAAAAQCcEUwAAAAB0QjAFAAAAQCcEUwAAAAB0YtSmelBVzU9yamvtiiFtH0jyuiS/31qb2Gv7gySnJXl+kkeS3J/k5NbawiHjbk1yR2vtqCFtlyf5X621r/Suz09yd2vt9LXVtdfjT2Tg3vuGZ5ErzH5mMvvh4Z0TAAAAYAuzKXdMzUkyY5W2GUlOXXFRVc9N8tUkH2mt/dfW2rTe/ZcM6bNnBuvev6qeMWSu9yU5paqeVVX7Jdk3yWdHZCUAAAAAbLRNtmMqydwkn6iq7Vtrj1fVhCTjkywZ0ufEJBe11q5b0dBau3aVed6e5CtJ9kxy2P/f3v0Ha1qW9wH/XrJKwB/EKM4IUjYSTEZYArhJmDG1IGqoKJGKdTc/CqkWI6WO2jiSRltMMyPRJBKnxHRNmZJmumAQCJWxxNhslSYBj4S6WS0qsiLGJisyGKWAhKt/nBc5Hvbsvuzuee+znM9nZmff537v53mvZ+aad8/57v08T+YDr3T39qralPnVVj+e5Pzu/s4ynQsAAAAAe2lmK6a6+64kNyU5bTK0IckVSXrBtGOS3LybQ712st/mJBsXvfcbk+Nv6+5PLHWAqjq3quaqam7Hvb3UNAAAAACW0axvfr7wcr4Nk+0lVdWNVfW5qvrtyfaPJdnR3V9O8vEkJ1bV0xfsclySSvIjVbXkuXX3pu5e393rDz249uJ0AAAAANhTsw6mrklyalWdmOSg7l68OmpbkhMf3ujun0jyziSHTIY2Zj502p7ktiRPS/LqJJkEUb+T5OeTfCHJG5fvNAAAAADYW7O8x1S6+1uTp/Ndmp2vlrokyY1Vdf2C+0wdnHw3eHpNkuO6+6uTsVOSvCPJ7yV5Q5IvdPeWqvp8kj+vqg91945dFnXYCcmFc3t/cgAAAAA8JjMNpiY2J7kqj35CX7r7/1bVa5P8elUdnuRvk3w9ya8meVGSrz4cSk18Isnzq+rIJG9PctLkOH89ufzvPUl+YTlPBgAAAIA9U92r++bf69ev77k5K6YAAAAA9pWq+nR3r9/dvFnfYwoAAAAAkgimAAAAABhEMAUAAADAEIIpAAAAAIYQTAEAAAAwhGAKAAAAgCEEUwAAAAAMIZgCAAAAYAjBFAAAAABDCKYAAAAAGEIwBQAAAMAQgikAAAAAhhBMAQAAADCEYAoAAACAIQRTAAAAAAwhmAIAAABgCMEUAAAAAEMIpgAAAAAYQjAFAAAAwBCCKQAAAACGEEwBAAAAMIRgCgAAAIAhBFMAAAAADLFmdAGjbbtrW9Zdtm50GQAAALDstp69dXQJ8D2smAIAAABgCMEUAAAAAEMIpgAAAAAYQjAFAAAAwBCCKQAAAACGWPZgqqq2VNVPLRp7Z1V9tqpuqapvVNXtk9d/UlVPqKr3V9VfVdXWqvpUVf3ggn1PqKpeeMyqOmJyjB+YbD99sn3kcp8fAAAAAHtmzQw+Y3OSDUmuXzB2epI3dPcnq+o/J/lId1+ZJFW1MclhSY7r7oeq6jlJvr1g341Jbpj8fX2SdPdXquoDSS5Kcu7k703d/eXdFXfM/Q9k7vY79vIUAQAAYAW68J7RFcAuzeJSviuTvKKqDkySqlqb+eDphiXmPzvJ17r7oSTp7ju7++7JvpXkrCTnJHlZVX3fgv3el+Skqnpzkp9M8pv7/EwAAAAA2GeWPZjq7ruS3JTktMnQhiRXdHcvscuHkrxycmnfb1bVCQvee2GS27v7tiRbkrx8wed8J8nbMh9Qvbm7H9i3ZwIAAADAvjSrm58/fDlfJn9vXmpid9+Z5IeT/HKSh5J8vKpOnby9Mcnlk9eXT7YX+sdJvpbk2F0VU1XnVtVcVc3tuHepfAwAAACA5TSLe0wlyTVJfquqTkxyUHffvKvJ3X1/ko8m+WhV/U2SV1XVliSvTnJGVf1KkkryjKp6anf/XVUdn+SlSU5KckNVXd7dX1vi+JuSbEqS9YcdIJkCAAAAGGAmK6a6+1uZv/Tu0uxitVSSVNWJVXXY5PUTkhyX5MtJXpLkf3f3Ed29truPTPLhzIdWleQDmb+E744k703yG8t1PgAAAADsvVmtmErmA6mr8sglfUt5VpIPPnyz9Mzfn+o/JPndJFcvmvvhJG9MclCSO7r7Y5Px30lyTlX9o+7+n7v6sK393Ky97+LpzwIAAAD2FxdcN7oCprD9otNHlzBMLX0P8tXhwGcf3c8+WzAFAAAAjPF4DKaq6tPdvX5382Z183MAAAAA+B6CKQAAAACGEEwBAAAAMIRgCgAAAIAhBFMAAAAADLFmdAGjrTv8kMw9Du9+DwAAALDSWTEFAAAAwBCCKQAAAACGEEwBAAAAMIRgCgAAAIAhBFMAAAAADCGYAgAAAGAIwRQAAAAAQwimAAAAABhCMAUAAADAEIIpAAAAAIYQTAEAAAAwhGAKAAAAgCEEUwAAAAAMIZgCAAAAYAjBFAAAAABDCKYAAAAAGEIwBQAAAMAQgikAAAAAhlgzuoDRtt21LesuWze6DGCgrWdvHV0CAADAqmTFFAAAAABDCKYAAAAAGEIwBQAAAMAQgikAAAAAhhBMAQAAADDETJ/KV1Vbkry7u69fMPbOJBuTPJDkHyS5Z/Ln60len+Qj3X3sgvm/neSsJEd090OTsbcmOaa7XzfZ/tkkP9Pdp++upmPufyBzt9+xb04Q2P9ceM/oCgAAAFatWa+Y2pxkw6Kx05O8obuPT3Jtkrd19/Hd/ZLFO1fVE5KcmeQrSV604K33J3lBVb2wqr4/ya8l+VfLcQIAAAAA7BuzDqauTPKKqjowSapqbZLDktww5f6nJPmrJB/I/CqrJEl3P5jkvCSXJHlPkku7+0v7rGoAAAAA9rmZBlPdfVeSm5KcNhnakOSK7u4pD7Ex86uurs58wPXEBcf+sySfS/KSzIdTAAAAAKxgI25+vvByvg2T7d2qqicleXmSa7r7m0luTPKyBe8/Jcn6JE9McuhujnVuVc1V1dyOe6fNxAAAAADYl2Z68/OJa5L8VlWdmOSg7r55yv1OS3JIkq1VlSQHJ7k3yXWT99+V5A+S/E2S9yV5zVIH6u5NSTYlyfrDDpBMAQAAAAww82Cqu781eTrfpZlytdTExiSv7+7NSVJVT05ye1UdnOSozN9E/fjMP93vn1fVS7v7Y/u0eAAAAAD2mRErppL5QOqqPPoJfTs1CZ9+KskbHh7r7m9X1Q1JXpn5J/C9pbvvm8w/L8nvV9Xx3f3Aro69tZ+btfddvGdnAez/Lrhuybe2X3T6DAsBAABYfYYEU919dZLayfg5i7a3Jzl2svkDO5n/TyYvr1g0Ppfk+fugVAAAAACWyYibnwMAAACAYAoAAACAMQRTAAAAAAwhmAIAAABgiFFP5Vsx1h1+SOY8eQsAAABg5qyYAgAAAGAIwRQAAAAAQwimAAAAABhCMAUAAADAEIIpAAAAAIYQTAEAAAAwhGAKAAAAgCEEUwAAAAAMIZgCAAAAYAjBFAAAAABDCKYAAAAAGEIwBQAAAMAQgikAAAAAhhBMAQAAADCEYAoAAACAIQRTAAAAAAwhmAIAAABgCMEUAAAAAEMIpgAAAAAYYs3oAkbbdte2rLts3egyZmLr2VtHlwAAAADwXVZMAQAAADCEYAoAAACAIQRTAAAAAAwhmAIAAABgCMEUAAAAAEPM5Kl8VbUlybu7+/oFY29O8rIkpyS5NUkl+XaSX0iyNsmvT6b+UJKvJvl/ST6T5NIkv9Tdr1hwrAOS3JTkLd39icnYHyf5YHf/4a5qO+b+BzJ3+x17f5IAAAAAPCazWjG1OcmGRWMbkrw7yW3dfXx3/2iSy5L8m+6+fjJ2fJK5JD872f5nOzt4d/99kvOSXFJVT6yqjfPDuw6lAAAAABhnJiumklyZ5Neq6sDuvr+q1iY5LMmdi+Y9Lcnde/IB3X1jVf1ZkguT/EySl+5xtQAAAAAsu5kEU919V1XdlOS0JH+U+dVSVyTpJEdV1S1Jnprk4CQ/sRcf9ctJvpLk4u7+4t5VDQAAAMBymuXNzxdezrdhsp08cinfUUnenGTTXnzGi5Lck+TYXU2qqnOraq6q5nbc23vxcQAAAADsqVkGU9ckObWqTkxyUHffvJM512Y+XHrMqurJSd6T5MVJDq2qly81t7s3dff67l5/6MG1Jx8HAAAAwF6aWTDV3d9KsiXzT9XbvMS0n0xy2x5+xL9N8qHu/j+ZvxH6+6rq+/bwWAAAAAAss1nd/Pxhm5Ncle99Qt/D95iqJA8kef0Uxzm1qhbeOP21Sc5M8qNJ0t23VNX1Sd6e5F27OtDWfm7W3nfx9GewP7vgutEVAOzS9otOH10CAAAwQzMNprr76swHUA9vb09y0G72OXnR9pYl9nneonlv2sMyAQAAAJiBWd5jCgAAAAC+SzAFAAAAwBCCKQAAAACGEEwBAAAAMMSsn8q34qw7/JDMeQoUAAAAwMxZMQUAAADAEIIpAAAAAIYQTAEAAAAwhGAKAAAAgCEEUwAAAAAMIZgCAAAAYAjBFAAAAABDCKYAAAAAGEIwBQAAAMAQgikAAAAAhhBMAQAAADCEYAoAAACAIQRTAAAAAAwhmAIAAABgCMEUAAAAAEMIpgAAAAAYQjAFAAAAwBCCKQAAAACGEEwBAAAAMIRgCgAAAIAhBFMAAAAADCGYAgAAAGAIwRQAAAAAQwimAAAAABhCMAUAAADAECsmmKqqM6uqq+pHFo2/paruq6pDFoydXFX3VNVfVtXnqurfLRj/yKxrBwAAAOCxWzHBVJKNSW5IsmEn459Kcuai8U929wlJ1if5uap6wfKXCAAAAMC+siKCqap6SpIXJnldFgRTVXVUkqckeUfmA6pH6e5vJ/l0kqOWv1IAAAAA9pUVEUwleVWS/97dn0/yjao6cTK+McnmJJ9M8sNV9azFO1bVM5KclGTbtB9WVedW1VxVze3YsWPvqwcAAADgMVspwdTGJJdPXl+eR1ZHbUhyeXc/lOSqJK9ZsM8/rKq/TPLHSS7q7qmDqe7e1N3ru3v9oYceuvfVAwAAAPCYrRldwGTF04uTHFtVneSAJF1Vf5Dk6CQfq6okeVKSLyW5ZLLrJ7v7FQNKBgAAAGAfWAkrps5K8vvdfWR3r+3uI5LcnuTiJBdOxtZ292FJDq+qI4dWCwAAAMA+MXzFVOYv27to0diHk7wlydWLxq/O/OV9N+7ieKdW1Z0Ltl/T3X++1OStX70nay+4brdFbr/o9N3OAQAAAGB6w4Op7j55J2PvT/L+nYy/dcHmlp28vyXJQfuuOgAAAACWy0q4lA8AAACAVUgwBQAAAMAQgikAAAAAhhBMAQAAADDE8Jufj7bu8EMy54l7AAAAADNnxRQAAAAAQwimAAAAABhCMAUAAADAEIIpAAAAAIYQTAEAAAAwhGAKAAAAgCEEUwAAAAAMIZgCAAAAYAjBFAAAAABDVHePrmGoqvq7JLeOrgN24ZlJvj66CNgNfcpKp0fZH+hTVjo9ykqnR1eWI7v70N1NWjOLSla4W7t7/egiYClVNadHWen0KSudHmV/oE9Z6fQoK50e3T+5lA8AAACAIQRTAAAAAAwhmEo2jS4AdkOPsj/Qp6x0epT9gT5lpdOjrHR6dD+06m9+DgAAAMAYVkwBAAAAMMSqCKaq6rSqurWqvlhVF+zk/QOr6orJ+zdW1drZV8lqN0Wfvqiqbq6qB6vqrBE1srpN0aNvrarPVtVnqurjVXXkiDpZ3abo01+sqq1VdUtV3VBVzx9RJ6vX7np0wbyzqqqrytOlmLkpvkvPqaodk+/SW6rq9SPqZPWa5ru0qv7p5GfTbVX1X2ddI9N73F/KV1UHJPl8kpcmuTPJp5Js7O7PLphzXpLjuvsXq2pDkjO7+7VDCmZVmrJP1yZ5WpJfSnJtd185+0pZrabs0VOS3Njd91bVG5Oc7LuUWZqyT5/W3bZsYgEAAANpSURBVN+cvD4jyXndfdqIell9punRybynJrkuyZOSnN/dc7OuldVryu/Sc5Ks7+7zhxTJqjZljx6d5ENJXtzdd1fVs7r7b4cUzG6thhVTP57ki939pe5+IMnlSX560ZyfTnLZ5PWVSU6tqpphjbDbPu3u7d39mSQPjSiQVW+aHv3T7r53svkXSZ4z4xphmj795oLNJyd5fP8PHSvNND+XJsm/T/KeJPfNsjiYmLZPYZRpevRfJLmku+9OEqHUyrYagqnDk3xlwfadk7GdzunuB5Pck+QZM6kO5k3TpzDSY+3R1yX56LJWBI82VZ9W1b+sqtsy/4v/m2ZUGyRT9GhVnZDkiO7+yCwLgwWm/Tf/1ZPL96+sqiNmUxokma5Hn5fkeVX1v6rqL6rK6ugVbDUEUztb+bT4f0enmQPLSQ+y0k3do1X1c0nWJ3nvslYEjzZVn3b3Jd19VJK3J3nHslcFj9hlj1bVE5K8L8m/nllF8GjTfJf+tyRru/u4JH+SR64+gVmYpkfXJDk6yclJNib5var6/mWuiz20GoKpO5MsTPCfk+Svl5pTVWuSHJLkGzOpDuZN06cw0lQ9WlUvSfIrSc7o7vtnVBs87LF+l16e5FXLWhF8r9316FOTHJtkS1VtT3JSkmvdAJ0Z2+13aXffteDf+Q8mecGMaoNk+t/x/6i7v9Pdtye5NfNBFSvQagimPpXk6Kr6wap6UpINSa5dNOfaJGdPXp+V5H/04/2u8Kw00/QpjLTbHp1cfvIfMx9KuY6fEabp04U/lJ6e5AszrA922aPdfU93P7O713b32szfr+8MNz9nxqb5Ln32gs0zknxuhvXBNL87XZPklCSpqmdm/tK+L820Sqa2ZnQBy627H6yq85Ncn+SAJJd297aq+tUkc919bZL/lOS/VNUXM79SasO4ilmNpunTqvqxJFcneXqSV1bVu7r7mIFls4pM+V363iRPSfKHk+dH3NHdZwwrmlVnyj49f7Ky7ztJ7s4j/zEFy27KHoWhpuzTN02ebPpg5n9/OmdYwaw6U/bo9UleVlWfTfL3Sd7W3XeNq5pdKQuDAAAAABhhNVzKBwAAAMAKJJgCAAAAYAjBFAAAAABDCKYAAAAAGEIwBQAAAMAQgikAAAAAhhBMAQAAADCEYAoAAACAIf4/Fn/MoyeUWAYAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 1440x720 with 1 Axes>"
      ]
     },
     "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": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>accounttype</th>\n",
       "      <th>accountid</th>\n",
       "      <th>ticker</th>\n",
       "      <th>shares</th>\n",
       "      <th>assetclass</th>\n",
       "      <th>close</th>\n",
       "      <th>value</th>\n",
       "      <th>final_shares_chg</th>\n",
       "      <th>new_shares</th>\n",
       "      <th>new_value</th>\n",
       "      <th>new_value_chg</th>\n",
       "      <th>final_allocation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>RIRA</td>\n",
       "      <td>1111</td>\n",
       "      <td>VBTLX</td>\n",
       "      <td>913.483</td>\n",
       "      <td>BD</td>\n",
       "      <td>10.39</td>\n",
       "      <td>9491.08837</td>\n",
       "      <td>-743.404775</td>\n",
       "      <td>170.078225</td>\n",
       "      <td>1767.112759</td>\n",
       "      <td>-7723.975611</td>\n",
       "      <td>0.019071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>RIRA</td>\n",
       "      <td>2222</td>\n",
       "      <td>VBTLX</td>\n",
       "      <td>772.407</td>\n",
       "      <td>BD</td>\n",
       "      <td>10.39</td>\n",
       "      <td>8025.30873</td>\n",
       "      <td>-628.595225</td>\n",
       "      <td>143.811775</td>\n",
       "      <td>1494.204341</td>\n",
       "      <td>-6531.104389</td>\n",
       "      <td>0.016125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>RIRA</td>\n",
       "      <td>1111</td>\n",
       "      <td>VTIAX</td>\n",
       "      <td>514.298</td>\n",
       "      <td>ST</td>\n",
       "      <td>25.17</td>\n",
       "      <td>12944.88066</td>\n",
       "      <td>47.000000</td>\n",
       "      <td>561.298000</td>\n",
       "      <td>14127.870660</td>\n",
       "      <td>1182.990000</td>\n",
       "      <td>0.152468</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>RIRA</td>\n",
       "      <td>1111</td>\n",
       "      <td>VTSAX</td>\n",
       "      <td>151.121</td>\n",
       "      <td>ST</td>\n",
       "      <td>61.08</td>\n",
       "      <td>9230.47068</td>\n",
       "      <td>1.418028</td>\n",
       "      <td>152.539028</td>\n",
       "      <td>9317.083821</td>\n",
       "      <td>86.613141</td>\n",
       "      <td>0.100550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>RIRA</td>\n",
       "      <td>2222</td>\n",
       "      <td>VTSAX</td>\n",
       "      <td>151.578</td>\n",
       "      <td>ST</td>\n",
       "      <td>61.08</td>\n",
       "      <td>9258.38424</td>\n",
       "      <td>1.422316</td>\n",
       "      <td>153.000316</td>\n",
       "      <td>9345.259305</td>\n",
       "      <td>86.875065</td>\n",
       "      <td>0.100854</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>TAXB</td>\n",
       "      <td>3333</td>\n",
       "      <td>VTSAX</td>\n",
       "      <td>549.871</td>\n",
       "      <td>ST</td>\n",
       "      <td>61.08</td>\n",
       "      <td>33586.12068</td>\n",
       "      <td>5.159656</td>\n",
       "      <td>555.030656</td>\n",
       "      <td>33901.272475</td>\n",
       "      <td>315.151795</td>\n",
       "      <td>0.365862</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>TAXB</td>\n",
       "      <td>3333</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>3.140</td>\n",
       "      <td>ST</td>\n",
       "      <td>156.15</td>\n",
       "      <td>490.31100</td>\n",
       "      <td>-3.140000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-490.311000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VIGAX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ST</td>\n",
       "      <td>68.02</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>17.000000</td>\n",
       "      <td>17.000000</td>\n",
       "      <td>1156.340000</td>\n",
       "      <td>1156.340000</td>\n",
       "      <td>0.012479</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VSMAX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ST</td>\n",
       "      <td>62.25</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>560.250000</td>\n",
       "      <td>560.250000</td>\n",
       "      <td>0.006046</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VSEQX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ST</td>\n",
       "      <td>26.77</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>22.000000</td>\n",
       "      <td>22.000000</td>\n",
       "      <td>588.940000</td>\n",
       "      <td>588.940000</td>\n",
       "      <td>0.006356</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VWIGX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ST</td>\n",
       "      <td>24.76</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>190.000000</td>\n",
       "      <td>190.000000</td>\n",
       "      <td>4704.400000</td>\n",
       "      <td>4704.400000</td>\n",
       "      <td>0.050770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VTRIX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ST</td>\n",
       "      <td>31.91</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>147.000000</td>\n",
       "      <td>147.000000</td>\n",
       "      <td>4690.770000</td>\n",
       "      <td>4690.770000</td>\n",
       "      <td>0.050623</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VTABX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BD</td>\n",
       "      <td>21.66</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>64.000000</td>\n",
       "      <td>64.000000</td>\n",
       "      <td>1386.240000</td>\n",
       "      <td>1386.240000</td>\n",
       "      <td>0.014960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VGSLX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>RE</td>\n",
       "      <td>105.06</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>44.000000</td>\n",
       "      <td>44.000000</td>\n",
       "      <td>4622.640000</td>\n",
       "      <td>4622.640000</td>\n",
       "      <td>0.049887</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VNQI</td>\n",
       "      <td>NaN</td>\n",
       "      <td>RE</td>\n",
       "      <td>52.05</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>17.000000</td>\n",
       "      <td>17.000000</td>\n",
       "      <td>884.850000</td>\n",
       "      <td>884.850000</td>\n",
       "      <td>0.009549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VDE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ST</td>\n",
       "      <td>77.44</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>36.000000</td>\n",
       "      <td>36.000000</td>\n",
       "      <td>2787.840000</td>\n",
       "      <td>2787.840000</td>\n",
       "      <td>0.030086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GLD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CS</td>\n",
       "      <td>120.57</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>1326.270000</td>\n",
       "      <td>1326.270000</td>\n",
       "      <td>0.014313</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "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": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>accountid</th>\n",
       "      <th>accounttype</th>\n",
       "      <th>new_value_chg_sum</th>\n",
       "      <th>order</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1111</td>\n",
       "      <td>RIRA</td>\n",
       "      <td>-445.492471</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2222</td>\n",
       "      <td>RIRA</td>\n",
       "      <td>-266.039324</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3333</td>\n",
       "      <td>TAXB</td>\n",
       "      <td>-979.959205</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "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": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>accountid</th>\n",
       "      <th>accounttype</th>\n",
       "      <th>new_value_chg_sum</th>\n",
       "      <th>order</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3333</td>\n",
       "      <td>TAXB</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1111</td>\n",
       "      <td>RIRA</td>\n",
       "      <td>-99.181676</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2222</td>\n",
       "      <td>RIRA</td>\n",
       "      <td>-266.039324</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "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": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>accounttype</th>\n",
       "      <th>accountid</th>\n",
       "      <th>ticker</th>\n",
       "      <th>shares</th>\n",
       "      <th>assetclass</th>\n",
       "      <th>close</th>\n",
       "      <th>value</th>\n",
       "      <th>new_shares</th>\n",
       "      <th>final_shares_chg</th>\n",
       "      <th>new_value</th>\n",
       "      <th>new_value_chg</th>\n",
       "      <th>final_allocation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>RIRA</td>\n",
       "      <td>1111</td>\n",
       "      <td>VBTLX</td>\n",
       "      <td>913.483</td>\n",
       "      <td>BD</td>\n",
       "      <td>10.39</td>\n",
       "      <td>9491.08837</td>\n",
       "      <td>170.078225</td>\n",
       "      <td>-743.404775</td>\n",
       "      <td>1767.112759</td>\n",
       "      <td>-7723.975611</td>\n",
       "      <td>0.019071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>RIRA</td>\n",
       "      <td>2222</td>\n",
       "      <td>VBTLX</td>\n",
       "      <td>772.407</td>\n",
       "      <td>BD</td>\n",
       "      <td>10.39</td>\n",
       "      <td>8025.30873</td>\n",
       "      <td>143.811775</td>\n",
       "      <td>-628.595225</td>\n",
       "      <td>1494.204341</td>\n",
       "      <td>-6531.104389</td>\n",
       "      <td>0.016125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>RIRA</td>\n",
       "      <td>1111</td>\n",
       "      <td>VTIAX</td>\n",
       "      <td>514.298</td>\n",
       "      <td>ST</td>\n",
       "      <td>25.17</td>\n",
       "      <td>12944.88066</td>\n",
       "      <td>561.298000</td>\n",
       "      <td>47.000000</td>\n",
       "      <td>14127.870660</td>\n",
       "      <td>1182.990000</td>\n",
       "      <td>0.152468</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>RIRA</td>\n",
       "      <td>1111</td>\n",
       "      <td>VTSAX</td>\n",
       "      <td>151.121</td>\n",
       "      <td>ST</td>\n",
       "      <td>61.08</td>\n",
       "      <td>9230.47068</td>\n",
       "      <td>152.539028</td>\n",
       "      <td>1.418028</td>\n",
       "      <td>9317.083821</td>\n",
       "      <td>86.613141</td>\n",
       "      <td>0.100550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>RIRA</td>\n",
       "      <td>2222</td>\n",
       "      <td>VTSAX</td>\n",
       "      <td>151.578</td>\n",
       "      <td>ST</td>\n",
       "      <td>61.08</td>\n",
       "      <td>9258.38424</td>\n",
       "      <td>153.000316</td>\n",
       "      <td>1.422316</td>\n",
       "      <td>9345.259305</td>\n",
       "      <td>86.875065</td>\n",
       "      <td>0.100854</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>TAXB</td>\n",
       "      <td>3333</td>\n",
       "      <td>VTSAX</td>\n",
       "      <td>549.871</td>\n",
       "      <td>ST</td>\n",
       "      <td>61.08</td>\n",
       "      <td>33586.12068</td>\n",
       "      <td>555.030656</td>\n",
       "      <td>5.159656</td>\n",
       "      <td>33901.272475</td>\n",
       "      <td>315.151795</td>\n",
       "      <td>0.365862</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>TAXB</td>\n",
       "      <td>3333</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>3.140</td>\n",
       "      <td>ST</td>\n",
       "      <td>156.15</td>\n",
       "      <td>490.31100</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-3.140000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-490.311000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>TAXB</td>\n",
       "      <td>3333</td>\n",
       "      <td>VIGAX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ST</td>\n",
       "      <td>68.02</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>17.000000</td>\n",
       "      <td>17.000000</td>\n",
       "      <td>1156.340000</td>\n",
       "      <td>1156.340000</td>\n",
       "      <td>0.012479</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>TAXB</td>\n",
       "      <td>3333</td>\n",
       "      <td>VSMAX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ST</td>\n",
       "      <td>62.25</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>560.250000</td>\n",
       "      <td>560.250000</td>\n",
       "      <td>0.006046</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>RIRA</td>\n",
       "      <td>2222</td>\n",
       "      <td>VSEQX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ST</td>\n",
       "      <td>26.77</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>22.000000</td>\n",
       "      <td>22.000000</td>\n",
       "      <td>588.940000</td>\n",
       "      <td>588.940000</td>\n",
       "      <td>0.006356</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>RIRA</td>\n",
       "      <td>2222</td>\n",
       "      <td>VWIGX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ST</td>\n",
       "      <td>24.76</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>190.000000</td>\n",
       "      <td>190.000000</td>\n",
       "      <td>4704.400000</td>\n",
       "      <td>4704.400000</td>\n",
       "      <td>0.050770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>TAXB</td>\n",
       "      <td>3333</td>\n",
       "      <td>VTRIX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ST</td>\n",
       "      <td>31.91</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>147.000000</td>\n",
       "      <td>147.000000</td>\n",
       "      <td>4690.770000</td>\n",
       "      <td>4690.770000</td>\n",
       "      <td>0.050623</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>RIRA</td>\n",
       "      <td>1111</td>\n",
       "      <td>VTABX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BD</td>\n",
       "      <td>21.66</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>64.000000</td>\n",
       "      <td>64.000000</td>\n",
       "      <td>1386.240000</td>\n",
       "      <td>1386.240000</td>\n",
       "      <td>0.014960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>RIRA</td>\n",
       "      <td>1111</td>\n",
       "      <td>VGSLX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>RE</td>\n",
       "      <td>105.06</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>44.000000</td>\n",
       "      <td>44.000000</td>\n",
       "      <td>4622.640000</td>\n",
       "      <td>4622.640000</td>\n",
       "      <td>0.049887</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>RIRA</td>\n",
       "      <td>2222</td>\n",
       "      <td>VNQI</td>\n",
       "      <td>NaN</td>\n",
       "      <td>RE</td>\n",
       "      <td>52.05</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>17.000000</td>\n",
       "      <td>17.000000</td>\n",
       "      <td>884.850000</td>\n",
       "      <td>884.850000</td>\n",
       "      <td>0.009549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>TAXB</td>\n",
       "      <td>3333</td>\n",
       "      <td>VDE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ST</td>\n",
       "      <td>77.44</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>36.000000</td>\n",
       "      <td>36.000000</td>\n",
       "      <td>2787.840000</td>\n",
       "      <td>2787.840000</td>\n",
       "      <td>0.030086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>TAXB</td>\n",
       "      <td>3333</td>\n",
       "      <td>GLD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CS</td>\n",
       "      <td>120.57</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>964.560000</td>\n",
       "      <td>964.560000</td>\n",
       "      <td>0.010565</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>RIRA</td>\n",
       "      <td>1111</td>\n",
       "      <td>GLD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CS</td>\n",
       "      <td>120.57</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>241.140000</td>\n",
       "      <td>241.140000</td>\n",
       "      <td>0.003749</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "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
}