diff options
| -rw-r--r-- | python/position.py | 79 | ||||
| -rw-r--r-- | sql/serenitasdb.sql | 34 |
2 files changed, 62 insertions, 51 deletions
diff --git a/python/position.py b/python/position.py index ded45f5a..79b10559 100644 --- a/python/position.py +++ b/python/position.py @@ -120,18 +120,20 @@ def init_swap_rates(conn, session, tenors=[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 15, 20 conn.commit() def init_swaption_vol(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], + source='BBIR'): tickers = [] for t1 in tenors: for t2 in tenors[4:]: - tickers.append(f"USSN{t1:0>2}{t2} Curncy") + tickers.append(f"USSN{t1:0>2}{t2} {source} Curncy") + import pdb;pdb.set_trace() data = retrieve_data(session, tickers, ['PX_LAST'], start_date=datetime.date(1998, 10, 7)) return data def split_tenor_expiry(ticker): - m = re.match("USSN(.{2})([^\s]*) Curncy", ticker) - expiry, tenor = m.groups() + m = re.match("USSN(.{2})([^\s]*) ([^\s]*) Curncy", ticker) + expiry, tenor, _ = m.groups() if expiry[0] == '0': expiry = expiry[1:] if not expiry.isalpha(): @@ -139,49 +141,54 @@ def split_tenor_expiry(ticker): tenor = int(tenor) return expiry, tenor -def insert_swaption_vol(data, conn): - tenors = ['A', 'C', 'F', 'I'] + list(range(1, 11)) + [15, 20, 25, 30] +def insert_swaption_vol(data, conn, source): + 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]) - for t in tenors[4:]: - sql_str = f'INSERT INTO swaption_vol(date, "{t}y") ' + \ - 'VALUES(%s, %s) ON CONFLICT (date)' + \ - f' DO UPDATE SET "{t}y" = %s' + for t in tenors[-14:]: + sql_str = f'INSERT INTO swaption_vol(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: for k, v in df.xs(t, axis=1, level=1)[tenors].iterrows(): if np.all(np.isnan(v.values)): continue - c.execute(sql_str, (k, v.tolist(), v.tolist())) + c.execute(sql_str, (k, v.tolist(), source, v.tolist(), source)) conn.commit() def update_swaption_vol(conn, session, tenors=['A', 'C', 'F', 'I'] + list(range(1, 11)) + [15, 20, 25, 30]): - tickers = [] - for expiry in tenors: - for tenor in tenors: - tickers.append(f"USSN{expiry:0>2}{tenor} 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") ' + \ - 'VALUES(%s, %s) ON CONFLICT (date)' + \ - f' DO UPDATE SET "{t}y" = %s' - r = [] - dates = [] + for source in ['BBIR', 'CMPN', 'ICPL']: + tickers = [] for expiry in tenors: - ticker = f"USSN{expiry:0>2}{t} 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') - with conn.cursor() as c: - c.execute(sql_str, (dates[0], r, r)) - conn.commit() + for tenor in tenors: + tickers.append(f"USSN{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) ' + \ + '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" + 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') + with conn.cursor() as c: + c.execute(sql_str, (dates[0], r, source, r, source)) + conn.commit() def update_swap_rates(conn, session, tenors=[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 15, 20, 30]): @@ -259,5 +266,5 @@ if __name__=="__main__": # 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) - # insert_swaption_vol(data, serenitas_conn) + # data = init_swaption_vol(session, source="CMPN") + # insert_swaption_vol(data, serenitas_conn, "CMPN") diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index 3e498781..46f56076 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -665,22 +665,26 @@ CREATE TABLE USD_swap_fixings( "30y" numeric(5, 3)
)
+CREATE TYPE VOL_SOURCE AS ENUM('BVOL', 'CMPN', 'BBIR');
+
CREATE TABLE swaption_vol(
- date date PRIMARY KEY,
- "1y" numeric(7, 4)[18],
- "2y" numeric(7, 4)[18],
- "3y" numeric(7, 4)[18],
- "4y" numeric(7, 4)[18],
- "5y" numeric(7, 4)[18],
- "6y" numeric(7, 4)[18],
- "7y" numeric(7, 4)[18],
- "8y" numeric(7, 4)[18],
- "9y" numeric(7, 4)[18],
- "10y" numeric(7, 4)[18],
- "15y" numeric(7, 4)[18],
- "20y" numeric(7, 4)[18],
- "25y" numeric(7, 4)[18],
- "30y" numeric(7, 4)[18])
+ 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_quotes(
quotedate timestamptz,
|