2 files changed, 41 insertions, 17 deletions
diff --git a/python/report_ops/custodians.py b/python/report_ops/custodians.py
index edf2af5f..9c0ee097 100644
--- a/python/report_ops/custodians.py
+++ b/python/report_ops/custodians.py
@@ -122,13 +122,15 @@ class UMB(Custodian, account="UMB"):
         for msg in em.get_msgs(count=20, path=["NYops", "Powerstation"]):
             for attach in msg.attachments:
                 timestamp = attach.last_modified_time
-                if (
-                    attach.name.startswith("cash_balances_umb")
-                    and timestamp.date() == date
-                ):
+                if timestamp.date() == date:
                     dest = get_dir(timestamp.date(), archived=False)
                     dest.mkdir(exist_ok=True, parents=True)
-                    p = dest / f"umb_{timestamp:%Y%m%d%H%M}.xlsx"
+                    if attach.name.startswith("cash_balances_umb"):
+                        p = dest / f"umb_{timestamp:%Y%m%d%H%M}.xlsx"
+                    elif attach.name.startswith("umb_serenitas_wires"):
+                        p = dest / f"umbwires_{timestamp:%Y%m%d%H%M}.xlsx"
+                    else:
+                        return
                     if not p.exists():
                         p.write_bytes(attach.content)
 
diff --git a/python/report_ops/wires.py b/python/report_ops/wires.py
index a2f5ce1d..60ad0e93 100644
--- a/python/report_ops/wires.py
+++ b/python/report_ops/wires.py
@@ -1,4 +1,5 @@
 from dataclasses import dataclass
+import pandas as pd
 from typing import Literal
 import datetime
 from serenitas.ops.trade_dataclasses import Deal, Ccy
@@ -44,11 +45,12 @@ class Wire(Deal, table_name="custodian_wires", deal_type="custodian_wires"):
         cls.dtkey = dtkey
 
     def __post_init__(self):
-        self.amount = self.amount.replace(",", "")
-        if "(" in self.amount:
-            self.amount = -float(self.amount[1:-1])
-        else:
-            self.amount = float(self.amount)
+        if not isinstance(self.amount, float):
+            self.amount = self.amount.replace(",", "")
+            if "(" in self.amount:
+                self.amount = -float(self.amount[1:-1])
+            else:
+                self.amount = float(self.amount)
 
     @classmethod
     def get_newest_report(cls, fname, date):
@@ -119,13 +121,33 @@ class SerenitasUMBWire(
 ):
     @classmethod
     def from_report_line(cls, line: dict):
-        return
+        return cls(
+            date=line["Transaction Date"],
+            entry_date=line["Transaction Date"],
+            value_date=line["Transaction Date"],
+            pay_date=line["Transaction Date"],
+            currency=line["Local Currency Code"],
+            amount=line["Net Amount"],
+            wire_details=line["Transaction Description"],
+            unique_ref=f'{line["Transaction Date"]}-{line["index"]}',
+        )
 
     @classmethod
     def to_db(cls, date):
-        # conn = cls._conn
-        # with conn.cursor() as c:
-        #     c.execute("DELETE FROM custodian_wires WHERE date=%s AND fund=%s AND custodian=%s", (date, cls.fund, cls.custodian,))
-        #     conn.commit()
-        # the unique ref will be generated from the row + the date and reports will only be delivered at 7pm daily
-        pass
+        p = cls.get_newest_report("umbwires_", date)
+        conn = cls._conn
+        with conn.cursor() as c:
+            c.execute(
+                "DELETE FROM custodian_wires WHERE date=%s AND fund=%s AND custodian=%s",
+                (
+                    date,
+                    cls.fund,
+                    cls.custodian,
+                ),
+            )
+        conn.commit()
+        df = pd.read_excel(p, skiprows=3)
+        df["index"] = df.index
+        for row_dict in df.to_dict(orient="records"):
+            cls.from_report_line(row_dict).stage()
+        cls.commit()