from glob import iglob from db import dbengine from pandas.tseries.offsets import MonthEnd 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']): 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.Grouper(freq='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 = 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 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" 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': {'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.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_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) #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 def get_net_navs(): 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() 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') 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'): 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() 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_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') 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['curr_face'] = df.principal_payment/(df.price/100) index = ['All','2017','2016','2015','2014','2013'] results = pd.DataFrame(index = index) win_per = len(df[df.winners].index)/len(df) loss_per = 1- win_per temp = {} temp1 = {} 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[] = len(df2[df2.winners == x].index)/len(df) df[df.days_held.notnull()]['days_held'].groupby(pd.Grouper(freq='A')).mean()