1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
|
from glob import iglob
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
def get_monthly_pnl():
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()
def curr_port_PNL(date = datetime.date.today(), asset_class='Subprime'):
date = (date - pd.tseries.offsets.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 + 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')
return df.join(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()
|
