diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/risk/bonds.py | 61 |
1 files changed, 32 insertions, 29 deletions
diff --git a/python/risk/bonds.py b/python/risk/bonds.py index 63ecb255..4bb3ad2d 100644 --- a/python/risk/bonds.py +++ b/python/risk/bonds.py @@ -1,11 +1,19 @@ import pandas as pd import numpy as np +from enum import Enum, auto from utils.db import dbengine from yieldcurve import YC from quantlib.termstructures.yield_term_structure import YieldTermStructure +class AssetClass(Enum): + Subprime = auto() + CLO = auto() + CSO = auto() + CRT = auto() + + def latest_sim(date, engine): sql_string = ( "SELECT model_id_sub FROM model_versions " @@ -55,7 +63,7 @@ def get_df(date, engine): def subprime_risk(date, conn, engine): df = get_df(date, engine) - df_pos = get_portfolio(date, conn, "Subprime") + df_pos = get_portfolio(date, conn, AssetClass.Subprime) df_pv = df.xs("pv", axis=1, level=0) df_pv.columns = ["pv1", "pv2", "pv3"] df_pv_perct = df.xs("PV", axis=1, level=0) @@ -117,24 +125,30 @@ def subprime_risk(date, conn, engine): return df_calc -def get_portfolio(date, conn, asset_class, fund="SERCGMAST"): +def get_portfolio(date, conn, asset_class: AssetClass, fund="SERCGMAST"): df = pd.read_sql_query( "SELECT * FROM risk_positions(%s, %s, %s)", conn, - params=(date, asset_class, fund), + params=(date, asset_class.name, fund), ) - df["cusip"] = df.identifier.str.slice(0, 9) - df = df.set_index("cusip") - return df + if asset_class is AssetClass.CLO: + with conn.cursor() as c: + c.execute( + "SELECT cusip, identifier FROM securities " "WHERE asset_class = 'CLO'" + ) + cusip_map = {identifier: cusip for cusip, identifier in c.fetchall()} + df["cusip"] = df["identifier"].replace(cusip_map) + else: # only CLOs used ISIN for now + df["cusip"] = df.identifier.str.slice(0, 9) + return df.set_index("cusip") -def crt_risk(date, conn, engine): - df = get_portfolio(date, conn, "Subprime") - df = df[df["strategy"].str.contains("CRT")].dropna() +def crt_risk(date, dawn_conn, engine): + df = get_portfolio(date, dawn_conn, AssetClass.CRT) df_model = pd.read_sql_query( - "SELECT * from priced_at_market where " - "timestamp BETWEEN %s AND date_add(%s, INTERVAL 1 DAY) " - "and model_des = 'hpi3_ir3'", + "SELECT * from priced_at_market WHERE " + "timestamp BETWEEN %s AND date_add(%s, INTERVAL 1 DAY) " + "AND model_des = 'hpi3_ir3'", engine, "cusip", params=(date, date), @@ -145,25 +159,14 @@ def crt_risk(date, conn, engine): return df -def clo_risk(date, conn, conn_1): - df = get_portfolio(date, conn, "CLO") - - sql_string = ( - "select distinct cusip, identifier from bonds where asset_class = 'CLO'" - ) - cur = conn.cursor() - cur.execute(sql_string) - cusip_map = {identifier: cusip for cusip, identifier in cur.fetchall()} - df["cusip"] = df["identifier"].replace(cusip_map) - placeholders = ",".join(["%s"] * (1 + len(df))) - sql_string = f"SELECT * FROM historical_cusip_risk({placeholders})" +def clo_risk(date, dawn_conn, et_conn): + df = get_portfolio(date, dawn_conn, AssetClass.CLO) + placeholders = ",".join(["%s"] * df.shape[0]) + sql_string = f"SELECT * FROM historical_cusip_risk(%s, {placeholders})" model = pd.read_sql_query( - sql_string, - conn_1, - parse_dates=["pricingdate"], - params=[date, *df["cusip"].tolist()], + sql_string, et_conn, parse_dates=["pricingdate"], params=(date, *df.index) ) - model.index = df["cusip"] + model.index = df.index df = df.join(model, lsuffix="mark") df["curr_ntl"] = df["notional"] * df["factor"] df["hy_equiv"] = df["curr_ntl"] * df["delta"] |