diff options
| -rw-r--r-- | python/position.py | 41 | ||||
| -rw-r--r-- | sql/serenitasdb.sql | 39 |
2 files changed, 54 insertions, 26 deletions
diff --git a/python/position.py b/python/position.py index 79b10559..10931d0a 100644 --- a/python/position.py +++ b/python/position.py @@ -121,18 +121,18 @@ def init_swap_rates(conn, session, tenors=[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 15, 20 def init_swaption_vol(session, tenors=['A', 'C', 'F', 'I'] + list(range(1, 11)) + [15, 20, 25, 30], - source='BBIR'): + source='BBIR', + vol_type='N'): tickers = [] for t1 in tenors: for t2 in tenors[4:]: - tickers.append(f"USSN{t1:0>2}{t2} {source} Curncy") - import pdb;pdb.set_trace() + tickers.append(f"USS{vol_type}{t1:0>2}{t2} {source} Curncy") data = retrieve_data(session, tickers, ['PX_LAST'], - start_date=datetime.date(1998, 10, 7)) + start_date=datetime.date(1990, 1, 1)) return data -def split_tenor_expiry(ticker): - m = re.match("USSN(.{2})([^\s]*) ([^\s]*) Curncy", ticker) +def split_tenor_expiry(ticker, vol_type='N'): + m = re.match(f"USS{vol_type}(.{{2}})([^\s]*) ([^\s]*) Curncy", ticker) expiry, tenor, _ = m.groups() if expiry[0] == '0': expiry = expiry[1:] @@ -141,17 +141,18 @@ def split_tenor_expiry(ticker): tenor = int(tenor) return expiry, tenor -def insert_swaption_vol(data, conn, source): +def insert_swaption_vol(data, conn, source, vol_type="N"): if source == "ICPL": tenors = ['A', 'C', 'F'] + list(range(1, 11)) + [15, 20, 25, 30] else: tenors = ['A', 'C', 'F', 'I'] + list(range(1, 11)) + [15, 20, 25, 30] df = pd.concat(data, axis=1) df.columns = df.columns.get_level_values(0) - df.columns = pd.MultiIndex.from_tuples([split_tenor_expiry(c) for c in df.columns]) + df.columns = pd.MultiIndex.from_tuples([split_tenor_expiry(c, vol_type) for c in df.columns]) + table_name = "swaption_normal_vol" if vol_type == "N" else "swaption_lognormal_vol" for t in tenors[-14:]: - sql_str = f'INSERT INTO swaption_vol(date, "{t}y", source) ' + \ + 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" = %s, source = %s' with conn.cursor() as c: @@ -162,22 +163,29 @@ def insert_swaption_vol(data, conn, source): conn.commit() def update_swaption_vol(conn, session, - tenors=['A', 'C', 'F', 'I'] + list(range(1, 11)) + [15, 20, 25, 30]): + tenors=['A', 'C', 'F', 'I'] + list(range(1, 11)) + [15, 20, 25, 30], + vol_type="N"): + """ + Parameters + ---------- + 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"USSN{expiry:0>2}{tenor} {source} Curncy") + 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 swaption_vol(date, "{t}y", source) ' + \ + 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" = %s, source = %s' r = [] dates = [] for expiry in tenors: - ticker = f"USSN{expiry:0>2}{t} {source} Curncy" + 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']) @@ -262,9 +270,10 @@ if __name__=="__main__": populate_cashflow_history(dawn_engine, session, workdate) update_fx(dawn_conn, session, ['EURUSD', 'CADUSD']) update_swap_rates(serenitas_conn, session) - update_swaption_vol(serenitas_conn, session) + for vol_type in ["N", "V"]: + update_swaption_vol(serenitas_conn, session, vol_type) # with init_bbg_session(BBG_IP) as session: # init_fx(session, engine, pd.datetime(2013, 1, 1)) # with init_bbg_session(BBG_IP) as session: - # data = init_swaption_vol(session, source="CMPN") - # insert_swaption_vol(data, serenitas_conn, "CMPN") + # data = init_swaption_vol(session, source="BBIR", vol_type="V") + # insert_swaption_vol(data, serenitas_conn, "BBIR", vol_type="V") diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index 46f56076..afa9e575 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -615,7 +615,7 @@ CREATE TABLE USD_rates( "15Y" real,
"20Y" real,
"25Y" real,
- "30Y" real)
+ "30Y" real);
CREATE TABLE EUR_rates(
effective_date date PRIMARY KEY,
@@ -638,15 +638,15 @@ CREATE TABLE EUR_rates( "15Y" real,
"20Y" real,
"25Y" real,
- "30Y" real)
+ "30Y" real);
CREATE TABLE USD_curves(
effective_date date PRIMARY KEY,
- curve bytea)
+ curve bytea);
CREATE TABLE EUR_curves(
effective_date date PRIMARY KEY,
- curve bytea)
+ curve bytea);
CREATE TABLE USD_swap_fixings(
fixing_date date PRIMARY KEY,
@@ -663,11 +663,30 @@ CREATE TABLE USD_swap_fixings( "15y" numeric(5, 3),
"20y" numeric(5, 3),
"30y" numeric(5, 3)
-)
+);
CREATE TYPE VOL_SOURCE AS ENUM('BVOL', 'CMPN', 'BBIR');
-CREATE TABLE swaption_vol(
+CREATE TABLE swaption_normal_vol(
+ date date,
+ "1y" float[18],
+ "2y" float[18],
+ "3y" float[18],
+ "4y" float[18],
+ "5y" float[18],
+ "6y" float[18],
+ "7y" float[18],
+ "8y" float[18],
+ "9y" float[18],
+ "10y" float[18],
+ "15y" float[18],
+ "20y" float[18],
+ "25y" float[18],
+ "30y" float[18],
+ source VOL_SOURCE,
+ PRIMARY KEY(date, source));
+
+CREATE TABLE swaption_lognormal_vol(
date date,
"1y" float[18],
"2y" float[18],
@@ -684,7 +703,7 @@ CREATE TABLE swaption_vol( "25y" float[18],
"30y" float[18],
source VOL_SOURCE,
- PRIMARY KEY(date, source))
+ PRIMARY KEY(date, source));
CREATE TABLE swaption_quotes(
quotedate timestamptz,
@@ -704,7 +723,7 @@ CREATE TABLE swaption_quotes( tail float,
quote_source varchar(4),
PRIMARY KEY(quotedate, index, series, expiry, strike),
- FOREIGN KEY(quotedate, index, series, expiry) REFERENCES swaption_ref_quotes)
+ FOREIGN KEY(quotedate, index, series, expiry) REFERENCES swaption_ref_quotes);
CREATE TABLE swaption_ref_quotes(
quotedate timestamptz,
@@ -715,7 +734,7 @@ CREATE TABLE swaption_ref_quotes( fwdprice float,
fwdspread float,
fwdbpv float,
- PRIMARY KEY(quotedate, index, series, expiry))
+ PRIMARY KEY(quotedate, index, series, expiry));
CREATE TABLE swaption_calib(
quotedate timestamptz,
@@ -727,4 +746,4 @@ CREATE TABLE swaption_calib( vol_receiver float
vol_payer_black float,
vol_receiver_black float,
- PRIMARY KEY(quotedate, index, series, expiry, strike))
+ PRIMARY KEY(quotedate, index, series, expiry, strike));
|