diff options
| -rw-r--r-- | python/position.py | 57 | ||||
| -rw-r--r-- | sql/serenitasdb.sql | 11 |
2 files changed, 37 insertions, 31 deletions
diff --git a/python/position.py b/python/position.py index 6be0f477..6f7c5e83 100644 --- a/python/position.py +++ b/python/position.py @@ -1,4 +1,5 @@ from serenitas.analytics.bbg_helpers import init_bbg_session, retrieve_data, BBG_IP +from itertools import product import datetime import numpy as np import pandas as pd @@ -223,9 +224,11 @@ def insert_swaption_vol(data, conn, source, vol_type="N"): def update_swaption_vol( conn, session, - tenors=["A", "C", "F", "I"] + list(range(1, 11)) + [15, 20, 25, 30], + expiries=["A", "C", "F", "I"] + list(range(1, 11)) + [15, 20, 25, 30], + tenors=[1, 2, 3, 4, 5, 7, 10, 12, 15, 20, 25, 30], + start_from=datetime.date.today(), *, - sources=["BBIR", "CMPN", "ICPL"], + sources=("BBIR", "CMPN", "ICPL"), vol_type="N", ): """ @@ -233,36 +236,28 @@ def update_swaption_vol( ---------- vol_type : one of 'N' or 'V' (normal or log-normal) """ - table_name = "swaption_normal_vol" if vol_type == "N" else "swaption_lognormal_vol" - for source in ["BBIR", "CMPN", "ICPL"]: - tickers = [] - for expiry in tenors: - for tenor in tenors: - tickers.append(f"USS{vol_type}{expiry:0>2}{tenor} {source} Curncy") - - data = retrieve_data(session, tickers, ["PX_YEST_CLOSE", "PX_CLOSE_DT"]) - for t in tenors[4:]: - sql_str = ( - f'INSERT INTO {table_name}(date, "{t}y", source) ' - + "VALUES(%s, %s, %s) ON CONFLICT (date, source)" - + f' DO UPDATE SET "{t}y" = excluded."{t}y", source = excluded.source' - ) - r = [] - dates = [] - for expiry in tenors: - ticker = f"USS{vol_type}{expiry:0>2}{t} {source} Curncy" - if data[ticker]: - - r.append(data[ticker]["PX_YEST_CLOSE"]) - dates.append(data[ticker]["PX_CLOSE_DT"]) - else: - r.append(None) - dates.append(dates[-1]) - if dates.count(dates[0]) < len(dates): - raise ValueError("Not all quotes are from the same date") + db_vol_type = "Normal" if vol_type == "N" else "LogNormal" + mappings = {"A": "1M", "C": "3M", "F": "6M", "I": "9M"} + for source in sources: + tickers = { + f"USS{vol_type}{e:0>2}{t} {source} Curncy": (e, t) + for e, t in product(expiries, tenors) + } + data = retrieve_data(session, tickers, ["PX_LAST"], start_date=start_from) + data = pd.concat(data, names=["ticker", "date"]) + for date, df in data.groupby(level="date"): with conn.cursor() as c: - c.execute(sql_str, (dates[0], r, source)) - conn.commit() + for ticker, val in df.reset_index("date", drop=True).itertuples(): + e, t = tickers[ticker] + tenor = f"{t}Y" + expiry = mappings.get(e, f"{e}Y") + c.execute( + "INSERT INTO swaption_vol VALUES (%s, %s, %s, %s, %s, %s)" + " ON CONFLICT (date, expiry, tenor, vol_type, source) " + "DO UPDATE SET vol=EXCLUDED.vol", + (date, expiry, tenor, db_vol_type, source, val), + ) + conn.commit() def update_swap_rates( diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index 01b9ad9b..ac509a9b 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -278,6 +278,7 @@ CREATE TYPE bbgSource AS ENUM('MSG1', 'CBIN', 'CBGN', 'MKIT', 'CMAN', 'SRNTAS'); CREATE TYPE DocClause AS ENUM('No Restructuring', 'Modified Modified Restructurin', 'Full Restructuring', 'Modified Restructuring');
CREATE TYPE ShortCode AS ENUM('CR14', 'XR14', 'MM14');
CREATE TYPE tenor AS ENUM('6mo', '1yr', '2yr', '3yr', '4yr', '5yr', '7yr', '10yr');
+CREATE TYPE TENOR AS ENUM('1M', '3M', '6M', '9M', '1Y', '2Y', '3Y', '4Y', '5Y', '6Y', '7Y', '8Y', '9Y', '10Y', '12Y', '15Y', '20Y', '25Y', '30Y');
CREATE TYPE index_type AS ENUM('IG', 'HY', 'EU', 'LCDX', 'XO', 'BS', 'HY.BB');
CREATE TYPE BBG_CC AS ENUM('OC');
@@ -999,6 +1000,7 @@ CREATE TABLE USD_swap_fixings( );
CREATE TYPE VOL_SOURCE AS ENUM('BVOL', 'CMPN', 'BBIR', 'GFIS');
+CREATE TYPE "VOL_TYPE" AS ENUM('Normal', 'LogNormal');
CREATE TABLE swaption_normal_vol(
date date,
@@ -1038,6 +1040,15 @@ CREATE TABLE swaption_lognormal_vol( source VOL_SOURCE,
PRIMARY KEY(date, source));
+CREATE TABLE swaption_vol(
+ date date,
+ expiry "TENOR",
+ tenor "TENOR",
+ vol_type "VOL_TYPE",
+ source vol_source,
+ vol double precision,
+PRIMARY KEY (date, expiry, tenor, vol_type, source));
+
CREATE TABLE swaption_quotes(
quote_id SERIAL PRIMARY KEY,
ref_id integer REFERENCES swaption_ref_quotes ON DELETE CASCADE,
|