diff options
Diffstat (limited to 'python/monthend_interest_recon.py')
| -rw-r--r-- | python/monthend_interest_recon.py | 54 |
1 files changed, 31 insertions, 23 deletions
diff --git a/python/monthend_interest_recon.py b/python/monthend_interest_recon.py index f1cb4305..1e29a520 100644 --- a/python/monthend_interest_recon.py +++ b/python/monthend_interest_recon.py @@ -3,11 +3,13 @@ import logging import argparse from collections import defaultdict from io import StringIO -import re +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 @@ -78,7 +80,7 @@ class InterestCounterparty: BASE_DIR = ( DAILY_DIR / "Serenitas" / f"{self.name}_reports" / "Interest Statements" ) - yield from BASE_DIR.glob(self.pat) + yield sorted(BASE_DIR.glob(self.pat), key=lambda x: -os.path.getmtime(x))[0] @property def pat(self): @@ -117,10 +119,19 @@ class BNP(InterestCounterparty, name="BNP"): class CITI(InterestCounterparty, name="CITI"): @staticmethod def get_interest_amount(file_path): - df = pd.read_excel(file_path, skiprows=14) - for row in df.itertuples(): - if "Net Interest Due To CP" in row or "Net Interest Due to Citi" in row: - return -row._6 + 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"): @@ -160,21 +171,10 @@ class MS(InterestCounterparty, name="MS"): @property def pat(self): - # GS files reflect month of interest rather than month of statement + # 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) -# class CS(InterestCounterparty, name='CS'): -# @staticmethod -# def get_interest_amount(file_path): -# pdf = load_pdf(file_path, pages=True) -# for row in pdf[-1]: -# if "This interest, margin" in row.text: -# if row == "No Accruals to Report": -# return 0 -# return float(row) - - def parse_args(): """Parses command line arguments""" parser = argparse.ArgumentParser(description="Generate IAM file for globeop") @@ -220,12 +220,12 @@ def main(): df = get_statement_totals( InterestCounterparty._registry.keys(), args.monthend.month, args.monthend.year ) - serenitas_calculations = export_data( + serenitas_df = export_data( args.monthend - relativedelta(months=1) + datetime.timedelta(days=1), args.monthend, ) df = pd.merge( - serenitas_calculations.groupby("broker").sum(), + serenitas_df.groupby("broker").sum(), df, how="outer", left_index=True, @@ -236,11 +236,19 @@ def main(): if args.accept: for cp, difference in df["difference"].items(): # Match with counterparty if within tolerance - serenitas_calculations.loc[cp].loc["CSH_CASH"] -= difference + 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_calculations.to_csv(buf) + serenitas_df.to_csv(buf) em.send_email( - subject=f"Allocation of Interest for {args.monthend.month:%b}", + 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",), |