diff options
Diffstat (limited to 'python/markit/rates.py')
| -rw-r--r-- | python/markit/rates.py | 130 |
1 files changed, 55 insertions, 75 deletions
diff --git a/python/markit/rates.py b/python/markit/rates.py index 29a39465..459b1cba 100644 --- a/python/markit/rates.py +++ b/python/markit/rates.py @@ -1,4 +1,5 @@ import datetime +from itertools import chain from serenitas.utils.env import DATA_DIR from io import BytesIO from psycopg2 import sql @@ -11,12 +12,20 @@ from serenitas.analytics.bbg_helpers import retrieve_data from serenitas.analytics.dates import prev_business_day -def downloadMarkitOISData(conn, download_date=datetime.date.today(), currency="USD"): +def downloadMarkitIRData( + conn, download_date=datetime.date.today(), currency="USD", ois_flag=False +): base_dir = DATA_DIR / "Yield Curves" - url = f"https://rfr.ihsmarkit.com/InterestRates_USD_{download_date:%Y%m%d}.zip?email=ghorel@lmcg.com" - curve_file = base_dir / f"InterestRatesOIS_{currency}_{download_date:%Y%m%d}.xml" + curve_file = ( + base_dir + / f"InterestRates{'OIS' if ois_flag else ''}_{currency}_{download_date:%Y%m%d}.xml" + ) + if ois_flag: + url = f"https://rfr.ihsmarkit.com/InterestRates_{currency}_{download_date:%Y%m%d}.zip?email=ghorel@lmcg.com" + else: + url = f"http://www.markit.com/news/{curve_file.stem}.zip" if not curve_file.exists(): - r = requests.get(url) + r = requests.get(url) if ois_flag else requests.post(url) if "zip" in r.headers["content-type"]: with zipfile.ZipFile(BytesIO(r.content)) as z: for f in z.filelist: @@ -27,99 +36,70 @@ def downloadMarkitOISData(conn, download_date=datetime.date.today(), currency="U raise ValueError(r.content.decode().rstrip()) tree = ET.parse(curve_file) - ois = zip( - [e.text for e in tree.findall(".//ois/*/tenor")], - [float(e.text) for e in tree.findall(".//ois/*/parrate")], - ) - effectiveasof = tree.find(".//effectiveasof").text - MarkitData = { - "ois": list(ois), - "effectiveasof": datetime.date.fromisoformat(effectiveasof), - } - ql_yc = YC( - currency=currency, - MarkitData=MarkitData, - evaluation_date=MarkitData["effectiveasof"], - ) - jp_yc = ql_to_jp(ql_yc) - sql_str = f"INSERT INTO {currency}_OIS_curves VALUES(%s, %s) ON CONFLICT DO NOTHING" - with conn.cursor() as c: - c.execute( - sql_str, - (MarkitData["effectiveasof"], jp_yc.__getstate__()), + if ois_flag: + ois = zip( + [e.text for e in tree.findall(".//ois/*/tenor")], + [float(e.text) for e in tree.findall(".//ois/*/parrate")], ) - instruments = MarkitData["ois"] - names = sql.SQL(", ").join([sql.Identifier(r[0]) for r in instruments]) - values = sql.SQL(", ").join( - sql.Placeholder() * (len(instruments) + 1) - ) # +1 for effective_date - insert_str = sql.SQL( - f"INSERT INTO {currency}_OIS_rates(effective_date, {{}}) VALUES({{}}) " - "ON CONFLICT DO NOTHING" - ).format(names, values) - with conn.cursor() as c: - c.execute( - insert_str, [MarkitData["effectiveasof"]] + [r[1] for r in instruments] + effectiveasof = tree.find(".//effectiveasof").text + MarkitData = { + "ois": list(ois), + "effectiveasof": datetime.date.fromisoformat(effectiveasof), + } + else: + deposits = zip( + [e.text for e in tree.findall("./deposits/*/tenor")], + [float(e.text) for e in tree.findall("./deposits/*/parrate")], ) - conn.commit() - - -def downloadMarkitIRData(conn, download_date=datetime.date.today(), currency="USD"): - base_dir = DATA_DIR / "Yield Curves" - curve_file = base_dir / f"InterestRates_{currency}_{download_date:%Y%m%d}.xml" - if not curve_file.exists(): - r = requests.post(f"http://www.markit.com/news/{curve_file.stem}.zip") - if "zip" in r.headers["content-type"]: - with zipfile.ZipFile(BytesIO(r.content)) as z: - z.extractall(path=base_dir) - else: - raise ValueError(r.content.decode().rstrip()) - - tree = ET.parse(curve_file) - deposits = zip( - [e.text for e in tree.findall("./deposits/*/tenor")], - [float(e.text) for e in tree.findall("./deposits/*/parrate")], - ) - swaps = zip( - [e.text for e in tree.findall("./swaps/*/tenor")], - [float(e.text) for e in tree.findall("./swaps/*/parrate")], - ) - effectiveasof = tree.find("./effectiveasof").text - MarkitData = { - "deposits": list(deposits), - "swaps": list(swaps), - "effectiveasof": datetime.date.fromisoformat(effectiveasof), - } + swaps = zip( + [e.text for e in tree.findall("./swaps/*/tenor")], + [float(e.text) for e in tree.findall("./swaps/*/parrate")], + ) + effectiveasof = tree.find("./effectiveasof").text + MarkitData = { + "deposits": list(deposits), + "swaps": list(swaps), + "effectiveasof": datetime.date.fromisoformat(effectiveasof), + } ql_yc = YC( currency=currency, MarkitData=MarkitData, evaluation_date=MarkitData["effectiveasof"], + curve_type="Ibor" if not ois_flag else "OIS", ) jp_yc = ql_to_jp(ql_yc) - sql_str = f"INSERT INTO {currency}_curves VALUES(%s, %s) ON CONFLICT DO NOTHING" + sql_str = ( + "INSERT INTO rate_curves(effective_date, curve_type, curve)" + "VALUES(%s, %s, %s) ON CONFLICT DO NOTHING" + ) + if ois_flag: + ref_index = {"USD": "SOFR", "EUR": "ESTR", "JPY": "TONA"} + ct = CurveType[f"{currency}_{ref_index[currency]}_ISDA"].value + else: + ct = CurveType[f"{currency}_ISDA"].value with conn.cursor() as c: c.execute( sql_str, - (MarkitData["effectiveasof"], jp_yc.__getstate__()), + (MarkitData["effectiveasof"], ct, jp_yc.__getstate__()), ) - instruments = MarkitData["deposits"] + MarkitData["swaps"] + effectiveasof = MarkitData.pop("effectiveasof") + instruments = list(chain.from_iterable(MarkitData.values())) names = sql.SQL(", ").join([sql.Identifier(r[0]) for r in instruments]) values = sql.SQL(", ").join( sql.Placeholder() * (len(instruments) + 1) ) # +1 for effective_date + table_name = sql.SQL(f"{currency}_{'OIS_' if ois_flag else ''}rates".lower()) insert_str = sql.SQL( - f"INSERT INTO {currency}_rates(effective_date, {{}}) VALUES({{}}) " - "ON CONFLICT DO NOTHING" - ).format(names, values) + "INSERT INTO {}(effective_date, {}) VALUES({}) " "ON CONFLICT DO NOTHING" + ).format(table_name, names, values) with conn.cursor() as c: - c.execute( - insert_str, [MarkitData["effectiveasof"]] + [r[1] for r in instruments] - ) + c.execute(insert_str, [effectiveasof] + [r[1] for r in instruments]) conn.commit() def update_bbg_members(conn, session, curve_type, download_date): - if curve_type == CurveType.USD_SOFR_SWAPS: + if curve_type.value > 514: + # none of the ISDAs OIS curves are accessible in Bloomberg return data = retrieve_data( session, |