1 files changed, 170 insertions, 0 deletions
diff --git a/python/risk/bonds.py b/python/risk/bonds.py
new file mode 100644
index 00000000..63ecb255
--- /dev/null
+++ b/python/risk/bonds.py
@@ -0,0 +1,170 @@
+import pandas as pd
+import numpy as np
+
+from utils.db import dbengine
+from yieldcurve import YC
+from quantlib.termstructures.yield_term_structure import YieldTermStructure
+
+
+def latest_sim(date, engine):
+    sql_string = (
+        "SELECT model_id_sub FROM model_versions "
+        "JOIN model_versions_nonagency USING (model_id_sub) "
+        "JOIN simulations_nonagency USING (simulation_id) "
+        "WHERE (date(start_time) <= %s) AND (description = 'normal') "
+        "ORDER BY start_time DESC"
+    )
+    conn = engine.raw_connection()
+    c = conn.cursor()
+    c.execute(sql_string, (date,))
+    model_id_sub, = next(c)
+    c.close()
+    return model_id_sub
+
+
+def get_df(date, engine):
+    model_id_sub = latest_sim(date, engine)
+    df_prices = pd.read_sql_query(
+        "SELECT cusip, model_version, pv, modDur, delta_yield, "
+        "wal, pv_io, pv_po, pv_RnW, delta_ir_io, delta_ir_po, "
+        "delta_hpi, delta_RnW, delta_mult, delta_ir, pv_FB "
+        "FROM priced WHERE "
+        "timestamp BETWEEN %s  AND date_add(%s, INTERVAL 1 DAY) "
+        "AND model_id_sub=%s "
+        "AND normalization='current_notional'",
+        engine,
+        ["cusip", "model_version"],
+        params=(date, date, model_id_sub),
+    )
+    df_percentiles = pd.read_sql_query(
+        "SELECT cusip, PV, percentile "
+        "FROM priced_percentiles WHERE "
+        "timestamp BETWEEN %s AND date_add(%s, INTERVAL 1 DAY) "
+        "AND model_version=3 "
+        "AND model_id_sub=%s "
+        "AND percentile IN (5, 25, 50, 75, 95) "
+        "AND normalization='current_notional'",
+        engine,
+        ["cusip", "percentile"],
+        params=(date, date, model_id_sub),
+    )
+    df_prices = df_prices.unstack("model_version")
+    df_percentiles = df_percentiles.unstack("percentile")
+    return df_prices.join(df_percentiles, how="left")
+
+
+def subprime_risk(date, conn, engine):
+    df = get_df(date, engine)
+    df_pos = get_portfolio(date, conn, "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)
+    df_pv_perct.columns = ["pv5", "pv25", "pv50", "pv75", "pv95"]
+    df_modDur = df[("modDur", 1)]
+    df_modDur.name = "modDur"
+    df_v1 = df.xs(1, axis=1, level="model_version")[
+        ["pv_RnW", "delta_mult", "delta_hpi", "delta_ir"]
+    ]
+    df_v1.columns = ["v1pv_RnW", "v1_lsdel", "v1_hpidel", "v1_irdel"]
+    df_pv_FB = df[("pv_FB", 3)]
+    df_pv_FB.name = "pv_FB"
+    df_risk = pd.concat(
+        [
+            df_pv,
+            df_modDur,
+            df_pv_perct,
+            df.xs(3, axis=1, level="model_version")[
+                [
+                    "delta_yield",
+                    "wal",
+                    "pv_io",
+                    "pv_po",
+                    "pv_RnW",
+                    "delta_ir_io",
+                    "delta_ir_po",
+                    "delta_hpi",
+                    "delta_RnW",
+                    "delta_mult",
+                ]
+            ],
+            df_v1,
+            df_pv_FB,
+        ],
+        axis=1,
+    )
+
+    df_calc = df_pos.join(df_risk)
+    df_calc = df_calc[~df_calc["strategy"].str.contains("CRT")].dropna()
+
+    yc = YC(evaluation_date=date)
+
+    df_calc = df_calc.assign(
+        b_yield=df_calc.modDur.apply(lambda x: 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,
+    )
+
+    df_calc.b_yield += np.minimum(
+        (df_calc.pv1 * df_calc.curr_ntl / df_calc.local_market_value)
+        ** (1 / df_calc.modDur)
+        - 1,
+        1,
+    ).dropna()
+    df_calc.delta_yield *= df_calc.local_market_value / df_calc.pv3
+    df_calc.delta_ir *= (
+        (df_calc.local_market_value / df_calc.curr_ntl) / df_calc.pv3 * df_calc.curr_ntl
+    )
+    return df_calc
+
+
+def get_portfolio(date, conn, asset_class, fund="SERCGMAST"):
+    df = pd.read_sql_query(
+        "SELECT * FROM risk_positions(%s, %s, %s)",
+        conn,
+        params=(date, asset_class, fund),
+    )
+    df["cusip"] = df.identifier.str.slice(0, 9)
+    df = df.set_index("cusip")
+    return df
+
+
+def crt_risk(date, conn, engine):
+    df = get_portfolio(date, conn, "Subprime")
+    df = df[df["strategy"].str.contains("CRT")].dropna()
+    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'",
+        engine,
+        "cusip",
+        params=(date, date),
+    )
+    df = df.join(df_model)
+    df["curr_ntl"] = df["notional"] * df["factor"]
+    df["delta_yield"] = df["curr_ntl"] * df["duration_FW"]
+    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})"
+    model = pd.read_sql_query(
+        sql_string,
+        conn_1,
+        parse_dates=["pricingdate"],
+        params=[date, *df["cusip"].tolist()],
+    )
+    model.index = df["cusip"]
+    df = df.join(model, lsuffix="mark")
+    df["curr_ntl"] = df["notional"] * df["factor"]
+    df["hy_equiv"] = df["curr_ntl"] * df["delta"]
+    return df