diff options
Diffstat (limited to 'python/globeop_reports.py')
| -rw-r--r-- | python/globeop_reports.py | 68 |
1 files changed, 48 insertions, 20 deletions
diff --git a/python/globeop_reports.py b/python/globeop_reports.py index 9ac2d013..55f21325 100644 --- a/python/globeop_reports.py +++ b/python/globeop_reports.py @@ -35,15 +35,6 @@ def get_portfolio(report_date = None): return df -def curr_port_PNL(date = datetime.date.today(), asset_class='Subprime'): - date = (date - off.MonthEnd(1)).date() - sql_string = "SELECT * FROM risk_positions(%s, %s) WHERE notional > 0" - df_positions = pd.read_sql_query(sql_string, dbengine('dawndb'), params=[date, asset_class]) - df_pnl = get_monthly_pnl()[:date] - df_all = df_positions.merge(df_pnl.groupby('identifier').sum().reset_index(), on=['identifier']) - return df_all - - def trade_performance(): sql_string = "SELECT * FROM bonds" df_trades = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates={'lastupdate': {'utc': True}, 'trade_date': {}, 'settle_date': {}}) @@ -133,10 +124,11 @@ def get_rmbs_pos_df(date = None): df = df.sort_index().loc[:end_date] df = df[(df.port == 'MORTGAGES') & (df.endbookmv > 0) & - (df['invid'].str.len() == 9)] + (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[['endbooknav', 'endlocalmarketprice', 'identifier']] + df = df[['endbookmv', 'endlocalmarketprice', 'identifier']] calc_df = pd.DataFrame() for d, g in df.groupby(pd.Grouper(freq='M')): @@ -153,7 +145,6 @@ def get_rmbs_pos_df(date = None): sql_string = """ SELECT date(timestamp) as timestamp, cusip, model_version, pv, moddur, delta_yield, delta_ir FROM priced where date(timestamp) = %s - and normalization ='current_notional' and model_version <> 2 and model_id_sub = %s""" params_list = [model_date, model_id] @@ -161,25 +152,62 @@ def get_rmbs_pos_df(date = None): 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 - and normalization ='current_notional'""" + and model_version <> 2""" params_list = [model_date] model = pd.read_sql_query(sql_string, engine, parse_dates=['timestamp'], params=params_list) - comb_g = g.loc[d].groupby('identifier').agg({'endbooknav': np.sum, + model = model[model.pv != 0] + comb_g = g.loc[d].groupby('identifier').agg({'endbookmv': np.sum, 'endlocalmarketprice': np.mean}) model = pd.merge(comb_g, model, left_on = 'identifier', right_on='cusip') positions = model.set_index(['cusip', 'model_version']).unstack(1).dropna() - positions = positions[positions.pv.iloc[:,0] != 0] v1 = positions.xs(1, level='model_version', axis=1) v3 = positions.xs(3, level='model_version', axis=1) - v3 = v3.assign(curr_ntl = v3.endbooknav/v3.endlocalmarketprice *100) + 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, 10).dropna() - v3.delta_yield = v3.delta_yield * (v3.endlocalmarketprice/100)/ v3.pv * v3.curr_ntl - v3.delta_ir = v3.delta_ir * np.minimum(1, 1/v3.moddur) * (v3.endlocalmarketprice/100)/ v3.pv * v3.curr_ntl + ** (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) return calc_df.reset_index().set_index('timestamp').sort_index() + +def get_clo_pos_df(date = None): + + etengine = dbengine('etdb') + dawnengine = 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 == '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 = pd.read_sql_query(sql_string, dawnengine) + + r = {} + for d, g in df.groupby(pd.Grouper(freq='M')): + cusip_list = g.loc[g.index[-1]].identifier + if isinstance(cusip_list, str): + pos = pd.merge(pd.DataFrame(g.loc[g.index[-1]]).T, cusip_map, on='identifier') + else: + pos = pd.merge(g.loc[g.index[-1]], cusip_map, on='identifier') + cusip_list = pos.cusip.tolist() + placeholders = ",".join(["%s"] * (1+len(cusip_list))) + sql_string = f"SELECT * FROM historical_cusip_risk({placeholders})" + model = pd.read_sql_query(sql_string, etengine, parse_dates = ['pricingdate'], + params=[d.date()] + cusip_list) + model = pd.concat([model, pd.Series(cusip_list, name='cusip')], axis=1) + model = model.dropna().set_index('pricingdate') + r[d] = pd.merge(model, pos, on='cusip').set_index('cusip') + calc_df = pd.concat(r, names=['date', 'cusip']) + calc_df['hy_equiv'] = calc_df.delta * calc_df.endbookmv + return calc_df + |