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
|
from glob import iglob
import os
import pandas as pd
from itertools import chain
from dates import bus_day
import pdb
def get_globs(fname, years=['2013', '2014', '2015', '2016']):
basedir = '/home/share/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
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']
for col in ['Strat','InvCcy','Fund','Port']:
df[col] = df[col].astype('category')
df.to_hdf('globeop.hdf', 'valuation_report', format='table', complib='blosc')
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
if date in df:
print(date)
df[date] = pd.read_csv(f)
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__M_", "", 1)
for col in ['Fund', 'Strat', 'Port', 'LongShortIndicator', 'InvCcy']:
df[col] = df[col].astype('category')
df.to_hdf('globeop.hdf', 'pnl', format='table', complib='blosc')
def ts(s):
return pd.Timestamp(s)
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', how='last')
# clo.groupby(level=0).sum()['2015-12-01':'2015-12-31']
|
