1 files changed, 69 insertions, 9 deletions
diff --git a/python/parse_baml_swaption.py b/python/parse_baml_swaption.py
index 81f97c55..35196a85 100644
--- a/python/parse_baml_swaption.py
+++ b/python/parse_baml_swaption.py
@@ -5,7 +5,8 @@ from sqlalchemy.exc import IntegrityError
 import datetime
 import logging
 import argparse
-from serenitas.ops.trade_dataclasses import SwaptionDeal
+import re
+from serenitas.ops.trade_dataclasses import SwaptionDeal, desc_str
 
 parser = argparse.ArgumentParser()
 parser.add_argument(
@@ -39,6 +40,65 @@ columns = [
 ]
 
 
+# Counterparty Ticket to Database
+_baml_funds = {
+    "LMIL-Boston Patriot Bowdoin St LLC": "BOWDST",
+    "Iso Selene Inc.": "ISOSEL",
+    "Serenitas Credit Gamma Master Fund LP": "SERCGMAST",
+    "Lmcg Investments, Llc": "SERCGMAST",
+    "Reep-Ofc One Bowdoin Square Ma Llc Et Al (Cob)": "BOWDST",
+}
+
+
+def regex_baml_numbers(text: str):
+    return float(re.search(r"[\d,]+", text)[0].replace(",", ""))
+
+
+def from_baml_email(d: dict, conn):
+    buysell = "Bank Of America" in d["Index Seller"]
+    index, series = re.search(r"(HY|IG)\.([\d]{2})", d["OPTION RECAP"]).groups()
+    security_desc = desc_str(index, series, "5")
+    trade_date = datetime.datetime.strptime(d["Trade Date"], "%d-%b-%Y")
+    price = float(d["Price"]) / 100
+    sql_str = (
+        "SELECT * FROM index_desc WHERE INDEX=%s AND series=%s "
+        "AND lastdate >= %s AND tenor='5yr'"
+    )
+    with conn.cursor() as c:
+        c.execute(sql_str, (index, series, trade_date))
+        row = c.fetchone()
+
+    return SwaptionDeal(
+        buysell=buysell,
+        fund=_baml_funds[d["Index Buyer"]]
+        if buysell
+        else _baml_funds[d["Index Seller"]],
+        cp_code="BAMSNY",
+        portfolio="OPTIONS",
+        folder=index + ("PAYER" if d["Option Type"] == "Payer" else "REC"),
+        trade_date=trade_date,
+        settle_date=datetime.datetime.strptime(d["Premium Fee Pay Date"], "%d-%b-%Y"),
+        expiration_date=datetime.datetime.strptime(d["Expiry Date"], "%d-%b-%Y"),
+        notional=regex_baml_numbers(d["Notional"]),
+        option_type=d["Option Type"].upper(),
+        strike=d["Strike"],
+        swap_type="CD_INDEX_OPTION",
+        fixed_rate=row.coupon / 100,
+        maturity=row.maturity,
+        security_desc=security_desc,
+        security_id=row.redindexcode,
+        currency="USD",
+        price=price,
+        initial_margin_percentage=(
+            regex_baml_numbers(d["Collateral"])
+            / regex_baml_numbers(d["Premium Fee"])
+            * price
+        )
+        if d.get("Collateral")
+        else None,
+    )
+
+
 em = ExchangeMessage()
 start = datetime.datetime.combine(args.workdate, datetime.time.min).replace(
     tzinfo=em._account.default_timezone
@@ -47,17 +107,17 @@ for msg in em.get_msgs(
     path=["AutoBook", "BAML Swaption"], datetime_received__gte=start
 ):
     dfs = pd.read_html(msg.body)
-    trades = []
-    for df in dfs:
-        if len(df[0]) > 5:
-            trade = dict(zip(df[0].values, df[1].values))
-            if "Block" in trade or "Unwind Price" in trade:
+    tickets = []
+    for ticket in dfs:
+        if len(ticket[0]) > 5:
+            ticket = ticket.set_index(0).to_dict()[1]
+            if "Block" in ticket or "Unwind Price" in ticket:
                 continue
-            trades.append(trade)
-            trade = SwaptionDeal.from_baml_email(trade)
+            tickets.append(ticket)
+            trade = from_baml_email(ticket, dawn_engine.raw_connection())
             trade.stage()
 
-    df = pd.DataFrame.from_dict(trades)
+    df = pd.DataFrame.from_dict(tickets)
     df.columns = df.columns.str.lower().str.replace(" ", "_")
     if "collateral" in df.columns:
         additional_columns = ["collateral"]