diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/risk/__main__.py | 3 | ||||
| -rw-r--r-- | python/risk/bonds.py | 67 |
2 files changed, 64 insertions, 6 deletions
diff --git a/python/risk/__main__.py b/python/risk/__main__.py index c8308427..85c14ea2 100644 --- a/python/risk/__main__.py +++ b/python/risk/__main__.py @@ -4,7 +4,7 @@ import os import pandas as pd from . import dbconn, dbengine from pandas.tseries.offsets import BDay -from .bonds import subprime_risk, clo_risk, crt_risk +from .bonds import subprime_risk, clo_risk, crt_risk, insert_subprime_risk from analytics import init_ontr from .swaptions import get_swaption_portfolio, insert_swaption_portfolio from .tranches import get_tranche_portfolio, insert_tranche_portfolio @@ -39,6 +39,7 @@ with dbconn("dawndb") as conn: with dbconn("etdb") as etconn, dbconn("dawndb") as dawnconn: subprime = subprime_risk(workdate, dawnconn, mysql_engine) + insert_subprime_risk(df, dawnconn) clo = clo_risk(workdate, dawnconn, etconn) crt = crt_risk(workdate, dawnconn, mysqlcrt_engine) # portf = get_rmbs_portfolio(workdate, conn) diff --git a/python/risk/bonds.py b/python/risk/bonds.py index 5db1ec5e..4e15561e 100644 --- a/python/risk/bonds.py +++ b/python/risk/bonds.py @@ -107,16 +107,18 @@ def subprime_risk(date, conn, engine): lambda x: x if np.isnan(x) else float(yc.zero_rate(x)) ), delta_ir=df_calc.delta_ir_io + df_calc.delta_ir_po, - curr_ntl=df_calc.notional * df_calc.factor, + # use original notional for 0 factor bonds to calc yield + curr_ntl=df_calc.notional * df_calc.factor.apply(lambda x: 1 if x == 0 else x), # assume beta and ontr is initialized from analytics hy_equiv=( df_calc.delta_yield - / analytics._ontr.DV01 + / analytics._ontr.risky_annuity * analytics._beta["SUBPRIME"] - * 1e5 + * 1e2 * df_calc.local_market_value / df_calc.pv3 ), + date=date, ) df_calc.bond_yield += ( np.log(df_calc.pv1 * df_calc.curr_ntl / df_calc.local_market_value) @@ -127,6 +129,54 @@ def subprime_risk(date, conn, engine): return df_calc +def insert_subprime_risk(df, conn): + cols = [ + "cusip", + "pv1", + "pv2", + "pv3", + "modDur", + "pv5", + "pv25", + "pv50", + "pv75", + "pv95", + "delta_yield", + "wal", + "pv_io", + "pv_po", + "pv_RnW", + "delta_ir_io", + "delta_ir_po", + "delta_hpi", + "delta_RnW", + "delta_mult", + "v1pv_RnW", + "v1_lsdel", + "v1_hpidel", + "v1_irdel", + "pv_FB", + "bond_yield", + "hy_equiv", + "delta_ir", + "date", + ] + update_str = ",".join(f"{c} = EXCLUDED.{c}" for c in cols) + col_names = ",".join(f"{c}" for c in cols) + sql_str = ( + f"INSERT INTO subprime_risk ({col_names}) " + f"VALUES ({','.join(['%s'] * len(cols))}) " + "ON CONFLICT (date, cusip) DO UPDATE " + f"SET {update_str}" + ) + + df_temp = df.reset_index()[cols] + with conn.cursor() as c: + for _, row in df_temp.iterrows(): + c.execute(sql_str, (*row,)) + conn.commit() + + def get_portfolio(date, conn, asset_class: AssetClass, fund="SERCGMAST"): df = pd.read_sql_query( "SELECT * FROM risk_positions(%s, %s, %s)", @@ -156,8 +206,15 @@ def crt_risk(date, dawn_conn, engine): params=(date, date), ) df = df.join(df_model) - df["curr_ntl"] = df["notional"] * df["factor"] - df["delta_yield"] = df["curr_ntl"] * df["duration_FW"] + df["curr_ntl"] = df.notional * df.factor + df["hy_equiv"] = ( + df.duration_FW + / analytics._ontr.risky_annuity + * analytics._beta["CRT"] + * df.curr_ntl + ) + delta = {"CRT_SD": 1.0, "CRT_LD": 1.5, "CRT_LD_JNR": 3.0} + df.hy_equiv *= df["strategy"].replace(delta) return df |