diff options
| -rw-r--r-- | python/position.py | 34 | ||||
| -rw-r--r-- | sql/serenitasdb.sql | 6 |
2 files changed, 31 insertions, 9 deletions
diff --git a/python/position.py b/python/position.py index 057feddb..6996a295 100644 --- a/python/position.py +++ b/python/position.py @@ -6,6 +6,7 @@ from itertools import product import datetime import numpy as np import pandas as pd +import requests from pandas import bdate_range import re import os @@ -392,17 +393,31 @@ def populate_cashflow_history(conn, session, workdate=None, funds=("SERCGMAST",) def update_iboxhy(conn, session, start_date: datetime.date = None): - d = retrieve_data( - session, - ["IBOXHY Index"], - fields=["PX_LAST", "CONTRBTD_ZSPREAD"], - start_date=start_date, - ) - df = d["IBOXHY Index"] + params = {"id": "usd.hytop.275_usd_n_l_us_eod_calc", "env": "prod", "period": "5Y"} + url = "https://indicesweb.ihsmarkit.com/indices/chartData" + l = [] + for s in ["Total Return Index Level", "Z-spread"]: + params["timeSeries"] = s + r = requests.get(url, params) + d = r.json()["chartData"]["chartSeries"]["data"] + df = pd.DataFrame.from_records(d) + df.date = pd.to_datetime(df.date, unit="ms") + df = df.set_index("date") + df.columns = [s] + l.append(df) + df = pd.concat(l, axis=1) + # d = retrieve_data( + # session, + # ["IBOXHY Index"], + # fields=["PX_LAST", "CONTRBTD_ZSPREAD"], + # start_date=start_date, + # ) + # df = d["IBOXHY Index"] with conn.cursor() as c: c.executemany( - "INSERT INTO iboxhy_data VALUES (%s, %s, %s)", - df.itertuples(index=True), + "INSERT INTO iboxhy_data VALUES (%s, %s, %s)" + "ON CONFLICT (date) DO UPDATE SET level=%s, zspread=%s", + [(*t, *t[1:]) for t in df.itertuples(index=True)], ) conn.commit() @@ -532,6 +547,7 @@ if __name__ == "__main__": update_hyg_option_quotes(serenitas_conn, session, workdate) update_hyg_vol(serenitas_conn, session, workdate) # update_hyg_dvd(serenitas_conn, session) + update_iboxhy(serenitas_conn, session, workdate) for vol_type in ["N", "V", "N_OIS"]: update_swaption_vol( serenitas_conn, session, start_from=workdate, vol_type=vol_type diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index 9b3ded65..0ad36041 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -1303,3 +1303,9 @@ CREATE TABLE hyg_dvd_hist( amount float,
dividend_type div_type
);
+
+CREATE TABLE iboxhy_data(
+ date date PRIMARY KEY,
+ "level" float,
+ zspread float
+);
|