1 files changed, 54 insertions, 2 deletions
diff --git a/python/load_globeop_report.py b/python/load_globeop_report.py
index ea150b2e..68c1aa9d 100644
--- a/python/load_globeop_report.py
+++ b/python/load_globeop_report.py
@@ -43,7 +43,7 @@ def valuation_reports():
     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 = [c.lower() for c in df.columns]
+    df.columns = df.columns.str.lower()
 
     df.to_sql('val_reports', dbengine('dawndb'), if_exists='append', index=False)
 
@@ -66,12 +66,64 @@ def pnl_reports():
 
     ## cleanups
     df = df.reset_index()
-    df.columns = [c.lower() for c in df.columns]
+    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',