diff options
| -rw-r--r-- | python/load_globeop_report.py | 3 | ||||
| -rw-r--r-- | sql/dawn.sql | 24 |
2 files changed, 17 insertions, 10 deletions
diff --git a/python/load_globeop_report.py b/python/load_globeop_report.py index f2ee406d..f6e13304 100644 --- a/python/load_globeop_report.py +++ b/python/load_globeop_report.py @@ -29,6 +29,9 @@ def read_valuation_report(f): df['row'] = df.index if 'AccountingPeriod' in df: del df['AccountingPeriod'] + if 'CounterParty' in df: + del df['CounterParty'] + df = df.rename(columns={'CounterPartyCode': 'counterparty'}) if "Strat" in df: df.Strat = df.Strat.str.replace("^(SERCGMAST__){1,2}(M_|SER_)?", "", 1) if "Port" in df: diff --git a/sql/dawn.sql b/sql/dawn.sql index 6b221da4..6de6957e 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -27,8 +27,8 @@ CREATE TYPE action AS ENUM('NEW', 'UPDATE', 'CANCEL'); CREATE TYPE currency AS ENUM('USD', 'CAD', 'EUR', 'YEN'); CREATE TYPE bbg_type AS ENUM('Mtge', 'Corp'); CREATE type day_count AS ENUM('ACT/360', 'ACT/ACT', '30/360', 'ACT/365'); -CREATE type bus_day_convention AS ENUM('Modified Following', 'Following', 'Modified Preceding', 'Preceding', - 'Second-Day-After', 'End-of-Month'); +CREATE type bus_day_convention AS ENUM('Modified Following', 'Following', + 'Modified Preceding', 'Preceding', 'Second-Day-After', 'End-of-Month'); CREATE type last_period_convention AS ENUM('Adjusted', 'Unadjusted'); CREATE type index_type AS ENUM('IG', 'HY', 'EU', 'LCDX', 'XO'); CREATE TYPE tenor AS ENUM('6mo', '1yr', '2yr', '3yr', '4yr', '5yr', '7yr', '10yr'); @@ -45,11 +45,12 @@ CREATE TYPE call_notice AS ENUM('24H', '48H', '3D', '4D', '5D', '6D', CREATE TABLE accounts( code varchar(5) PRIMARY KEY, + name text, custodian text, cash_account text, - counterparty text REFERENCES counterparties(code)) + counterparty varchar(12) REFERENCES counterparties(code)) -CREATE TABLE counterparties(code varchar(12) primary key, +CREATE TABLE counterparties(code varchar(12) PRIMARY KEY, name text, city text, state varchar(2), @@ -314,17 +315,19 @@ WHERE date<= p_date ORDER BY identifier, date DESC) b USING (identifier); END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE function list_risk_numbers(p_date date, assetclass asset_class, include_unsettled boolean DEFAULT False) -RETURNS TABLE(identifier varchar(12), description varchar(32), mark float, delta float, index_delta index_type, duration float, wal float, -undiscounted_price float, model_price float) AS $$ +CREATE OR REPLACE function list_risk_numbers(p_date date, assetclass asset_class, + include_unsettled boolean DEFAULT False) +RETURNS TABLE(identifier varchar(12), description varchar(32), mark float, + delta float, index_delta index_type, duration float, wal float, + undiscounted_price float, model_price float) AS $$ BEGIN RETURN QUERY SELECT a.identifier, a.description, b.price, c.delta, c.index_delta, c.duration, c.wal, c.undiscounted_price, c.model_price FROM list_positions(p_date, assetclass, include_unsettled) a LEFT JOIN (SELECT DISTINCT ON (identifier) date, marks.identifier, marks.price FROM marks -WHERE date<= p_date ORDER BY identifier, date DESC) b USING (identifier) +WHERE date <= p_date ORDER BY identifier, date DESC) b USING (identifier) LEFT JOIN (SELECT DISTINCT ON (identifier) * FROM risk_numbers - WHERE date<=p_date ORDER BY identifier, date DESC) c USING (identifier); + WHERE date <=p_date ORDER BY identifier, date DESC) c USING (identifier); END; $$ LANGUAGE plpgsql; @@ -1111,7 +1114,8 @@ CREATE TABLE valuation_reports( periodenddate date NOT NULL, port portfolio, strat strategy, - row integer, + row integer NOT NULL, + counterparty varchar(12) REFERENCES counterparties(code), PRIMARY KEY(periodenddate, row) ); |