diff options
| -rw-r--r-- | python/markit_tranche_quotes.py | 143 |
1 files changed, 85 insertions, 58 deletions
diff --git a/python/markit_tranche_quotes.py b/python/markit_tranche_quotes.py index febab2fd..9ad756c8 100644 --- a/python/markit_tranche_quotes.py +++ b/python/markit_tranche_quotes.py @@ -5,23 +5,26 @@ import pytz import requests from utils.db import dbconn -params = {"username": "serenitasreports", - "password": "_m@rk1t_", - "reportUri": "/MarkitQuotes/CLIENTS/AllTrancheQuotes", - "exportType": "csv", - "START_TIME": "11/13/2017"} +params = { + "username": "serenitasreports", + "password": "_m@rk1t_", + "reportUri": "/MarkitQuotes/CLIENTS/AllTrancheQuotes", + "exportType": "csv", + "START_TIME": "11/13/2017", +} -r = requests.post("https://quotes.markit.com/reports/runReport", - params=params) +r = requests.post("https://quotes.markit.com/reports/runReport", params=params) f = io.StringIO(r.text) with open("quotes.csv", "w") as fh: fh.write(r.text) -index_mapping = {'ITRAXX-Europe': 'EU', - 'ITRAXX-Xover': 'XO', - 'CDX-NAIG': 'IG', - 'CDX-NAHY': 'HY'} +index_mapping = { + "ITRAXX-Europe": "EU", + "ITRAXX-Xover": "XO", + "CDX-NAIG": "IG", + "CDX-NAHY": "HY", +} sql_str = f"""INSERT INTO tranche_quotes(quotedate, index, series, version, tenor, attach, detach, trancheupfrontbid, trancheupfrontmid, trancheupfrontask, @@ -29,16 +32,19 @@ sql_str = f"""INSERT INTO tranche_quotes(quotedate, index, series, version, teno indexrefprice, indexrefspread, tranchedelta, quotesource, markit_id) VALUES({",".join(["%s"]*18)}) ON CONFLICT DO NOTHING""" + def get_latest_quote_id(db): with db.cursor() as c: c.execute("SELECT max(markit_id) FROM tranche_quotes") markit_id, = c.fetchone() return markit_id + def convert_float(s): return float(s) if s else None -serenitasdb = dbconn('serenitasdb') + +serenitasdb = dbconn("serenitasdb") runningdict1 = {0: 500, 3: 100, 7: 100, 15: 25} runningdict2 = {0: 500, 3: 500, 7: 500, 10: 100, 15: 100, 30: 100} @@ -47,60 +53,81 @@ markit_id = get_latest_quote_id(serenitasdb) headers = [h.lower() for h in next(f).strip().split(",")] count = 0 for d in csv.DictReader(f, fieldnames=headers): - d['quotedate'] = datetime.datetime.strptime(d['time'], "%m/%d/%Y %H:%M:%S") - d['quotedate'] = d['quotedate'].replace(tzinfo=pytz.UTC) - d['index'] = index_mapping[d['ticker']] - d['tenor'] = d['tenor'] + 'yr' - for k1 in ['upfront', 'spread', 'price']: - for k2 in ['_bid', '_ask']: + d["quotedate"] = datetime.datetime.strptime(d["time"], "%m/%d/%Y %H:%M:%S") + d["quotedate"] = d["quotedate"].replace(tzinfo=pytz.UTC) + d["index"] = index_mapping[d["ticker"]] + d["tenor"] = d["tenor"] + "yr" + for k1 in ["upfront", "spread", "price"]: + for k2 in ["_bid", "_ask"]: d[k1 + k2] = convert_float(d[k1 + k2]) - for k in ['upfront', 'spread', 'price']: - d[k+'_mid'] = 0.5 * (d[k+'_bid'] + d[k+'_ask']) \ - if d[k + '_bid'] and d[k + '_ask'] else None - d['series'] = int(d['series']) - d['attachment'], d['detachment'] = int(d['attachment']), int(d['detachment']) - if d['ticker'] == 'CDX-NAHY': - d['indexrefprice'] = convert_float(d['reference']) + for k in ["upfront", "spread", "price"]: + d[k + "_mid"] = ( + 0.5 * (d[k + "_bid"] + d[k + "_ask"]) + if d[k + "_bid"] and d[k + "_ask"] + else None + ) + d["series"] = int(d["series"]) + d["attachment"], d["detachment"] = int(d["attachment"]), int(d["detachment"]) + if d["ticker"] == "CDX-NAHY": + d["indexrefprice"] = convert_float(d["reference"]) else: - d['indexrefspread'] = convert_float(d['reference']) - if d['ticker'] == 'CDX-NAHY': - for k in ['_bid', '_mid', '_ask']: - d['upfront' + k] = d['price' + k] - d['spread' + k] = 0 if d['series'] in [9, 10] and d['attachment'] == 10 else 500 + d["indexrefspread"] = convert_float(d["reference"]) + if d["ticker"] == "CDX-NAHY": + for k in ["_bid", "_mid", "_ask"]: + d["upfront" + k] = d["price" + k] + d["spread" + k] = ( + 0 if d["series"] in [9, 10] and d["attachment"] == 10 else 500 + ) - if d['ticker'] == 'ITRAXX-Xover': - if int(d['attachment']) < 35: - for k in ['_bid', '_mid', '_ask']: + if d["ticker"] == "ITRAXX-Xover": + if int(d["attachment"]) < 35: + for k in ["_bid", "_mid", "_ask"]: ##d[f'upfront{k}'] = d[f'spread{k}'] - d['spread' + k] = 500 - if d['ticker'] == 'ITRAXX-Europe': - if d['attachment'] <= 3: - for k in ['_bid', '_mid', '_ask']: - #d[f'upfront{k}'] = d[f'spread{k}'] - d['spread' + k] = 500 if d['series'] == 19 else 100 - if d['ticker'] == 'CDX-NAIG': - for k in ['Bid', 'Mid', 'Ask']: - #d[f'upfront{k}'] = d[f'spread{k}'] - if d['series'] < 19: - running = runningdict2[d['attachment']] - elif d['series'] < 25: - running = runningdict1[d['attachment']] + d["spread" + k] = 500 + if d["ticker"] == "ITRAXX-Europe": + if d["attachment"] <= 3: + for k in ["_bid", "_mid", "_ask"]: + # d[f'upfront{k}'] = d[f'spread{k}'] + d["spread" + k] = 500 if d["series"] == 19 else 100 + if d["ticker"] == "CDX-NAIG": + for k in ["Bid", "Mid", "Ask"]: + # d[f'upfront{k}'] = d[f'spread{k}'] + if d["series"] < 19: + running = runningdict2[d["attachment"]] + elif d["series"] < 25: + running = runningdict1[d["attachment"]] else: running = 100 - for k in ['_bid', '_mid', '_ask']: - d['spread' + k] = running - + for k in ["_bid", "_mid", "_ask"]: + d["spread" + k] = running - d['delta'] = convert_float(d['delta']) + d["delta"] = convert_float(d["delta"]) with serenitasdb.cursor() as c: - if d['contributor'] == 'BROWNSTONE': - d['contributor'] = 'BIG' - c.execute(sql_str, (d['quotedate'], d['index'], d['series'], d['version'], d['tenor'], - d['attachment'], d['detachment'], - d['upfront_bid'], d.get('upfront_mid'), d['upfront_ask'], - d['spread_bid'], d.get('spread_mid'), d['spread_ask'], - d.get('indexrefprice'), d.get('indexrefspread'), d['delta'], - d['contributor'][:4], d['quote_id'])) + if d["contributor"] == "BROWNSTONE": + d["contributor"] = "BIG" + c.execute( + sql_str, + ( + d["quotedate"], + d["index"], + d["series"], + d["version"], + d["tenor"], + d["attachment"], + d["detachment"], + d["upfront_bid"], + d.get("upfront_mid"), + d["upfront_ask"], + d["spread_bid"], + d.get("spread_mid"), + d["spread_ask"], + d.get("indexrefprice"), + d.get("indexrefspread"), + d["delta"], + d["contributor"][:4], + d["quote_id"], + ), + ) count += 1 serenitasdb.commit() print(f"loaded {count} new quotes") |