diff options
Diffstat (limited to 'python/globeop_reports.py')
| -rw-r--r-- | python/globeop_reports.py | 117 |
1 files changed, 6 insertions, 111 deletions
diff --git a/python/globeop_reports.py b/python/globeop_reports.py index b92eabfa..4562c605 100644 --- a/python/globeop_reports.py +++ b/python/globeop_reports.py @@ -18,9 +18,10 @@ def get_monthly_pnl(group_by = ['identifier']): 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.Grouper(freq='M')).apply(lambda df: df.loc[df.index[-1]]) + monthend_pnl = df_pnl.groupby(pd.Grouper(freq='M'), group_keys=False).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 = None): if report_date is not None: sql_string = "SELECT * FROM valuation_reports where periodenddate = %s" @@ -33,6 +34,7 @@ def get_portfolio(report_date = None): df['identifier'] = df.invid.str.replace("_A$", "") 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" @@ -41,6 +43,7 @@ def curr_port_PNL(date = datetime.date.today(), asset_class='Subprime'): 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': {}}) @@ -74,11 +77,9 @@ def trade_performance(): 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 + def get_net_navs(): sql_string = "SELECT * FROM valuation_reports" df_val = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates=['periodenddate']) @@ -91,94 +92,6 @@ def get_net_navs(): df.at[('2013-01-31', 'begbooknav')] = 12500000 return df -#def alloc(report_date, alloc = 'pnl'): -def alloc(alloc = 'pnl'): - - """ Takes strategy grouping """ - "Alloc: pnl or capital" - - if alloc == 'pnl': - #nav = go.get_net_navs() - #df = go.get_monthly_pnl(['strat', 'custacctname']) - nav = get_net_navs() - df = get_monthly_pnl(['strat', 'custacctname']) - df = df.join(nav.begbooknav) - df['strat_return'] = df.mtdtotalbookpl / df.begbooknav - df = df.reset_index().dropna(subset = ['custacctname']) - df.set_index(['strat', 'custacctname'], inplace=True) - df = df.rename(columns={"date": "periodenddate"}) - #df = df.loc[report_date.date()] - elif alloc == 'capital': - df = get_portfolio().reset_index() - df.dropna(subset = ['custacctname'], inplace=True) - 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.groupby(['periodenddate', 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_pie(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':'utc=True', '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 num_bond_by_strat(): - df = get_portfolio() - df = df[(df.custacctname == 'V0NSCLMAMB') & - ~(df.invid.isin(['USD', 'CAD', 'EUR'])) & (df.endqty > 0)] - df = df.groupby(pd.Grouper(freq='M')).apply(lambda df: df.loc[df.index[-1]]) - return df.groupby(['periodenddate', 'port']).identifier.nunique().unstack() - -def num_bond_trades(): - sql_string = "SELECT * FROM bonds" - df = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates=['trade_date'], - index_col=['trade_date']) - df = df.groupby([pd.Grouper(freq='M'), 'buysell']).identifier.count().unstack() - idx = pd.date_range(df.index[0], df.index[-1], freq = 'M') - return df.reindex(idx, fill_value = 0) def shift_cash(date, amount, df, strat): nav = get_net_navs() @@ -186,23 +99,6 @@ def shift_cash(date, amount, df, strat): df.loc[date,'Cash'] = df.loc[date, 'Cash'] + amount/nav.loc[date].endbooknav return df -def cap_alloc_plot_bar(df): - #ax = df.plot.bar(stacked=True) - ax = df[:-1].plot.bar(stacked=True, legend=False, figsize=(10,4)) - - #Format Y Axis - vals = ax.get_yticks() - ax.set_yticklabels(['{:3.0f}%'.format(x*100) for x in vals]) - - #Format X Axis - visible = ax.xaxis.get_ticklabels()[::6] - for label in ax.xaxis.get_ticklabels(): - if label not in visible: - label.set_visible(False) - ax.xaxis.set_major_formatter(plt.FixedFormatter(df.index.to_series().dt.strftime("%b %Y"))) - ax.xaxis.set_label_text("") - - return ax def calc_trade_performance_stats(): df = trade_performance().set_index('trade_date') @@ -222,11 +118,10 @@ 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) - df[df.days_held.notnull()]['days_held'].groupby(pd.Grouper(freq='A')).mean() + def get_rmbs_pos_df(date = None): engine = dbengine('dawndb') |