diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/Dawn/utils.py | 94 | ||||
| -rw-r--r-- | python/dawn_utils.py | 114 |
2 files changed, 112 insertions, 96 deletions
diff --git a/python/Dawn/utils.py b/python/Dawn/utils.py index 2ce8beb8..75ef43d7 100644 --- a/python/Dawn/utils.py +++ b/python/Dawn/utils.py @@ -2,91 +2,6 @@ import pandas as pd import os import re from pickle import dumps -from sqlalchemy import create_engine - -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 bump_rev(filename): pattern = "([^r]*)(\srev(\d)|).pdf" @@ -96,12 +11,3 @@ 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__": - qa = False - if qa: - engine = create_engine('postgresql://qa@debian/dawndb') - load_trades_futures(engine, 'qa') - else: - engine = create_engine('postgresql://dawn_user@debian/dawndb') - load_trades_futures(engine) diff --git a/python/dawn_utils.py b/python/dawn_utils.py index 60a90d1f..0268b4cc 100644 --- a/python/dawn_utils.py +++ b/python/dawn_utils.py @@ -1,6 +1,7 @@ from sqlalchemy.sql import text from sqlalchemy import create_engine import os +import pandas as pd def create_trigger_function(db): # need to escape % with %% @@ -44,16 +45,125 @@ def create_triggers(db): FOR EACH ROW EXECUTE PROCEDURE auto_dealid()""") for name, tb in db.metadata.tables.items(): - if not name.endswith('counterparties'): + 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 + from Dawn import db, app create_trigger_function(db) create_triggers(db) db.create_all() + load_wires(db.engine, app.config['SCHEMA']) |