1 files changed, 163 insertions, 0 deletions
diff --git a/python/mtm_upload.py b/python/mtm_upload.py
new file mode 100644
index 00000000..a4042682
--- /dev/null
+++ b/python/mtm_upload.py
@@ -0,0 +1,163 @@
+from serenitas.utils.db import dbconn
+from process_queue import rename_keys
+from io import StringIO
+import csv
+from serenitas.utils.env import DAILY_DIR
+import datetime
+
+HEADERS = {
+    "tranche": [
+        "Markit Field Name",
+        "Swap ID",
+        "Allocation ID",
+        "Description",
+        "Broker Id",
+        "DTCC CounterParty ID",
+        "Trade ID",
+        "Trade Date",
+        "Effective Date",
+        "Settle Date",
+        "Maturity Date",
+        "Account Abbreviation",
+        "1st Leg Notional",
+        "Currency Code",
+        "1st Leg Rate",
+        "Initial Payment",
+        "Initial Payment Currency",
+        "Original Issue Date",
+        "Interest Payment Method Description",
+        "First Payment Date",
+        "Product Type",
+        "Product Sub Type",
+        "Transaction Type",
+        "Protection",
+        "Transaction Code",
+        "Remaining Party",
+        "DTCC Remaining CounterParty ID",
+        "Independent Amount (%)",
+        "Independent Amount ($)",
+        "RED",
+        "Issuer Name",
+        "Settlement Amount",
+        "Trader",
+        "Executing Broker",
+        "Dealer",
+        "Trade ID",
+        "Notes",
+        "Parent Transaction Code",
+        "Parent Trade Date",
+        "Parent Notional",
+        "Parent Currency Code",
+        "Parent Net Amount",
+        "Parent Effective Date",
+        "Parent First Payment Date",
+        "Parent Settle Date",
+        "ComplianceHubAction",
+        "DTCC Ineligible",
+        "Master Document Date",
+        "Master Document Version",
+        "Include Contractual Supplement",
+        "Contractual Supplement",
+        "Supplement Date",
+        "Entity Matrix",
+        "Entity Matrix Date",
+        "Modified Equity Delivery",
+        "Calculation Agent Business Center",
+        "Calculation Agent",
+        "Attachment Point",
+        "Exhaustion Point",
+        "Strategy",
+        "First Payment Period Accrual Start Date",
+        "TieOut Ineligible",
+        "Electronic Consent Ineligible",
+        "External OMS ID",
+        "Independent Amount Currency",
+        "Independent Amount Payer",
+        "Trade Revision",
+        "Alternate Swap ID",
+        "Alternate Trade ID",
+        "Definitions Type",
+    ]
+}
+
+
+def tranche_trades(conn):
+    with conn.cursor() as c:
+        trades = []
+        c.execute(
+            "SELECT * FROM cds where attach is not NULL and trade_date > %s",
+            (datetime.date(2021, 12, 1),),
+        )
+        for row in c:
+            obj = row._asdict()
+            rename_keys(
+                obj,
+                {
+                    "dealid": "Trade ID",
+                    "cp_code": "Broker Id",
+                    "trade_date": "Trade Date",
+                    "effective_date": "Effective Date",
+                    "maturity": "Maturity Date",
+                    "notional": "1st Leg Notional",
+                    "fixed_rate": "1st Leg Rate",
+                    "upfront": "Initial Payment",
+                    "security_id": "RED",
+                    "orig_attach": "Attachment Point",
+                    "orig_detach": "Exhaustion Point",
+                    "upfront_settle_date": "First Payment Date",
+                },
+            )
+            if obj["Initial Payment"] >= 0:
+                obj["Transaction Code"] = "Receive"
+            else:
+                obj["Initial Payment"] = abs(obj["Initial Payment"])
+                obj["Transaction Code"] = "Pay"
+            obj["Swap ID"] = obj["Trade ID"]
+            obj["Product Type"] = "TRN"
+            obj["Transaction Type"] = "NEW"
+            obj["Protection"] = "Buy" if obj["protection"] == "Buyer" else "Sell"
+            obj["Trader"] = "Serenitas_Trader"
+            obj["Entity Matrix"] = "Publisher"
+            obj["Definitions Type"] = "ISDA2003Credit"
+            obj["Independent Amount (%)"] = obj["initial_margin_percentage"]
+            if "ITRX" in obj["security_desc"]:
+                obj["Include Contractual Supplement"] = "Y"
+                obj["Contractual Supplement"] = "iTraxxEuropeTranche"
+            # Temporary Static Values
+            obj["Account Abbreviation"] = "Serenitas-test1"
+            obj["Currency Code"] = "USD"
+            obj["Initial Payment Currency"] = "USD"
+            obj["First Payment Date"] = "2022-01-18"
+            obj["Broker Id"] = "0000571T"
+            # obj['DTCC Ineligible'] = 'N'
+            # obj['Master Document Date'] = '2001-01-01'
+            trades.append(obj)
+    return trades
+
+
+def build_line(obj, asset_type):
+    return [obj.get(h, None) for h in HEADERS[asset_type]]
+
+
+def process_upload(trades, asset_type, upload):
+    buf = StringIO()
+    csvwriter = csv.writer(buf)
+    csvwriter.writerow(HEADERS[asset_type])
+    csvwriter.writerows(build_line(trade, asset_type) for trade in trades)
+    buf = buf.getvalue().encode()
+    fname = f"MTM.{datetime.datetime.now():%Y%m%d.%H%M%S}.{asset_type.capitalize()}.csv"
+    # if upload:
+    #     sftp = SftpClient.from_creds("hm_globeop")
+    #     sftp.client.chdir("incoming")
+    #     sftp.put(buf, fname)
+    dest = DAILY_DIR / str(datetime.date.today()) / fname
+    dest.write_bytes(buf)
+
+
+def upload_trades(conn):
+    process_upload(tranche_trades(conn), "tranche", upload=False)
+
+
+if __name__ == "__main__":
+    conn = dbconn("dawndb")
+    upload_trades(conn)