1 files changed, 71 insertions, 0 deletions
diff --git a/python/risk/subprime.py b/python/risk/subprime.py
new file mode 100644
index 00000000..84f5c70e
--- /dev/null
+++ b/python/risk/subprime.py
@@ -0,0 +1,71 @@
+import pandas as pd
+from . import mysql_engine
+
+def latest_sim(date):
+    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 = mysql_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):
+    model_id_sub = latest_sim(date)
+    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'",
+        mysql_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'",
+                                       mysql_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):
+    df = get_df(date)
+    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'
+    return 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)
+
+def get_rmbs_portfolio(date, conn, fund="SERCGMAST",
+                           include_unsettled=True):
+    df = pd.read_sql_query("SELECT * FROM list_positions(%s, 'Subprime', %s, %s)",
+                           conn, params=(date, include_unsettled, fund))
+    df["cusip"] = df.identifier.str.slice(0, 9)
+    df = df.set_index("cusip")
+    return df