diff options
| -rw-r--r-- | python/load_bbh_reports.py | 83 | ||||
| -rw-r--r-- | sql/dawn.sql | 54 |
2 files changed, 137 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() diff --git a/sql/dawn.sql b/sql/dawn.sql index 11890840..0da551b7 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -505,6 +505,60 @@ CREATE TABLE mark_source_mapping( final text, PRIMARY KEY (globeop)); +CREATE TABLE subscription_and_fee( + date date, + fund fund, + subscription float, + redemption float, + incentive_fee float, + management_fee float, + PRIMARY KEY (fund, date)); + +CREATE TABLE bbh_val( + accounting_date date NOT NULL, + row integer NOT NULL, + custody_head_account_number float, + security_id text, + security_description text, + asset_currency text, + original_face float, + base_price float, + local_unit_cost float, + base_unit_cost float, + local_market_value float, + base_market_value float, + security_id_type text, + sub_security_type_code text, + source text, + investment_type_code text, + investment_type_description text, + security_long_description text, + security_type_code text, + total_current_assets float, + total_current_liabilities float, + total_net_assets float, + PRIMARY KEY (accounting_date, row)); + +CREATE TABLE bbh_pnl( + accounting_date date NOT NULL, + row integer NOT NULL, + security_id text, + custody_head_account_number float, + issue_name text, + local_currency text, + base_market_value float, + base_change_income float, + base_change_fx_realized_gain_loss float, + base_change_fx_unrealized_gain_loss float, + base_change_unrealized_gain_loss float, + base_change_realized_gain_loss float, + base_change_miscellaneous_income float, + base_change_expense float, + base_change_total float, + sub_security_type_code text, + source text, + PRIMARY KEY (accounting_date, row)); + CREATE OR REPLACE function list_marks(p_date date) RETURNS TABLE(p_date date, identifier varchar(12), price float) AS $$ BEGIN |