1 files changed, 129 insertions, 0 deletions
diff --git a/python/report_ops/cash.py b/python/report_ops/cash.py
new file mode 100644
index 00000000..092dbb7d
--- /dev/null
+++ b/python/report_ops/cash.py
@@ -0,0 +1,129 @@
+from dataclasses import field, dataclass
+from serenitas.ops.trade_dataclasses import Deal, Fund
+from serenitas.analytics.dates import prev_business_day, next_business_day
+import datetime
+from serenitas.utils.exchange import ExchangeMessage
+from serenitas.utils.env import DAILY_DIR
+import pandas as pd
+from serenitas.utils.db import dbconn, dawn_engine
+from typing import ClassVar
+from .misc import get_dir, dt_from_fname
+from .custodians import NT, UMB
+
+
+@dataclass
+class IAMDeal(Deal, deal_type=None, table_name="iam_tickets"):
+    trade_date: datetime.date = field(metadata={"globeop": "SettlementDate"})
+    action: str = field(metadata={"globeop": "Action"})
+    strategy: str = field(metadata={"globeop": "Folder"})
+    counterparty: str = field(metadata={"globeop": "Counterparty"})
+    maturity: datetime.date
+    start_money: float = field(metadata={"globeop": "StartMoney"})
+    currency: str = field(metadata={"globeop": "Currency"})
+    booked_offset: bool
+    uploaded: bool
+    fund: Fund
+    dealid: str = field(metadata={"insert": False})
+    id: int = field(metadata={"insert": False})
+
+    def to_globeop(self, action):
+        obj = super().to_globeop(action)
+        obj["Deal Type"] = "IamDeal"
+        obj["ExpirationDate"] = self.trade_date if self.action == "UPDATE" else None
+        obj["CallNoticeIndicator"] = "24H" if self.action == "NEW" else None
+        obj["TransactionIndicator"] = ("DEPOSIT" if obj["StartMoney"] > 0 else "LOAN",)
+        obj["StartMoney"] = abs(obj["StartMoney"])
+        obj["Folder"] = (
+            "M_CSH_CASH" if obj["strategy"] == "CSH_CASH" else obj["strategy"]
+        )
+        obj["DealFunction"] = "OTC"
+        obj["MarginType"] = "Variation"
+        obj["Basis"] = "ACT/360"
+        return obj
+
+
+@dataclass
+class CashReport:
+    fund: ClassVar[str]
+    account_number: ClassVar[str]
+    date: datetime.date
+    _conn: ClassVar[dbconn] = dbconn("dawndb")
+    _staging_queue: ClassVar[set] = set()
+    _insert_sql = "INSERT INTO cash_balances VALUES (%s, %s, %s, %s, %s, %s) ON CONFLICT DO NOTHING"
+
+    def __init_subclass__(cls, fund, account_number):
+        cls.fund = fund
+        cls.account_number = account_number
+
+    def to_db(self, report_name):
+        self.download_reports(self.date)
+        report_dir = get_dir(self.date)
+        report_dir.mkdir(exist_ok=True, parents=True)
+        p = max(
+            [f for f in get_dir(self.date).iterdir() if f.name.startswith(report_name)],
+            key=dt_from_fname,
+            default=None,
+        )
+        if not p:
+            raise ValueError(
+                f"No reports found for fund: {self.fund} date: {self.date}"
+            )
+        return p
+
+    @classmethod
+    def commit(cls):
+        with cls._conn.cursor() as c:
+            c.executemany(cls._insert_sql, cls._staging_queue)
+        cls._conn.commit()
+
+
+class NTCashReport(CashReport, NT, fund="ISOSEL", account_number="ISOS01"):
+    def to_db(self):
+        p = super().to_db("cash_")
+        df = pd.read_csv(p, on_bad_lines="warn")
+        df = df[df["T-NARR-LONG"] == "CLOSING BALANCE"]
+        df = df[["Consolidation", "Currency code", "A-TRAN-AMT"]]
+        df.columns = df.columns.str.replace(" |-|_", "", regex=True).str.lower()
+        df = df.set_index(["consolidation", "currencycode"])
+        for row in df.itertuples():
+            self.stage_from_row(row)
+        self.commit()
+        self._staging_queue.clear()
+
+    def stage_from_row(self, row):
+        (account, currency), amount = row
+        self._staging_queue.add(
+            (
+                prev_business_day(self.date),
+                self.fund,
+                f"NT Custody Account {self.fund}",
+                account,
+                currency,
+                amount,
+            )
+        )
+
+
+class UMBCashReport(CashReport, UMB, fund="SERCGMAST", account_number="159260.1"):
+    def to_db(self):
+        p = super().to_db("umb_")
+        df = pd.read_excel(p, skiprows=3)
+        for row in (
+            df.groupby(["Portfolio #", "Currency"]).sum()["Current Balance"].items()
+        ):
+            self.stage_from_row(row)
+        self.commit()
+        self._staging_queue.clear()
+
+    def stage_from_row(self, row):
+        (account, currency), amount = row
+        self._staging_queue.add(
+            (
+                prev_business_day(self.date),
+                self.fund,
+                f"UMB Custody Account {self.fund}",
+                account,
+                currency,
+                amount,
+            )
+        )