diff options
| -rw-r--r-- | python/notebooks/Reto Report.ipynb | 83 |
1 files changed, 75 insertions, 8 deletions
diff --git a/python/notebooks/Reto Report.ipynb b/python/notebooks/Reto Report.ipynb index 1b8211f4..8440088c 100644 --- a/python/notebooks/Reto Report.ipynb +++ b/python/notebooks/Reto Report.ipynb @@ -43,7 +43,7 @@ "metadata": {}, "outputs": [], "source": [ - "#Find the strategies that are not defined: undefined needs to be mapped in strat_map\n", + "################################### Find the strategies that are not defined: undefined needs to be mapped in strat_map\n", "strats = pd.read_csv('/home/serenitas/edwin/Python/strat_map.csv')\n", "nav = go.get_net_navs()\n", "m_pnl = go.get_monthly_pnl(['strat', 'custacctname'])\n", @@ -66,14 +66,17 @@ "metadata": {}, "outputs": [], "source": [ - "#Average Portfolio Sales Turnover - as of last monthend from today\n", + "################################### Average Portfolio Sales Turnover - as of last monthend from today\n", "#(total Bond Sales Proceeds + paydown)/average starting 12 months NAV\n", + "#Actually: Rolling 12 months sum of (total bond sales proceeds + paydown)/monthly NAV\n", "nav = go.get_net_navs()\n", "sql_string = \"SELECT * FROM bonds WHERE buysell IS False\"\n", "df = pd.read_sql_query(sql_string, dawn_engine,\n", " parse_dates={'lastupdate':{'utc':True}, 'trade_date': {}, 'settle_date':{}},\n", " index_col = 'trade_date')\n", "df = df.groupby(pd.Grouper(freq='M')).sum()\n", + "#Average traded volume (Bonds only)\n", + "\n", "#Now get portfolio paydown per month\n", "portfolio = go.get_portfolio()\n", "portfolio = portfolio[(portfolio.custacctname == 'V0NSCLMAMB') &\n", @@ -92,7 +95,8 @@ "df_1['paydown'] = df_1.apply(lambda df: df.endqty/df.principal_bal * df.principal, axis=1)\n", "paydowns = df_1.paydown.groupby(pd.Grouper(freq='M')).sum()\n", "temp = pd.concat([paydowns, df.principal_payment, df.accrued_payment], axis=1).fillna(0)\n", - "turnover = temp.rolling(12).sum().sum(axis=1)/ nav.begbooknav.rolling(12).mean()\n", + "turnover = (temp.sum(axis=1)/nav.begbooknav).rolling(12).sum()\n", + "#turnover = temp.rolling(12).sum().sum(axis=1)/ nav.begbooknav.rolling(12).mean()\n", "turnover[12:].plot()\n", "turnover[-1]" ] @@ -103,6 +107,51 @@ "metadata": {}, "outputs": [], "source": [ + "################################### Average Monthly Traded Volume\n", + "nav = go.get_net_navs()\n", + "sql_string = \"SELECT * FROM bonds\"\n", + "df = pd.read_sql_query(sql_string, dawn_engine,\n", + " parse_dates={'lastupdate':{'utc':True}, 'trade_date': {}, 'settle_date':{}},\n", + " index_col = 'trade_date')\n", + "df = df.groupby(pd.Grouper(freq='M')).sum()\n", + "volume = df.principal_payment/nav.endbooknav\n", + "volume.mean()" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": {}, + "outputs": [], + "source": [ + "################################### Average Holding Period\n", + "#Time series of bond portfolio age (portfolio date - latest buy date of position) - weighted by MV of all bonds.\n", + "#Problem is if we buy the same position again it resets to the holding period to 0\n", + "nav = go.get_net_navs()\n", + "sql_string = \"SELECT * FROM bonds order by trade_date desc\"\n", + "df = pd.read_sql_query(sql_string, dawn_engine,\n", + " parse_dates={'lastupdate':{'utc':True}, 'trade_date': {}, 'settle_date':{}},\n", + " index_col = 'trade_date')\n", + "buys = df[df.buysell == True].sort_index()\n", + "buys['buy_date'] = buys.index\n", + "#get portfolio \n", + "port = go.get_portfolio()\n", + "port.sort_index(inplace=True)\n", + "buy_dates = pd.merge_asof(port, buys[['buy_date', 'identifier']], left_index=True, right_index=True,by='identifier', direction='backward')\n", + "buy_dates = buy_dates[['identifier', 'endbooknav','buy_date']][~buy_dates.buy_date.isna()]\n", + "buy_dates['hold_days'] = (buy_dates.index - buy_dates.buy_date)/np.timedelta64(1, 'D')\n", + "def weighted_average(df):\n", + " return np.average(df.hold_days,weights=df.endbooknav)\n", + "hold_period = buy_dates.groupby('periodenddate').apply(func = weighted_average)" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": {}, + "outputs": [], + "source": [ + "################################### Calculate stress scenario \n", "position_date = (datetime.date.today() - BDay(1)).date()\n", "shock_date = (datetime.date.today() - BDay(1)).date()\n", "spread_date = shock_date\n", @@ -179,9 +228,6 @@ " rmbs_pos = subprime_risk(position_date, dawnconn, mysql_engine)\n", " clo_pos = clo_risk(position_date, dawnconn, etconn)\n", " crt_pos = crt_risk(position_date, dawnconn, mysqlcrt_engine)\n", - "duration = analytics._ontr.risky_annuity\n", - "rmbs_pos['hy_equiv'] = rmbs_pos['delta_yield']/duration * 100\n", - "crt_pos['hy_equiv'] = crt_pos['delta_yield']/duration * 100\n", "notional = rmbs_pos['hy_equiv'].sum() + clo_pos['hy_equiv'].sum() + crt_pos['hy_equiv'].sum()\n", "portf.add_trade(CreditIndex('HY', on_the_run('HY', spread_date), '5yr', \n", " value_date = spread_date, \n", @@ -222,8 +268,8 @@ "metadata": {}, "outputs": [], "source": [ - "#Run set of scenario\n", - "spread_shock = np.round(np.arange(-.2, 1, .025), 3)\n", + "################################### Run set of scenario\n", + "spread_shock = np.round(np.arange(-.2, 1, .1), 3)\n", "scens = run_portfolio_scenarios(portf, date_range, params=['pnl', 'delta'],\n", " spread_shock=spread_shock,\n", " vol_shock=vol_shock,\n", @@ -247,6 +293,27 @@ "synthetic['total'] = synthetic.sum(axis = 1)\n", "synthetic.plot()" ] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": {}, + "outputs": [], + "source": [] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": {}, + "outputs": [], + "source": [] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": {}, + "outputs": [], + "source": [] } ], "metadata": { |