1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
|
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):
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 bump_rev(filename):
pattern = "([^r]*)(\srev(\d)|).pdf"
begin, _, rev_number = re.match(pattern, filename).groups()
rev_number = int(rev_number) + 1 if rev_number else 1
return "{0} rev{1}.pdf".format(begin, rev_number)
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)
|
