diff options
| -rw-r--r-- | python/risk/bonds.py | 46 |
1 files changed, 17 insertions, 29 deletions
diff --git a/python/risk/bonds.py b/python/risk/bonds.py index fda36111..ff0823c2 100644 --- a/python/risk/bonds.py +++ b/python/risk/bonds.py @@ -14,47 +14,35 @@ class AssetClass(Enum): CRT = auto() -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 " + "FROM priced " + "JOIN model_versions USING (model_id_sub) " + "JOIN model_versions_nonagency USING (model_id_sub) " + "JOIN simulations_nonagency USING (simulation_id) " + "WHERE timestamp BETWEEN %s AND date_add(%s, INTERVAL 1 DAY) " + "AND description='normal' " "AND normalization='current_notional'", engine, ["cusip", "model_version"], - params=(date, date, model_id_sub), + params=(date, date), ) df_percentiles = pd.read_sql_query( "SELECT cusip, PV, percentile " - "FROM priced_percentiles WHERE " - "timestamp BETWEEN %s AND date_add(%s, INTERVAL 1 DAY) " + "FROM priced_percentiles " + "JOIN model_versions USING (model_id_sub) " + "JOIN model_versions_nonagency USING (model_id_sub) " + "JOIN simulations_nonagency USING (simulation_id) " + "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), + params=(date, date), ) df_prices = df_prices.unstack("model_version") df_percentiles = df_percentiles.unstack("percentile") @@ -103,15 +91,15 @@ def subprime_risk(date, conn, engine): df_calc = df_pos.join(df_risk) yc = YC(evaluation_date=date) - df_calc = df_calc.assign( - b_yield=df_calc.modDur.apply(lambda x: float(yc.zero_rate(x))), + b_yield=df_calc.modDur.apply( + lambda x: x if np.isnan(x) else 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 += ( - (df_calc.pv1 * df_calc.curr_ntl / df_calc.local_market_value).log() + np.log(df_calc.pv1 * df_calc.curr_ntl / df_calc.local_market_value) / df_calc.modDur ).clip_upper(1.0) # delta scaled by ratio of market_value to model value |