diff options
Diffstat (limited to 'python/notebooks')
| -rw-r--r-- | python/notebooks/Allocation Reports.ipynb | 182 | ||||
| -rw-r--r-- | python/notebooks/Reto Report.ipynb | 78 |
2 files changed, 42 insertions, 218 deletions
diff --git a/python/notebooks/Allocation Reports.ipynb b/python/notebooks/Allocation Reports.ipynb index e64b84b0..69e6e620 100644 --- a/python/notebooks/Allocation Reports.ipynb +++ b/python/notebooks/Allocation Reports.ipynb @@ -27,56 +27,6 @@ "metadata": {}, "outputs": [], "source": [ - "#PNL Allocation\n", - "report_date = datetime.date.today() - off.MonthEnd(1)\n", - "report_date" - ] - }, - { - "cell_type": "code", - "execution_count": null, - "metadata": {}, - "outputs": [], - "source": [ - "#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", - "m_pnl = m_pnl.reset_index().merge(strats, on=['strat', 'custacctname'], how='left')\n", - "undefined = m_pnl[m_pnl.pnl.isna()].groupby(['strat', 'custacctname']).last()" - ] - }, - { - "cell_type": "code", - "execution_count": null, - "metadata": {}, - "outputs": [], - "source": [ - "#Get PNL Allocation\n", - "#Input latest NAVS to: '/home/serenitas/edwin/Python/subscription_fee_data.csv'\n", - "pnl_alloc = m_pnl.groupby(['date', 'pnl']).sum()\n", - "pnl_alloc = pnl_alloc.join(nav.begbooknav)\n", - "pnl_alloc['strat_return'] = pnl_alloc.mtdtotalbookpl / pnl_alloc.begbooknav" - ] - }, - { - "cell_type": "code", - "execution_count": null, - "metadata": {}, - "outputs": [], - "source": [ - "#rolling 12 months PNL per strategy - copy to RiskMonitor\n", - "start_date = report_date - pd.tseries.offsets.MonthEnd(11)\n", - "rolling_return = pnl_alloc[start_date:report_date].groupby('pnl').sum()['strat_return']\n", - "rolling_return.to_clipboard()" - ] - }, - { - "cell_type": "code", - "execution_count": null, - "metadata": {}, - "outputs": [], - "source": [ "#Plot this month's PNL\n", "pnl_alloc_last_month = pnl_alloc.xs(report_date)\n", "ax = pnl_alloc_last_month['strat_return'].plot(kind='bar', figsize = (6,6), width = .35)\n", @@ -132,52 +82,6 @@ "metadata": {}, "outputs": [], "source": [ - "#Average Portfolio Sales Turnover - as of last monthend from today\n", - "#(total Bond Sales Proceeds + paydown)/average starting 12 months NAV\n", - "nav = go.get_net_navs()\n", - "sql_string = \"SELECT * FROM bonds where buysell = 'False'\"\n", - "df = pd.read_sql_query(sql_string, dbengine('dawndb'),\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", - "#Now get portfolio paydown per month\n", - "portfolio = go.get_portfolio()\n", - "portfolio = portfolio[(portfolio.custacctname == 'V0NSCLMAMB') &\n", - " (portfolio.port == 'MORTGAGES') &\n", - " (portfolio.identifier != 'USD') &\n", - " (portfolio.endqty != 0)]\n", - "portfolio = portfolio.set_index('identifier', append=True)\n", - "portfolio = portfolio['endqty'].groupby(['identifier', 'periodenddate']).sum()\n", - "portfolio = portfolio.reset_index('identifier') \n", - "sql_string = \"SELECT * from cashflow_history\"\n", - "cf = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates=['date'],\n", - " index_col=['date']).sort_index()\n", - "df_1 = pd.merge_asof(cf, portfolio.sort_index(), left_index=True, right_index=True, by='identifier')\n", - "df_1 = df_1.dropna(subset=['endqty'])\n", - "df_1 = df_1[(df_1.principal_bal != 0) & (df_1.principal != 0)]\n", - "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[12:].plot()\n", - "turnover[-1]" - ] - }, - { - "cell_type": "code", - "execution_count": null, - "metadata": {}, - "outputs": [], - "source": [ - "turnover.to_clipboard(sep='\\t')" - ] - }, - { - "cell_type": "code", - "execution_count": null, - "metadata": {}, - "outputs": [], - "source": [ "#Number of bond positions by strategy by month\n", "df = go.get_portfolio()\n", "df = df[(df.custacctname == 'V0NSCLMAMB') &\n", @@ -243,92 +147,14 @@ "execution_count": null, "metadata": {}, "outputs": [], - "source": [ - "#Positions and Risks\n", - "rmbs_pos = go.hist_pos(asset_class = 'rmbs')\n", - "clo_pos = go.hist_pos(asset_class = 'clo')" - ] - }, - { - "cell_type": "code", - "execution_count": null, - "metadata": {}, - "outputs": [], - "source": [ - "#Plot Duration and Yield to Maturity of RMBS Portfolio\n", - "#Filtering out RMBS Bonds:\n", - "#df = df[df.strat != 'MTG_FP']\n", - "bond_dur, bond_yield = {}, {}\n", - "for d, g in rmbs_pos.groupby(pd.Grouper(freq='M')):\n", - " bond_dur[d] = sum(g.curr_ntl * g.moddur)/sum(g.curr_ntl)\n", - " bond_yield[d] = sum(g.endbookmv * g.moddur * g.b_yield) /sum(g.endbookmv * g.moddur)\n", - "a = pd.Series(bond_dur)\n", - "b = pd.Series(bond_yield)\n", - "a.name = 'Duration'\n", - "b.name = 'Yield-to-maturity'\n", - "\n", - "fig = plt.figure()\n", - "ax0 = fig.add_subplot(111)\n", - "ax1 = ax0.twinx()\n", - "\n", - "a.plot(kind='line', color = 'r', ax=ax0, label = a.name, legend=True)\n", - "b.plot(kind='line', secondary_y=True, ax=ax1, label = b.name, legend=True)\n", - "\n", - "ax1.set_xlabel('date')\n", - "ax1.set_xlim([a.index.min(), a.index.max()])\n", - "ax0.set_ylabel('Duration')\n", - "ax1.set_ylabel('Yield-to-Maturity')\n", - "\n", - "ax0.legend(loc=2)\n", - "fig.tight_layout()" - ] - }, - { - "cell_type": "code", - "execution_count": null, - "metadata": {}, - "outputs": [], - "source": [ - "a.to_clipboard()\n", - "#b.to_clipboard()" - ] - }, - { - "cell_type": "code", - "execution_count": null, - "metadata": {}, - "outputs": [], - "source": [ - "#RMBS Risk - need RMBS Positions and Risks\n", - "sql_string = \"select date, duration, series from on_the_run where index = 'HY'\"\n", - "duration = pd.read_sql_query(sql_string, Sengine, parse_dates=['date'], index_col=['date'])\n", - "rmbs_pos = pd.merge_asof(rmbs_pos.sort_index(), duration, left_index=True, right_index=True)\n", - "rmbs_pos['hy_equiv'] = rmbs_pos.delta_yield/rmbs_pos.duration * 100\n", - "rmbs_pos.groupby('timestamp').sum()\n", - "#hy_equiv.plot()" - ] + "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], - "source": [ - "#Calculate Average Holding Period of RMBS portfolio - Need RMBS Positions and Risks\n", - "sql_string = \"SELECT cusip, trade_date FROM bonds where buysell= True\"\n", - "df_trades = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates=['trade_date'])\n", - "df_trades['trade_date2'] = df_trades['trade_date']\n", - "df_with_trades = pd.merge_asof(rmbs_pos.sort_index(), df_trades.set_index('trade_date').sort_index(), \n", - " left_index=True,\n", - " right_index=True,\n", - " by='cusip')\n", - "df_with_trades['hold'] = (df_with_trades.index - df_with_trades.trade_date2).dt.days/365\n", - "holding_period = df_with_trades.groupby('timestamp').apply(lambda df: sum(df.endbookmv * df.hold)/sum(df.endbookmv))\n", - "ax = holding_period.plot(legend=False, title='Average Holding Period')\n", - "ax.set_xlabel('date')\n", - "ax.set_ylabel('Years')\n", - "holding_period[-1]" - ] + "source": [] }, { "cell_type": "code", @@ -387,9 +213,9 @@ "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", - "version": "3.7.1" + "version": "3.7.3" } }, "nbformat": 4, - "nbformat_minor": 2 + "nbformat_minor": 4 } diff --git a/python/notebooks/Reto Report.ipynb b/python/notebooks/Reto Report.ipynb index 8440088c..f266cd63 100644 --- a/python/notebooks/Reto Report.ipynb +++ b/python/notebooks/Reto Report.ipynb @@ -20,6 +20,7 @@ "from utils.db import dbconn, dbengine\n", "\n", "from risk.tranches import get_tranche_portfolio\n", + "from risk.swaptions import get_swaption_portfolio\n", "from risk.bonds import subprime_risk, clo_risk, crt_risk\n", "\n", "dawn_engine = dbengine('dawndb')" @@ -151,6 +152,31 @@ "metadata": {}, "outputs": [], "source": [ + "################################## Calculate Historical Bond Duration/Yield\n", + "mysql_engine = dbengine('rmbs_model')\n", + "end_date = pd.datetime.today() - MonthEnd(1)\n", + "dates = pd.date_range(datetime.date(2013, 1, 30), end_date, freq=\"M\")\n", + "calc_df = pd.DataFrame()\n", + "sql_string = (\"SELECT distinct timestamp::date FROM priced where normalization = 'current_notional' and model_version = 1 \"\n", + " \"and date(timestamp) < %s and date(timestamp) > %s order by timestamp desc\")\n", + "with dbconn('etdb') as etconn, dbconn('dawndb') as dawnconn:\n", + " for d in dates:\n", + " timestamps = pd.read_sql_query(sql_string, dawn_engine, parse_dates=[\"timestamp\"], params=[d, d - pd.tseries.offsets.DateOffset(15, \"D\")])\n", + " calc_df = calc_df.append(subprime_risk(d.date(), dawnconn, mysql_engine, timestamps.iloc[0,0].date()))\n", + "calc_df=calc_df.reset_index().set_index('date')\n", + "calc_df = calc_df.dropna(subset=['bond_yield', 'hy_equiv']) \n", + "bond_stats = pd.DataFrame()\n", + "for d, g in calc_df.groupby(pd.Grouper(freq='M')):\n", + " bond_stats.loc[d, 'dur'] = sum(g.notional * g.factor * g.modDur)/sum(g.notional * g.factor)\n", + " bond_stats.loc[d, 'yield'] = sum(g.usd_market_value * g.modDur * g.bond_yield) /sum(g.usd_market_value * g.modDur)" + ] + }, + { + "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", @@ -188,41 +214,33 @@ "source": [ "#tranche positions\n", "conn = dawn_engine.raw_connection()\n", - "portf = get_tranche_portfolio(position_date, conn, False, 'SERCGMAST')\n", - "\n", - "#swaption positions\n", - "swaption_sql_string = (\"select id, folder, expiration_date from swaptions where expiration_date > %s \"\n", - " \"AND swap_type = 'CD_INDEX_OPTION' \"\n", - " \"AND trade_date <= %s AND termination_date IS NULL\")\n", + "mysql_engine = dbengine('rmbs_model')\n", + "mysqlcrt_engine = dbengine('crt')\n", "\n", - "with conn.cursor() as c:\n", - " c.execute(swaption_sql_string, (position_date, position_date))\n", - " for trade_id, strat, expiration_date in c:\n", - " if expiration_date > shock_date:\n", - " portf.add_trade(BlackSwaption.from_tradeid(trade_id), (strat, trade_id))\n", - "conn.close()\n", + "portf = get_tranche_portfolio(position_date, conn, False, 'SERCGMAST')\n", + "s_portf = get_swaption_portfolio(position_date, conn)\n", + "for t, id in zip(s_portf.trades, s_portf.trade_ids):\n", + " portf.add_trade(t, id)\n", "\n", "#index positions\n", "df = pd.read_sql_query(\"SELECT * from list_cds_positions_by_strat(%s)\",\n", " dawn_engine, params=(position_date,))\n", - "df_curve = df[df.folder.str.contains(\"CURVE\")]\n", "df_no_curve = df[~df.folder.str.contains(\"CURVE\")]\n", "for t in df_no_curve.itertuples(index=False):\n", " portf.add_trade(CreditIndex(redcode=t.security_id, maturity=t.maturity, notional=t.notional),\n", " (t.folder, t.security_desc))\n", " \n", "#separately add in curve delta\n", + "df_curve = df[df.folder.str.contains(\"CURVE\")]\n", "curve_portf = Portfolio([CreditIndex(redcode=t.security_id, maturity=t.maturity, notional=t.notional)\n", " for t in df_curve.itertuples(index=False)])\n", "curve_portf.value_date = spread_date\n", "curve_portf.mark()\n", + "\n", "portf.add_trade(CreditIndex('HY', on_the_run('HY', spread_date), '5yr', \n", " value_date=spread_date, \n", " notional=curve_portf.hy_equiv), ('curve_trades', ''))\n", "\n", - "mysql_engine = dbengine('rmbs_model')\n", - "mysqlcrt_engine = dbengine('crt')\n", - "\n", "#get bond risks:\n", "with dbconn('etdb') as etconn, dbconn('dawndb') as dawnconn:\n", " rmbs_pos = subprime_risk(position_date, dawnconn, mysql_engine)\n", @@ -269,7 +287,7 @@ "outputs": [], "source": [ "################################### Run set of scenario\n", - "spread_shock = np.round(np.arange(-.2, 1, .1), 3)\n", + "spread_shock = np.round(np.arange(-.2, 1, .05), 3)\n", "scens = run_portfolio_scenarios(portf, date_range, params=['pnl', 'delta'],\n", " spread_shock=spread_shock,\n", " vol_shock=vol_shock,\n", @@ -291,29 +309,9 @@ "\n", "synthetic = scenarios[['options', 'tranches', 'curve_trades']]\n", "synthetic['total'] = synthetic.sum(axis = 1)\n", - "synthetic.plot()" + "nav = go.get_net_navs()\n", + "(synthetic/nav.endbooknav[-1]).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": { @@ -336,5 +334,5 @@ } }, "nbformat": 4, - "nbformat_minor": 2 + "nbformat_minor": 4 } |