diff options
| -rw-r--r-- | python/report_ops/cash.py | 25 |
1 files changed, 19 insertions, 6 deletions
diff --git a/python/report_ops/cash.py b/python/report_ops/cash.py index 43fda7ed..39044a0d 100644 --- a/python/report_ops/cash.py +++ b/python/report_ops/cash.py @@ -9,6 +9,7 @@ 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, BNY +from functools import partial @dataclass @@ -47,13 +48,15 @@ class CashReport: fund: ClassVar[str] custodian: ClassVar[str] date: datetime.date + dtkey: ClassVar _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, custodian): + def __init_subclass__(cls, fund, custodian, dtkey): cls.fund = fund cls.custodian = custodian + cls.dtkey = dtkey def to_db(self, report_name): self.download_reports(self.date) @@ -61,7 +64,7 @@ class CashReport: 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, + key=partial(dt_from_fname, dt_format=self.dtkey), default=None, ) if not p: @@ -90,7 +93,7 @@ class CashReport: ) -class NTCashReport(CashReport, NT, fund="ISOSEL", custodian="NT"): +class NTCashReport(CashReport, NT, fund="ISOSEL", custodian="NT", dtkey="%Y%m%d%H%M"): def to_db(self): p = super().to_db("cash_") df = pd.read_csv(p, on_bad_lines="warn") @@ -104,7 +107,9 @@ class NTCashReport(CashReport, NT, fund="ISOSEL", custodian="NT"): self._staging_queue.clear() -class UMBCashReport(CashReport, UMB, fund="SERCGMAST", custodian="UMB"): +class UMBCashReport( + CashReport, UMB, fund="SERCGMAST", custodian="UMB", dtkey="%Y%m%d%H%M" +): def to_db(self): p = super().to_db("umb_") df = pd.read_excel(p, skiprows=3) @@ -116,13 +121,21 @@ class UMBCashReport(CashReport, UMB, fund="SERCGMAST", custodian="UMB"): self._staging_queue.clear() -class BNYCashReport(CashReport, BNY, fund="BOWDST", custodian="BNY"): +class BNYCashReport( + CashReport, BNY, fund="BOWDST", custodian="BNY", dtkey="%Y%m%d%H%M%S" +): def to_db(self): p = super().to_db("Live-cash") df = pd.read_csv(p) + df["Beginning Balance Local"] = ( + df["Beginning Balance Local"] + .str.replace("(", "-") + .str.replace("[),]", "", regex=True) + ) + df = df.astype({"Beginning Balance Local": "float64"}) for row in ( df.groupby(["Account Number", "Local Currency Code"]) - .sum()["Opening Balance Local"] + .sum()["Beginning Balance Local"] .items() ): self.stage_from_row(row) |