diff options
| -rw-r--r-- | python/position.py | 34 | ||||
| -rw-r--r-- | sql/serenitasdb.sql | 17 |
2 files changed, 50 insertions, 1 deletions
diff --git a/python/position.py b/python/position.py index 555f7a17..12e2b6a8 100644 --- a/python/position.py +++ b/python/position.py @@ -1,4 +1,5 @@ from bbg_helpers import init_bbg_session, retrieve_data, BBG_IP +import datetime import pandas as pd from sqlalchemy import create_engine from pandas.tseries.offsets import BDay @@ -100,6 +101,35 @@ def update_fx(engine, session, currencies): c.execute(sqlstr, values) conn.commit() +def init_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'], + start_date=datetime.date(1998, 10, 7)) + for t in tenors: + ticker = f"USISDA{t:02} Index" + sql_str = f'INSERT INTO USD_swap_fixings(fixing_date, "{t}y") ' + \ + 'VALUES(%s, %s) ON CONFLICT (fixing_date)' + \ + f' DO UPDATE SET "{t}y" = %s' + conn = engine.raw_connection() + with conn.cursor() as c: + c.executemany(sql_str, + [(d, r, r) for d, r in data[ticker]['PX_LAST'].items()]) + 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']) + 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() + def populate_cashflow_history(engine, session, workdate=None): securities = get_list(engine, workdate) conn = engine.raw_connection() @@ -145,7 +175,8 @@ def populate_cashflow_history(engine, session, workdate=None): if __name__=="__main__": engine = create_engine('postgresql://dawn_user@debian/dawndb') - if len(sys.argv)>1: + serenitas_engine = create_engine('postgresql://serenitas_user@debian/serenitasdb') + if len(sys.argv) > 1: workdate = pd.Timestamp(sys.argv[1]) else: workdate = pd.datetime.today() @@ -153,5 +184,6 @@ if __name__=="__main__": update_securities(engine, session, workdate) populate_cashflow_history(engine, session, workdate) update_fx(engine, session, ['EURUSD', 'CADUSD']) + update_swap_rates(serenitas_engine, session) # with init_bbg_session(BBG_IP) as session: # init_fx(session, engine, pd.datetime(2013, 1, 1)) diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index f423c3fd..b75e3a84 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -648,6 +648,23 @@ CREATE TABLE EUR_curves( effective_date date PRIMARY KEY,
curve bytea)
+CREATE TABLE USD_swap_fixings(
+ fixing_date date PRIMARY KEY,
+ "1y" numeric(5, 3),
+ "2y" numeric(5, 3),
+ "3y" numeric(5, 3),
+ "4y" numeric(5, 3),
+ "5y" numeric(5, 3),
+ "6y" numeric(5, 3),
+ "7y" numeric(5, 3),
+ "8y" numeric(5, 3),
+ "9y" numeric(5, 3),
+ "10y" numeric(5, 3),
+ "15y" numeric(5, 3),
+ "20y" numeric(5, 3),
+ "30y" numeric(5, 3)
+)
+
CREATE TABLE swaption_quotes(
quotedate timestamptz,
index index_type,
|