diff options
| -rw-r--r-- | python/calibrate_tranches_BC.py | 12 | ||||
| -rw-r--r-- | sql/serenitasdb.sql | 22 |
2 files changed, 28 insertions, 6 deletions
diff --git a/python/calibrate_tranches_BC.py b/python/calibrate_tranches_BC.py index e8ce2c2f..bda7ed6f 100644 --- a/python/calibrate_tranches_BC.py +++ b/python/calibrate_tranches_BC.py @@ -1,5 +1,5 @@ from utils.db import dbconn -from analytics import TrancheBasket +from analytics.tranche_basket import TrancheBasket, MarkitTrancheBasket from pandas.tseries.offsets import BDay import datetime import logging @@ -23,13 +23,13 @@ def get_lastdate(conn, index, series, tenor): return date -def build_sql_str(df): +def build_sql_str(df, use_markit=False): cols = ",".join(df.columns) cols_ex_tranche_id = ",".join([c for c in df.columns if c != "tranche_id"]) cols_excluded = ",".join([f"excluded.{c}" for c in df.columns if c != "tranche_id"]) place_holders = ",".join([f"%({c})s" for c in df.columns]) sql_str = ( - f"INSERT INTO tranche_risk({cols}) " + f"INSERT INTO {'markit_' if use_markit else ''}tranche_risk({cols}) " f"VALUES({place_holders}) ON CONFLICT (tranche_id) DO " f"UPDATE SET ({cols_ex_tranche_id}) = ({cols_excluded})" ) @@ -68,8 +68,12 @@ if __name__ == "__main__": parser.add_argument( "-s", "--skewtype", action="store", help="skew type", default="bottomup" ) + parser.add_argument("-m", "--markit", action="store_true", help="Use Markit quotes") args = parser.parse_args() logger.setLevel(logging.DEBUG if args.debug else logging.INFO) + if args.markit: + TrancheBasket = MarkitTrancheBasket + CODE_DIR = Path(os.environ["CODE_DIR"]) if not args.debug: handler = SerenitasFileHandler(f"calib_tranches_{datetime.date.today()}.log") @@ -199,7 +203,7 @@ if __name__ == "__main__": if data: data = pd.concat(data) - sql_str = build_sql_str(data) + sql_str = build_sql_str(data, args.markit) with serenitas_conn.cursor() as c: c.executemany(sql_str, data.to_dict(orient="record")) serenitas_conn.commit() diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index 5778632f..e50d27a8 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -375,6 +375,7 @@ GRANT ALL ON cds_quotes TO serenitas_user; CREATE INDEX IF NOT EXISTS cds_quotes_date_index ON cds_quotes(date);
CREATE TABLE markit_tranche_quotes(
+ id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
quotedate date,
basketid integer REFERENCES index_version,
tenor tenor,
@@ -385,7 +386,7 @@ CREATE TABLE markit_tranche_quotes( upfront_ask float,
tranche_spread smallint,
index_price float,
- PRIMARY KEY (quotedate, basketid, tenor, attach, detach)
+ UNIQUE (quotedate, basketid, tenor, attach, detach)
);
GRANT ALL ON markit_tranche_quotes TO serenitas_user;
@@ -763,7 +764,24 @@ CREATE TABLE tranche_risk( GRANT ALL ON tranche_risk to serenitas_user;
-ALTER TABLE tranche_risk ADD UNIQUE(tranche_id);
+CREATE TABLE markit_tranche_risk(
+ id integer GENERATED BY DEFAULT PRIMARY KEY,
+ tranche_id integer UNIQUE REFERENCES markit_tranche_quotes(id) ON DELETE CASCADE,
+ index_price float,
+ index_basis float,
+ index_expected_loss float,
+ index_duration float,
+ index_theta float,
+ corr_at_detach float,
+ delta float,
+ fwd_delta float,
+ gamma float,
+ theta float,
+ corr01 float,
+ duration float,
+ spread float,
+ expected_loss float);
+
CREATE OR REPLACE FUNCTION riskmonitor_getindicesinfo2(
IN p_date date,
|