diff options
| -rw-r--r-- | python/headers.py | 70 | ||||
| -rw-r--r-- | python/upload_bbh_trades.py | 68 |
2 files changed, 138 insertions, 0 deletions
diff --git a/python/headers.py b/python/headers.py index 53c0cfca..e271796a 100644 --- a/python/headers.py +++ b/python/headers.py @@ -12,6 +12,76 @@ class DealType(Enum): Fx = "FX" +BBH_BOND_HEADERS = [ + "Function of Instruction", + "Client Reference Number", + "Previous Reference Number", + "Account Number", + "Transaction Type", + "Place of Settlement/Country", + "Place of Safekeeping", + "Trade Date", + "Settlement Date", + "Security ID", + "Security Description", + "Unit / Original Face Amount", + "Currency", + "Unit Price Amount", + "Net Amount", + "Trading Broker Type/ID", + "Trading Broker Description", + "Beneficiary of Securities Account", + "Clearing Broker ID / Type", + "Clearing Broker Description", + "Clearing Agent Account", + "Stamp Duty Code", + "Stamp Duty Amount", + "Special Settlement Type", + "Special Indicator #1", + "Special Indicator #2", + "Registration Details", + "Special Instruction", + "Originator of Message", + "Current Face/Amortize Value", + "Principal Amount", + "Interest Amount", + "Other Fees Amount", + "Commission Amount", + "SEC Fees Amount", + "Transaction Tax Amount", + "Withholding Tax Amount", + "Exchange Rate", + "Resulting Currency", + "Resulting Amount", + "FX Currency", + "Pool Reference Number", + "Total Group Number", + "Trade Number", + "Repo Term Date (REPO only)", + "Repo Amount (REPO only)", + "Repo Reference Number (REPO only)", + "Repo Rate (REPO Only)", + "Ticker (CPF and CRF Only)", + "Strike Price (CPF and CRF Only)", + "Expiration Date (CPF and CRF Only)", + "Broker Number (CPF and CRF Only)", + "Broker Account (CPF and CRF Only)", + "Contract Size (Option Contract and Future Contract Only)", + "Place of Trade Narrative", + "Common Reference", + "Partial Settlement Allowed", + "Partial Settlement Tolerance", + "No Automatic Market Claim", + "Corporate Action", + "Coupon Option", + "Triparty Collateral Segregation", + "FX Cancel - For CANC instructions only", + "Fund Accounting Only Trade (RPTO)", + "Custody Only Trade (NACT)", + "Research Fee (RSCH)", +] + + HEADERS_PRE = [ "Deal Type", "Deal Id", diff --git a/python/upload_bbh_trades.py b/python/upload_bbh_trades.py new file mode 100644 index 00000000..68e6bf66 --- /dev/null +++ b/python/upload_bbh_trades.py @@ -0,0 +1,68 @@ +from headers import BBH_BOND_HEADERS +import csv +from serenitas.utils.db import dbconn, dawn_engine +import datetime +from process_queue import rename_keys +import pandas as pd +from sqlalchemy.exc import IntegrityError +from io import StringIO +from serenitas.utils.env import DAILY_DIR + +if __name__ == "__main__": + conn = dbconn("dawndb") + with conn.cursor() as c: + 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';" + c.execute(sql_query, (datetime.date(2022, 4, 5),)) + for row in c: + obj = row._asdict() + rename_keys( + obj, + { + "dealid": "Client Reference Number", + "identifier": "Security ID", + "accrued_payment": "Interest Amount", + "dtc_number": "Trading Broker Type/ID", + "principal_payment": "Principal Amount", + "trade_date": "Trade Date", + "settle_date": "Settlement Date", + "face_amount": "Unit / Original Face Amount", + "current_face": "Current Face/Amortize Value", + "price": "Unit Price Amount", + "net_amount": "Net Amount", + }, + ) + 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"] + new_obj = {"tradeid": obj["tradeid"]} + for header in BBH_BOND_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(BBH_BOND_HEADERS) + csvwriter.writerow( + [obj.get(header, None) for header in BBH_BOND_HEADERS] + ) + buf = buf.getvalue().encode() + dest = ( + DAILY_DIR + / str(datetime.date.today()) + / f'LMCG_BBH_TRADES_P.{obj["Client Reference Number"].replace("_", "")}.csv' + ) + dest.write_bytes(buf) |