{ "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": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
tickerlastrebaldateassetclassbasisdatecostbasissharesallocation_targetclosevalueallocationcorrectionnew_money_inrebal_flagfinal_shares_chgnew_sharesnew_valuenew_value_chgfinal_allocation
0AAPL2018-11-16ST2018-11-161.0000003.1400.0000156.15490.311000.005905-0.0059050.01-3.140.0000.00000-490.3110.000000
1VBTLX2018-11-16BD2018-11-161.0000001685.8900.035010.3917516.397100.210973-0.175973350.01-1372.00313.8903261.31710-14255.0800.035196
2VTIAX2018-11-16ST2018-11-161.000000514.2980.152125.1712944.880660.155913-0.0038131521.0147.00561.29814127.870661182.9900.152468
3VTSAX2018-11-16ST2018-11-1611.777895852.5700.565261.0852074.975600.627209-0.0620095652.018.00860.57052563.61560488.6400.567266
4VIGAXNaTST2018-12-2768.0200000.0000.013168.020.000000.0000000.013100131.0117.0017.0001156.340001156.3400.012479
5VSMAXNaTST2018-12-2762.2500000.0000.006662.250.000000.0000000.00660066.019.009.000560.25000560.2500.006046
6VSEQXNaTST2018-12-2726.7700000.0000.006626.770.000000.0000000.00660066.0122.0022.000588.94000588.9400.006356
7VWIGXNaTST2018-12-2724.7600000.0000.050724.760.000000.0000000.050700507.01190.00190.0004704.400004704.4000.050770
8VTRIXNaTST2018-12-2731.9100000.0000.050731.910.000000.0000000.050700507.01147.00147.0004690.770004690.7700.050623
9VTABXNaTBD2018-12-2721.6600000.0000.015021.660.000000.0000000.015000150.0164.0064.0001386.240001386.2400.014960
10VGSLXNaTRE2018-12-27105.0600000.0000.0500105.060.000000.0000000.050000500.0144.0044.0004622.640004622.6400.049887
11VNQINaTRE2018-12-2752.0500000.0000.010052.050.000000.0000000.010000100.0117.0017.000884.85000884.8500.009549
12VDENaTST2018-12-2777.4400000.0000.030077.440.000000.0000000.030000300.0136.0036.0002787.840002787.8400.030086
13GLDNaTCS2018-12-27120.5700000.0000.0150120.570.000000.0000000.015000150.0111.0011.0001326.270001326.2700.014313
\n", "
" ], "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": [ "" ] }, "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": [ "
" ] }, "metadata": { "needs_background": "light" }, "output_type": "display_data" } ], "source": [ "#Lets add a bar chart here to show the new allocation vs. the target allocation and vs. the original portfolio\n", "graph_port = exec_port[['ticker','allocation','allocation_target','final_allocation']].copy()\n", "graph_port.plot.barh(x='ticker',figsize=(20,10))" ] }, { "cell_type": "code", "execution_count": 24, "metadata": { "scrolled": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
accounttypeaccountidtickersharesassetclassclosevaluefinal_shares_chgnew_sharesnew_valuenew_value_chgfinal_allocation
0RIRA1111VBTLX913.483BD10.399491.08837-743.404775170.0782251767.112759-7723.9756110.019071
1RIRA2222VBTLX772.407BD10.398025.30873-628.595225143.8117751494.204341-6531.1043890.016125
2RIRA1111VTIAX514.298ST25.1712944.8806647.000000561.29800014127.8706601182.9900000.152468
3RIRA1111VTSAX151.121ST61.089230.470681.418028152.5390289317.08382186.6131410.100550
4RIRA2222VTSAX151.578ST61.089258.384241.422316153.0003169345.25930586.8750650.100854
5TAXB3333VTSAX549.871ST61.0833586.120685.159656555.03065633901.272475315.1517950.365862
6TAXB3333AAPL3.140ST156.15490.31100-3.1400000.0000000.000000-490.3110000.000000
7NaNNaNVIGAXNaNST68.020.0000017.00000017.0000001156.3400001156.3400000.012479
8NaNNaNVSMAXNaNST62.250.000009.0000009.000000560.250000560.2500000.006046
9NaNNaNVSEQXNaNST26.770.0000022.00000022.000000588.940000588.9400000.006356
10NaNNaNVWIGXNaNST24.760.00000190.000000190.0000004704.4000004704.4000000.050770
11NaNNaNVTRIXNaNST31.910.00000147.000000147.0000004690.7700004690.7700000.050623
12NaNNaNVTABXNaNBD21.660.0000064.00000064.0000001386.2400001386.2400000.014960
13NaNNaNVGSLXNaNRE105.060.0000044.00000044.0000004622.6400004622.6400000.049887
14NaNNaNVNQINaNRE52.050.0000017.00000017.000000884.850000884.8500000.009549
15NaNNaNVDENaNST77.440.0000036.00000036.0000002787.8400002787.8400000.030086
16NaNNaNGLDNaNCS120.570.0000011.00000011.0000001326.2700001326.2700000.014313
\n", "
" ], "text/plain": [ " accounttype accountid ticker shares assetclass close value \\\n", "0 RIRA 1111 VBTLX 913.483 BD 10.39 9491.08837 \n", "1 RIRA 2222 VBTLX 772.407 BD 10.39 8025.30873 \n", "2 RIRA 1111 VTIAX 514.298 ST 25.17 12944.88066 \n", "3 RIRA 1111 VTSAX 151.121 ST 61.08 9230.47068 \n", "4 RIRA 2222 VTSAX 151.578 ST 61.08 9258.38424 \n", "5 TAXB 3333 VTSAX 549.871 ST 61.08 33586.12068 \n", "6 TAXB 3333 AAPL 3.140 ST 156.15 490.31100 \n", "7 NaN NaN VIGAX NaN ST 68.02 0.00000 \n", "8 NaN NaN VSMAX NaN ST 62.25 0.00000 \n", "9 NaN NaN VSEQX NaN ST 26.77 0.00000 \n", "10 NaN NaN VWIGX NaN ST 24.76 0.00000 \n", "11 NaN NaN VTRIX NaN ST 31.91 0.00000 \n", "12 NaN NaN VTABX NaN BD 21.66 0.00000 \n", "13 NaN NaN VGSLX NaN RE 105.06 0.00000 \n", "14 NaN NaN VNQI NaN RE 52.05 0.00000 \n", "15 NaN NaN VDE NaN ST 77.44 0.00000 \n", "16 NaN NaN GLD NaN CS 120.57 0.00000 \n", "\n", " final_shares_chg new_shares new_value new_value_chg \\\n", "0 -743.404775 170.078225 1767.112759 -7723.975611 \n", "1 -628.595225 143.811775 1494.204341 -6531.104389 \n", "2 47.000000 561.298000 14127.870660 1182.990000 \n", "3 1.418028 152.539028 9317.083821 86.613141 \n", "4 1.422316 153.000316 9345.259305 86.875065 \n", "5 5.159656 555.030656 33901.272475 315.151795 \n", "6 -3.140000 0.000000 0.000000 -490.311000 \n", "7 17.000000 17.000000 1156.340000 1156.340000 \n", "8 9.000000 9.000000 560.250000 560.250000 \n", "9 22.000000 22.000000 588.940000 588.940000 \n", "10 190.000000 190.000000 4704.400000 4704.400000 \n", "11 147.000000 147.000000 4690.770000 4690.770000 \n", "12 64.000000 64.000000 1386.240000 1386.240000 \n", "13 44.000000 44.000000 4622.640000 4622.640000 \n", "14 17.000000 17.000000 884.850000 884.850000 \n", "15 36.000000 36.000000 2787.840000 2787.840000 \n", "16 11.000000 11.000000 1326.270000 1326.270000 \n", "\n", " final_allocation \n", "0 0.019071 \n", "1 0.016125 \n", "2 0.152468 \n", "3 0.100550 \n", "4 0.100854 \n", "5 0.365862 \n", "6 0.000000 \n", "7 0.012479 \n", "8 0.006046 \n", "9 0.006356 \n", "10 0.050770 \n", "11 0.050623 \n", "12 0.014960 \n", "13 0.049887 \n", "14 0.009549 \n", "15 0.030086 \n", "16 0.014313 " ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "#Next we turn our ticker-level strategy into account level actions\n", "#Join in our rebalanced portfolio and determine how to split value across accounts for a given ticker\n", "port = pd.merge(start_port[['accounttype','accountid','ticker','shares']], \n", " exec_port[['ticker','assetclass','close','value','final_shares_chg','new_shares','new_value','new_value_chg','final_allocation']], \n", " how = 'right', \n", " left_on = 'ticker', \n", " right_on = 'ticker')\n", "port['value_orig'] = port.close * port.shares\n", "#Calculate the value-weight of each ticker by account\n", "port['tick_alloc'] = port.value_orig / port.value #What pct of each ticker is in a given account?\n", "port['tick_alloc'].fillna(1.0,inplace=True)\n", "\n", "#check our sub-allocations\n", "assert(port.groupby('ticker').tick_alloc.sum().sum() == len(port.groupby('ticker').tick_alloc.sum()))\n", "\n", "#Recalculate the values proportionately\n", "port['final_shares_chg_n'] = port.final_shares_chg * port.tick_alloc\n", "port['new_shares_n'] = port.new_shares * port.tick_alloc\n", "port['new_value_n'] = port.new_value * port.tick_alloc\n", "port['new_value_chg_n'] = port.new_value_chg * port.tick_alloc\n", "port['final_allocation_n'] = port.final_allocation * port.tick_alloc\n", "\n", "#double check our final_allocation is 100%\n", "assert(np.round(port.final_allocation_n.sum(),4)==1.0)\n", "\n", "#Now we must double check to ensure we are not allocating buys to accounts with no sells (we cannot just add funds to a Traditional IRA account, for example)\n", "#accounts with single securities in them which also exist in other accounts can cause issues if we don't do this\n", "acctsdf = port.groupby(['accountid','accounttype']).new_value_chg_n.sum()\n", "acctsdf = acctsdf.reset_index().rename(columns={'new_value_chg_n':'new_value_chg_sum'})\n", "errordf = acctsdf[acctsdf.new_value_chg_sum > 0].copy() #a value >0 at the account-level implies we have allocated buys to an account with insufficient sells\n", "erroraccts = errordf.accountid.values\n", "if len(errordf) > 0: \n", " for t in port[port.accountid.isin(erroraccts)].ticker.unique(): #Loop by security (not by account)\n", " print(\"Correcting distribution for single-security accounts edge case: {}\".format(t))\n", " index = (port.accountid.isin(erroraccts)) & (port.ticker == t)\n", " display(port[port.ticker == t])\n", " #adjust numerator and denominator for proper recalculation of asset distribution across accounts\n", " port.loc[index,'new_shares_n'] = port.new_shares_n - port.final_shares_chg_n\n", " port.loc[index,'new_value_n'] = port.new_value_n - port.new_value_chg_n\n", " port.loc[index,'final_shares_chg_n'] = 0\n", " port.loc[index,'new_value_chg_n'] = 0\n", "\n", " #remove from denominator\n", " port.loc[port.ticker == t,'value'] = port.loc[port.ticker == t,'value'] - port[index].value_orig.sum()\n", " \n", " #recalculate values for this ticker\n", " port.loc[port.ticker == t,'tick_alloc'] = port[port.ticker == t].value_orig / port[port.ticker == t].value\n", " port.loc[index,'tick_alloc'] = 0 #set new money allocation to zero for funds with insufficient assets\n", " port.loc[port.ticker == t,'final_shares_chg_n'] = port.final_shares_chg * port.tick_alloc\n", " port.loc[port.ticker == t,'new_shares_n'] = port.shares + port.final_shares_chg_n\n", " port.loc[port.ticker == t,'new_value_chg_n'] = port.new_value_chg * port.tick_alloc\n", " port.loc[port.ticker == t,'new_value_n'] = port.value_orig + port.new_value_chg_n\n", " port.loc[port.ticker == t,'final_allocation_n'] = (port.new_value_n / port.new_value) * port.final_allocation\n", " \n", " display(port[port.ticker == t])\n", "\n", "\n", "#Cleanup\n", "port['value'] = port.value_orig\n", "port['final_shares_chg'] = port.final_shares_chg_n\n", "port['new_shares'] = port.new_shares_n\n", "port['new_value'] = port.new_value_n\n", "port['new_value_chg'] = port.new_value_chg_n\n", "port['final_allocation'] = port.final_allocation_n\n", "port.drop(['value_orig','tick_alloc','final_shares_chg_n','new_shares_n','new_value_n','new_value_chg_n','final_allocation_n'],axis=1,inplace=True)\n", "port.fillna({'value':0.0},inplace=True)\n", "\n", "#Check our work\n", "assert(np.round(port.final_allocation.sum(),4)==1.0)\n", "assert(np.round(np.sum((port.shares+port.final_shares_chg)-port.new_shares))==0)\n", "assert(np.round(np.sum(port.new_value-(port.new_shares*port.close)))==0)\n", "assert(np.round(np.sum(port.new_value_chg-(port.final_shares_chg*port.close)))==0)\n", "\n", "#Lets look at our final portfolio at the account level\n", "display(port)" ] }, { "cell_type": "code", "execution_count": 30, "metadata": { "scrolled": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Distributing new securities to existing accounts . . .\n", " FITS VTABX in 1111-RIRA with -5068.132470695083 remaining\n", " FITS VGSLX in 1111-RIRA with -445.4924706950824 remaining\n", " VNQI 884.8499999999999 does not fit in 1111-RIRA\n", " FITS VNQI in 2222-RIRA with -5559.379323808596 remaining\n", " VWIGX 4704.400000000001 does not fit in 1111-RIRA\n", " FITS VWIGX in 2222-RIRA with -854.9793238085958 remaining\n", " VTRIX 4690.77 does not fit in 1111-RIRA\n", " VTRIX 4690.77 does not fit in 2222-RIRA\n", " FITS VTRIX in 3333-TAXB with -5484.389205496322 remaining\n", " VDE 2787.84 does not fit in 1111-RIRA\n", " VDE 2787.84 does not fit in 2222-RIRA\n", " FITS VDE in 3333-TAXB with -2696.5492054963215 remaining\n", " VIGAX 1156.34 does not fit in 1111-RIRA\n", " VIGAX 1156.34 does not fit in 2222-RIRA\n", " FITS VIGAX in 3333-TAXB with -1540.2092054963216 remaining\n", " VSEQX 588.9399999999999 does not fit in 1111-RIRA\n", " FITS VSEQX in 2222-RIRA with -266.0393238085959 remaining\n", " VSMAX 560.25 does not fit in 1111-RIRA\n", " VSMAX 560.25 does not fit in 2222-RIRA\n", " FITS VSMAX in 3333-TAXB with -979.9592054963216 remaining\n", " GLD 1326.27 does not fit in 1111-RIRA\n", " GLD 1326.27 does not fit in 2222-RIRA\n", " GLD 1326.27 does not fit in 3333-TAXB\n", " GLD 1326.27 does not fit in 1111-RIRA\n", " GLD 1326.27 does not fit in 2222-RIRA\n", " GLD 1326.27 does not fit in 3333-TAXB\n", "\n", "Lets see what remains in our accounts after 2 loops . . .\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
accountidaccounttypenew_value_chg_sumorder
01111RIRA-445.4924711
12222RIRA-266.0393241
23333TAXB-979.9592053
\n", "
" ], "text/plain": [ " accountid accounttype new_value_chg_sum order\n", "0 1111 RIRA -445.492471 1\n", "1 2222 RIRA -266.039324 1\n", "2 3333 TAXB -979.959205 3" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "Splitting remaining securities across accounts . . .\n", " GLD move 979.9592054963216 or 0.7388836402062338% into account 3333-TAXB. GLD bal remaining 346.3107945036784\n", " GLD move 346.3107945036784 or 0.26111635979376624% into account 1111-RIRA. GLD bal remaining 0\n", " GLD move 0 or 0.0% into account 2222-RIRA. GLD bal remaining 0\n" ] } ], "source": [ "#Finally, all new tickers need an account to land in\n", "dport = None\n", "acctsdf = None\n", "if len(port[port.accounttype.isnull()])>0: #if we have none, skip this step\n", " print('Distributing new securities to existing accounts . . .')\n", " dport = port.copy()\n", "\n", " #account-level fund surplus or deficit - must match these with our orphaned securities\n", " acctsdf = port.groupby(['accountid','accounttype']).new_value_chg.sum()\n", " acctsdf = acctsdf.reset_index().rename(columns={'new_value_chg':'new_value_chg_sum'})\n", " #establish sort order so we can allocate tax-efficient account space first\n", " actype_sortorder = pd.DataFrame(data=[['RIRA',1],['TIRA',2],['TAXB',3]],columns=['accounttype','order'])\n", " acctsdf = pd.merge(acctsdf,actype_sortorder,how='left',left_on='accounttype',right_on='accounttype')\n", " #We make a consequential assumption here that any new_money_in will be allocated 100% in one of the Taxable accounts (first in list).\n", " #if you have a Roth-IRA which has not met its contribution limits for the year, it may be preferrential to distribute the funds there first.\n", " #IF YOU HAVE NO TAXABLE ACCOUNT AND YOU WISH TO REBALANCE WITH new_money_in > 0 this will cause errors - so we assert here:\n", " assert(new_money_in == 0 or (len(acctsdf[acctsdf.accounttype == 'TAXB'])>0 and new_money_in > 0))\n", " min_idx = acctsdf[acctsdf.accounttype == 'TAXB'].index.min()\n", " acctsdf.loc[min_idx,'new_value_chg_sum'] = acctsdf.loc[min_idx,'new_value_chg_sum'] - new_money_in\n", " #only return accounts that have space\n", " acctsdf = acctsdf[acctsdf.new_value_chg_sum<0].copy()\n", "\n", " #establish sort order so we can allocate tax-inefficient assets first\n", " aclass_sortorder = pd.DataFrame(data=[['ST',3],['BD',1],['CS',4],['RE',2],['ALT',5]],columns=['assetclass','order'])\n", " dport = pd.merge(dport,aclass_sortorder,how='left',left_on='assetclass',right_on='assetclass')\n", "\n", " #We loop twice, first to fit whole securities in accounts with tax location in mind, then again without tax location for anything leftover\n", " loop = 0\n", " while loop < 2:\n", " loop+=1\n", " #loop through orphaned tickers and place them in accounts until all assets are allocated or we are forced to split a security across accounts\n", " # in the first loop we do not allow tax-inefficient assets to wind up in Taxable accounts, in the second loop we relax this constraint\n", " for index, row in dport[dport.accounttype.isnull()].sort_values(['order','new_value_chg'],ascending=[True,False]).iterrows():\n", " #loop through accounts and place the assets\n", " for i, r in acctsdf.iterrows():\n", " aid = r.accountid\n", " atype = r.accounttype\n", " bal = r.new_value_chg_sum\n", " #print('Evaluating {}-{} with {} starting bal'.format(aid,atype,bal))\n", " if loop == 0 and (row.assetclass in ('BD','RE') and atype == 'TAXB'):\n", " continue #skip this case, since we don't want to place Bonds and Real-Estate assets in Taxable accounts\n", " elif loop == 0 and (row.assetclass not in ('BD','RE') and atype != 'TAXB'):\n", " continue #skip this case, since we don't want to place tax-efficient assets into tax sheltered accounts \n", "\n", " if row.new_value_chg + bal <=0: #it fits\n", " bal+=row.new_value_chg\n", " print(' FITS {} in {}-{} with {} remaining'.format(row.ticker,aid,atype,bal))\n", " #update our portfolio\n", " dport.loc[index,'accountid'] = aid\n", " dport.loc[index,'accounttype'] = atype\n", " #update account bal for next loop\n", " acctsdf.loc[i,'new_value_chg_sum'] = bal\n", " break\n", " else:\n", " print(' {} {} does not fit in {}-{}'.format(row.ticker,row.new_value_chg,aid,atype))\n", " \n", " print('\\nLets see what remains in our accounts after 2 loops . . .')\n", " display(acctsdf)\n", " \n", " #Here we are forced to split a security across multiple accounts because no one account can fit it\n", " # in this loop we allow tax-inefficient assets to wind up in Taxable accounts, but only as a last resort\n", " if len(dport[dport.accounttype.isnull()])>0:\n", " print('Splitting remaining securities across accounts . . .')\n", " #loop through accounts and place portions of asset in each, create a new row in the df for each placement.\n", " for index, row in dport[dport.accounttype.isnull()].sort_values(['order','new_value_chg'],ascending=[True,False]).iterrows():\n", " final_shares_chg = row.final_shares_chg\n", " asset_bal = row.new_value_chg\n", " #if its a tax-inefficent asset, order the accounts by 'order'\n", " if row.assetclass in ('BD','RE'):\n", " acctsdf = acctsdf.sort_values('order',ascending=True)\n", " else:\n", " acctsdf = acctsdf.sort_values('order',ascending=False)\n", " \n", " for i, r in acctsdf.iterrows():\n", " bal = r.new_value_chg_sum\n", " if asset_bal>-bal:\n", " to_move = -bal\n", " pct_move = -bal/row.new_value_chg\n", " asset_bal+=bal\n", " else:\n", " to_move = asset_bal\n", " pct_move = asset_bal/row.new_value_chg\n", " asset_bal=0\n", " print(' {} move {} or {}% into account {}-{}. {} bal remaining {}'.format(row.ticker,to_move,pct_move,r.accountid,r.accounttype,row.ticker,asset_bal))\n", " \n", " #update our account to reflect this change\n", " if asset_bal > 0:\n", " acctsdf.loc[i,'new_value_chg_sum'] = 0.0\n", " else:\n", " acctsdf.loc[i,'new_value_chg_sum'] = to_move+bal\n", " \n", " if (np.floor(pct_move*row.new_shares)*row.close)-row.value > 0:\n", " #create new row in our portfolio for this asset in this account\n", " dport.loc[max(dport.index)+1] = [r.accounttype,\n", " r.accountid,\n", " row.ticker,\n", " row.shares,\n", " row.assetclass,\n", " row.close,\n", " row.value,\n", " np.floor(pct_move*row.final_shares_chg), #we round down to get back to whole shares\n", " np.floor(pct_move*row.new_shares),\n", " np.floor(pct_move*row.new_shares)*row.close,\n", " (np.floor(pct_move*row.new_shares)*row.close)-row.value, #rounding can cause us to be short of our total allocatable funds\n", " np.floor(pct_move*row.new_value)/dport.new_value.sum(),\n", " row.order]\n", " \n", " #finally delete the original row from the df\n", " dport.drop(dport[dport.accounttype.isnull()].index,inplace=True)\n", " \n", " #double check our work - we just care that distributed funds < total available funds for this ticker\n", " assert(dport[dport.ticker==row.ticker].new_value_chg.sum() < row.new_value_chg)" ] }, { "cell_type": "code", "execution_count": 31, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
accountidaccounttypenew_value_chg_sumorder
23333TAXB0.0000003
01111RIRA-99.1816761
12222RIRA-266.0393241
\n", "
" ], "text/plain": [ " accountid accounttype new_value_chg_sum order\n", "2 3333 TAXB 0.000000 3\n", "0 1111 RIRA -99.181676 1\n", "1 2222 RIRA -266.039324 1" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "#Lets see whats left in our accounts, it should be very close to zero\n", "if acctsdf is not None:\n", " display(acctsdf)" ] }, { "cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
accounttypeaccountidtickersharesassetclassclosevaluenew_sharesfinal_shares_chgnew_valuenew_value_chgfinal_allocation
0RIRA1111VBTLX913.483BD10.399491.08837170.078225-743.4047751767.112759-7723.9756110.019071
1RIRA2222VBTLX772.407BD10.398025.30873143.811775-628.5952251494.204341-6531.1043890.016125
2RIRA1111VTIAX514.298ST25.1712944.88066561.29800047.00000014127.8706601182.9900000.152468
3RIRA1111VTSAX151.121ST61.089230.47068152.5390281.4180289317.08382186.6131410.100550
4RIRA2222VTSAX151.578ST61.089258.38424153.0003161.4223169345.25930586.8750650.100854
5TAXB3333VTSAX549.871ST61.0833586.12068555.0306565.15965633901.272475315.1517950.365862
6TAXB3333AAPL3.140ST156.15490.311000.000000-3.1400000.000000-490.3110000.000000
7TAXB3333VIGAXNaNST68.020.0000017.00000017.0000001156.3400001156.3400000.012479
8TAXB3333VSMAXNaNST62.250.000009.0000009.000000560.250000560.2500000.006046
9RIRA2222VSEQXNaNST26.770.0000022.00000022.000000588.940000588.9400000.006356
10RIRA2222VWIGXNaNST24.760.00000190.000000190.0000004704.4000004704.4000000.050770
11TAXB3333VTRIXNaNST31.910.00000147.000000147.0000004690.7700004690.7700000.050623
12RIRA1111VTABXNaNBD21.660.0000064.00000064.0000001386.2400001386.2400000.014960
13RIRA1111VGSLXNaNRE105.060.0000044.00000044.0000004622.6400004622.6400000.049887
14RIRA2222VNQINaNRE52.050.0000017.00000017.000000884.850000884.8500000.009549
15TAXB3333VDENaNST77.440.0000036.00000036.0000002787.8400002787.8400000.030086
17TAXB3333GLDNaNCS120.570.000008.0000008.000000964.560000964.5600000.010565
18RIRA1111GLDNaNCS120.570.000002.0000002.000000241.140000241.1400000.003749
\n", "
" ], "text/plain": [ " accounttype accountid ticker shares assetclass close value \\\n", "0 RIRA 1111 VBTLX 913.483 BD 10.39 9491.08837 \n", "1 RIRA 2222 VBTLX 772.407 BD 10.39 8025.30873 \n", "2 RIRA 1111 VTIAX 514.298 ST 25.17 12944.88066 \n", "3 RIRA 1111 VTSAX 151.121 ST 61.08 9230.47068 \n", "4 RIRA 2222 VTSAX 151.578 ST 61.08 9258.38424 \n", "5 TAXB 3333 VTSAX 549.871 ST 61.08 33586.12068 \n", "6 TAXB 3333 AAPL 3.140 ST 156.15 490.31100 \n", "7 TAXB 3333 VIGAX NaN ST 68.02 0.00000 \n", "8 TAXB 3333 VSMAX NaN ST 62.25 0.00000 \n", "9 RIRA 2222 VSEQX NaN ST 26.77 0.00000 \n", "10 RIRA 2222 VWIGX NaN ST 24.76 0.00000 \n", "11 TAXB 3333 VTRIX NaN ST 31.91 0.00000 \n", "12 RIRA 1111 VTABX NaN BD 21.66 0.00000 \n", "13 RIRA 1111 VGSLX NaN RE 105.06 0.00000 \n", "14 RIRA 2222 VNQI NaN RE 52.05 0.00000 \n", "15 TAXB 3333 VDE NaN ST 77.44 0.00000 \n", "17 TAXB 3333 GLD NaN CS 120.57 0.00000 \n", "18 RIRA 1111 GLD NaN CS 120.57 0.00000 \n", "\n", " new_shares final_shares_chg new_value new_value_chg \\\n", "0 170.078225 -743.404775 1767.112759 -7723.975611 \n", "1 143.811775 -628.595225 1494.204341 -6531.104389 \n", "2 561.298000 47.000000 14127.870660 1182.990000 \n", "3 152.539028 1.418028 9317.083821 86.613141 \n", "4 153.000316 1.422316 9345.259305 86.875065 \n", "5 555.030656 5.159656 33901.272475 315.151795 \n", "6 0.000000 -3.140000 0.000000 -490.311000 \n", "7 17.000000 17.000000 1156.340000 1156.340000 \n", "8 9.000000 9.000000 560.250000 560.250000 \n", "9 22.000000 22.000000 588.940000 588.940000 \n", "10 190.000000 190.000000 4704.400000 4704.400000 \n", "11 147.000000 147.000000 4690.770000 4690.770000 \n", "12 64.000000 64.000000 1386.240000 1386.240000 \n", "13 44.000000 44.000000 4622.640000 4622.640000 \n", "14 17.000000 17.000000 884.850000 884.850000 \n", "15 36.000000 36.000000 2787.840000 2787.840000 \n", "17 8.000000 8.000000 964.560000 964.560000 \n", "18 2.000000 2.000000 241.140000 241.140000 \n", "\n", " final_allocation \n", "0 0.019071 \n", "1 0.016125 \n", "2 0.152468 \n", "3 0.100550 \n", "4 0.100854 \n", "5 0.365862 \n", "6 0.000000 \n", "7 0.012479 \n", "8 0.006046 \n", "9 0.006356 \n", "10 0.050770 \n", "11 0.050623 \n", "12 0.014960 \n", "13 0.049887 \n", "14 0.009549 \n", "15 0.030086 \n", "17 0.010565 \n", "18 0.003749 " ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "#Review our final portfolio with recommended buys/sells in 'final_shares_chg' column\n", "if dport is not None:\n", " #Cleanup\n", " dport.drop(columns=['order'],inplace=True)\n", " dport = dport[['accounttype','accountid','ticker','shares','assetclass','close','value','new_shares','final_shares_chg','new_value','new_value_chg','final_allocation']]\n", " display(dport)\n", "else:\n", " port = port[['accounttype','accountid','ticker','shares','assetclass','close','value','new_shares','final_shares_chg','new_value','new_value_chg','final_allocation']]\n", " display(port)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Conclusion\n", "Through this portfolio rebalancing demonstration using Pandas you can see we have achieved a rebalanced portfolio very closely approximating our desired target allocation. We can see how GLD was added as a new security, and AAPL was removed from the portfolio. The remaining securities were iteratively bought or sold as required by our target allocation. We accounted for whole-share rounding because most of our assets in this sample are index funds. This simple rebalancer can be adapted to your needs and I urge you grab the code and see if you can improve upon it. I welcome your thoughts or feedback in the comments." ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.6.5" } }, "nbformat": 4, "nbformat_minor": 2 }