diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/notebooks/Valuation Backtest.ipynb | 108 |
1 files changed, 89 insertions, 19 deletions
diff --git a/python/notebooks/Valuation Backtest.ipynb b/python/notebooks/Valuation Backtest.ipynb index dfddbf86..5e64c8e0 100644 --- a/python/notebooks/Valuation Backtest.ipynb +++ b/python/notebooks/Valuation Backtest.ipynb @@ -6,15 +6,15 @@ "metadata": {}, "outputs": [], "source": [ - "from utils.db import dbengine\n", + "from utils.db import dbconn, dbengine, serenitas_engine, dawn_engine\n", "\n", "import datetime\n", "import mark_backtest_underpar as mark\n", "import globeop_reports as ops\n", "import pandas as pd\n", "import matplotlib.pyplot as plt\n", - "\n", - "engine = dbengine('dawndb')" + "import numpy as np\n", + "import math" ] }, { @@ -236,20 +236,30 @@ "metadata": {}, "outputs": [], "source": [ - "#Portfolio MTM Gains/Loss/Net\n", - "df_pnl = ops.get_monthly_pnl()[:date][['mtdbookunrealmtm', 'mtdbookrealmtm']].sum(axis=1)\n", - "df_pnl.name = 'mtm'\n", - "r=[]\n", - "for d, g in df_pnl.reset_index('identifier').groupby(pd.Grouper(freq='M')):\n", - " sql_string = \"SELECT * FROM risk_positions(%s, 'Subprime') WHERE notional > 0\"\n", - " pos = pd.read_sql_query(sql_string, engine, params=[g.index[-1].date()])\n", - " pos.identifier = pos.identifier.str[:9]\n", - " pos = pos.join(df_pnl.groupby('identifier').cumsum().loc[g.index[-1]],\n", - " on='identifier')['mtm'] / nav.loc[d]\n", - " r.append([g.index[-1], pos[pos>=0].sum(), pos[pos<0].sum()])\n", - "summary = pd.DataFrame.from_records(r, index='date', columns=['date','gains','loss'])\n", - "summary['Net'] = summary.gains + summary.loss\n", - "summary.plot()" + "#Portfolio MTM Gains/Loss/Net each month\n", + "df_pnl = ops.get_monthly_pnl()\n", + "df_pnl_temp = df_pnl[:date][['mtdbookunrealmtm', 'mtdbookrealmtm']].sum(axis=1)\n", + "df_pnl_temp.name = 'mtm'\n", + "r={}\n", + "for d, g in df_pnl_temp.reset_index('identifier').groupby(pd.Grouper(freq='M')):\n", + " p = []\n", + " for a in ['Subprime', 'CLO', 'CSO', 'CRT']:\n", + " sql_string = \"SELECT * FROM risk_positions(%s, %s) WHERE notional > 0\"\n", + " p.append(pd.read_sql_query(sql_string, dawn_engine, params=[d.date(), a], index_col = ['identifier']))\n", + " r[d]= pd.concat(p).merge(g.groupby('identifier').sum(), left_index=True, right_index=True)\n", + "pos = pd.concat(r, names=['date','identifier'])\n", + "pos = pos.groupby('date').sum().merge(navs, left_index=True, right_index=True)\n", + "pos['mtm_ret'] = pos['mtm']/pos['endbooknav']" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": {}, + "outputs": [], + "source": [ + "#MTM in each portfolio\n", + "df_pnl.groupby(['identifier']).cumsum()" ] }, { @@ -258,7 +268,67 @@ "metadata": {}, "outputs": [], "source": [ - "summary.iloc[-1]" + "################################## How much unrealized gains in bonds?\n", + "#1) sum up all the (paid-down adjusted proceeds) of all buy trades up to the point of where current face = sum of all traded notionals (FIFO)\n", + "#2) (paid down adjusted proceeds) of each trade = principal_paid - paydown since purchase of that bond to the date\n", + "#3) cost basis = sum of all (paid down adjusted proceeds)\n", + "#4) Doesn't work with CSOs - their cashflows are not correctly recorded in bloomberg\n", + "fund = 'SERCGMAST'\n", + "globeop_val_rep = ops.get_portfolio()\n", + "navs = ops.get_net_navs()\n", + "all_cf = pd.read_sql_query(\"SELECT * FROM cashflow_history\", dawn_engine,\n", + " parse_dates=['date'],\n", + " index_col=['date']).sort_index()\n", + "all_trades = pd.read_sql_query(\"SELECT * FROM bonds where fund = %s order by trade_date desc\", dawn_engine,\n", + " parse_dates={'lastupdate':{'utc':True}, 'trade_date': {}, 'settle_date':{}},\n", + " index_col = 'trade_date', params=[fund])\n", + "all_trades['curr_notional'] = all_trades['principal_payment']/all_trades['price'] *100\n", + "\n", + "bonds = {}\n", + "monthend_val_rep = globeop_val_rep.groupby(pd.Grouper(freq=\"M\"), group_keys=False).apply(lambda df: df.loc[df.index[-1]])\n", + "for position_date, go_val in monthend_val_rep.groupby('periodenddate'):\n", + " for a in [\"Subprime\", \"CRT\", \"CLO\"]:\n", + " bonds[position_date, a] = pd.read_sql_query(\"select * from risk_positions(%s, %s, %s)\", dawn_engine,\n", + " params=[position_date.date(), a, fund], index_col = 'identifier').sort_index()\n", + "\n", + "bonds = pd.concat(bonds, names=['periodenddate','asset_class', 'identifier'])\n", + "bonds['curr_notional'] = bonds['notional'] * bonds['factor']\n", + "monthend_val_rep = monthend_val_rep.groupby(['periodenddate', 'identifier'])['endbookmv'].sum()\n", + "bonds = bonds.reset_index().merge(monthend_val_rep, \n", + " left_on=['periodenddate','identifier'], \n", + " right_on=['periodenddate','identifier'],\n", + " validate='1:1',\n", + " how='left')\n", + "\n", + "#can also use endbookmv in field to use official globeop values, but having a Repo looks like a loss \n", + "def gains_calc(pos, trades, cf, field='usd_market_value'):\n", + " bond_trade = trades[(trades.identifier == pos.identifier) & (trades.buysell==True)]\n", + " cost_basis = 0\n", + " for i, bt in bond_trade.iterrows():\n", + " bond_cf_hist = cf[cf.identifier==pos.identifier]\n", + " bond_cf_hist['beg_principal_bal'] = bond_cf_hist['principal_bal'].shift(1)\n", + " bond_cf_hist = bond_cf_hist[bt.settle_date:pos.periodenddate]\n", + " paydown = 0\n", + " if ~bond_cf_hist.empty:\n", + " bt.orig_curr_notional = bt.curr_notional\n", + " for i, bond_cf in bond_cf_hist.iterrows():\n", + " paydown = bt.curr_notional/bond_cf.beg_principal_bal * bond_cf.principal if bond_cf.beg_principal_bal > 0 else 0\n", + " bt.curr_notional -= paydown\n", + " bt.curr_notional = bt.orig_curr_notional\n", + " if len(pos.identifier) == 11:\n", + " ratio = np.max([0,np.min([1.0, pos.notional/bt.faceamount])])\n", + " pos.notional -= bt.faceamount\n", + " else:\n", + " ratio = np.max([0,np.min([1.0, pos.curr_notional/bt.curr_notional])]) if bt.curr_notional > 0 else 0\n", + " pos.curr_notional -= bt.curr_notional\n", + " adj_principal_payment = np.max([0,bt.principal_payment - paydown])\n", + " cost_basis += adj_principal_payment * ratio\n", + " return pos[field] - cost_basis\n", + " \n", + "bonds['gains'] = bonds.apply(lambda position: gains_calc(position, all_trades, all_cf), axis=1)\n", + "navs = navs.merge(bonds.groupby('periodenddate')['gains'].sum(), left_index=True, right_index=True)\n", + "navs['mtm_in_portf'] = navs['gains']/navs['begbooknav']\n", + "navs['mtm_in_portf'].plot()" ] }, { @@ -299,7 +369,7 @@ "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", - "version": "3.8.1" + "version": "3.8.5" } }, "nbformat": 4, |