diff options
| -rw-r--r-- | python/markit/rates.py | 99 | ||||
| -rw-r--r-- | sql/serenitasdb.sql | 31 |
2 files changed, 127 insertions, 3 deletions
diff --git a/python/markit/rates.py b/python/markit/rates.py index 9aa8c513..8c8e54d7 100644 --- a/python/markit/rates.py +++ b/python/markit/rates.py @@ -2,10 +2,12 @@ import datetime from serenitas.utils.env import DATA_DIR from io import BytesIO from psycopg2 import sql +from psycopg2.extras import DateRange import requests import xml.etree.ElementTree as ET import zipfile from serenitas.analytics.yieldcurve import YC, ql_to_jp +from serenitas.analytics.bbg_helpers import retrieve_data def downloadMarkitIRData(conn, download_date=datetime.date.today(), currency="USD"): @@ -60,3 +62,100 @@ def downloadMarkitIRData(conn, download_date=datetime.date.today(), currency="US insert_str, [MarkitData["effectiveasof"]] + [r[1] for r in instruments] ) conn.commit() + + +def update_bbg_members(conn, session, curve_type, download_date): + data = retrieve_data( + session, + [curve_type.bbg_name], + ["CURVE_MEMBERS"], + overrides={"CURVE_DATE": download_date}, + ) + new_members = data[curve_type.bbg_name]["CURVE_MEMBERS"]["Curve Members"].to_list() + with conn.cursor() as c: + c.execute( + "SELECT members, in_effect FROM bbg_curves WHERE curve_type=%s AND in_effect @> %s", + (curve_type.value, download_date), + ) + try: + (members, in_effect) = c.fetchone() + except TypeError: + with conn.cursor() as c: + c.execute( + "INSERT INTO bbg_curves VALUES(%s, %s, %s)", + (curve_type.value, new_members, DateRange(download_date)), + ) + conn.commit() + return + if new_members != members: + with conn.cursor() as c: + c.execute( + "UPDATE bbg_curves SET in_effect=%s " + "WHERE curve_type=%s AND in_effect @> %s", + ( + DateRange(in_effect.lower, download_date), + curve_type.value, + download_date, + ), + ) + c.execute( + "INSERT INTO bbg_curves VALUES(%s, %s, %s)", + (curve_type.value, new_members, DateRange(download_date)), + ) + conn.commit() + + +def update_bbg_desc(conn, session, download_date): + with conn.cursor() as c: + c.execute( + "SELECT bbg_ticker FROM (" + " SELECT unnest(members) AS bbg_ticker FROM bbg_curves " + " WHERE in_effect @> %s) a " + "LEFT JOIN bbg_rate_tickers USING (bbg_ticker) " + "WHERE quote_type IS null", + (download_date,), + ) + missing_tickers = [t for (t,) in c] + fields = [ + "SECURITY_TENOR_ONE", + "SECURITY_TENOR_TWO", + "SECURITY_TYP2", + "INT_RATE_FUT_START_DT", + "INT_RATE_FUT_END_DT", + "CONVEXITY_BIAS_BASIS_POINTS", + "CRNCY", + ] + data = retrieve_data(session, missing_tickers, fields) + with conn.cursor() as c: + for k, v in data.items(): + if v["SECURITY_TYP2"] == "Future": + params = (k, "FUT", v["CRNCY"], None, v["INT_RATE_FUT_START_DT"].date()) + elif v["SECURITY_TYP2"] == "FIXED_FLOAT": + params = (k, "SWP", v["CRNCY"], v["SECURITY_TENOR_TWO"], None) + elif v["SECURITY_TYP2"] == "FIXED_FLOAT_OIS": + params = (k, "OIS", v["CRNCY"], v["SECURITY_TENOR_TWO"], None) + elif v["SECURITY_TYP2"] == "DEPOSIT": + params = (k, "DEP", v["CRNCY"], v["SECURITY_TENOR_ONE"], None) + elif v["SECURITY_TYP2"] == "Index": + params = (k, "IND", v["CRNCY"], None, None) + elif v["SECURITY_TYP2"] == "BASIS": + params = (k, "BASIS", v["CRNCY"], v["SECURITY_TENOR_TWO"], None) + else: + raise ValueError("Unkown security type") + c.execute("INSERT INTO bbg_rate_tickers VALUES(%s, %s, %s, %s, %s)", params) + conn.commit() + + +def get_bbg_quotes(conn, session, start_from): + fields = ["PX_LAST"] + with conn.cursor() as c: + c.execute("SELECT unnest(members) AS bbg_ticker FROM bbg_curves") + tickers = set(t for (t,) in c) + data = retrieve_data(session, tickers, fields, start_date=start_from) + with conn.cursor() as c: + for k, v in data.items(): + c.executemany( + "INSERT INTO bbg_rate_quotes(bbg_ticker, date, quote1) VALUES(%s, %s, %s)", + [(k, *t) for t in v.itertuples()], + ) + conn.commit() diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index 2ed20248..01b9ad9b 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -938,11 +938,36 @@ CREATE TABLE JPY_curves( effective_date date PRIMARY KEY,
curve bytea);
+CREATE TYPE rate_type AS ENUM('OIS', 'FUT', 'SWP', 'DEP', 'IND', 'BASIS');
+
+CREATE TABLE bbg_rate_tickers(
+ bbg_ticker text PRIMARY KEY,
+ quote_type rate_type NOT NULL,
+ currency curr NOT NULL,
+ tenor text,
+ start_date date
+);
+
+CREATE TABLE bbg_rate_quotes(
+ date date,
+ bbg_ticker text REFERENCES bbg_rate_tickers,
+ quote1 double,
+ quote2 double,
+ PRIMARY KEY (date, bbg_ticker)
+);
+
+-- require the btree_gist extension
+CREATE TABLE bbg_curves(
+ curve_type smallint,
+ members text[] NOT NULL,
+ in_effect daterange,
+ EXCLUDE USING gist (curve_type WITH =, in_effect WITH &&)
+);
+
CREATE TABLE bbg_curves(
- curve_date date,
curve_type smallint,
- quotes jsonb,
- PRIMARY KEY (curve_date, curve_type)
+ members text[] NOT NULL,
+ in_effect daterange
);
CREATE TABLE cds_curves(
|