diff options
Diffstat (limited to 'python/globeop_reports.py')
| -rw-r--r-- | python/globeop_reports.py | 109 |
1 files changed, 96 insertions, 13 deletions
diff --git a/python/globeop_reports.py b/python/globeop_reports.py index 3e7eb3df..f5fa8dbb 100644 --- a/python/globeop_reports.py +++ b/python/globeop_reports.py @@ -1,42 +1,55 @@ from glob import iglob +from db import dbengine +from pandas.tseries.offsets import MonthEnd + import os import pandas as pd import datetime -from db import dbengine -from pandas.tseries.offsets import MonthEnd -import load_globeop_report as load_globeop +import numpy as np +import matplotlib.pyplot as plt +import pandas.tseries.offsets as off -def get_monthly_pnl(): +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'], index_col=['date']) df_pnl['identifier'] = df_pnl.invid.str.replace("_A$", "") pnl_cols = ['bookunrealmtm', 'bookrealmtm', 'bookrealincome', 'bookunrealincome', 'totalbookpl'] monthend_pnl = df_pnl.groupby(pd.TimeGrouper('M')).apply(lambda df: df.loc[df.index[-1]]) - return monthend_pnl.groupby(['date', 'identifier'])[['mtd' + col for col in pnl_cols]].sum() + return monthend_pnl.groupby(['date'] + group_by)[['mtd' + col for col in pnl_cols]].sum() + +def get_portfolio(report_date): + 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,]) + df['identifier'] = df.invid.str.replace("_A$", "") + return df def curr_port_PNL(date = datetime.date.today(), asset_class='Subprime'): - date = (date - pd.tseries.offsets.MonthEnd(1)).date() + 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_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', 'trade_date','settle_date']) + 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_sell['trade_pnl_date'] = df_sell.trade_date + MonthEnd(0) + 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_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']], left_on=['trade_pnl_date','identifier'], right_on=['date','identifier'], suffixes=('','_at_trade_month')) - df_all = df_all.drop(['date','trade_pnl_date'], axis = 1) + df_all = df_all.merge(df_pnl.reset_index()[['date', 'identifier', 'mtdtotalbookpl']], + left_on=['trade_pnl_date', 'identifier'], + right_on=['date', 'identifier'], + suffixes=('', '_at_trade_month')) + df_all = df_all.drop(['date', 'trade_pnl_date'], axis=1) #now build up the table g = df_buy.groupby('identifier').sum() @@ -65,7 +78,77 @@ def get_net_navs(): nav = nav.resample('M').last() 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') - return df.join(nav) + df = df.join(nav) + df['begbooknav'] = (df.endbooknav + df.net_flow).shift(1) + df.at[('2013-01-31', 'begbooknav')] = 12500000 + return df + +def alloc(report_date, alloc = 'pnl'): + + """ Takes strategy grouping """ + "Alloc: pnl or capital" + + if alloc == 'pnl': + nav = get_net_navs() + df = get_monthly_pnl(['strat', 'custacctname']) + df = df.join(nav.begbooknav) + df['strat_return'] = df.mtdtotalbookpl / df.begbooknav + df = df.loc[report_date.date()] + elif alloc == 'capital': + df = get_portfolio(report_date) + df.set_index(['strat','custacctname'], inplace=True) + + #get strategy lookup table: group-by a merged DF to spot unmapped strategies + strats = pd.read_csv('/home/serenitas/edwin/Python/strat_map.csv', index_col=['strat','custacctname']) + #Check for empty sets: df.set_index(['strat','custacctname']).groupby(['strat','custacctname']) + df = df.merge(strats, left_index=True, right_index=True) + df = df.fillna(-1) + return df.set_index(alloc).groupby(alloc).sum() + +def pnl_alloc_plot(df): + + """ Takes the alloc('pnl') dataframe """ + y = df.strat_return + x = df.index + x_loc = np.arange(len(df.index)) + + width = .35 #width of the bar + fig, ax = plt.subplots(figsize = (6,6)) + ax.bar(x_loc, y, width) + + ax.set_xlabel('Strategy') + ax.set_xticks(x_loc + width /2) + ax.set_xticklabels(x, rotation='45') + + #set y-axis as percentage + ax.set_ylabel('Return (%)') + y_ticks = ax.get_yticks() + ax.set_yticklabels(['{:.2f}%'.format(y*100) for y in y_ticks]) + plt.tight_layout() + +def cap_alloc_plot(df): + + """ Takes the alloc('capital') dataframe""" + # create piechart and add a circle at the center + + df['alloc'] = df.endbooknav/df.endbooknav.sum() + fig, ax = plt.subplots(figsize=(8,4)) + ax.pie(df.alloc, labels=df.index, autopct='%1.1f%%', + pctdistance=1.25, labeldistance=1.5) + ax.add_artist(plt.Circle((0,0), 0.7, color='white')) + ax.axis('equal') + plt.tight_layout() + +def avg_turnover(): + #Total Bond Sales Proceeds/Average starting 12 months NAV + avg_nav = get_net_navs().begbooknav[-12:].mean() + last_monthend = datetime.date.today() - off.MonthEnd(1) + sql_string = "SELECT * FROM bonds where buysell = 'False'" + df = pd.read_sql_query(sql_string, dbengine('dawndb'), + parse_dates=['lastupdate', 'trade_date', 'settle_date']) + df = df[(df.trade_date > last_monthend - off.MonthEnd(12)) + & (df.trade_date <= last_monthend)] + return (df.principal_payment + df.accrued_payment).sum()/avg_nav def calc_trade_performance_stats(): df = trade_performance().set_index('trade_date') @@ -85,7 +168,7 @@ def calc_trade_performance_stats(): 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[] = len(df2[df2.winners == x].index)/len(df) + #results.loc[] = len(df2[df2.winners == x].index)/len(df) df[df.days_held.notnull()]['days_held'].groupby(pd.TimeGrouper(freq='A')).mean() |