diff options
| -rw-r--r-- | python/risk/bonds.py | 73 |
1 files changed, 51 insertions, 22 deletions
diff --git a/python/risk/bonds.py b/python/risk/bonds.py index 4e15561e..2a4e064f 100644 --- a/python/risk/bonds.py +++ b/python/risk/bonds.py @@ -1,6 +1,7 @@ import pandas as pd import numpy as np import analytics +import datetime from enum import Enum, auto from utils.db import dbengine @@ -24,31 +25,56 @@ def get_df(date, engine, *, zero_factor=False): normalization = "current_notional" table1 = "priced" table2 = "priced_percentiles" + if date > datetime.date(2017, 10, 1): + sql_string_prices = ( + "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 " + f"FROM {table1} " + "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=%s" + ) + sql_string_percentile = ( + "SELECT cusip, PV, percentile " + f"FROM {table2} " + "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 percentile IN (5, 25, 50, 75, 95) " + "AND normalization=%s " + "AND description='normal'" + ) + else: + sql_string_prices = ( + "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 " + f"FROM {table1} " + "WHERE timestamp BETWEEN %s AND date_add(%s, INTERVAL 1 DAY) " + "AND normalization=%s" + ) + sql_string_percentile = ( + "SELECT cusip, PV, percentile " + f"FROM {table2} " + "WHERE timestamp BETWEEN %s AND date_add(%s, INTERVAL 1 DAY) " + "AND model_version=3 " + "AND percentile IN (5, 25, 50, 75, 95) " + "AND normalization=%s" + ) 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 " - f"FROM {table1} " - "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=%s", + sql_string_prices, engine, ["cusip", "model_version"], params=(date, date, normalization), ) df_percentiles = pd.read_sql_query( - "SELECT cusip, PV, percentile " - f"FROM {table2} " - "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 percentile IN (5, 25, 50, 75, 95) " - "AND normalization=%s", + sql_string_percentile, engine, ["cusip", "percentile"], params=(date, date, normalization), @@ -58,9 +84,11 @@ def get_df(date, engine, *, zero_factor=False): return df_prices.join(df_percentiles, how="left") -def subprime_risk(date, conn, engine): - df = get_df(date, engine, zero_factor=False) - df_zero = get_df(date, engine, zero_factor=True) +def subprime_risk(date, conn, engine, date1=None): + if date1 is None: + date1 = date + df = get_df(date1, engine, zero_factor=False) + df_zero = get_df(date1, engine, zero_factor=True) df.loc[df_zero.index] = df_zero df_pos = get_portfolio(date, conn, AssetClass.Subprime) df_pv = df.xs("pv", axis=1, level=0) @@ -120,6 +148,7 @@ def subprime_risk(date, conn, engine): ), date=date, ) + df_calc.date = pd.to_datetime(df_calc.date) df_calc.bond_yield += ( np.log(df_calc.pv1 * df_calc.curr_ntl / df_calc.local_market_value) / df_calc.modDur |