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 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.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,]) 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', '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'): """ 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') 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.TimeGrouper(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.TimeGrouper(freq='A')).mean()