diff options
Diffstat (limited to 'python/bowdst.py')
| -rw-r--r-- | python/bowdst.py | 34 |
1 files changed, 30 insertions, 4 deletions
diff --git a/python/bowdst.py b/python/bowdst.py index 055db11b..f7fab0cc 100644 --- a/python/bowdst.py +++ b/python/bowdst.py @@ -7,7 +7,7 @@ from exchangelib import FileAttachment from io import StringIO from remote import SftpClient from typing import Tuple -from utils.db import dbconn +from utils.db import dbconn, dawn_engine def download_messages(em): @@ -29,10 +29,36 @@ def download_messages(em): if not p.exists(): p.write_bytes(attach.content) -def load_reports(workdate: datetime.date): +def load_report(workdate: datetime.date): + p = DAILY_DIR / str(workdate) / "Reports" / f"Asset Detail_{workdate:%d %b %Y}.csv" - df = pd.read_csv(p, thousands=",", parse_dates=["As Of Date"]) - return df + 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", "Acctg Status Update (EDT)", + "Accounting Status", "Primary GSP Account", + "Extended GSP Account Number", "Percent Of Total"], axis=1) + 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']: + 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 + df.to_sql("bowdst_val", dawn_engine, if_exists="append", index=False) def cmp_positions(cob: datetime.date, df_blotter: pd.DataFrame) -> pd.DataFrame: |