diff options
Diffstat (limited to 'python/globeop_reports.py')
| -rw-r--r-- | python/globeop_reports.py | 52 |
1 files changed, 49 insertions, 3 deletions
diff --git a/python/globeop_reports.py b/python/globeop_reports.py index f624971c..b92eabfa 100644 --- a/python/globeop_reports.py +++ b/python/globeop_reports.py @@ -1,6 +1,7 @@ from glob import iglob from db import dbengine from pandas.tseries.offsets import MonthEnd +from yieldcurve import YC import os import pandas as pd @@ -10,6 +11,7 @@ import matplotlib.pyplot as plt import matplotlib.dates as mdates import pandas.tseries.offsets as off + def get_monthly_pnl(group_by = ['identifier']): sql_string = "SELECT * FROM pnl_reports" df_pnl = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates=['date'], @@ -19,8 +21,8 @@ def get_monthly_pnl(group_by = ['identifier']): monthend_pnl = df_pnl.groupby(pd.Grouper(freq='M')).apply(lambda df: df.loc[df.index[-1]]) return monthend_pnl.groupby(['date'] + group_by)[['mtd' + col for col in pnl_cols]].sum() -def get_portfolio(report_date = False): - if report_date != False: +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'], index_col=['periodenddate'], params=[report_date,]) @@ -208,7 +210,7 @@ def calc_trade_performance_stats(): df['winners'] = df.apply(lambda df: True if df.percent_gain > 0 else False, axis = 1) df['curr_face'] = df.principal_payment/(df.price/100) - index = ['All','2017','2016','2015','2014','2013'] + index = ['All', '2017', '2016', '2015', '2014', '2013'] results = pd.DataFrame(index = index) win_per = len(df[df.winners].index)/len(df) @@ -225,3 +227,47 @@ 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): + + engine = dbengine('dawndb') + calc_df = pd.DataFrame() + 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] + mask = (df.port == 'MORTGAGES') & (df.endbookmv > 0) & (df['invid'].str.len() == 9) + df = df[mask] + sql_string = "SELECT distinct timestamp FROM priced" + timestamps = pd.read_sql_query(sql_string, engine) + + for d, g in df.groupby(pd.Grouper(freq='M')): + model_date = pd.to_datetime(timestamps[timestamps.timestamp <= d+off.DateOffset(days=1)].max()[0]).date() + yc = YC(evaluation_date=model_date) + libor = float(yc.zero_rate(.125)) + if d > pd.datetime(2017, 9, 30): + model_id_sql_string = "SELECT * FROM latest_sim(%s)" + model_id = pd.read_sql_query(model_id_sql_string, engine, params=[model_date]) + model_id = model_id.loc[0][0] + #special case + if model_date == pd.datetime(2017, 10, 27).date(): + model_id = 4 + sql_string = "SELECT * FROM priced where date(timestamp) = %s and model_id_sub = %s" + model = pd.read_sql_query(sql_string, engine, params=[model_date, model_id]) + else: + sql_string = "SELECT * FROM priced where date(timestamp) = %s" + model = pd.read_sql_query(sql_string, engine, params=[model_date]) + model['timestamp'] = model['timestamp'].dt.date + model = model[model.normalization == 'current_notional'] + model = model.set_index(['cusip', 'model_version']).unstack(1) + temp = pd.merge(g.loc[d], model, left_on='identifier', right_index=True) + temp['curr_ntl'] = temp.endbooknav/temp.endlocalmarketprice *100 + temp['b_yield'] = np.minimum((temp[('pv', 1)]/temp.endlocalmarketprice*100) ** (1/temp[('moddur', 1)]) - 1, 10) + temp = temp.dropna(subset=['b_yield']) + temp['b_yield'] = temp.apply(lambda df: df['b_yield'] + float(yc.zero_rate(df[('moddur', 3)])) - libor, axis=1) + temp = temp[(temp[('pv', 3)] != 0)] + temp['percent_model'] = temp.apply(lambda df: df.endlocalmarketprice/100/df[('pv', 3)], axis=1) + calc_df = calc_df.append(temp) + + return calc_df |