path: root/python/monthend_interest_recon.py

import datetime
import logging
import argparse
from collections import defaultdict
from io import StringIO
import os

import pandas as pd
from dateutil.relativedelta import relativedelta
from dataclasses import dataclass
import openpyxl
from zipfile import BadZipFile

from serenitas.utils.env import DAILY_DIR
from serenitas.utils.exchange import ExchangeMessage, FileAttachment

from collateral.common import load_pdf
from interest_statement import export_data
from report_ops.misc import em_date_filter


logger = logging.getLogger(__name__)


def get_pat(month, year, counterparty):
    match counterparty:
        case "BoA":
            month_abbr = datetime.datetime.strptime(str(month), "%m").strftime("%b")
            pattern = f"??{month_abbr}{year}*.pdf"
        case "BNP":
            month_name = datetime.datetime.strptime(str(month), "%m").strftime("%B")
            pattern = (
                f"Interest Statement SCAR GCM_RVM - USD - ?? {month_name} {year}.pdf"
            )
        case "CITI":
            pattern = f"*_USD_ICS_SYSTEM.*.{year}{str(month).zfill(2)}??.xlsx"
        case "JPM":
            pattern = f"CSINSTMT-*-{str(year)[2:]}{str(month).zfill(2)}??-*_*_*.pdf"
        case "GS":
            pattern = f"MVPInterestStatement_{year}{str(month).zfill(2)}??_*_*.pdf"
        case "MS":
            pattern = f"*IntStmt_{year}{str(month).zfill(2)}??.xls"
        case _:
            raise ValueError(f"Missing counterparty: {counterparty}")
    return pattern


def download_messages(em, counterparties, start, end):
    for counterparty in counterparties:
        for msg in em.get_msgs(
            20, path=["Interest", counterparty], **em_date_filter(em, start, end)
        ):
            BASE_DIR = (
                DAILY_DIR
                / "Serenitas"
                / f"{counterparty}_reports"
                / "Interest Statements"
            )
            for attach in msg.attachments:
                p = BASE_DIR / attach.name
                p.parent.mkdir(parents=True, exist_ok=True)
                if not p.exists():
                    p.write_bytes(attach.content)


@dataclass
class InterestCounterparty:
    month: datetime.date.month
    year: datetime.date.year
    _registry = {}

    def __init_subclass__(cls, name):
        cls.name = name
        cls._registry[name] = cls

    def __class_getitem__(cls, name: str):
        return cls._registry[name]

    def yield_files(self):
        BASE_DIR = (
            DAILY_DIR / "Serenitas" / f"{self.name}_reports" / "Interest Statements"
        )
        yield sorted(BASE_DIR.glob(self.pat), key=lambda x: -os.path.getmtime(x))[0]

    @property
    def pat(self):
        statement_month = datetime.date(
            year=self.year, month=self.month, day=1
        ) + relativedelta(months=1)
        return get_pat(statement_month.month, statement_month.year, self.name)


class BoA(InterestCounterparty, name="BoA"):
    @staticmethod
    def get_interest_amount(file_path):
        pdf = load_pdf(file_path)
        for e, n in zip(pdf, pdf[1:]):
            if "Net interest Amount" in e.text:
                return -float(
                    n.text.replace("(", "-").replace(")", "").replace(",", "")
                )


class BNP(InterestCounterparty, name="BNP"):
    @staticmethod
    def get_interest_amount(file_path):
        pdf = load_pdf(file_path)
        for e, n in zip(pdf, pdf[1:]):
            if "Due to" in e.text:
                value = n.text.replace(",", "")
                return -float(value)

    @property
    def pat(self):
        # BNP files reflect month of interest rather than month of statement
        return get_pat(self.month, self.year, self.name)


class CITI(InterestCounterparty, name="CITI"):
    @staticmethod
    def get_interest_amount(file_path):
        sheet = openpyxl.load_workbook(file_path).active
        for row in sheet.rows:
            if row[1].value in (
                "Net Interest Due To CP",
                "Net Interest Due to Citi",
            ):
                return -row[5].value

    def yield_files(self):
        BASE_DIR = (
            DAILY_DIR / "Serenitas" / f"{self.name}_reports" / "Interest Statements"
        )
        yield from BASE_DIR.glob(self.pat)  # Citi has two files VM and IM files


