import os import pandas as pd def create_trigger_function(db): # need to escape % with %% auto_dealid = db.DDL(""" CREATE OR REPLACE FUNCTION auto_dealid() RETURNS TRIGGER AS $$ DECLARE stub text; sqlstr text; BEGIN sqlstr:= 'UPDATE '|| TG_TABLE_NAME ||' SET %%s WHERE id = %%L AND dealid is NULL'; IF (TG_TABLE_NAME = 'bonds') THEN stub := 'SC_'; sqlstr := format(sqlstr, 'dealid = $1||upper(left(asset_class::text,3))||id, identifier = COALESCE(identifier, cusip, isin)', NEW.id); 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; RETURN NEW; END; $$ language plpgsql""") db.event.listen(db.metadata, 'before_create', auto_dealid) def create_triggers(db): trigger = db.DDL("""CREATE TRIGGER dealid AFTER INSERT ON %(fullname)s FOR EACH ROW EXECUTE PROCEDURE auto_dealid()""") for name, tb in db.metadata.tables.items(): if not name.endswith('counterparties') and not name.endswith('accounts'): db.event.listen(tb, 'after_create', trigger) 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.to_sql('counterparties', engine, if_exists='append', index=False) def load_trades(engine, schema=None): blotter = pd.read_excel("/home/serenitas/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, schema=schema) return blotter def load_trades_futures(engine, schema=None): blotter = pd.read_excel("/home/serenitas/Daily/Blotter.xlsm", 'Futures', 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(25, 27)] + \ ['Deal Type', 'Asset Class', 'Initial Margin Currency', 'Client', 'State'], axis=1, inplace=True) blotter.rename(columns = {'Trade Date': 'trade_date', 'Settlement Date': 'settle_date', 'Cash Account': 'cashaccount', 'Strategy': 'folder', 'Quantity': 'quantity', 'Commission': 'commission', 'CP Alias': 'cp_code', 'Description': 'security_desc', 'Buy/Sell': 'buysell', 'Price': 'price', 'Deal ID': 'id', 'Exchange': 'exchange', 'Ticker': 'bbg_ticker', 'Maturity': 'maturity', 'Action': 'action', 'Custodian': 'custodian', 'Trade Currency': 'currency', 'Swap Type': 'swap_type', 'Counterparty': 'cp_code'}, inplace=True) blotter.buysell = blotter.buysell.apply(lambda x: x=='Buy') blotter['action'] = 'NEW' blotter['cashaccount'] = 'IANSCLMAFU' blotter['swap_type'] = 'FUTURE' blotter['id'] = blotter['id'].str.replace('[A-Z_]', '').astype('int') blotter.to_sql('futures', engine, if_exists='append', index=False, schema=schema) return blotter def load_wires(engine, schema=None): blotter = pd.read_excel("/home/serenitas/Daily/Blotter_gh.xlsm", 'Wires', 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, 25)] + \ ['Deal Type', 'Asset Class', 'Client', 'State', "Counterparty", "Settlement Date", "Instrument Type", "Transaction Type", "Custodian", "CashAccount"], axis=1, inplace=True) blotter.rename(columns = {'Date': 'trade_date', 'Account': 'code', 'Cash Account': 'cashaccount', 'Strategy': 'folder', 'Deal ID': 'id', 'Action': 'action', 'Currency': 'currency', 'Amount': 'amount'}, inplace=True) blotter.folder = blotter.folder.str.rstrip() blotter['action'] = 'NEW' blotter['id'] = blotter['id'].str.replace('[A-Z_]', '').astype('int') blotter.to_sql('wires', engine, if_exists='append', index=False, schema=schema) return blotter if __name__ == "__main__": """ This script will create the tables and triggers for the Dawn app. If schema is not None, it will create it under a specific schema""" #conn.execute("CREATE SCHEMA IF NOT EXISTS {}".format(schema)) #conn.execute("SET search_path TO %s", (schema,)) os.environ['CONF'] = 'config.ini' from Dawn import db, app create_trigger_function(db) create_triggers(db) db.create_all() #load_wires(db.engine, app.config['SCHEMA'])