diff options
Diffstat (limited to 'python/dawn_utils.py')
| -rw-r--r-- | python/dawn_utils.py | 89 |
1 files changed, 44 insertions, 45 deletions
diff --git a/python/dawn_utils.py b/python/dawn_utils.py index 0268b4cc..1ce4007d 100644 --- a/python/dawn_utils.py +++ b/python/dawn_utils.py @@ -1,5 +1,3 @@ -from sqlalchemy.sql import text -from sqlalchemy import create_engine import os import pandas as pd @@ -17,20 +15,21 @@ def create_trigger_function(db): stub := 'SC_'; sqlstr := format(sqlstr, 'dealid = $1||upper(left(asset_class::text,3))||id, identifier = COALESCE(identifier, cusip, isin)', NEW.id); - ELSIF (TG_TABLE_NAME = 'cds') THEN - stub := 'SCCDS'; - sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id); - ELSIF (TG_TABLE_NAME = 'repo') THEN - stub := 'SC_REP'; - sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id); - ELSIF (TG_TABLE_NAME = 'swaptions') THEN - stub := 'SWPTN'; - sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id); - ELSIF (TG_TABLE_NAME = 'futures') THEN - stub := 'SCFUT'; - sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id); - ELSIF (TG_TABLE_NAME = 'wires') THEN - stub := 'SCCSH'; + ELSE + CASE TG_TABLE_NAME + WHEN 'cds' THEN + stub := 'SCCDS'; + WHEN 'repo' THEN + stub := 'SC_REP'; + WHEN 'swaptions' THEN + stub := 'SWPTN'; + WHEN 'futures' THEN + stub := 'SCFUT'; + WHEN 'wires' THEN + stub := 'SCCSH'; + WHEN 'capfloors' THEN + stub := 'CAP'; + END CASE; sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id); END IF; EXECUTE sqlstr USING stub; @@ -52,19 +51,19 @@ def load_counterparties(engine): 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', - 'DTC Number': 'dtc_number', - 'Email1': 'sales_email', - 'FIRM': 'name', - 'Phone': 'sales_phone', - 'Sales Contact': 'sales_contact', - 'Valuation Contact1': 'valuation_contact1', - 'Valuation Contact1 Email': 'valuation_email1', - 'Valuation Contact2': 'valuation_contact2', - 'Valuation Contact2 Email': 'valuation_email2', - 'Valuation Contact3': 'valuation_contact3', - 'Valuation Contact3 Email': 'valuation_email3', - 'Valuation Note': 'notes'}, inplace=True) + counterparties.rename(columns={'CODE': 'code', + 'DTC Number': 'dtc_number', + 'Email1': 'sales_email', + 'FIRM': 'name', + 'Phone': 'sales_phone', + 'Sales Contact': 'sales_contact', + 'Valuation Contact1': 'valuation_contact1', + 'Valuation Contact1 Email': 'valuation_email1', + 'Valuation Contact2': 'valuation_contact2', + 'Valuation Contact2 Email': 'valuation_email2', + 'Valuation Contact3': 'valuation_contact3', + 'Valuation Contact3 Email': 'valuation_email3', + 'Valuation Note': 'notes'}, inplace=True) counterparties.to_sql('counterparties', engine, if_exists='append', index=False) def load_trades(engine, schema=None): @@ -74,21 +73,21 @@ def load_trades(engine, schema=None): 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.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' @@ -166,4 +165,4 @@ if __name__ == "__main__": create_trigger_function(db) create_triggers(db) db.create_all() - load_wires(db.engine, app.config['SCHEMA']) + #load_wires(db.engine, app.config['SCHEMA']) |