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
69
70
71
72
73
74
|
import csv
from serenitas.utils.db import dbconn, dawn_engine
import datetime
from serenitas.utils.misc import rename_keys
import pandas as pd
from sqlalchemy.exc import IntegrityError
from io import StringIO
from serenitas.utils.env import DAILY_DIR
from serenitas.utils.remote import SftpClient
from csv_headers.bond_upload import BBH_BONDS
if __name__ == "__main__":
conn = dbconn("dawndb")
with conn.cursor() as c:
headers = BBH_BONDS
sql_query = "SELECT bond_trades.*, counterparties.dtc_number FROM bond_trades LEFT JOIN counterparties ON cp_code=code WHERE cash_counterparty AND trade_date >= '2022-04-05' AND fund='BRINKER' and faceamount is not null and faceamount >0;"
c.execute(
sql_query, (datetime.date(2022, 4, 5),)
) # We don't want to upload trades before this date
for row in c:
obj = row._asdict()
if obj["current_face"] == 0:
raise Exception(f"Current face missing {obj['dealid']}")
rename_keys(
obj,
{
"dealid": "Client Reference Number",
"identifier": "Security ID",
"accrued_payment": "Interest Amount",
"dtc_number": "Trading Broker Type/ID",
"principal_payment": "Principal Amount",
"faceamount": "Unit / Original Face Amount",
"current_face": "Current Face/Amortize Value",
"price": "Unit Price Amount",
"net_amount": "Net Amount",
},
)
obj["Trade Date"] = obj["trade_date"].strftime("%m/%d/%Y")
obj["Settlement Date"] = obj["settle_date"].strftime("%m/%d/%Y")
obj["Place of Settlement/Country"] = "DTCYUS33"
obj["Transaction Type"] = "RVP" if obj["buysell"] else "DVP"
obj["Function of Instruction"] = "NEWM"
obj["Account Number"] = "4023461"
obj["Currency"] = "USD"
obj["Clearing Broker ID / Type"] = obj["Trading Broker Type/ID"]
obj["Other Fees Amount"] = 0
obj["Commission Amount"] = 0
obj["SEC Fees Amount"] = 0
new_obj = {"tradeid": obj["tradeid"]}
for header in headers:
new_obj[header] = obj.get(header, None)
df = pd.DataFrame(
new_obj,
index=[
"i",
],
)
try:
df.to_sql(
"bbh_bond_upload", dawn_engine, index=False, if_exists="append"
)
except IntegrityError:
conn.rollback()
else:
buf = StringIO()
csvwriter = csv.writer(buf)
csvwriter.writerow(headers)
csvwriter.writerow([obj.get(header, None) for header in headers])
buf = buf.getvalue().encode()
fname = f'LMCG_BBH_TRADES_P.{obj["Client Reference Number"].replace("_", "")}.csv'
dest = DAILY_DIR / str(datetime.date.today()) / fname
sftp = SftpClient.from_creds("bbh")
sftp.put(buf, fname)
dest.write_bytes(buf)
|
