diff options
| -rw-r--r-- | python/notebooks/Reto Report.ipynb | 92 |
1 files changed, 39 insertions, 53 deletions
diff --git a/python/notebooks/Reto Report.ipynb b/python/notebooks/Reto Report.ipynb index cf519316..f1a8d9e8 100644 --- a/python/notebooks/Reto Report.ipynb +++ b/python/notebooks/Reto Report.ipynb @@ -134,47 +134,47 @@ "metadata": {}, "outputs": [], "source": [ - "#Current tranche and swaptions positions\n", - "t_sql_string = (\"SELECT id, folder, sum(notional * case when protection='Buyer' then -1 else 1 end) \"\n", - " \"OVER (partition by security_id, attach) AS ntl_agg \"\n", - " \"FROM cds WHERE swap_type='CD_INDEX_TRANCHE' AND termination_cp IS NULL \"\n", - " \"AND trade_date <= %s\")\n", - "swaption_sql_string = (\"select id, folder, expiration_date from swaptions where date(expiration_date) \"\n", - " \"> %s and swap_type = 'CD_INDEX_OPTION' \"\n", - " \"AND trade_date <= %s AND termination_date iS NULL\")\n", - "index_sql_string = (\"SELECT id, folder, sum(notional * case when protection='Buyer' then -1 else 1 end) \"\n", - " \"OVER (partition by security_id, attach) AS ntl_agg \"\n", - " \"FROM cds WHERE swap_type='CD_INDEX' AND termination_cp IS null \"\n", - " \"AND trade_date <= %s AND maturity > %s\")\n", + "#tranche positions\n", + "df_tranches = pd.read_sql_query(\"SELECT * from list_tranche_positions_by_strat(%s)\",\n", + " dawn_engine, params=(position_date,))\n", + "portf = Portfolio([DualCorrTranche(redcode=t.security_id, maturity=t.maturity,\n", + " notional=t.notional,\n", + " tranche_running=t.fixed_rate*100,\n", + " attach=t.orig_attach,\n", + " detach=t.orig_detach,\n", + " corr_attach=None, corr_detach=None) for t in df_tranches.itertuples()])\n", + "portf.trade_ids = [(strat, f\"{t.index_type} {t.series} {t.tenor} {t.attach}-{t.detach}\")\n", + " for t, strat in zip(portf.trades, df_tranches.folder)]\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", "conn = dawn_engine.raw_connection()\n", "with conn.cursor() as c:\n", - " c.execute(t_sql_string, (position_date,))\n", - " t_trades = [[dealid, f\"{folder}_{dealid}\"] for dealid, folder, ntl in c if ntl != 0]\n", " c.execute(swaption_sql_string, (position_date, position_date))\n", - " swaption_trades = [[dealid, f\"{folder}_{dealid}\", expiration_date] for dealid, folder, expiration_date in c]\n", - " c.execute(index_sql_string, (position_date, position_date))\n", - " index_trades = [[dealid, f\"{folder}_{dealid}\"] for dealid, folder, ntl in c if ntl != 0]\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", "\n", - "index_curve = list(filter(lambda x: \"CURVE\" in x[1], index_trades))\n", - "index_no_curve = list(filter(lambda x: \"CURVE\" not in x[1], index_trades))\n", - "\n", - "portf = Portfolio([DualCorrTranche.from_tradeid(dealid) for dealid, _ in t_trades],\n", - " [trade_id for _, trade_id in t_trades])\n", - "for trade_id, name, expiration_date in swaption_trades:\n", - " if expiration_date > shock_date:\n", - " portf.add_trade(BlackSwaption.from_tradeid(trade_id), name)\n", - "for trade_id, name in index_no_curve:\n", - " portf.add_trade(CreditIndex.from_tradeid(trade_id), name)\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", - "curve_portf = Portfolio([CreditIndex.from_tradeid(dealid) for dealid, _ in index_curve], \n", - " [trade_id for _, trade_id in index_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", "portf.add_trade(CreditIndex('HY', on_the_run('HY'), '5yr', \n", - " value_date = spread_date, \n", - " notional = -curve_portf.hy_equiv), 'curve_trades')\n", + " value_date=spread_date, \n", + " notional=-curve_portf.hy_equiv), ('curve_trades', ''))\n", " \n", "#get bond risks:\n", "rmbs_pos = go.rmbs_pos(position_date)\n", @@ -186,7 +186,7 @@ "notional = rmbs_pos['hy_equiv'].sum() + clo_pos['hy_equiv'].sum()\n", "portf.add_trade(CreditIndex('HY', on_the_run('HY'), '5yr', \n", " value_date = spread_date, \n", - " notional = -notional), 'bonds')\n", + " notional = -notional), ('bonds', ''))\n", " \n", "portf.value_date = spread_date\n", "portf.mark(interp_method=\"bivariate_linear\")\n", @@ -208,12 +208,12 @@ " corr_shock=corr_shock,\n", " vol_surface=vol_surface)\n", "\n", - "attrib = scens.stack(level=0).reset_index()\n", - "attrib['strategy'] = attrib['level_4'].str.split('_\\d+', expand=True).iloc[:,0]\n", - "attrib = attrib.set_index(['spread_shock', 'strategy', 'corr_shock'])\n", - "attrib = attrib.groupby(['spread_shock', 'strategy', 'corr_shock']).sum()\n", - "\n", - "results = attrib.xs((widen[2], -0.0), level = ['spread_shock','corr_shock'])" + "attrib = (scens.\n", + " reset_index(level=['date'], drop=True).\n", + " groupby(level=0, axis=1).sum())\n", + "attrib.columns.name = 'strategy'\n", + "results = attrib.xs((widen[2], 0.), level=['spread_shock', 'corr_shock']).unstack('strategy')\n", + "#results.to_clipboard(header=True)" ] }, { @@ -222,20 +222,6 @@ "metadata": {}, "outputs": [], "source": [] - }, - { - "cell_type": "code", - "execution_count": null, - "metadata": {}, - "outputs": [], - "source": [] - }, - { - "cell_type": "code", - "execution_count": null, - "metadata": {}, - "outputs": [], - "source": [] } ], "metadata": { |