diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/citco_ops/cash.py | 115 | ||||
| -rw-r--r-- | python/isosel_reports.py | 2 |
2 files changed, 82 insertions, 35 deletions
diff --git a/python/citco_ops/cash.py b/python/citco_ops/cash.py index 13fdfb4f..1dc6d49a 100644 --- a/python/citco_ops/cash.py +++ b/python/citco_ops/cash.py @@ -13,7 +13,7 @@ from .misc import get_dir def dt_from_fname(f): return datetime.datetime.strptime( - f.name.removesuffix(".csv").rsplit("_")[-1], "%Y%m%d%H%M" + f.name.removesuffix(".csv").removesuffix(".xlsx").rsplit("_")[-1], "%Y%m%d%H%M" ) @@ -54,11 +54,30 @@ class CashReport: 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, + ) + return p + + @classmethod + def commit(cls): + with cls._conn.cursor() as c: + c.executemany(cls._insert_sql, cls._staging_queue) + cls._conn.commit() + class IsoselCashReport(CashReport, fund="ISOSEL", account_number="ISOS01"): @classmethod @@ -85,19 +104,7 @@ class IsoselCashReport(CashReport, fund="ISOSEL", account_number="ISOS01"): csvFile.write(text) def to_db(self): - self.download_reports(self.date) - report_dir = get_dir(next_business_day(self.date)) - report_dir.mkdir(exist_ok=True, parents=True) - p = max( - [ - f - for f in get_dir(next_business_day(self.date)).iterdir() - if "cash" in f.name - ], - key=dt_from_fname, - default=None, - ) - + p = super.to_db("cash_") if not p: raise ValueError( f"No reports found for fund: {self.fund} date: {self.date}" @@ -105,25 +112,65 @@ class IsoselCashReport(CashReport, fund="ISOSEL", account_number="ISOS01"): 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", "Account name"]] - df = df.reset_index(drop=True).rename( - { - "Consolidation": "account_number", - "Currency code": "currency_code", - "Account name": "account_name", - "A-TRAN-AMT": "balance", - }, - axis=1, + 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, + ) ) - df["date"] = self.date - df["fund"] = self.fund - with self._conn.cursor() as c: - c.execute( - "DELETE FROM cash_balances WHERE fund=%s AND date=%s AND account_number=%s", - ( - self.fund, - self.date, - self.account_number, - ), + + +class UmbCashReport(CashReport, fund="SERCGMAST", account_number="159260.1"): + @classmethod + def download_reports(cls, date=datetime.date.today()): + em = ExchangeMessage() + 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_reporting") + and 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 not p.exists(): + p.write_bytes(attach.content) + + def to_db(self): + p = super().to_db("umb_") + if not p: + raise ValueError( + f"No reports found for fund: {self.fund} date: {self.date}" + ) + 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, ) - self._conn.commit() - df.to_sql("cash_balances", index=False, if_exists="append", con=dawn_engine) + ) diff --git a/python/isosel_reports.py b/python/isosel_reports.py index bc80f970..10f0a46e 100644 --- a/python/isosel_reports.py +++ b/python/isosel_reports.py @@ -17,7 +17,7 @@ if __name__ == "__main__": help="report date", ) args = parser.parse_args() - for report_cls in AccruedReport, AllReport, IsoselCashReport: + for report_cls in (IsoselCashReport,): report = report_cls(args.date) try: report.to_db() |