diff options
| -rw-r--r-- | python/globeop_reports.py | 68 |
1 files changed, 68 insertions, 0 deletions
diff --git a/python/globeop_reports.py b/python/globeop_reports.py new file mode 100644 index 00000000..bf9e627b --- /dev/null +++ b/python/globeop_reports.py @@ -0,0 +1,68 @@ +from glob import iglob +import os +import pandas as pd +import datetime +from db import dbengine +import load_globeop_report as load_globeop + +def get_monthly_pnl(): + sql_string = "SELECT * FROM pnl_reports" + df_pnl = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates=['date']) + df_pnl = df_pnl.set_index('date') + zero_factor = df_pnl[df_pnl.invid.str.match('^.{9}_A$')]['invid'].unique() + df_pnl.invid = df_pnl.invid.apply(lambda x: x[:9] if x in zero_factor.tolist() else x) + pnl_cols = ['bookunrealmtm', 'bookrealmtm', 'bookrealincome', 'bookunrealincome', 'totalbookpl'] + df_pnl = df_pnl.reset_index().groupby(['date','invid']).sum().reset_index('invid') + df_pnl = df_pnl.groupby('invid').resample('M').last()[['mtd'+col for col in pnl_cols]] + df_pnl.index.names = ['identifier','date'] + return df_pnl.reset_index() + +def curr_port_PNL(date = datetime.date.today(), asset_class='Subprime'): + 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() + df_all = df_positions.merge(df_pnl.groupby('identifier').sum().reset_index(), on=['identifier']) + return df_all + +def check_valuation(): + sql_string = "SELECT * FROM valuation_reports" + df_val = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates=['periodenddate']) + nav = df_val[df_val.fund == 'SERCGMAST'].groupby('periodenddate')['endbooknav'].sum() + return nav.resample('M').last() + +def trade_performance(): + sql_string = "SELECT * FROM bonds" + df_trades = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates= ['lastupdate', '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.identifier = df_sell.identifier.str[:9] + df_buy = df_trades[df_trades.buysell == True].groupby('identifier').last().reset_index() + + df_all = df_sell.merge(df_pnl.groupby('identifier').sum().reset_index(), on=['identifier']) + + #now build up the table + g = df_buy.groupby('identifier').sum() + init_inv = g.principal_payment + g.accrued_payment + init_inv.name = 'initialinvestment' + + first_buy_date = df_buy.groupby('identifier').first().trade_date + first_buy_date.name = 'firstbuydate' + + df_all = df_all.join(init_inv, on='identifier') + df_all = df_all.join(first_buy_date, on='identifier') + df_all['percent_gain'] = df_all.mtdtotalbookpl / df_all.initialinvestment + df_all['days_held'] = df_all.trade_date - df_all.firstbuydate + + df_all = df_all.sort_values('trade_date', ascending=False) + + table = pd.DataFrame() + #table['average_days_held'] = df_all.days_held.mean() + + return df_all + +if __name__=='__main__': + nav = check_valuation() + df_pnl = trade_performance() + |