diff options
| -rw-r--r-- | sql/dawn.sql | 71 |
1 files changed, 70 insertions, 1 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index a94b3fc3..ca726078 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -174,7 +174,7 @@ CREATE TABLE bonds(id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, CREATE OR REPLACE FUNCTION notify_id() -RETURNS trigger +RETURNS TRIGGER AS $$ BEGIN PERFORM pg_notify(TG_TABLE_NAME, NEW.id::text); @@ -329,6 +329,75 @@ UPDATE protection ON cds FOR EACH ROW WHEN (current_setting('application_name') != 'autobooker') EXECUTE FUNCTION notify_id(); +CREATE OR REPLACE FUNCTION cds_enrich() +RETURNS TRIGGER +AS $$ +from serenitas.analytics.api import CreditIndex +trade = TD["new"] +if trade["swap_type"] == "BESPOKE": + return +if TD["event"] == "INSERT" and all([trade["upfront"], trade["traded_level"]]): + return +index = CreditIndex( + redcode=trade["security_id"], + maturity=trade["maturity"], + notional=trade["notional"], + value_date=trade["trade_date"], +) +index.direction = trade["protection"] +if trade["traded_level"]: + if trade["swap_type"] == "CD_INDEX": + index.ref = float(trade["traded_level"]) + upfront = -index.pv + else: + tranche_factor = (trade["detach"] - trade["attach"]) / ( + trade["orig_detach"] - trade["orig_attach"] + ) + accrued = index._accrued * trade["fixed_rate"] + match index.index_type: + case "HY": + dirty_price = float(trade["traded_level"]) + accrued + upfront = ( + -(100 - dirty_price) + * index.notional + * tranche_factor + * 0.01 + ) + case "EU" | "XO" if trade["orig_attach"] in (6, 12, 35): + if trade["orig_attach"] == 6: + index.recovery = 0.0 + index.spread = float(trade["traded_level"]) + upfront = -index._pv * trade["notional"] * tranche_factor + case _: + dirty_protection = float(trade["traded_level"]) - accrued + upfront = ( + -dirty_protection + * index.notional + * tranche_factor + * 0.01 + ) + trade["upfront"] = upfront +else: + index.pv = trade["upfront"] + trade["traded_level"] = index.ref +return "MODIFY" +$$ LANGUAGE plpython3u; + + +CREATE OR REPLACE TRIGGER upfront_setter AFTER +INSERT + OR +UPDATE OF + security_id, + maturity, + trade_date, + upfront_settle_date, + traded_level, + notional, + protection +ON + cds FOR EACH ROW WHEN (current_setting('application_name') != 'autobooker') EXECUTE FUNCTION cds_enrich(); + CREATE OR REPLACE function update_attach() RETURNS TRIGGER AS $$ |