1 files changed, 100 insertions, 16 deletions

diff --git a/python/monthly_interest.py b/python/monthly_interest.py
index 13a19ab6..e48e84c9 100644
--- a/python/monthly_interest.py
+++ b/python/monthly_interest.py
@@ -7,30 +7,45 @@ from urllib.parse import urlsplit, parse_qs, urlunsplit
 import logging
 import argparse
 
+from collateral.common import load_pdf
+from pathlib import Path
+import pandas as pd
+from collections import defaultdict
+import shutil
+
 logger = logging.getLogger(__name__)
 
 
-def download_messages(em, counterparty, start, end):
+def download_messages(em, counterparty, start, end, auto=False):
     for msg in em.get_msgs(
         20,
         path=["Interest", counterparty],
     ):
-        if counterparty == "CITI":
-            base_dir = DAILY_DIR / f"{counterparty}_reports" / "Interest Statements"
-        elif counterparty == "BAML":
-            base_dir = (
-                DAILY_DIR / "Serenits" / "Test" / f"BoA_reports" / "Interest Statements"
-            )
+        if not auto:
+            if counterparty == "CITI":
+                base_dir = DAILY_DIR / f"{counterparty}_reports" / "Interest Statements"
+            elif counterparty == "BAML":
+                base_dir = (
+                    DAILY_DIR
+                    / "Serenitas"
+                    / "Test"
+                    / f"BoA_reports"
+                    / "Interest Statements"
+                )
+            else:
+                base_dir = (
+                    DAILY_DIR
+                    / "Serenitas"
+                    / "Test"
+                    / f"{counterparty}_reports"
+                    / "Interest Statements"
+                )
         else:
             base_dir = (
-                DAILY_DIR
-                / "Serenitas"
-                / "Test"
-                / f"{counterparty}_reports"
-                / "Interest Statements"
+                DAILY_DIR / "Serenitas" / "MonthlyInterest" / f"{counterparty}_reports"
             )
-        if (msg.datetime_sent.date() > datetime.date.fromisoformat(start)) and (
-            msg.datetime_sent.date() < datetime.date.fromisoformat(end)
+        if (msg.datetime_sent.date() >= datetime.date.fromisoformat(start)) and (
+            msg.datetime_sent.date() <= datetime.date.fromisoformat(end)
         ):
             if counterparty == "BAML":
                 soup = BeautifulSoup(msg.body, features="lxml")
@@ -57,13 +72,82 @@ def download_messages(em, counterparty, start, end):
                     p.write_bytes(attach.content)
 
 
+def get_CS(g):
+    for e in g:
+        if "This interest, margin" in e.text:
+            return float(value)
+        value = e.text
+
+
+def get_BNP(g):
+    for e in g:
+        if "Due to" in e.text:
+            value = next(g).text.replace(",", "")
+            return -float(value)
+
+
+def get_CITI(path):
+    df = pd.read_excel(path)
+    for row in df.itertuples():
+        if "Net Interest Due To CP" in row:
+            return -row._6
+
+
+def get_GS(g):
+    for e in g:
+        if "TOTAL INTEREST" in e.text:
+            next(g)
+            next(g)
+            return float(next(g).text.replace("USD", "").replace(",", ""))
+
+
+def get_MS(path):
+    df = pd.read_excel(path)
+    return -round(df["LOCAL_ACCRUAL"].sum(), 2)
+
+
+def get_interest():
+    interest_amounts = defaultdict(float)
+    counterparties = ["BNP", "CITI", "CS", "GS", "MS"]
+    for cp in counterparties:
+        try:
+            func = globals()[f"get_{cp}"]
+        except KeyError:
+            continue
+        if cp in ("CITI", "MS"):
+            for file in Path(
+                f"/home/serenitas/Daily/Serenitas/MonthlyInterest/{cp}_reports"
+            ).glob("*.xls*"):
+                amount = func(file)
+                interest_amounts[cp] = interest_amounts[cp] + amount
+        else:
+            for file in Path(
+                f"/home/serenitas/Daily/Serenitas/MonthlyInterest/{cp}_reports"
+            ).glob("*.pdf"):
+                g = iter(load_pdf(file))
+                amount = func(g)
+                interest_amounts[cp] = interest_amounts[cp] + amount
+        # try:
+        #     shutil.rmtree(f'/home/serenitas/Daily/Serenitas/MonthlyInterest/{cp}_reports')
+        # except FileNotFoundError:
+        #     pass
+    return pd.DataFrame(interest_amounts, index=[0]).T
+
+
 em = ExchangeMessage()
-counterparties = ["BAML", "BNP", "CITI", "CS", "GS", "MS"]
+counterparties = ["BNP", "CITI", "CS", "GS", "MS"]
 
 parser = argparse.ArgumentParser(description="determine sender destination")
 parser.add_argument("start")
 parser.add_argument("end")
+parser.add_argument("--auto", action="store_true", help="for automation or for monthly")
 args = parser.parse_args()
 
 for cp in counterparties:
-    download_messages(em, cp, args.start, args.end)
+    if args.auto:
+        download_messages(em, cp, args.start, args.end, auto=args.auto)
+    else:
+        download_messages(em, cp, args.start, args.end)
+
+if args.auto:
+    df = get_interest()