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
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
|
from glob import iglob
import os
import pandas as pd
from itertools import chain
from dates import bus_day
from db import dbengine
def get_globs(fname, years=['2013', '2014', '2015', '2016', '2017']):
basedir = '/home/serenitas/Daily'
globs = [iglob(os.path.join(basedir,
year,
"{0}_*/{0}*/Reports/{1}.csv".format(year, fname)))
for year in years]
for year in years[-2:]:
globs.append(iglob(os.path.join(basedir,
'{0}-*/Reports/{1}.csv'.format(year,
fname))))
return globs
def valuation_reports():
df = pd.DataFrame()
for f in chain.from_iterable(get_globs('Valuation_Report')):
try:
date = pd.Timestamp(f.split('/')[6])
except ValueError:
date = pd.Timestamp(f.split('/')[4])
if date >= pd.Timestamp('2013-02-06'):
newdf = pd.read_csv(f, parse_dates=['KnowledgeDate','PeriodEndDate'])
else:
newdf = pd.read_csv(f)
newdf['KnowledgeDate'] = date
newdf['PeriodEndDate'] = date - bus_day
newdf['row'] = newdf.index
if newdf.empty or ('PeriodEndDate' in df and \
not df[df.PeriodEndDate == newdf.PeriodEndDate.iat[0]].empty):
continue
df = df.append(newdf)
del df['AccountingPeriod']
## cleanups
df.Strat = df.Strat.str.replace("^(SERCGMAST__){1,2}(M_|SER_)?", "", 1)
df.Port = df.Port.str.replace("^(SERCGMAST__){1,2}(SERG__|SERG_)?", "", 1)
for col in ['Strat', 'InvCcy', 'Fund', 'Port']:
df[col] = df[col].astype('category')
df.columns = df.columns.str.lower()
df.to_sql('val_reports', dbengine('dawndb'), if_exists='append', index=False)
def pnl_reports():
df = {}
for f in chain.from_iterable(get_globs('Pnl')):
try:
date = pd.Timestamp(f.split('/')[6])
except ValueError:
date = pd.Timestamp(f.split('/')[4])
date = date - bus_day
df[date] = pd.read_csv(f)
df[date]['row'] = df[date].index
df = pd.concat(df, names=['date', 'to_drop'])
df.reset_index(level='to_drop', drop=True, inplace=True)
df.Strat = df.Strat.str.replace("^(SERCGMAST__){1,2}(M_|SER_)?", "", 1)
df.Port = df.Port.str.replace("^(SERCGMAST__){1,2}(SERG__|SERG_)?", "", 1)
for col in ['Fund', 'Strat', 'Port', 'LongShortIndicator', 'InvCcy']:
df[col] = df[col].astype('category')
## cleanups
df = df.reset_index()
df.columns = df.columns.str.lower()
df['longshortindicator'] = df['longshortindicator'].str.strip()
df.columns = [c.replace(" ", "") for c in df.columns]
df.to_sql('pnl_reports', dbengine('dawndb'), if_exists='append', index=False)
def cds_reports():
df = {}
for f in chain.from_iterable(get_globs('CDS_Report')):
try:
date = pd.Timestamp(f.split('/')[6])
except ValueError:
date = pd.Timestamp(f.split('/')[4])
date = date - bus_day
df[date] = pd.read_csv(f)
df[date]['row'] = df[date].index
df = pd.concat(df, names=['date', 'to_drop'])
df.reset_index(level='to_drop', drop=True, inplace=True)
for col in ['Buy/Sell', 'Counterparty', 'CCP', 'Ccy', 'Direction', 'Price Ccy',
'Period End Date', 'Basis', 'Roll Convention', 'Settle Mode',
'Strategy', 'Trade Type', 'Trade Status', 'Prime Broker']:
df[col] = df[col].astype('category')
for col in df.columns:
if 'Date' in col and col != 'Period End Date':
df[col] = pd.to_datetime(df[col])
for col in df.columns:
vc = len(df[col].value_counts())
if vc == 0:
del df[col]
continue
if df[col].dtype == 'object' and vc < 20:
df[col] = df[col].astype('category')
contract = df['Contractual Definition']
contract = contract.where(contract.isin(['ISDA2014', 'ISDA2003Cred']), 'ISDA2014').astype('category')
df['Contractual Definition'] = contract
df = df.drop(['Bloomberg Yellow key', 'Created User', 'Last Modified User',
'Last Modified Date', 'Fund Long Name', 'Instrument Sub Type',
'Netting Id', 'Client', 'Trade Status', 'Position Status',
'Clearing Broker', 'Settle Mode', 'Off Price', 'On Price',
'Price Ccy'],
axis=1)
df.columns = df.columns.str.lower()
df.columns = df.columns.str.replace(" ", "_")
df.roll_convention = df.roll_convention.str.title()
df = df[df.strategy != 'SER_TEST']
df.loc[df.strategy == 'SERCGMAST__MBSCDS', 'strategy'] = 'MBSCDS'
df.strategy = df.strategy.str.replace("SER_","")
df.loc[df['buy/sell'].isnull(), 'buy/sell'] = df.loc[df['buy/sell'].isnull(), 'direction']
df['buy/sell'].cat.categories = ['Buyer', 'Seller']
del df['direction']
df.prime_broker = df.prime_broker.cat.remove_categories('NONE')
df.calendar = df.calendar.str.replace(" ", "")
df['executing_broker'] = df['executing_broker'].astype('object')
df.loc[df.executing_broker.isnull(),'executing_broker'] = df[df.executing_broker.isnull()].counterparty
del df['counterparty']
df = df.rename(columns={'executing_broker': 'counterparty'})
return df
def monthly_pnl_bycusip(df, strats):
df = df[(df.Strat.isin(strats)) & (df.CustAcctName=='V0NSCLMAMB')]
pnl_cols = ['BookUnrealMTM', 'BookRealMTM', 'BookRealIncome', 'BookUnrealIncome',
'TotalBookPL']
return df.groupby('InvId').resample('M').last()[['MTD '+col for col in pnl_cols]]
if __name__=='__main__':
valuation_reports()
pnl_reports()
df_val = pd.read_hdf('globeop.hdf', 'valuation_report')
df_pnl = pd.read_hdf('globeop.hdf', 'pnl')
nav = df_val[df_val.Fund == 'SERCGMAST'].groupby('PeriodEndDate')['EndBookNAV'].sum()
subprime_strats = ['MTG_GOOD', 'MTG_RW', 'MTG_IO','MTG_THRU', 'MTG_B4PR']
clo_strats = ['CLO_BBB', 'CLO_AAA', 'CLO_BB20']
## daily pnl by cusip
#subprime_daily_pnl = daily_pnl_bycusip(df_pnl, subprime_strats)
df_monthly = monthly_pnl_bycusip(df_pnl, subprime_strats)
#df_monthly.loc[idx[ts('2015-01-01'):ts('2015-01-31'),:],:]
# clo = df_pnl[df_pnl.Strat.isin(clo_strats)]
# clo_monthly_pnl = clo.groupby(level=0).sum()['MTD TotalBookPL'].resample('M').last()
# clo.groupby(level=0).sum()['2015-12-01':'2015-12-31']
df_val.set_index(['custacctname', 'periodenddate', 'invid', 'strat'])
|
