diff options
| -rw-r--r-- | python/monthend_recon_bowdst.py | 55 |
1 files changed, 36 insertions, 19 deletions
diff --git a/python/monthend_recon_bowdst.py b/python/monthend_recon_bowdst.py index ec966f72..6b82f15e 100644 --- a/python/monthend_recon_bowdst.py +++ b/python/monthend_recon_bowdst.py @@ -10,6 +10,7 @@ import datetime from pandas.tseries.offsets import BDay import numpy as np import pandas_market_calendars as mcal +from dateutil.relativedelta import relativedelta def get_dir(date): @@ -24,7 +25,7 @@ def clear_date(date, conn): def load_val_report(date, conn): - p = get_dir(date) / f"Asset_Detail.csv" + p = get_dir(date) / f"Asset Detail.csv" df = pd.read_csv( p, thousands=",", parse_dates=["As Of Date", "Maturity Date", "Report Run Date"] ) @@ -116,21 +117,23 @@ def sums(df): return df[["db_notional", "admin_notional"]].sum() -def recon(hierarchy_file, date): - df = pd.read_excel(hierarchy_file) - bowd_bond_trades = df[df["CUSIP"].notnull()] +def recon(date, conn): + df = pd.read_sql_query( + "SELECT * FROM bowdst_val where as_of_date=%s", conn, params=(date,) + ) + bowd_bond_trades = df[df["cusip"].notnull()] bond_asset_classes = ["Subprime", "CRT", "CLO"] bond_trades_combined = [] for asset in bond_asset_classes: db_bond_trades = pd.read_sql_query( f"select * from risk_positions(%s, %s, 'BOWDST')", - dawndb, + conn, params=(date, asset), ) bond_trades = bowd_bond_trades.merge( db_bond_trades, - left_on="Mellon Security ID", + left_on="mellon_security_id", right_on="identifier", how="right", )[ @@ -139,8 +142,8 @@ def recon(hierarchy_file, date): "identifier", "notional", "factor", - "Shares/Par", - "Base Market Value", + "current_notional", + "base_market_value", "usd_market_value", ] ] @@ -148,8 +151,8 @@ def recon(hierarchy_file, date): bond_trades.rename( columns={ "usd_market_value": "db_mv", - "Shares/Par": "admin_notional", - "Base Market Value": "admin_mv", + "current_notional": "admin_notional", + "base_market_value": "admin_mv", }, inplace=True, ) @@ -158,25 +161,25 @@ def recon(hierarchy_file, date): tranche_trades = pd.read_sql_query( f"select security_desc, maturity, orig_attach, orig_detach, sum(notional * tranche_factor) as db_notional, sum(admin_notional) as admin_notional, sum(serenitas_clean_nav) as db_mv, sum(admin_clean_nav) as admin_mv from tranche_risk_bowdst where date=%s group by security_desc, maturity, orig_attach, orig_detach ;", - dawndb, + conn, params=(last_bus_day(date),), ) cdx_trades = pd.read_sql_query( f"select security_id, security_desc, index, series, version, maturity, globeop_notional as admin_notional, notional * factor as db_notional, clean_nav as db_mv, globeop_nav as admin_mv from list_cds_marks(%s, null, 'BOWDST')", - dawndb, + conn, params=(date,), ) cdx_swaption_trades = pd.read_sql_query( f"select security_id, option_type, strike, expiration_date, sum(serenitas_nav) as db_mv, sum(globeop_nav) as admin_mv from list_swaption_positions_and_risks(%s, 'BOWDST') group by security_id, option_type, strike, expiration_date;", - dawndb, + conn, params=(date,), ) ir_swaption_trades = pd.read_sql_query( "SELECT deal_id, option_type, strike, SECURITY_Id, expiration_date, notional AS db_notional, current_notional AS admin_notional, nav AS db_mv, base_market_value AS admin_mv FROM list_ir_swaption_positions(%s, 'BOWDST') LEFT JOIN bowdst_val ON deal_id=link_ref WHERE as_of_date=%s;", - dawndb, + conn, params=(last_bus_day(date), date), ) @@ -231,13 +234,27 @@ def last_bus_day(date): return date -parser = argparse.ArgumentParser() -parser.add_argument("end_date", type=datetime.date.fromisoformat) -args = parser.parse_args() +def download_reports(date: datetime.date): + target_directory = get_dir(date) + + if not target_directory.exists(): + target_directory.mkdir() + + em = ExchangeMessage() + + for msg in em.get_msgs(path=["Month End Recon", "BNY"]): + # We are getting reports the month after + if msg.datetime_received.month == date.month + 1: + for attachment in msg.attachments: + (target_directory / attachment.name).write_bytes(attachment.content) + if __name__ == "__main__": + parser = argparse.ArgumentParser() + parser.add_argument("end_date", type=datetime.date.fromisoformat) + args = parser.parse_args() + dawndb = dbconn("dawndb") date = args.end_date load_val_report(date, dawndb) - hierarchy_file = get_dir(date) / "hierarchy.xls" - recon(hierarchy_file, date) + recon(date, dawndb) |