diff options
| -rw-r--r-- | python/upload_bbh_trades.py | 79 |
1 files changed, 45 insertions, 34 deletions
diff --git a/python/upload_bbh_trades.py b/python/upload_bbh_trades.py index 9a7cefb7..18b48656 100644 --- a/python/upload_bbh_trades.py +++ b/python/upload_bbh_trades.py @@ -9,47 +9,57 @@ from serenitas.utils.env import DAILY_DIR from serenitas.utils.remote import SftpClient from csv_headers.bond_upload import BBH_BONDS as headers + +def _include_headers_only(obj, headers): + new_obj = {} + for header in headers: + new_obj[header] = obj.get(header, None) + return new_obj + + +def _serialize(obj): + 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", + }, + ) + trade_details = { + "Trade Date": obj["trade_date"].strftime("%m/%d/%Y"), + "Settlement Date": obj["settle_date"].strftime("%m/%d/%Y"), + "Place of Settlement/Country": "DTCYUS33", + "Transaction Type": "RVP" if obj["buysell"] else "DVP", + "Function of Instruction": "NEWM", + "Account Number": "4023461", + "Currency": "USD", + "Clearing Broker ID / Type": obj["Trading Broker Type/ID"], + "Other Fees Amount": 0, + "Commission Amount": 0, + "SEC Fees Amount": 0, + } + obj.update(trade_details) + return _include_headers_only(obj, headers) + + 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' 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 + c.execute(sql_query) # We don't want to upload trades before 2022-04-05 + 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) + obj = _serialize(obj) df = pd.DataFrame( - new_obj, + obj, index=[ "i", ], @@ -61,6 +71,7 @@ if __name__ == "__main__": except IntegrityError: conn.rollback() else: + breakpoint() buf = StringIO() csvwriter = csv.writer(buf) csvwriter.writerow(headers) |