diff options
Diffstat (limited to 'python/bowdst.py')
| -rw-r--r-- | python/bowdst.py | 56 |
1 files changed, 35 insertions, 21 deletions
diff --git a/python/bowdst.py b/python/bowdst.py index 76a286ca..fd40ee31 100644 --- a/python/bowdst.py +++ b/python/bowdst.py @@ -1,5 +1,6 @@ import datetime import pandas as pd +from dates import bus_day from env import DAILY_DIR from exchange import ExchangeMessage, Message from exchangelib import FileAttachment @@ -29,7 +30,9 @@ def download_messages(em): p.write_bytes(attach.content) -def get_positions(workdate: datetime.date) -> Tuple[pd.DataFrame, pd.DataFrame]: +def get_positions( + workdate: datetime.date, +) -> Tuple[pd.DataFrame, pd.DataFrame, pd.DataFrame]: p = DAILY_DIR / str(workdate) / "Reports" / f"Asset Detail_{workdate:%d %b %Y}.csv" df = pd.read_csv(p, thousands=",") df = df[df["Asset Type"] == "FIXED INCOME SECURITIES"] @@ -39,10 +42,11 @@ def get_positions(workdate: datetime.date) -> Tuple[pd.DataFrame, pd.DataFrame]: df[col] = df[col].apply(lambda s: s[1:-1] if s.startswith("(") else s) df[col] = pd.to_numeric(df[col].str.replace(",", "")) dawndb = dbconn("dawndb") + d = (workdate - bus_day).date() df_blotter = pd.read_sql_query( "SELECT * FROM risk_positions(%s, NULL, %s)", dawndb, - params=(workdate, "BOWDST"), + params=(d, "BOWDST"), index_col=["identifier"], ) check = df_blotter.join(df) @@ -50,13 +54,29 @@ def get_positions(workdate: datetime.date) -> Tuple[pd.DataFrame, pd.DataFrame]: cds_positions = pd.read_sql_query( "SELECT * FROM list_cds_marks_pre(%s, NULL, %s)", dawndb, - params=(workdate, "BOWDST"), + params=(d, "BOWDST"), + index_col=["security_id"], ) - return df_blotter, cds_positions + tranche_positions = pd.read_sql_query( + "SELECT id, security_id, security_desc, maturity, a.notional, " + "protection, orig_attach, orig_detach, tranche_factor, clean_nav, " + "accrued, cp_code, cpty_id from list_cds(%s, %s) a " + "LEFT JOIN tranche_risk ON id=tranche_id AND date=%s " + "WHERE orig_attach IS NOT NULL", + dawndb, + params=(d, "BOWDST", workdate), + index_col=["id"], + ) + + return df_blotter, cds_positions, tranche_positions def send_email( - em, workdate: datetime.date, df_bonds: pd.DataFrame, df_cds: pd.DataFrame + em, + workdate: datetime.date, + df_bonds: pd.DataFrame, + df_cds: pd.DataFrame, + df_tranches: pd.DataFrame, ): email = Message( account=em._account, @@ -66,20 +86,14 @@ def send_email( to_recipients=("reconfiles@bnymellon.com", "hm-operations@bnymellon.com"), cc_recipients=("bowdoin-ops@lmcg.com",), ) - buf = StringIO() - df_bonds.to_csv(buf) - bond_attach = FileAttachment( - name=f"{workdate} bonds.csv", content=buf.getvalue().encode() - ) - buf.close() - email.attach(bond_attach) - buf = StringIO() - df_cds.to_csv(buf, index=False) - cds_attach = FileAttachment( - name=f"{workdate} cds.csv", content=buf.getvalue().encode() - ) - buf.close() - email.attach(cds_attach) + for name, df in zip(("bonds", "cds", "tranches"), (df_bonds, df_cds, df_tranches)): + buf = StringIO() + df.to_csv(buf) + attachment = FileAttachment( + name=f"{workdate} {name}.csv", content=buf.getvalue().encode() + ) + buf.close() + email.attach(attachment) email.send_and_save() @@ -87,6 +101,6 @@ if __name__ == "__main__": em = ExchangeMessage() download_messages(em) workdate = datetime.date.today() - df_bonds, df_cds = get_positions(workdate) - send_email(em, workdate, df_bonds, df_cds) + df_bonds, df_cds, df_tranches = get_positions(workdate) + send_email(em, workdate, df_bonds, df_cds, df_tranches) # sftp = SftpClient.from_creds("gs") |