1 files changed, 105 insertions, 39 deletions

diff --git a/python/position_file_bowdst.py b/python/position_file_bowdst.py
index 534f5a9b..2d45ead0 100644
--- a/python/position_file_bowdst.py
+++ b/python/position_file_bowdst.py
@@ -1,52 +1,82 @@
 from serenitas.utils.db import dbconn
-import datetime 
+import datetime
 import csv
 from io import StringIO
 from pathlib import Path
 from process_queue import rename_keys
 
 
-
 def process_upload(trades, asset_type):
     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()
-    dest = Path('/home/serenitas/flint/test.csv')
+    dest = Path("/home/serenitas/flint/{asset_type}.csv")
     dest.write_bytes(buf)
 
+
 def build_line(obj, asset_type):
     return [obj.get(h, None) for h in HEADERS[asset_type]]
-#variables
 
-date = datetime.date(2021,10,28)
 
+# variables
 
-dawndb = dbconn('dawndb')
+date = datetime.date(2021, 11, 9)
 
 
+dawndb = dbconn("dawndb")
 
-HEADERS = {
-        "bond":
-            [
-                "AccountNumber",
-                "COB Date",
-                "Prime Broker",
-                "SecurityType",
-                "CUSIP",
-                "ISIN",
-                "SEDOL",
-                "SecurityDescription",
-                "Position",
-                "MarketPrice",
-                "Currency",
-                "Base Market Value",
-                "Local Market Value",
-                "Fx Rate"
 
-            ],
-    }
+HEADERS = {
+    "bond": [
+        "AccountNumber",
+        "COB Date",
+        "Prime Broker",
+        "SecurityType",
+        "CUSIP",
+        "ISIN",
+        "SEDOL",
+        "SecurityDescription",
+        "Position",
+        "MarketPrice",
+        "Currency",
+        "Base Market Value",
+        "Local Market Value",
+        "Fx Rate",
+    ],
+    "otc": [
+        "Client Name",
+        "Fund Name",
+        "Counterparty",
+        "Product Type",
+        "Unique Deal ID",
+        "TransactionIndicator (Buy/Sell)",
+        "PutCall Indicator (Call/Put)",
+        "CapFloorIndicator",
+        "CurrencyPair",
+        "DealCurrencyA",
+        "DealCurrencyB",
+        "NotionalA",
+        "NotionalB",
+        "OriginalPrice",
+        "Strike",
+        "FixedRate",
+        "Quantity",
+        "Start Date",
+        "Effective Date",
+        "Maturity Date",
+        "Underlying Maturity",
+        "RecPayFixed",
+        "Underlying (ISIN / CUSP / RED CODES)",
+        "Underlying Desc",
+        "Exercise Type",
+        "MTM Currency",
+        "MTM Valuation",
+        "COB Date",
+        "Clearing House Name",
+    ],
+}
 
 
 with dawndb.cursor() as c:
@@ -54,21 +84,57 @@ with dawndb.cursor() as c:
     trades = []
     for row in c:
         obj = row._asdict()
-        rename_keys(obj, {
-            'identifier': 'CUSIP',
-            'description': 'SecurityDescription',
-            'notional': 'Position',
-            'price': 'MarketPrice',
-            'local_market_value': 'Local Market Value',
-            'usd_market_value': 'Base Market Value',
-        })
+        rename_keys(
+            obj,
+            {
+                "identifier": "CUSIP",
+                "description": "SecurityDescription",
+                "notional": "Position",
+                "price": "MarketPrice",
+                "local_market_value": "Local Market Value",
+                "usd_market_value": "Base Market Value",
+            },
+        )
         try:
-            obj['Fx Rate'] = obj['Local Market Value'] / obj['Base Market Value']
+            obj["Fx Rate"] = obj["Local Market Value"] / obj["Base Market Value"]
         except ZeroDivisionError:
-            obj['Fx Rate'] = 1
-        obj['AccountNumber'] = "TELHEEACPB"
-        obj['Prime Broker'] = 'TEST'
-        obj['COB Date'] = date
+            obj["Fx Rate"] = 1
+        obj["AccountNumber"] = "TELHEEACPB"
+        obj["Prime Broker"] = "TEST"
+        obj["COB Date"] = date
         trades.append(obj)
-        process_upload(trades, 'bond')
+        process_upload(trades, "bond")
 
+    c.execute(
+        "SELECT trb.trade_id, trb.serenitas_clean_nav + trb.serenitas_accrued as mtm, cds.* FROM tranche_risk_bowdst trb left join cds on trade_id=id WHERE date=%s",
+        (date,),
+    )
+    trades = []
+    for row in c:
+        obj = row._asdict()
+        obj["Client Name"] = "HEDGEMARK"
+        obj["Fund Name"] = "BOS_PAT_BOWDOIN"
+        obj["Product Type"] = "Credit Index Tranche"
+        # obj['TransactionIndicator (Buy/Sell)'] = 'Buyer' if obj['notional'] > 0 else 'Seller'
+        obj["MTM Currency"] = "USD"
+        obj["COB Date"] = date
+        obj["Clearing House Name"] = "Bilateral"
+        rename_keys(
+            obj,
+            {
+                "dealid": "Unique Deal ID",
+                "cp_code": "Counterparty",
+                "currency": "DealCurrencyA",
+                "notional": "NotionalA",
+                "fixed_rate": "FixedRate",
+                "trade_date": "Start Date",
+                "effective_date": "EffectiveDate",
+                "maturity": "Maturity Date",
+                "security_id": "Underlying (ISIN / CUSP / RED CODES)",
+                "security_desc": "Underlying Desc",
+                "protection": "TransactionIndicator (Buy/Sell)",
+                "mtm": "MTM Valuation",
+            },
+        )
+        trades.append(obj)
+        process_upload(trades, "otc")