diff options
Diffstat (limited to 'python/globeop_reports.py')
| -rw-r--r-- | python/globeop_reports.py | 158 |
1 files changed, 84 insertions, 74 deletions
diff --git a/python/globeop_reports.py b/python/globeop_reports.py index e656ae5f..2de3f13e 100644 --- a/python/globeop_reports.py +++ b/python/globeop_reports.py @@ -7,10 +7,14 @@ from quantlib.termstructures.yield_term_structure import YieldTermStructure import pandas as pd import numpy as np +import datetime + +etengine = dbengine('etdb') +dawnengine = dbengine('dawndb') def get_monthly_pnl(group_by=['identifier']): sql_string = "SELECT * FROM pnl_reports" - df_pnl = pd.read_sql_query(sql_string, dbengine('dawndb'), + df_pnl = pd.read_sql_query(sql_string, dawnengine, parse_dates=['date'], index_col=['date']) df_pnl['identifier'] = df_pnl.invid.str.replace("_A$", "") @@ -22,11 +26,11 @@ def get_monthly_pnl(group_by=['identifier']): def get_portfolio(report_date=None): if report_date is not None: sql_string = "SELECT * FROM valuation_reports where periodenddate = %s" - df = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates=['periodenddate'], + df = pd.read_sql_query(sql_string, dawnengine, parse_dates=['periodenddate'], index_col=['periodenddate'], params=[report_date,]) else: sql_string = "SELECT * FROM valuation_reports" - df = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates=['periodenddate'], + df = pd.read_sql_query(sql_string, dawnengine, parse_dates=['periodenddate'], index_col=['periodenddate']) df['identifier'] = df.invid.str.replace("_A$", "") return df @@ -34,7 +38,7 @@ def get_portfolio(report_date=None): def trade_performance(): sql_string = "SELECT * FROM bonds" - df_trades = pd.read_sql_query(sql_string, dbengine('dawndb'), + df_trades = pd.read_sql_query(sql_string, dawnengine, parse_dates={'lastupdate': {'utc': True}, 'trade_date': {}, 'settle_date': {}}) @@ -73,7 +77,7 @@ def trade_performance(): def get_net_navs(): sql_string = "SELECT * FROM valuation_reports" - df_val = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates=['periodenddate']) + df_val = pd.read_sql_query(sql_string, dawnengine, parse_dates=['periodenddate']) nav = df_val[df_val.fund == 'SERCGMAST'].groupby('periodenddate')['endbooknav'].sum() nav = nav.resample('M').last() df = pd.read_csv('/home/serenitas/edwin/Python/subscription_fee_data.csv', @@ -112,91 +116,97 @@ def calc_trade_performance_stats(): df[df.days_held.notnull()]['days_held'].groupby(pd.Grouper(freq='A')).mean() -def get_rmbs_pos_df(date=None): +def hist_pos(date=None, asset_class = 'rmbs'): - engine = dbengine('dawndb') end_date = pd.datetime.today() - MonthEnd(1) - - if date is not None: - date = date + MonthEnd(0) - df = get_portfolio(date) - df = df.sort_index().loc[:end_date] - df = df[(df.port == 'MORTGAGES') & - (df.endbookmv > 0) & - (df.custacctname == 'V0NSCLMAMB') & - (df['invid'].str.len() >= 9)] - sql_string = "SELECT distinct timestamp FROM priced where normalization = 'current_notional'" - timestamps = pd.read_sql_query(sql_string, engine) - df = df[['endbookmv', 'endlocalmarketprice', 'identifier']] + dates = pd.date_range(datetime.date(2013,1,31), end_date, freq='M') calc_df = pd.DataFrame() - yc = YieldTermStructure() - libor1m = USDLibor(Period(1, Months), yc) - for d, g in df.groupby(pd.Grouper(freq='M')): - model_date = pd.to_datetime(timestamps[timestamps.timestamp <= d + DateOffset(days=1)].max()[0]).date() - yc.link_to(YC(evaluation_date=model_date)) - libor = libor1m.fixing(libor1m.fixing_calendar.adjust(Date.from_datetime(d))) - sql_string = ("SELECT date(timestamp) as timestamp, cusip, model_version, " - "pv, moddur, delta_yield, delta_ir " - "FROM priced where date(timestamp) = %s " - "and model_version <> 2") - params_list = (model_date,) - if d > pd.datetime(2017, 9, 30): - r = engine.execute("SELECT latest_sim FROM latest_sim(%s)", model_date) - model_id, = next(r) - #special case - if model_date == pd.datetime(2017, 10, 27).date(): - model_id = 4 - sql_string += " AND model_version <>2 AND model_id_sub = %s" - params_list += (model_id,) - model = pd.read_sql_query(sql_string, engine, parse_dates=['timestamp'], - params=params_list) - model = model[model.pv != 0] - comb_g = g.loc[d].groupby('identifier').agg({'endbookmv': 'sum', - 'endlocalmarketprice': 'mean'}) - model = pd.merge(comb_g, model, left_on='identifier', right_on='cusip') - positions = model.set_index(['cusip', 'model_version']).unstack(1).dropna() - v1 = positions.xs(1, level='model_version', axis=1) - v3 = positions.xs(3, level='model_version', axis=1) - v3 = v3.assign(curr_ntl = v3.endbookmv/v3.endlocalmarketprice *100) - v3 = v3.assign(b_yield = v3.moddur.apply(lambda x: - float(yc.zero_rate(x)) - libor)) - v3.b_yield += np.minimum((v1.pv / v1.endlocalmarketprice * 100) - ** (1/v1.moddur) - 1, 1).dropna() - v3.delta_yield *= v3.endbookmv / v3.pv - v3.delta_ir *= np.minimum(1, 1/v3.moddur) * (v3.endlocalmarketprice/100)/ v3.pv * v3.curr_ntl - calc_df = calc_df.append(v3) + for d in dates: + if asset_class == 'rmbs': + calc_df = calc_df.append(rmbs_pos(d)) + else: + calc_df = calc_df.append(clo_pos(d), sort=True) + return calc_df - return calc_df.reset_index().set_index('timestamp').sort_index() +def rmbs_pos(date): + date = date.date() if isinstance(date, pd.Timestamp) else date -def get_clo_pos_df(date=None): + pos = get_portfolio(date) + pos = pos[(pos.port == 'MORTGAGES') & + (pos.endbookmv > 0) & + (pos.custacctname == 'V0NSCLMAMB') & + (pos['invid'].str.len() >= 9)] + pos = pos[['endbookmv', 'endlocalmarketprice', 'identifier']] - etengine = dbengine('etdb') - dawnengine = dbengine('dawndb') - end_date = pd.datetime.today() - MonthEnd(1) + sql_string = ("SELECT distinct timestamp FROM priced where " + "normalization = 'current_notional' and " + "model_version = 1 and " + "date(timestamp) < %s and date(timestamp) > %s " + "order by timestamp desc") + timestamps = pd.read_sql_query(sql_string, dawnengine, parse_dates=['timestamp'], + params=[date, date - DateOffset(15, 'D')]) + model_date = (timestamps.loc[0][0]).date() + + yc = YieldTermStructure() + libor1m = USDLibor(Period(1, Months), yc) + yc.link_to(YC(evaluation_date=model_date)) + libor = libor1m.fixing(libor1m.fixing_calendar.adjust(Date.from_datetime(date))) + sql_string = ("SELECT date(timestamp) as timestamp, cusip, model_version, " + "pv, moddur, delta_yield, delta_ir " + "FROM priced where date(timestamp) = %s " + "and model_version <> 2") + params_list = (model_date,) + if date > datetime.date(2017, 9, 30): + r = dawnengine.execute("SELECT latest_sim FROM latest_sim(%s)", + model_date) + model_id, = next(r) + sql_string += " AND model_id_sub = %s" + params_list += (model_id,) + model = pd.read_sql_query(sql_string, dawnengine, parse_dates=['timestamp'], + params=params_list) + model = model[model['pv'] != 0] + comb_g = pos.loc[date].groupby('identifier').agg({'endbookmv': 'sum', + 'endlocalmarketprice': 'mean'}) + model = pd.merge(comb_g, model, left_on='identifier', right_on='cusip') + positions = model.set_index(['cusip', 'model_version']).unstack(1).dropna() + v1 = positions.xs(1, level='model_version', axis=1) + v3 = positions.xs(3, level='model_version', axis=1) + v3 = v3.assign(curr_ntl = v3.endbookmv/v3.endlocalmarketprice *100) + v3 = v3.assign(b_yield = v3.moddur.apply(lambda x: + float(yc.zero_rate(x)) - libor)) + v3.b_yield += np.minimum((v1.pv / v1.endlocalmarketprice * 100) + ** (1/v1.moddur) - 1, 1).dropna() + v3.delta_yield *= v3.endbookmv / v3.pv + v3.delta_ir *= np.minimum(1, 1/v3.moddur) * \ + (v3.endlocalmarketprice/100)/ v3.pv * v3.curr_ntl + return v3.reset_index().set_index('timestamp') + +def clo_pos(date): + + date = date.date() if isinstance(date, pd.Timestamp) else date - if date is not None: - date = date + MonthEnd(0) df = get_portfolio(date) - df = df.sort_index().loc[:end_date] df = df[(df.port == 'CLO') & (df.endbookmv > 0) & (df.custacctname == 'V0NSCLMAMB') & (df['invid'].str.len() >= 9)] df = df[['endbookmv', 'endlocalmarketprice', 'identifier']] - sql_string = "select distinct cusip, identifier from bonds where asset_class = 'CLO'" - cusip_map = {r['identifier']: r['cusip'] for r in dawnengine.execute(sql_string)} - df['cusip'] = df['identifier'].replace(cusip_map) - r = {} - for d, g in df.groupby(pd.Grouper(freq='M')): - cusips = g.loc[[g.index[-1]], 'cusip'] + + if df.empty is True: + return df + else: + sql_string = "select distinct cusip, identifier from bonds where asset_class = 'CLO'" + cusip_map = {r['identifier']: r['cusip'] for r in dawnengine.execute(sql_string)} + df['cusip'] = df['identifier'].replace(cusip_map) + cusips = df.loc[[df.index[-1]], 'cusip'] placeholders = ",".join(["%s"] * (1 + len(cusips))) sql_string = f"SELECT * FROM historical_cusip_risk({placeholders})" model = pd.read_sql_query(sql_string, etengine, parse_dates=['pricingdate'], - params=(d.date(), *cusips)) + params=(date, *cusips)) model.index = cusips - r[d] = g.loc[[g.index[-1]]].set_index('cusip').join(model) - calc_df = pd.concat(r, names=['date', 'cusip']) - calc_df['hy_equiv'] = calc_df.delta * calc_df.endbookmv - return calc_df + calc_df = df.loc[[df.index[-1]]].set_index('cusip').join(model) + calc_df['hy_equiv'] = calc_df.delta * calc_df.endbookmv + calc_df['date'] = date + return calc_df.set_index('date') |