diff options
Diffstat (limited to 'python/Dawn/utils.py')
| -rw-r--r-- | python/Dawn/utils.py | 51 |
1 files changed, 45 insertions, 6 deletions
diff --git a/python/Dawn/utils.py b/python/Dawn/utils.py index 99451080..2ce8beb8 100644 --- a/python/Dawn/utils.py +++ b/python/Dawn/utils.py @@ -23,9 +23,9 @@ 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]) +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)] + @@ -50,9 +50,43 @@ def load_trades(engine): 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) + 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" @@ -64,5 +98,10 @@ 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) + 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) |