diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/recon_bowdst.py | 96 |
1 files changed, 90 insertions, 6 deletions
diff --git a/python/recon_bowdst.py b/python/recon_bowdst.py index 76909495..d295d57f 100644 --- a/python/recon_bowdst.py +++ b/python/recon_bowdst.py @@ -5,6 +5,92 @@ from serenitas.utils.exchange import ExchangeMessage from exchangelib import FileAttachment from io import StringIO from serenitas.utils.env import DAILY_DIR +from serenitas.utils.db import dbconn, dawn_engine +import datetime + + +def get_dir(date): + p = DAILY_DIR / "BOWD_recon" / f"{date:%Y_%m}" + return p + + +def clear_date(date, conn): + with conn.cursor() as c: + c.execute("DELETE FROM bowdst_val bv WHERE as_of_date = %s;", (date,)) + conn.commit() + + +def load_val_report(date, conn): + p = get_dir(date) / f"Asset_Detail.csv" + df = pd.read_csv( + p, thousands=",", parse_dates=["As Of Date", "Maturity Date", "Report Run Date"] + ) + df = df.drop( + [ + "Reporting Account Number", + "Reporting Account Name", + "Source Account Name", + "Xref Security ID", + "Country Name", + "Country Code", + "Local Currency Name", + "Acct Base Currency Name", + "Acct Base Currency Code", + "CINS", + "Issuer ID", + "SEDOL", + "Valoren", + "Sicovam", + "WPK", + "Quick", + "Underlying Sec ID", + "Loan ID", + "Manager", + "Book Yield Value", + "Counterparty", + "Ticker with Exchange Code", + "Ticker with Yellow Key", + "Accounting Status", + "Primary GSP Account", + "Extended GSP Account Number", + "Percent Of Total", + ], + axis=1, + ) + if "Acctg Status Update (EDT)" in df: + del df["Acctg Status Update (EDT)"] + elif "Acctg Status Update (EST)" in df: + del df["Acctg Status Update (EST)"] + df["Source Account Number"] = df["Source Account Number"].str[-4:].astype("int") + df.columns = df.columns.str.replace(" ", "_").str.lower() + df = df.rename( + columns={ + "shares/par": "current_notional", + "local_unrealized_gain/loss": "local_unrealized_pnl", + "base_unrealized_gain/loss": "base_unrealized_pnl", + } + ) + for col in [ + "current_notional", + "local_price", + "base_price", + "local_cost", + "base_cost", + "local_market_value", + "base_market_value", + "local_unrealized_pnl", + "base_unrealized_pnl", + "local_notional_cost", + "base_notional_cost", + "local_notional_value", + "base_notional_value", + ]: + if df[col].dtype != "float64": + df[col] = df[col].apply(lambda s: "-" + s[1:-1] if s.startswith("(") else s) + df[col] = pd.to_numeric(df[col].str.replace(",", "")) + df["row"] = df.index + clear_date(date, conn) + df.to_sql("bowdst_val", dawn_engine, if_exists="append", index=False) def difference(df): @@ -91,7 +177,6 @@ def recon(hierarchy_file, date): em = ExchangeMessage() attachments = [] for kind, name in zip(kinds, names): - # difference(kind).to_csv(f"/home/serenitas/flint/{name}_{date}.csv") buf = StringIO() difference(kind.round(decimals=2)).to_csv(buf) attachments.append( @@ -99,10 +184,8 @@ def recon(hierarchy_file, date): ) pd.set_option("display.float_format", lambda x: "%.2f" % x) message += f"\n{name}: {pd.DataFrame(sums(kind), columns=['sums'])}" - - # print(f"{name}: {sums(kind)}" em.send_email( - subject="Notional Totals", + subject=f"Notional Totals {date}", body=message, to_recipients=("fyu@lmcg.com",), attach=attachments, @@ -110,11 +193,12 @@ def recon(hierarchy_file, date): parser = argparse.ArgumentParser() -parser.add_argument("end_date") +parser.add_argument("end_date", type=datetime.date.fromisoformat) args = parser.parse_args() if __name__ == "__main__": dawndb = dbconn("dawndb") date = args.end_date - hierarchy_file = DAILY_DIR / date / "hierarchy.xls" + load_val_report(date, dawndb) + hierarchy_file = get_dir(date) / "hierarchy.xls" recon(hierarchy_file, date) |