diff options
Diffstat (limited to 'python/report_ops/cash.py')
| -rw-r--r-- | python/report_ops/cash.py | 86 |
1 files changed, 36 insertions, 50 deletions
diff --git a/python/report_ops/cash.py b/python/report_ops/cash.py index b1823465..7068d649 100644 --- a/python/report_ops/cash.py +++ b/python/report_ops/cash.py @@ -4,7 +4,7 @@ import pandas as pd from serenitas.utils.db import dbconn from typing import ClassVar from .misc import get_dir, dt_from_fname -from .custodians import NT, UMB, BNY, SCOTIA +from .custodians import get_custodian_download_fun from functools import partial from dataclasses import dataclass @@ -14,7 +14,6 @@ from serenitas.utils.env import DAILY_DIR @dataclass class CashReport: - fund: ClassVar[str] custodian: ClassVar[str] date: datetime.date dtkey: ClassVar @@ -23,16 +22,10 @@ class CashReport: _insert_sql = "INSERT INTO cash_balances VALUES (%s, %s, %s, %s, %s, %s) ON CONFLICT DO NOTHING" _registry = {} - def __init_subclass__(cls, fund, custodian, dtkey): - cls.fund = fund + def __init_subclass__(cls, custodian, dtkey): cls.custodian = custodian cls.dtkey = dtkey - cls._registry[ - ( - fund, - custodian, - ) - ] = cls + cls._registry[custodian] = cls def __class_getitem__(cls, key): return cls._registry[key] @@ -61,7 +54,11 @@ class CashReport: c.executemany(cls._insert_sql, cls._staging_queue) cls._conn.commit() - def stage_from_row(self, row): + @classmethod + def clear(cls): + cls._staging_queue.clear() + + def stage_from_row(self, row, fund): (account, currency), amount = row self._staging_queue.add( ( @@ -74,44 +71,41 @@ class CashReport: ) ) + @classmethod + def to_db(self, fund): + for row in self.yield_rows(): + self.stage_from_row(row, fund) + self.commit() + self.clear() -class SeleneNTCashReport( - CashReport, NT, fund="ISOSEL", custodian="NT", dtkey="%Y%m%d%H%M" -): - def to_db(self): - p = self.get_cash_report("cash_") + +class NTCashReport(CashReport, NT, custodian="NT", dtkey="%Y%m%d%H%M"): + def yield_rows(self, fund): + p = self.get_cash_report(f"NT_CASH_{fund}") 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() + yield from df.itertuples() -class SerenitasUMBCashReport( +class UMBCashReport( CashReport, UMB, fund="SERCGMAST", custodian="UMB", dtkey="%Y%m%d%H%M" ): - def to_db(self): - p = self.get_cash_report("umb_") + def yield_rows(self, fund): + p = self.get_cash_report("UMB_CASH_{fund}") df = pd.read_excel(p, skiprows=3) - for row in ( - df.groupby(["Portfolio #", "Currency"]) - .sum(numeric_only=True)["Current Balance"] - .items() - ): - self.stage_from_row(row) - self.commit() - self._staging_queue.clear() + yield from df.groupby(["Portfolio #", "Currency"]).sum(numeric_only=True)[ + "Current Balance" + ].items() -class BowdstBNYCashReport( +class BNYCashReport( CashReport, BNY, fund="BOWDST", custodian="BNY", dtkey="%Y%m%d%H%M%S" ): - def to_db(self): - p = self.get_cash_report("Live-cash") + def yield_rows(self): + p = self.get_cash_report("BNY_CASH_{fund}") df = pd.read_csv(p) df["Beginning Balance Local"] = df["Beginning Balance Local"].apply( lambda s: "-" + s[1:-1] if s.startswith("(") else s @@ -119,30 +113,22 @@ class BowdstBNYCashReport( df["Beginning Balance Local"] = pd.to_numeric( df["Beginning Balance Local"].str.replace(",", "") ) - for row in ( - df.groupby(["Account Number", "Local Currency Code"]) - .sum(numeric_only=True)["Beginning Balance Local"] - .items() - ): - self.stage_from_row(row) - self.commit() - self._staging_queue.clear() + yield from df.groupby(["Account Number", "Local Currency Code"]).sum( + numeric_only=True + )["Beginning Balance Local"].items() -class SeleneScotiaCashReport( - CashReport, SCOTIA, fund="ISOSEL", custodian="SCOTIA", dtkey="%Y%m%d%H%M%S" -): - def to_db(self): +class ScotiaCashReport(CashReport, SCOTIA, custodian="SCOTIA", dtkey="%Y%m%d%H%M%S"): + def yield_rows(self): p = self.get_cash_report() df = pd.read_excel(p, skipfooter=1) if df.empty: # No wires, so we can't skip the footer df = pd.read_excel(p) - self.stage_from_row( - ((int(df.loc[0]["Account"]), df.loc[0]["Curr."]), df.loc[0]["Closing Bal."]) + yield ( + (int(df.loc[0]["Account"]), df.loc[0]["Curr."]), + df.loc[0]["Closing Bal."], ) - self.commit() - self._staging_queue.clear() def get_cash_report(self, prefix=None): self.download_reports(self.date) |