diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/bbg_index_quotes.py | 16 | ||||
| -rw-r--r-- | python/markit/import_quotes.py | 19 |
2 files changed, 21 insertions, 14 deletions
diff --git a/python/bbg_index_quotes.py b/python/bbg_index_quotes.py index 92e3d180..349762f9 100644 --- a/python/bbg_index_quotes.py +++ b/python/bbg_index_quotes.py @@ -14,8 +14,14 @@ for series in range(10, 30): securities[f"ITRX {index_type} CDSI S{series} {t}Y Corp"] = \ (series, index_type, f"{t}yr") -sql_str = (f"INSERT INTO bbg_index_quotes VALUES({','.join(['%s'] * 8)}) " - "ON CONFLICT DO NOTHING") +place_holders = ",".join(['%s'] * 7) +sql_str_price = ("INSERT INTO index_quotes_pre" + "(date, index, series, version, tenor, close_price, source)" + f" VALUES({place_holders}) ON CONFLICT DO NOTHING") +sql_str_spread = ("INSERT INTO index_quotes_pre" + "(date, index, series, version, tenor, close_spread, source)" + f" VALUES({place_holders}) ON CONFLICT DO NOTHING") + start_date = datetime.date.today() - datetime.timedelta(days=7) # one weeek of overlap conn = dbconn('serenitasdb') with init_bbg_session(BBG_IP) as session: @@ -34,8 +40,12 @@ with init_bbg_session(BBG_IP) as session: index = "EU" if index == "XOVER": index = "XO" + if index == "HY": + sql_str = sql_str_price + else: + sql_str = sql_str_spread if not v.empty: c.executemany(sql_str, - [(t[0], ticker, index, series, tenor, version, t[1], pcs) + [(t[0], index, series, version, tenor, t[1], pcs) for t in v.itertuples()]) conn.commit() diff --git a/python/markit/import_quotes.py b/python/markit/import_quotes.py index 5bdd289c..9892478b 100644 --- a/python/markit/import_quotes.py +++ b/python/markit/import_quotes.py @@ -132,14 +132,13 @@ def insert_index(engine, workdate=None): "CDXNAIG": "IG", 'iTraxx Eur': "EU", 'iTraxx Eur Xover': "XO"} - cols = ['closeprice', 'closespread', 'modelprice', 'modelspread'] + cols = ['close_price', 'close_spread', 'model_price', 'model_spread'] colmapping={'Date': 'date', 'Name': 'index', 'Series': 'series', 'Version': 'version', 'Term': 'tenor', - 'Composite Price': 'closeprice', - 'Composite Spread': 'closespread', + 'Composite Price': 'close_price', + 'Composite Spread': 'close_spread', 'Model Price': 'modelprice', 'Model Spread': 'modelspread'} - ext_cols = ['date', 'index', 'series', 'version', 'tenor'] + cols + \ - ['adjcloseprice', 'adjmodelprice'] + ext_cols = ['date', 'index', 'series', 'version', 'tenor'] + cols dates_to_files = {} for f in filenames: @@ -163,14 +162,12 @@ def insert_index(engine, workdate=None): data['tenor'] = data['tenor'].apply(lambda x: x.lower()+'r') data['index'] = data['index'].apply(lambda x: name_mapping[x] if x in name_mapping else np.NaN) data = data.dropna(subset=['index']) - data['closespread'] *= 100 - data['modelspread'] *= 100 + data['close_spread'] *= 100 + data['model_spread'] *= 100 ## we renumbered the version for HY9, 10 and 11 data.loc[data.series.isin([9, 10, 11]) & (data.index=='HY'), 'version'] -= 3 - data['adjcloseprice'] = data['closeprice'] - data['adjmodelprice'] = data['modelprice'] - data = data.groupby(['index', 'series', 'tenor', 'date'], as_index=False).last() - data[ext_cols].to_sql('index_quotes', engine, if_exists='append', index=False) + #data = data.groupby(['index', 'series', 'tenor', 'date'], as_index=False).last() + data[ext_cols].to_sql('index_quotes_pre', engine, if_exists='append', index=False) def insert_tranche(engine, workdate=None): """insert Markit index quotes into the database |