diff options
| -rw-r--r-- | python/bowdst.py | 93 |
1 files changed, 54 insertions, 39 deletions
diff --git a/python/bowdst.py b/python/bowdst.py index efc3aa00..76a286ca 100644 --- a/python/bowdst.py +++ b/python/bowdst.py @@ -5,50 +5,59 @@ from exchange import ExchangeMessage, Message from exchangelib import FileAttachment from io import StringIO from remote import SftpClient +from typing import Tuple from utils.db import dbconn -em = ExchangeMessage() -for msg in em.get_msgs( - 20, path=["BowdoinOps", "Reports"], subject__startswith="Document(s) from Reporting" -): - if msg.sender == "notify@bnymellon.com": - for attach in msg.attachments: - fname = attach.name - if fname.endswith("csv") and fname.startswith("Asset Detail"): - date = datetime.datetime.strptime( - fname.split("_")[1].split(".")[0], "%d %b %Y" - ).date() - p = DAILY_DIR / str(date) / "Reports" / fname - if not p.parent.exists(): - p.parent.mkdir(parents=True) - if not p.exists(): - p.write_bytes(attach.content) -workdate = datetime.date.today() -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"] -df = df.set_index("CUSIP") -df = df[["Shares/Par", "Base Price", "Local Market Value"]] -for col in df.select_dtypes(include=["object"]).columns: - df[col] = pd.to_numeric(df[col].str.replace(",", "")) -dawndb = dbconn("dawndb") -df_blotter = pd.read_sql_query( - "SELECT * FROM risk_positions(%s, NULL, %s)", - dawndb, - params=(workdate, "BOWDST"), - index_col=["identifier"], -) -check = df_blotter.join(df) +def download_messages(em): + for msg in em.get_msgs( + 20, + path=["BowdoinOps", "Reports"], + subject__startswith="Document(s) from Reporting", + ): + if msg.sender == "notify@bnymellon.com": + for attach in msg.attachments: + fname = attach.name + if fname.endswith("csv") and fname.startswith("Asset Detail"): + date = datetime.datetime.strptime( + fname.split("_")[1].split(".")[0], "%d %b %Y" + ).date() + p = DAILY_DIR / str(date) / "Reports" / fname + if not p.parent.exists(): + p.parent.mkdir(parents=True) + if not p.exists(): + p.write_bytes(attach.content) + + +def get_positions(workdate: datetime.date) -> Tuple[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"] + df = df.set_index("CUSIP") + df = df[["Shares/Par", "Base Price", "Local Market Value"]] + for col in df.select_dtypes(include=["object"]).columns: + 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") + df_blotter = pd.read_sql_query( + "SELECT * FROM risk_positions(%s, NULL, %s)", + dawndb, + params=(workdate, "BOWDST"), + index_col=["identifier"], + ) + check = df_blotter.join(df) -cds_positions = pd.read_sql_query( - "SELECT * FROM list_cds_marks_pre(%s, NULL, %s)", - dawndb, - params=(workdate, "BOWDST"), -) + cds_positions = pd.read_sql_query( + "SELECT * FROM list_cds_marks_pre(%s, NULL, %s)", + dawndb, + params=(workdate, "BOWDST"), + ) + return df_blotter, cds_positions -def send_email(workdate: datetime.date, df_bonds: pd.DataFrame, df_cds: pd.DataFrame): +def send_email( + em, workdate: datetime.date, df_bonds: pd.DataFrame, df_cds: pd.DataFrame +): email = Message( account=em._account, folder=em._account.sent, @@ -74,4 +83,10 @@ def send_email(workdate: datetime.date, df_bonds: pd.DataFrame, df_cds: pd.DataF email.send_and_save() -# sftp = SftpClient.from_creds("gs") +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) + # sftp = SftpClient.from_creds("gs") |