diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/risk/__main__.py | 11 | ||||
| -rw-r--r-- | python/risk/subprime.py | 71 |
2 files changed, 81 insertions, 1 deletions
diff --git a/python/risk/__main__.py b/python/risk/__main__.py index acc07be3..01e0e052 100644 --- a/python/risk/__main__.py +++ b/python/risk/__main__.py @@ -1,7 +1,8 @@ import argparse import pandas as pd -from db import dbconn +from db import dbconn, dbengine from pandas.tseries.offsets import BDay +from .subprime import get_rmbs_portfolio, subprime_risk from .swaptions import get_swaption_portfolio, insert_swaption_portfolio parser = argparse.ArgumentParser() @@ -12,6 +13,14 @@ if args.workdate is None: workdate = (pd.Timestamp.today()-BDay()).date() else: workdate = args.workdate + with dbconn('dawndb') as conn: portf = get_swaption_portfolio(workdate, conn, source_list=["GS"]) insert_swaption_portfolio(portf, conn) + portf = get_rmbs_portfolio(workdate, conn) + crt_portf = portf[portf.strategy.str.contains("CRT")] + subprime_portf = portf[~portf.strategy.str.contains("CRT")] + subprime_portf_zero = subprime_portf[subprime_portf.identifier.str.endswith("_A")] + subprime_portf = subprime_portf[~subprime_portf.identifier.str.endswith("_A")] + df = subprime_risk(workdate) + subprime_portf = subprime_portf.join(df) 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 |