diff options
| -rw-r--r-- | python/notify_bowdst.py | 73 |
1 files changed, 32 insertions, 41 deletions
diff --git a/python/notify_bowdst.py b/python/notify_bowdst.py index 782dc629..e51ea094 100644 --- a/python/notify_bowdst.py +++ b/python/notify_bowdst.py @@ -9,14 +9,21 @@ from serenitas.utils.exchange import ExchangeMessage from serenitas.utils import SerenitasFileHandler import logging import argparse +import numpy as np parser = argparse.ArgumentParser(description="determine sender destination") parser.add_argument("--globeop", help="send to globeop") +parser.add_argument( + "--debug", "-d", action="store_true", default=False, help="log to the console" +) args = parser.parse_args() +if args.debug: + logging.basicConfig() + logger = logging.getLogger(__name__) -if not logger.hasHandlers(): +if not logger.handlers: fh = SerenitasFileHandler("mismatched_trades.log") logger.addHandler(fh) logger.setLevel(logging.WARNING) @@ -25,77 +32,61 @@ conn = dbconn("dawndb") cob = (datetime.date.today() - BDay(1)).date() dates = pd.date_range(end=cob, periods=50) df = pd.concat( - { - d: pd.read_sql_query( + [ + pd.read_sql_query( "SELECT *, notional * factor AS db_notional " "FROM list_cds_marks(%s, NULL, 'BOWDST')", conn, params=(d,), + index_col=["security_desc"], ) for d in dates.date - } -).droplevel(level=1) -data = df.loc[ - cob, ["tenor", "security_desc", "security_id", "globeop_notional", "db_notional"] -] -inaccurate_balances = data[ - round(data["db_notional"], 2) != round(data["globeop_notional"], 2) -] + ], + keys=dates, +) +mask = np.isclose(df.globeop_notional, df.db_notional, atol=1e-2, rtol=0.0) +inaccurate_balances = df[~mask].loc[ + pd.Timestamp(cob), ["tenor", "security_id", "globeop_notional", "db_notional"] +] +accurate_balances = df[mask] for row in inaccurate_balances.itertuples(): - subject = ( - f"ACTION REQUESTED: Missing Trades {row.security_desc} RED: {row.security_id}" - ) - - new_df = df[df["security_desc"] == row.security_desc] + subject = f"ACTION REQUESTED: Missing Trades {row.Index} RED: {row.security_id}" try: - data = ( - new_df[ - round(new_df["globeop_notional"], 2) == round(new_df["db_notional"], 2) - ] - .sort_index(ascending=False) - .iloc[0, :] - ) - security_desc = data.security_desc - date = data.name - globeop_notional = data.globeop_notional + last_date = accurate_balances.xs(row.Index, level="security_desc").index[-1] + except IndexError: logger.warning(f"No matches for {row.security_desc}") continue - buf = StringIO() output = pd.read_sql_query( "SELECT * FROM cds WHERE trade_date > %s and trade_date <= %s " "AND fund = 'BOWDST' AND security_desc = %s " "AND orig_detach IS NULL AND orig_attach IS NULL ORDER BY trade_date DESC", conn, - params=(date, cob, security_desc), + params=(last_date, cob, row.Index), ) if not output[output.trade_date >= cob - BDay(1)].empty: - print( - f"Mismatch balance {row.security_desc} {row.globeop_notional} :{row.db_notional} Trades Recently" - ) logger.warning( - f"Mismatch balance {row.security_desc} {row.globeop_notional} :{row.db_notional} Trades Recently" + f"Mismatch balance {row.Index} {row.globeop_notional} :{row.db_notional} Trades Recently" ) continue - output.to_csv(buf) + output.to_csv(buf, index=False) attachments = [ - FileAttachment(name=f"{security_desc}.csv", content=buf.getvalue().encode()) + FileAttachment(name=f"{row.Index}.csv", content=buf.getvalue().encode()) ] buf.close() msg = ( - f"Good morning!\n\n" + "Good morning!\n\n" f"We notice a difference in our notional ({row.db_notional:,.2f}) versus yours ({row.globeop_notional:,.2f}) " - f"for security {row.security_desc} RED: {row.security_id}.\n" - f"Our notionals last matched on {date} for ${globeop_notional:,.2f}.\n\n" - f"Please see attached all the trades since we last matched. Please ensure they match your data.\n\n" - f"Thanks for your help!\n\n" - f"Flint" + f"for security {row.Index} RED: {row.security_id}.\n" + f"Our notionals last matched on {last_date} for ${row.globeop_notional:,.2f}.\n\n" + "Please see attached all the trades since we last matched. Please ensure they match your data.\n\n" + "Thanks for your help!\n\n" + "Flint" ) em = ExchangeMessage() - if args.globeop: recipients = ( "caagtradecapture@bnymellon.com", |