diff options
Diffstat (limited to 'python/monthend_interest_recon.py')
| -rw-r--r-- | python/monthend_interest_recon.py | 200 |
1 files changed, 200 insertions, 0 deletions
diff --git a/python/monthend_interest_recon.py b/python/monthend_interest_recon.py new file mode 100644 index 00000000..93844d14 --- /dev/null +++ b/python/monthend_interest_recon.py @@ -0,0 +1,200 @@ +from serenitas.utils.env import DAILY_DIR +from serenitas.utils.exchange import ExchangeMessage +import datetime +from collateral.baml_isda import download_from_secure_id +from bs4 import BeautifulSoup +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 + +from interest_statement import export_data +from dateutil.relativedelta import relativedelta + +logger = logging.getLogger(__name__) + + +def download_messages(em, counterparty, start, end, recon=False): + for msg in em.get_msgs( + 20, + path=["Interest", counterparty], + ): + if recon: + base_dir = ( + DAILY_DIR / "Serenitas" / "MonthlyInterest" / f"{counterparty}_reports" + ) + else: + if counterparty == "CITI": + base_dir = DAILY_DIR / f"{counterparty}_reports" / "Interest Statements" + elif counterparty == "BAML": + base_dir = ( + DAILY_DIR / "Serenitas" / f"BoA_reports" / "Interest Statements" + ) + else: + base_dir = ( + DAILY_DIR + / "Serenitas" + / f"{counterparty}_reports" + / "Interest Statements" + ) + 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") + a = soup.find("a") + url = urlsplit(a["href"]) + query = parse_qs(url.query) + base_url = urlunsplit(url[:2] + ("",) * 3) + try: + download_from_secure_id( + query["id"][0], query["brand"][0], base_dir, base_url + ) + except ValueError as e: + logging.error(e) + continue + continue + for attach in msg.attachments: + fname = attach.name + if (counterparty == "CS") and not ("Interest" in fname): + continue + p = base_dir / fname + if not p.parent.exists(): + p.parent.mkdir(parents=True) + if not p.exists(): + 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 "due to" in e.text: + 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_BAML(g): + for e in g: + if "Net interest Amount" in e.text: + return -float(next(g).text.replace("(", "-").replace(")", "")) + + +def get_JPM(g): + for e in g: + if "Page" in e.text: + return float(value.replace(",", "")) + value = e.text + + +def start_end(year, month): + start = datetime.date(year, month, 1) + end = start + relativedelta(months=1) + end -= datetime.timedelta(days=1) + return start, end + + +def get_interest(counterparties, save=False): + interest_amounts = defaultdict(float) + for cp in counterparties: + try: + func = globals()[f"get_{cp}"] + except KeyError: + print(f"Missing cp {cp}") + 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 + if not save: + try: + shutil.rmtree( + f"/home/serenitas/Daily/Serenitas/MonthlyInterest/{cp}_reports" + ) + except FileNotFoundError: + pass + return pd.DataFrame(interest_amounts, index=[0]).T.rename( + index={"BAML": "BAML_ISDA"}, columns={0: "monthly_statement"} + ) + + +def main(): + em = ExchangeMessage() + counterparties = ["BNP", "CITI", "CS", "GS", "MS", "BAML", "JPM"] + + parser = argparse.ArgumentParser(description="determine sender destination") + parser.add_argument("start") + parser.add_argument("end", default=datetime.date.today()) + parser.add_argument( + "--recon", + action="store_true", + default=False, + help="for automation or for monthly", + ) + parser.add_argument( + "--save", + action="store_true", + default=False, + help="for automation or for monthly", + ) + args = parser.parse_args() + + for cp in counterparties: + download_messages(em, cp, args.start, args.end, recon=args.recon) + + if args.recon: + df = get_interest(counterparties, save=args.save) + start, end = start_end( + datetime.datetime.today().year, datetime.datetime.today().month - 1 + ) + global interest_recon + interest_recon = pd.merge( + export_data(start, end).groupby("broker").sum(), + df, + how="outer", + left_index=True, + right_index=True, + ) + interest_recon["difference"] = ( + interest_recon["amount"] - interest_recon["monthly_statement"] + ) + + +if __name__ == "__main__": + main() |