diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/notebooks/Reto Report.ipynb | 22 | ||||
| -rw-r--r-- | python/risk/tranches.py | 45 |
2 files changed, 38 insertions, 29 deletions
diff --git a/python/notebooks/Reto Report.ipynb b/python/notebooks/Reto Report.ipynb index f45b54ce..43bcc0e4 100644 --- a/python/notebooks/Reto Report.ipynb +++ b/python/notebooks/Reto Report.ipynb @@ -110,7 +110,8 @@ "outputs": [], "source": [ "#Calculate amount of stress for reports\n", - "df = get_index_quotes('HY', list(range(on_the_run('HY') - 10, on_the_run('HY') + 1)),\n", + "from analytics.curve_trades import on_the_run\n", + "df = get_index_quotes('HY', list(range(on_the_run('HY', spread_date) - 10, on_the_run('HY', spread_date) + 1)),\n", " tenor=['5yr'], years=5)\n", "df = df.xs('5yr', level='tenor')['close_spread'].groupby(['date', 'series']).last()\n", "\n", @@ -130,22 +131,15 @@ "outputs": [], "source": [ "#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", + "from risk.tranches import get_tranche_portfolio\n", + "conn = dawn_engine.raw_connection()\n", + "portf = get_tranche_portfolio(position_date, conn, by_strat=True)\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", + "\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", @@ -215,9 +209,7 @@ "execution_count": null, "metadata": {}, "outputs": [], - "source": [ - "results.to_clipboard(header=True)" - ] + "source": [] } ], "metadata": { diff --git a/python/risk/tranches.py b/python/risk/tranches.py index 2c13daf7..4b13dc22 100644 --- a/python/risk/tranches.py +++ b/python/risk/tranches.py @@ -1,16 +1,33 @@ -from db import dbconn, dbengine from analytics import Portfolio, DualCorrTranche -import datetime -import pandas as pd +import logging -sql_string = ("SELECT id, sum(notional * case when protection='Buyer' then -1 else 1 end) " - "OVER (partition by security_id, attach) AS ntl_agg " - "FROM cds WHERE swap_type='CD_INDEX_TRANCHE' AND termination_cp IS NULL") -conn = dbconn('dawndb') -with conn.cursor() as c: - c.execute(sql_string) - trade_ids = [dealid for dealid, ntl in c if ntl != 0] -portf = Portfolio([DualCorrTranche.from_tradeid(dealid) for dealid in trade_ids], - trade_ids) -portf.value_date = datetime.date(2018, 10, 10) -portf.mark() +logger = logging.getLogger(__name__) + +def get_tranche_portfolio(date, conn, by_strat=False): + if by_strat: + sql_string = "SELECT * from list_tranche_positions_by_strat(%s)" + else: + sql_string = "SELECT dealid from list_tranche_positions(%s)" + with conn.cursor() as c: + c.execute(sql_string, (date,)) + if by_strat: + trade_ids = list(c) + else: + trade_ids = [dealid for dealid, in c] + if by_strat: + portf = Portfolio([DualCorrTranche(redcode=d['security_id'], + maturity=d['maturity'], + notional=d['notional'], + tranche_running=d['fixed_rate']*100, + attach=d['orig_attach'], + detach=d['orig_detach'], + corr_attach=None, corr_detach=None) for d in trade_ids]) + portf.trade_ids = [(d["folder"], + f"{t.index_type} {t.series} {t.tenor} {t.attach}-{t.detach}") + for d, t in zip(trade_ids, portf.trades)] + else: + portf = Portfolio([DualCorrTranche.from_tradeid(dealid) for dealid in trade_ids], + trade_ids) + portf.value_date = date + portf.mark() + return portf |