diff options
| -rw-r--r-- | python/risk/bonds.py | 2 | ||||
| -rw-r--r-- | python/risk/subprime.py | 71 |
2 files changed, 0 insertions, 73 deletions
diff --git a/python/risk/bonds.py b/python/risk/bonds.py index 56687aa9..edb4ceb8 100644 --- a/python/risk/bonds.py +++ b/python/risk/bonds.py @@ -4,9 +4,7 @@ import analytics import datetime 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): diff --git a/python/risk/subprime.py b/python/risk/subprime.py deleted file mode 100644 index 84f5c70e..00000000 --- a/python/risk/subprime.py +++ /dev/null @@ -1,71 +0,0 @@ -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 |