diff options
Diffstat (limited to 'python/Dawn/utils.py')
| -rw-r--r-- | python/Dawn/utils.py | 39 |
1 files changed, 37 insertions, 2 deletions
diff --git a/python/Dawn/utils.py b/python/Dawn/utils.py index b478f3ad..50b01ced 100644 --- a/python/Dawn/utils.py +++ b/python/Dawn/utils.py @@ -3,9 +3,10 @@ from sqlalchemy.sql import text import os import re from pickle import dumps +from sqlalchemy import create_engine def load_counterparties(engine): - counterparties = pd.read_csv("/home/share/CorpCDOs/data/counterparties.csv") + counterparties = pd.read_excel("/home/share/Daily/blotter.xlsm", 'Counterparties') counterparties[['city', 'state']] = counterparties.Location.str.split(", ", expand=True) counterparties.drop(['Location', 'Valuation Contact4', 'Valuation Contact4 Email'], axis=1, inplace=True) counterparties.rename(columns ={'CODE': 'code', @@ -23,12 +24,42 @@ def load_counterparties(engine): 'Valuation Note': 'notes'}, inplace=True) counterparties.to_sql('counterparties', engine, if_exists='append', index=False) +def load_trades(engine): + blotter = pd.read_excel("/home/share/Daily/blotter.xlsm", 'Bonds', + skiprows = [0, 1, 2, 3, 4]) + blotter.dropna(axis=0, subset=['Deal ID'], inplace=True) + blotter = blotter.iloc[:,2:] + blotter.drop(['Unnamed: %s' % (i,) for i in range(19, 28)] + + ['Comments', 'Acc Int $', 'Counterparty'], axis=1, inplace=True) + blotter.rename(columns = {'Date': 'trade_date', + 'Settle Date': 'settle_date', + 'Strategy': 'folder', + 'Custodian': 'custodian', + 'Cash Account': 'cashaccount', + 'CP Alias': 'cp_code', + 'CUSIP': 'cusip', + 'ISIN': 'isin', + 'Description': 'description', + 'Buy/Sell': 'buysell', + 'Notional': 'faceamount', + 'Price': 'price', + 'Acc Int': 'accrued', + 'Asset Class': 'asset_class', + 'Deal ID': 'id'}, inplace=True) + blotter.buysell = blotter.buysell.apply(lambda x: x=='Buy') + blotter['action'] = 'NEW' + blotter['cashaccount'] = 'V0NSCLMAMB' + blotter['id'] = blotter['id'].str.replace('[A-Z_]', '').astype('int') + blotter.loc[blotter.asset_class == 'CLO','id'] = blotter.loc[blotter.asset_class == 'CLO','id'] + 5 + blotter.to_sql('bonds', engine, if_exists='append', index=False) + return blotter + def add_triggers(engine): engine.execute(text(""" CREATE OR REPLACE FUNCTION mydealid() RETURNS TRIGGER AS $$ BEGIN - UPDATE bonds SET dealid = 'SC_' ||left(asset_class::text,3)||id WHERE bonds.id = NEW.id; + UPDATE bonds SET dealid = 'SC_' ||upper(left(asset_class::text,3))||id WHERE bonds.id = NEW.id; RETURN NEW; END; $$ language plpgsql""")) @@ -47,3 +78,7 @@ def bump_rev(filename): def simple_serialize(obj): return dumps({c.name: getattr(obj, c.name) for c in obj.__table__.columns}) + +if __name__=="__main__": + engine = create_engine('postgresql://dawn_user@debian/dawndb') + load_trades(engine) |