1 files changed, 99 insertions, 0 deletions
diff --git a/python/bowdst.py b/python/bowdst.py
index a76c82ef..5ca685d5 100644
--- a/python/bowdst.py
+++ b/python/bowdst.py
@@ -28,6 +28,17 @@ def download_messages(em):
                         p.parent.mkdir(parents=True)
                     if not p.exists():
                         p.write_bytes(attach.content)
+                if fname.endswith("csv") and fname.startswith(
+                    "Net Investment Earned Income by Security"
+                ):
+                    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 load_report(workdate: datetime.date):
@@ -99,6 +110,94 @@ def load_report(workdate: datetime.date):
     df.to_sql("bowdst_val", dawn_engine, if_exists="append", index=False)
 
 
+def load_pnl_report(workdate: datetime.date):
+
+    p = (
+        DAILY_DIR
+        / str(workdate)
+        / "Reports"
+        / f"Net Investment Earned Income by Security_{workdate:%d %b %Y}.csv"
+    )
+    df = pd.read_csv(p, thousands=",", parse_dates=["Begin Date", "End Date"])
+    df = df.drop(
+        [
+            "Reporting Account Number",
+            "Reporting Account Name",
+            "Reporting Account Base Currency",
+            "Accounting Status",
+            "Security Cross Reference Type",
+            "Security Cross Reference Cusip",
+            "Local Currency Description",
+            "Country Of Issue",
+            "Country Of Issue Description",
+            "State Code",
+            "Asset Type Code",
+            "5500 category code",
+            "5500 class code Description",
+            "CINS",
+            "SEDOL",
+            "Valoren",
+            "Sicovam",
+            "WPK",
+            "QUICK",
+            "Underlying Sec ID",
+            "Loan ID",
+            "Counterparty",
+            "Source Account Name",
+            "Source Account Number",
+            "Fair Value Hierarchy - Beginning of Period",
+            "Fair Value Override - Beginning of Period",
+            "Fair Value Hierarchy - End of Period",
+            "Fair Value Override - End of Period",
+            "Country of Registration name",
+            "Country of Registration code",
+        ],
+        axis=1,
+    )
+    df.columns = df.columns.str.replace(" ", "_").str.lower()
+    df = df.rename(
+        columns={
+            "change_in_unrealized_currency_gain_loss": "unrealized_currency",
+            "change_in_unrealized_investment_gain_loss": "unrealized_investment",
+            "total_change_in_unrealized_gain_loss": "total_unrealized",
+            "accretion/amortization": "accretion_amortization",
+            "journal_entry_accretion/amortization": "journal_entry_accretion_amortization",
+            "realized_gain/loss": "realized_gain_loss",
+            "journal_entry_realized_g/l": "journal_entry_realized_gl",
+            "manager": "link_ref",
+            "realized_gain/loss_investment": "realized_investment",
+            "realized_gain/loss_currency": "realized_currency",
+            "realized_gain/loss_settled": "realized_settled",
+            "realized_gain/loss_traded": "realized_traded",
+        }
+    )
+    for col in [
+        "unrealized_currency",
+        "unrealized_investment",
+        "total_unrealized",
+        "ending_shares_par",
+        "opening_receivables",
+        "closing_receivables",
+        "income",
+        "journal_entry_income",
+        "accretion_amortization",
+        "journal_entry_accretion_amortization",
+        "realized_gain_loss",
+        "journal_entry_realized_gl",
+        "realized_loss_impaired_securities",
+        "net_investment_income",
+        "realized_investment",
+        "realized_currency",
+        "realized_settled",
+        "realized_traded",
+    ]:
+        if df[col].dtypes == "object":
+            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_pnl", dawn_engine, if_exists="append", index=False)
+
+
 def cmp_positions(cob: datetime.date, df_blotter: pd.DataFrame) -> pd.DataFrame:
     workdate = (cob + bus_day).date()
     p = DAILY_DIR / str(workdate) / "Reports" / f"Asset Detail_{workdate:%d %b %Y}.csv"