diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/report_ops/custodians.py | 12 | ||||
| -rw-r--r-- | python/report_ops/wires.py | 46 |
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() |