diff options
Diffstat (limited to 'python/globeop_reports.py')
| -rw-r--r-- | python/globeop_reports.py | 85 |
1 files changed, 37 insertions, 48 deletions
diff --git a/python/globeop_reports.py b/python/globeop_reports.py index 55f21325..862fccce 100644 --- a/python/globeop_reports.py +++ b/python/globeop_reports.py @@ -1,20 +1,14 @@ -from glob import iglob from db import dbengine -from pandas.tseries.offsets import MonthEnd +from pandas.tseries.offsets import DateOffset, MonthEnd from yieldcurve import YC -import os import pandas as pd -import datetime import numpy as np -import matplotlib.pyplot as plt -import matplotlib.dates as mdates -import pandas.tseries.offsets as off - -def get_monthly_pnl(group_by = ['identifier']): +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'], + df_pnl = pd.read_sql_query(sql_string, dbengine('dawndb'), + parse_dates=['date'], index_col=['date']) df_pnl['identifier'] = df_pnl.invid.str.replace("_A$", "") pnl_cols = ['bookunrealmtm', 'bookrealmtm', 'bookrealincome', 'bookunrealincome', 'totalbookpl'] @@ -22,7 +16,7 @@ def get_monthly_pnl(group_by = ['identifier']): return monthend_pnl.groupby(['date'] + group_by)[['mtd' + col for col in pnl_cols]].sum() -def get_portfolio(report_date = None): +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'], @@ -37,14 +31,17 @@ def get_portfolio(report_date = None): 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': {}}) + df_trades = pd.read_sql_query(sql_string, dbengine('dawndb'), + parse_dates={'lastupdate': {'utc': True}, + 'trade_date': {}, + 'settle_date': {}}) df_trades = df_trades[df_trades['asset_class'] == 'Subprime'] df_pnl = get_monthly_pnl() - df_sell = df_trades[df_trades.buysell == False].groupby('identifier').last().reset_index() + df_sell = df_trades[~df_trades.buysell].groupby('identifier').last().reset_index() df_sell.identifier = df_sell.identifier.str[:9] - df_sell['trade_pnl_date'] = df_sell.trade_date + off.MonthEnd(0) - df_buy = df_trades[df_trades.buysell == True].groupby('identifier').last().reset_index() + df_sell['trade_pnl_date'] = df_sell.trade_date + MonthEnd(0) + df_buy = df_trades[df_trades.buysell].groupby('identifier').last().reset_index() df_all = df_sell.merge(df_pnl.groupby('identifier').sum().reset_index(), on=['identifier']) df_all = df_all.merge(df_pnl.reset_index()[['date', 'identifier', 'mtdtotalbookpl']], @@ -76,7 +73,9 @@ def get_net_navs(): df_val = pd.read_sql_query(sql_string, dbengine('dawndb'), 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', parse_dates=['date'], index_col =['date']) + df = pd.read_csv('/home/serenitas/edwin/Python/subscription_fee_data.csv', + parse_dates=['date'], + index_col =['date']) df.index = df.index.to_period('M').to_timestamp('M') df = df.join(nav) df['begbooknav'] = (df.endbooknav + df.net_flow).shift(1) @@ -94,26 +93,23 @@ def shift_cash(date, amount, df, strat): def calc_trade_performance_stats(): df = trade_performance().set_index('trade_date') df.days_held = df.days_held.dt.days - df['winners'] = df.apply(lambda df: True if df.percent_gain > 0 else False, axis = 1) + df['winners'] = df.percent_gain > 0 df['curr_face'] = df.principal_payment/(df.price/100) index = ['All', '2017', '2016', '2015', '2014', '2013'] - results = pd.DataFrame(index = index) + results = pd.DataFrame(index=index) - win_per = len(df[df.winners].index)/len(df) - loss_per = 1- win_per - temp = {} - temp1 = {} + win_per = df.winners.mean() + loss_per = 1 - win_per for x, df1 in df.groupby('winners'): for y, df2 in df1.groupby(pd.Grouper(freq='A')): - import pdb; pdb.set_trace() y = y.date().year - results.loc[y] = df2[df2.days_held.notnull()].mean()[['curr_face','initialinvestment', 'days_held']] + results.loc[y] = df2[df2.days_held.notnull()].mean()[['curr_face', 'initialinvestment', 'days_held']] df[df.days_held.notnull()]['days_held'].groupby(pd.Grouper(freq='A')).mean() -def get_rmbs_pos_df(date = None): +def get_rmbs_pos_df(date=None): engine = dbengine('dawndb') end_date = pd.datetime.today() - MonthEnd(1) @@ -132,42 +128,36 @@ def get_rmbs_pos_df(date = None): calc_df = pd.DataFrame() 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() + model_date = pd.to_datetime(timestamps[timestamps.timestamp <= d + DateOffset(days=1)].max()[0]).date() yc = YC(evaluation_date=model_date) libor = float(yc.zero_rate(.125)) + 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): - 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] + 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 = """ - 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 model_id_sub = %s""" - params_list = [model_date, model_id] - else: - 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] + 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) + params=params_list) 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') + 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)) + float(yc.zero_rate(x)) - libor)) v3.b_yield += np.minimum((v1.pv / v1.endlocalmarketprice * 100) - ** (1/v1.moddur) - 1, 1).dropna() + ** (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) @@ -210,4 +200,3 @@ def get_clo_pos_df(date = None): calc_df = pd.concat(r, names=['date', 'cusip']) calc_df['hy_equiv'] = calc_df.delta * calc_df.endbookmv return calc_df - |