diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/position.py | 79 |
1 files changed, 73 insertions, 6 deletions
diff --git a/python/position.py b/python/position.py index 12e2b6a8..d5bf4558 100644 --- a/python/position.py +++ b/python/position.py @@ -116,16 +116,81 @@ def init_swap_rates(engine, session, tenors=[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 15, [(d, r, r) for d, r in data[ticker]['PX_LAST'].items()]) conn.commit() +def init_swaption_vol(session, + tenors=['A', 'C', 'F', 'I'] + list(range(1, 11)) + [15, 20, 25, 30]): + tickers = [] + for t1 in tenors: + for t2 in tenors[4:]: + tickers.append(f"USSN{t1:0>2}{t2} Curncy") + 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() + if expiry[0] == '0': + expiry = expiry[1:] + if not expiry.isalpha(): + expiry = int(expiry) + tenor = int(tenor) + return expiry, tenor + +def insert_swaption_vol(data, engine): + 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]) + conn = engine.raw_connection() + + 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' + 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())) + conn.commit() + +def update_swaption_vol(session, engine, + 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_LAST', 'LAST_UPDATE_DT']) + conn = engine.raw_connection() + 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 expiry in tenors: + ticker = f"USSN{expiry:0>2}{t} Curncy" + if data[ticker]: + r.append(data[ticker]['PX_LAST']) + dates.append(data[ticker]['LAST_UPDATE_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() + def update_swap_rates(engine, session, tenors=[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 15, 20, 30]): securities = [f"USISDA{t:02} Index" for t in tenors] data = retrieve_data(session, securities, ['PX_LAST', 'LAST_UPDATE_DT']) + conn = engine.raw_connection() for t in tenors: ticker = f"USISDA{t:02} Index" sql_str = f'INSERT INTO USD_swap_fixings(fixing_date, "{t}y") ' + \ 'VALUES(%(LAST_UPDATE_DT)s, %(PX_LAST)s) ON CONFLICT (fixing_date)' + \ f' DO UPDATE SET "{t}y" = %(PX_LAST)s' - conn = engine.raw_connection() with conn.cursor() as c: c.execute(sql_str, data[ticker]) conn.commit() @@ -174,16 +239,18 @@ def populate_cashflow_history(engine, session, workdate=None): conn.commit() if __name__=="__main__": - engine = create_engine('postgresql://dawn_user@debian/dawndb') - serenitas_engine = create_engine('postgresql://serenitas_user@debian/serenitasdb') + from db import dbengine + dawn_engine = dbengine('dawndb') + serenitas_engine = dbengine('serenitasdb') if len(sys.argv) > 1: workdate = pd.Timestamp(sys.argv[1]) else: workdate = pd.datetime.today() with init_bbg_session(BBG_IP) as session: - update_securities(engine, session, workdate) - populate_cashflow_history(engine, session, workdate) - update_fx(engine, session, ['EURUSD', 'CADUSD']) + update_securities(dawn_engine, session, workdate) + populate_cashflow_history(dawn_engine, session, workdate) + update_fx(dawn_engine, session, ['EURUSD', 'CADUSD']) update_swap_rates(serenitas_engine, session) + update_swaption_vil(serenitas_engine, session) # with init_bbg_session(BBG_IP) as session: # init_fx(session, engine, pd.datetime(2013, 1, 1)) |