1 files changed, 68 insertions, 0 deletions
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)