diff options
Diffstat (limited to 'python/globeop_reports.py')
| -rw-r--r-- | python/globeop_reports.py | 75 |
1 files changed, 64 insertions, 11 deletions
diff --git a/python/globeop_reports.py b/python/globeop_reports.py index f5fa8dbb..e44641b9 100644 --- a/python/globeop_reports.py +++ b/python/globeop_reports.py @@ -7,6 +7,7 @@ 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']): @@ -18,10 +19,15 @@ def get_monthly_pnl(group_by = ['identifier']): monthend_pnl = df_pnl.groupby(pd.TimeGrouper('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): - 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,]) +def get_portfolio(report_date = False): + if report_date != False: + 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,]) + else: + sql_string = "SELECT * FROM valuation_reports" + df = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates=['periodenddate'], + index_col=['periodenddate']) df['identifier'] = df.invid.str.replace("_A$", "") return df @@ -83,27 +89,34 @@ def get_net_navs(): df.at[('2013-01-31', 'begbooknav')] = 12500000 return df -def alloc(report_date, alloc = 'pnl'): +#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.loc[report_date.date()] + 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(report_date) - df.set_index(['strat','custacctname'], inplace=True) + 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']) + 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() + return df.groupby(['periodenddate', alloc]).sum() def pnl_alloc_plot(df): @@ -131,7 +144,7 @@ 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() + 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) @@ -150,6 +163,46 @@ def avg_turnover(): & (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.TimeGrouper('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.TimeGrouper('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() + df.loc[date, strat] = df.loc[date, strat] - amount/nav.loc[date].endbooknav + df.loc[date,'Cash'] = df.loc[date, 'Cash'] + amount/nav.loc[date].endbooknav + return df + +def cap_alloc_plot(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 + #plt.tight_layout() + def calc_trade_performance_stats(): df = trade_performance().set_index('trade_date') df.days_held = df.days_held.dt.days |