1 files changed, 12 insertions, 39 deletions
diff --git a/python/risk/bonds.py b/python/risk/bonds.py
index 7cb14c34..9f02a5fd 100644
--- a/python/risk/bonds.py
+++ b/python/risk/bonds.py
@@ -225,59 +225,32 @@ def get_portfolio(date, conn, asset_class: AssetClass, fund="SERCGMAST"):
         return df.set_index("identifier")
 
 
-def crt_risk(date, dawn_conn, crt_engine, fund="SERCGMAST"):
+def crt_risk(date, dawn_conn, fund="SERCGMAST"):
     Trade.init_ontr(date)
     yc = YC(evaluation_date=date, curve_type="OIS")
-    df = get_portfolio(date, dawn_conn, AssetClass.CRT, fund)
-    scen = {
-        datetime.date(2019, 5, 31): "base",
-        datetime.date(2020, 1, 29): "hpi3_ir3",
-        datetime.date(2020, 3, 18): "hpi4_ir3",
-        datetime.date(2020, 10, 20): "hpi5_ir3",
-        datetime.date(3000, 1, 1): "econ6_ir3",
-    }
-    scen_type = None
-    for d, s in scen.items():
-        if scen_type is None and date < d:
-            scen_type = s
-    sql_string = (
-        "SELECT a.*, be.* FROM crt.priced_at_market a "
-        "JOIN (SELECT cusip, MAX(timestamp) timestamp "
-        "FROM crt.priced_at_market where timestamp between %s "
-        "AND date_add(%s, INTERVAL 1 DAY) "
-        "and model_des = %s GROUP BY cusip) b "
-        "using (cusip, timestamp) "
-        "LEFT JOIN map_cusip using (cusip) "
-        "LEFT JOIN bond_types USING (bond) "
-        "LEFT JOIN (select bond_type, value from crt.beta_estimates "
-        "where date = (SELECT MAX(date) FROM crt.beta_estimates where date <= %s)) be "
-        "using (bond_type)"
-    )
-    df_model = pd.read_sql_query(
-        sql_string,
-        crt_engine,
-        "cusip",
-        params=(date - datetime.timedelta(days=15), date, scen_type, date),
+    df = pd.read_sql_query(
+        "SELECT * FROM list_crt_data(%s, %s)",
+        dawn_conn,
+        "identifier",
+        params=(date, fund),
     )
-    if any(~df_model["delta.ir"].isna()):
-        df_model = df_model[~df_model["delta.ir"].isna()]
-    df = df.join(df_model)
-    df.rename(columns={"duration_FW": "modDur"}, inplace=True)
+    if any(~df["delta_ir"].isna()):
+        df = df[~df["delta_ir"].isna()]
     df = df[(df.notional != 0)]
     df = df.assign(
-        swap_rate=df.modDur.apply(
+        swap_rate=df.duration.apply(
             lambda x: x if np.isnan(x) else float(yc.zero_rate(x))
         ),
         curr_ntl=df.notional * df.factor,
         hy_equiv=(
-            df.modDur
+            df.duration
             / Trade._ontr["HY"].risky_annuity
-            * df.value
+            * df.delta
             * df.notional
             * df.factor
         ),
     )
-    df["bond_yield"] = df.swap_rate + df.DM / 10000
+    df["bond_yield"] = df.swap_rate + df.dm / 10000
     return df