diff options
| -rw-r--r-- | python/load_globeop_report.py | 24 | ||||
| -rw-r--r-- | sql/dawn.sql | 77 |
2 files changed, 98 insertions, 3 deletions
diff --git a/python/load_globeop_report.py b/python/load_globeop_report.py index 03297caa..ea150b2e 100644 --- a/python/load_globeop_report.py +++ b/python/load_globeop_report.py @@ -3,6 +3,7 @@ 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' @@ -30,14 +31,21 @@ def valuation_reports(): 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.to_hdf('globeop.hdf', 'valuation_report', format='table', complib='blosc') + df.columns = [c.lower() for c in df.columns] + + df.to_sql('val_reports', dbengine('dawndb'), if_exists='append', index=False) def pnl_reports(): df = {} @@ -48,12 +56,21 @@ def pnl_reports(): 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__M_", "", 1) + 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') - df.to_hdf('globeop.hdf', 'pnl', format='table', complib='blosc') + + ## cleanups + df = df.reset_index() + df.columns = [c.lower() for c in df.columns] + 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 monthly_pnl_bycusip(df, strats): df = df[(df.Strat.isin(strats)) & (df.CustAcctName=='V0NSCLMAMB')] @@ -80,3 +97,4 @@ if __name__=='__main__': # 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']) diff --git a/sql/dawn.sql b/sql/dawn.sql index 1d2f0f4e..89e9091a 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -892,3 +892,80 @@ CREATE TABLE swaption_marks( delta float, gamma float, vega float) + +CREATE TYPE portfolio AS ENUM('CASH', 'CLO', 'CURVE', 'GFS_HELPER_BUSINESS_UNIT', 'HEDGE_MAC', 'HY', 'IG', 'LQD_TRANCH', 'MORTGAGES', + 'OPTIONS', 'SERCGLLC__SERCGLLC', 'SERCGLTD__SERCGLTD', 'SER_TEST__SER_TEST', 'STRUCTURED'); + +CREATE TYPE fund AS ENUM('SERCGLLC', 'SERCGLTD', 'SERCGMAST', 'SER_TEST') + +CREATE TYPE strategy AS ENUM('CLOCDSCSH', 'CLO_AAA', 'CLO_BB20', 'CLO_BBB', 'CSH_CASH', 'CSH_EXP', 'CSOCDSCSH', 'CSO_TRANCH', 'GFS_TRANSFER_HELPER', + 'HEDGE_CLO', 'HEDGE_CSO', 'HEDGE_MAC', 'HEDGE_MBS', 'HYCURVE', 'HYEQY', 'HYINX', 'HYMEZ', 'HYSNR', 'IGCDSCSH', + 'IGCURVE', 'IGCVECSH', 'IGEQY', 'IGINX', 'IGMEZ', 'IGOPTDEL', 'IGPAYER', 'IGSNR', 'IGTCDSCSH', 'LQD_TRANCH', + 'MACCDSCSH', 'MBSCDS', 'MBSCDSCSH', 'MTG_B4PR', 'MTG_FP', 'MTG_GOOD', 'MTG_IO', 'MTG_LMG', 'MTG_PR', + 'MTG_RW', 'MTG_SD', 'MTG_THRU', 'SERCGLLC__SERCGLLC', 'SERCGLTD__SERCGLTD', 'SERCGLTD__SERLTD_EXP', 'SER_TEST__SER_TEST', 'STR_MAV', 'STR_MEZZ') + +CREATE TABLE val_reports( + custacctname text, + endbookcost float, + endbookmv float, + endbooknav float, + endbookunrealfxgl float, + endbookunrealincome float, + endbookunrealmtm float, + endlocalcost float, + endlocalmv float, + endlocalmarketprice float, + endqty float, + fund fund, + gfstranid1 text, + invccy currency, + invdesc text, + invid text, + invtype text, + knowledgedate date NOT NULL, + periodenddate date NOT NULL, + port portfolio, + strat strategy, + row integer, + PRIMARY KEY(periodenddate, row) +); + +CREATE INDEX on val_reports (periodenddate); + +CREATE TYPE longshort AS ENUM('L', 'S'); + +CREATE TABLE pnl_reports( + date date, + fund fund, + port portfolio, + strat strategy, + longshortindicator longshort, + custacctname text, + pricelist text, + invassettype text, + invccy currency, + invdesc text, + invid text, + endqty float, + endlocalmarketprice float, + dailybookrealmtm float, + dailybookunrealmtm float, + dailybookrealfxgl float, + dailybookunrealfxgl float, + dailybookrealincome float, + dailybookunrealincome float, + dailybookmiscrevexp float, + dailytotalbookpl float, + mtdbookrealmtm float, + mtdbookunrealmtm float, + mtdbookrealfxgl float, + mtdbookunrealfxgl float, + mtdbookrealincome float, + mtdbookunrealincome float, + mtdbookmiscrevexp float, + mtdtotalbookpl float, + row integer, + PRIMARY KEY(date, row) +); + +CREATE INDEX on pnl_reports (date);
\ No newline at end of file |