1 files changed, 83 insertions, 0 deletions
diff --git a/python/load_bbh_reports.py b/python/load_bbh_reports.py
new file mode 100644
index 00000000..a433a050
--- /dev/null
+++ b/python/load_bbh_reports.py
@@ -0,0 +1,83 @@
+import datetime
+import pandas as pd
+from utils.db import dbengine
+
+
+def pnl_report(f):
+    df = pd.read_csv(f, thousands=",")
+    column_list = [
+        "Accounting Date",
+        "Security ID",
+        "Custody Head Account Number",
+        "Issue Name",
+        "Local Currency",
+        "Base Market Value",
+        "Base Change Income",
+        "Base Change FX Realized Gain Loss",
+        "Base Change FX Unrealized Gain Loss",
+        "Base Change Unrealized Gain Loss",
+        "Base Change Realized Gain Loss",
+        "Base Change Miscellaneous Income",
+        "Base Change Expense",
+        "Base Change Total",
+        "Sub Security Type Code",
+        "Source",
+    ]
+    df = df[column_list]
+    df.columns = (
+        df.columns.str.strip()
+        .str.lower()
+        .str.replace(" ", "_")
+        .str.replace("(", "")
+        .str.replace(")", "")
+    )
+    df["row"] = df.index
+    df.to_sql("bbh_pnl", dbengine("dawndb"), if_exists="append", index=False)
+
+
+def val_report(f):
+    df = pd.read_csv(f, thousands=",")
+    df = df.apply(lambda x: x.str.strip() if x.dtype == "object" else x)
+    column_list = [
+        "Accounting Date",
+        "Custody Head Account Number",
+        "Security ID",
+        "Security Description",
+        "Asset Currency",
+        "Original Face",
+        "Base Price",
+        "Local Unit Cost",
+        "Base Unit Cost",
+        "Local Market Value",
+        "Base Market Value",
+        "Security ID Type",
+        "Sub Security Type Code",
+        "Source",
+        "Investment Type Code",
+        "Investment Type Description",
+        "Security Long Description",
+        "Security Type Code",
+        "Total Current Assets",
+        "Total Current Liabilities",
+        "Total Net Assets",
+    ]
+    df = df[column_list]
+    df.columns = (
+        df.columns.str.strip()
+        .str.lower()
+        .str.replace(" ", "_")
+        .str.replace("(", "")
+        .str.replace(")", "")
+    )
+    df["row"] = df.index
+    df.to_sql("bbh_val", dbengine("dawndb"), if_exists="append", index=False)
+
+
+def load_reports(date=datetime.date.today()):
+    f = "/home/serenitas/Daily/" + date.strftime("%Y-%m-%d") + "/Reports/BBH"
+    pnl_report(f + "_PNL.csv")
+    val_report(f + "_VAL.csv")
+
+
+if __name__ == "__main__":
+    load_reports()