diff options
| -rw-r--r-- | python/bowdst.py | 99 | ||||
| -rw-r--r-- | sql/dawn.sql | 41 |
2 files changed, 139 insertions, 1 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" diff --git a/sql/dawn.sql b/sql/dawn.sql index a9ae718e..253e516c 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -576,7 +576,7 @@ CREATE TABLE bbh_pnl( CREATE TYPE bony_asset_type AS ENUM( -'CASH & CASH EQUIVALENTS', 'FIXED INCOME SECURITIES', 'FUTURES CONTRACTS'); +'CASH & CASH EQUIVALENTS', 'FIXED INCOME SECURITIES', 'FUTURES CONTRACTS', 'NON CUSIP RELATED INCOME'); CREATE TABLE bowdst_val( row integer not null, @@ -620,6 +620,44 @@ CREATE TABLE bowdst_accounts( account_name text NOT NULL ); +CREATE TABLE bowdst_pnl( + row integer not null, + begin_date date not null, + end_date date not null, + derivative_type text, + security_id text, + security_description_1 text, + security_description_2 text, + local_currency currency, + coupon_rate float, + asset_type_name text, + asset_type_category bony_asset_type, + unrealized_currency float, + unrealized_investment float, + total_unrealized float, + ending_shares_par float, + opening_receivables float, + closing_receivables float, + income float, + journal_entry_income float, + accretion_amortization float, + journal_entry_accretion_amortization float, + realized_gain_loss float, + journal_entry_realized_gl float, + realized_loss_impaired_securities float, + net_investment_income float, + cusip text, + ticker text, + isin text, + link_ref text, + realized_investment float, + realized_currency float, + realized_settled float, + realized_traded float, + PRIMARY KEY (begin_date, end_date, row) +); + + CREATE OR REPLACE function list_marks(p_date date) RETURNS TABLE(p_date date, identifier varchar(12), price float) AS $$ BEGIN @@ -1186,6 +1224,7 @@ WHERE a.notional IS DISTINCT FROM terminated_amount AND a.expiration_date > p_date AND trade_date <= p_date AND swap_type = 'CD_INDEX_OPTION' AND periodenddate <= p_date +AND a.fund = p_fund ORDER BY dealid, date DESC, periodenddate DESC; END $$ LANGUAGE plpgsql; |