class JPM(InterestCounterparty, name="JPM"):
    @staticmethod
    def get_interest_amount(file_path):
        pdf = load_pdf(file_path)
        for e in pdf:
            if "Page" in e.text:
                return float(value.replace(",", ""))
            value = e.text

    @property
    def pat(self):
        # JPM files reflect month of interest rather than month of statement
        return get_pat(self.month, self.year, self.name)


class GS(InterestCounterparty, name="GS"):
    @staticmethod
    def get_interest_amount(file_path):
        pdf = load_pdf(file_path)
        for e, n in zip(pdf, pdf[1:]):
            if "due to" in e.text:
                return float(n.text.replace("USD", "").replace(",", ""))

    @property
    def pat(self):
        # GS files reflect month of interest rather than month of statement
        return get_pat(self.month, self.year, self.name)


class MS(InterestCounterparty, name="MS"):
    @staticmethod
    def get_interest_amount(file_path):
        df = pd.read_excel(file_path)
        return -round(df["LOCAL_ACCRUAL"].sum(), 2)

    @property
    def pat(self):
        # MS files reflect previous business day, could potenitally be the same month so we'll have to edit accordingly
        return get_pat(self.month, self.year, self.name)


def parse_args():
    """Parses command line arguments"""
    parser = argparse.ArgumentParser(description="Generate IAM file for globeop")
    parser.add_argument(
        "monthend",
        nargs="?",
        type=datetime.date.fromisoformat,
        default=datetime.date.today().replace(day=1) - datetime.timedelta(days=1),
    )
    parser.add_argument(
        "--accept",
        "-a",
        action="store_true",
        default=False,
        help="accept the differences are within tolerance and edit the csv accordingly",
    )
    return parser.parse_args()


def get_statement_totals(counterparties, month, year):
    interest_amounts = defaultdict(float)
    for counterparty in counterparties:
        interest_counterparty = InterestCounterparty[counterparty](month, year)
        for f in interest_counterparty.yield_files():
            interest_amounts[counterparty] += interest_counterparty.get_interest_amount(
                f
            )
    return pd.DataFrame.from_dict(
        interest_amounts, orient="index", columns=["statement_interest"]
    ).rename(index={"BoA": "BAML_ISDA"})


def main():
    args = parse_args()
    em = ExchangeMessage()
    download_messages(
        em,
        InterestCounterparty._registry.keys(),
        args.monthend.replace(day=1),
        args.monthend + relativedelta(months=1),
    )
    global df
    df = get_statement_totals(
        InterestCounterparty._registry.keys(), args.monthend.month, args.monthend.year
    )
    serenitas_df = export_data(
        args.monthend - relativedelta(months=1) + datetime.timedelta(days=1),
        args.monthend,
    )
    df = pd.merge(
        serenitas_df.groupby("broker").sum(),
        df,
        how="outer",
        left_index=True,
        right_index=True,
    )
    df = df.fillna(0)
    df["difference"] = df["amount"] - df["statement_interest"]
    if args.accept:
        for cp, difference in df["difference"].items():
            # Match with counterparty if within tolerance
            try:
                serenitas_df.at[(cp, "CSH_CASH"), "amount"] -= difference
            except KeyError:
                serenitas_df = serenitas_df.reindex(
                    serenitas_df.index.append(
                        pd.MultiIndex.from_tuples([(cp, "CSH_CASH")])
                    )
                )
                serenitas_df.at[(cp, "CSH_CASH"), "amount"] = -difference
        buf = StringIO()
        serenitas_df.to_csv(buf)
        em.send_email(
            subject=f"Allocation of Interest for {args.monthend:%b-%y}",
            body="Please see attached for allocation of interest by strategy and counterparty",
            to_recipients=("serenitas.otc@sscinc.com", "SERENITAS.ops@sscinc.com"),
            cc_recipients=("nyops@lmcg.com",),
            attach=[
                FileAttachment(
                    name=f"{args.monthend:%b-%y}.csv", content=buf.getvalue().encode()
                )
            ],
        )


if __name__ == "__main__":
    main()