diff options
Diffstat (limited to 'python/task_server')
| -rw-r--r-- | python/task_server/insert_tranche_quotes.py | 230 |
1 files changed, 137 insertions, 93 deletions
diff --git a/python/task_server/insert_tranche_quotes.py b/python/task_server/insert_tranche_quotes.py index 7c3a257b..b4f3dfd1 100644 --- a/python/task_server/insert_tranche_quotes.py +++ b/python/task_server/insert_tranche_quotes.py @@ -8,13 +8,14 @@ import warnings logger = logging.getLogger(__name__) -engine = create_engine('postgresql://serenitas_user@debian/serenitasdb') +engine = create_engine("postgresql://serenitas_user@debian/serenitasdb") metadata = MetaData(bind=engine) with warnings.catch_warnings(): warnings.simplefilter("ignore", category=SAWarning) - quotes = Table('tranche_quotes', metadata, autoload=True) + quotes = Table("tranche_quotes", metadata, autoload=True) ins = quotes.insert() + def convert(x): if x: try: @@ -24,6 +25,7 @@ def convert(x): else: return None + def convert_int(x): if x: try: @@ -33,13 +35,15 @@ def convert_int(x): else: return None -tenordict = {'3': '3yr', '5': '5yr', '7':'7yr', '10':'10yr'} -runningdict1 = {0: 500, 3:100, 7:100, 15: 25} -runningdict2 = {0: 500, 3:500, 7:500, 10:100, 15:100, 30:100} + +tenordict = {"3": "3yr", "5": "5yr", "7": "7yr", "10": "10yr"} +runningdict1 = {0: 500, 3: 100, 7: 100, 15: 25} +runningdict2 = {0: 500, 3: 500, 7: 500, 10: 100, 15: 100, 30: 100} + def insert_quotes(year=2016, quote_dir=None): if not quote_dir: - quote_dir = BASE_DIR / 'Tranche_data' / 'Quotes' + quote_dir = BASE_DIR / "Tranche_data" / "Quotes" quotefiles = [f for f in quote_dir.iterdir() if f.stem == ".csv"] for quotefile in quotefiles: with quotefile.open() as fh: @@ -48,152 +52,192 @@ def insert_quotes(year=2016, quote_dir=None): for csvdict in reader: for p in ["%d %b %Y %H:%M", "%m/%d/%Y %H:%M"]: try: - timestamp = datetime.datetime.strptime(csvdict['Date'], p) + timestamp = datetime.datetime.strptime(csvdict["Date"], p) break except ValueError: - logger.error("%s: Date in the wrong format", csvdict['Date']) + logger.error("%s: Date in the wrong format", csvdict["Date"]) continue else: - logger.error("%s: Date in the wrong format", csvdict['Date']) + logger.error("%s: Date in the wrong format", csvdict["Date"]) - attach = int(csvdict['Attach']) - detach = int(csvdict['Detach']) - series = int(csvdict['Series']) - version = int(csvdict['Version']) - if csvdict['Ref']=='': #no delta quote maybe - logger.info('ref missing') + attach = int(csvdict["Attach"]) + detach = int(csvdict["Detach"]) + series = int(csvdict["Series"]) + version = int(csvdict["Version"]) + if csvdict["Ref"] == "": # no delta quote maybe + logger.info("ref missing") - #common values to all indices + # common values to all indices try: - d = {'quotedate' : timestamp, - 'tranchedelta': convert(csvdict['Delta']), - 'quotesource' : csvdict['Source'], - 'series': series, - 'version': version, - 'attach': attach, - 'detach': detach, - 'tenor': tenordict[csvdict['Tenor']] - } + d = { + "quotedate": timestamp, + "tranchedelta": convert(csvdict["Delta"]), + "quotesource": csvdict["Source"], + "series": series, + "version": version, + "attach": attach, + "detach": detach, + "tenor": tenordict[csvdict["Tenor"]], + } except KeyError: continue - if csvdict['Ticker'] == 'CDX-NAHY': - trancheupfrontbid = convert(csvdict['Price Bid']) - trancheupfrontask = convert(csvdict['Price Ask']) + if csvdict["Ticker"] == "CDX-NAHY": + trancheupfrontbid = convert(csvdict["Price Bid"]) + trancheupfrontask = convert(csvdict["Price Ask"]) try: - trancheupfrontmid = (trancheupfrontbid+trancheupfrontask)/2 + trancheupfrontmid = (trancheupfrontbid + trancheupfrontask) / 2 except TypeError: trancheupfrontmid = None - running = 0 if series in [9, 10] and attach==10 else 500 - d.update({'indexrefprice': convert(csvdict['Ref']), - 'indexrefspread': 375 if series==9 else 500, - 'trancheupfrontbid' : trancheupfrontbid, - 'trancheupfrontmid' : trancheupfrontmid, - 'trancheupfrontask' : trancheupfrontask, - 'trancherunningbid' : running, - 'trancherunningmid' : running, - 'trancherunningask' : running, - 'index': 'HY' - }) + running = 0 if series in [9, 10] and attach == 10 else 500 + d.update( + { + "indexrefprice": convert(csvdict["Ref"]), + "indexrefspread": 375 if series == 9 else 500, + "trancheupfrontbid": trancheupfrontbid, + "trancheupfrontmid": trancheupfrontmid, + "trancheupfrontask": trancheupfrontask, + "trancherunningbid": running, + "trancherunningmid": running, + "trancherunningask": running, + "index": "HY", + } + ) - elif csvdict['Ticker'] == 'CDX-NAIG': + elif csvdict["Ticker"] == "CDX-NAIG": if series >= 30: logger.info("series doesn't exist") continue - trancheupfrontbid = convert(csvdict['Upfront Bid']) - trancheupfrontask = convert(csvdict['Upfront Ask']) + trancheupfrontbid = convert(csvdict["Upfront Bid"]) + trancheupfrontask = convert(csvdict["Upfront Ask"]) try: - trancheupfrontmid = (trancheupfrontbid + trancheupfrontask)/2 + trancheupfrontmid = (trancheupfrontbid + trancheupfrontask) / 2 except TypeError: trancheupfrontmid = None if series >= 25: running = 100 else: - running = runningdict2[attach] if series<19 else runningdict1[attach] + running = ( + runningdict2[attach] + if series < 19 + else runningdict1[attach] + ) try: - d.update({'indexrefspread': convert_int(csvdict['Ref']), - 'trancheupfrontbid' : trancheupfrontbid, - 'trancheupfrontmid' : trancheupfrontmid, - 'trancheupfrontask' : trancheupfrontask, - 'trancherunningbid' : running, - 'trancherunningmid' : running, - 'trancherunningask' : running, - 'index' : 'IG'}) + d.update( + { + "indexrefspread": convert_int(csvdict["Ref"]), + "trancheupfrontbid": trancheupfrontbid, + "trancheupfrontmid": trancheupfrontmid, + "trancheupfrontask": trancheupfrontask, + "trancherunningbid": running, + "trancherunningmid": running, + "trancherunningask": running, + "index": "IG", + } + ) except KeyError: continue - elif csvdict['Ticker'] == 'ITRAXX-Europe': + elif csvdict["Ticker"] == "ITRAXX-Europe": if series <= 15: if attach <= 6: - trancherunningbid = trancherunningmid = trancherunningask = 500 if attach<=3 else 300 - trancheupfrontbid = convert(csvdict['Upfront Bid']) - trancheupfrontask = convert(csvdict['Upfront Ask']) + trancherunningbid = ( + trancherunningmid + ) = trancherunningask = (500 if attach <= 3 else 300) + trancheupfrontbid = convert(csvdict["Upfront Bid"]) + trancheupfrontask = convert(csvdict["Upfront Ask"]) try: - trancheupfrontmid = (trancheupfrontbid + trancheupfrontask)/2 + trancheupfrontmid = ( + trancheupfrontbid + trancheupfrontask + ) / 2 except TypeError: trancheupfrontmid = None else: - trancherunningbid = convert(csvdict['Bid']) - trancherunningask = convert(csvdict['Ask']) + trancherunningbid = convert(csvdict["Bid"]) + trancherunningask = convert(csvdict["Ask"]) try: - trancherunningmid = (trancherunningbid+trancherunningask)/2 + trancherunningmid = ( + trancherunningbid + trancherunningask + ) / 2 except TypeError: trancherunning = None - trancheupfrontbid = trancheupfrontmid = trancheupfrontask = 0 + trancheupfrontbid = ( + trancheupfrontmid + ) = trancheupfrontask = 0 if series in [19, 21, 22, 24, 26]: if attach <= 3: - trancherunningbid = trancherunningask = trancherunningmid = 500 if series==19 else 100 - trancheupfrontbid = convert(csvdict['Upfront Bid']) - trancheupfrontask = convert(csvdict['Upfront Ask']) + trancherunningbid = ( + trancherunningask + ) = trancherunningmid = (500 if series == 19 else 100) + trancheupfrontbid = convert(csvdict["Upfront Bid"]) + trancheupfrontask = convert(csvdict["Upfront Ask"]) try: - trancheupfrontmid = (trancheupfrontbid + trancheupfrontask)/2 + trancheupfrontmid = ( + trancheupfrontbid + trancheupfrontask + ) / 2 except TypeError: trancheupfrontmid = None else: - trancherunningbid = convert(csvdict['Bid']) - trancherunningask = convert(csvdict['Ask']) + trancherunningbid = convert(csvdict["Bid"]) + trancherunningask = convert(csvdict["Ask"]) try: - trancherunningmid = (trancherunningbid+trancherunningask)/2 + trancherunningmid = ( + trancherunningbid + trancherunningask + ) / 2 except TypeError: trancherunning = None - trancheupfrontbid = trancheupfrontmid = trancheupfrontask = 0 + trancheupfrontbid = ( + trancheupfrontmid + ) = trancheupfrontask = 0 - d.update({'indexrefspread': convert_int(csvdict['Ref']), - 'trancheupfrontbid' : trancheupfrontbid, - 'trancheupfrontmid' : trancheupfrontmid, - 'trancheupfrontask' : trancheupfrontask, - 'trancherunningbid' : trancherunningbid, - 'trancherunningmid' : trancherunningmid, - 'trancherunningask' : trancherunningask, - 'index' : 'EU'}) - elif csvdict['Ticker'] == 'ITRAXX-Xover': + d.update( + { + "indexrefspread": convert_int(csvdict["Ref"]), + "trancheupfrontbid": trancheupfrontbid, + "trancheupfrontmid": trancheupfrontmid, + "trancheupfrontask": trancheupfrontask, + "trancherunningbid": trancherunningbid, + "trancherunningmid": trancherunningmid, + "trancherunningask": trancherunningask, + "index": "EU", + } + ) + elif csvdict["Ticker"] == "ITRAXX-Xover": if attach < 35: ## the quote is sometimes in Price Bid, sometimes in Bid/Ask ## so we try both - trancheupfrontbid = convert(csvdict['Upfront Bid']) - trancheupfrontask = convert(csvdict['Upfront Ask']) + trancheupfrontbid = convert(csvdict["Upfront Bid"]) + trancheupfrontask = convert(csvdict["Upfront Ask"]) try: - trancheupfrontmid = (trancheupfrontbid + trancheupfrontask)/2 + trancheupfrontmid = ( + trancheupfrontbid + trancheupfrontask + ) / 2 except TypeError: trancheupfrontmid = None trancherunningbid = trancherunningask = trancherunningmid = 500 else: - trancherunningbid= convert(csvdict['Bid']) - trancherunningask= convert(csvdict['Ask']) + trancherunningbid = convert(csvdict["Bid"]) + trancherunningask = convert(csvdict["Ask"]) try: - trancherunningmid = (trancherunningbid + trancherunningask)/2 + trancherunningmid = ( + trancherunningbid + trancherunningask + ) / 2 except TypeError: trancherunningmid = None trancheupfrontbid = trancheupfrontmid = trancheupfrontask = 0 - d.update({'indexrefspread': convert_int(csvdict['Ref']), - 'trancheupfrontbid' : trancheupfrontbid, - 'trancheupfrontmid' : trancheupfrontmid, - 'trancheupfrontask' : trancheupfrontask, - 'trancherunningbid' : trancherunningbid, - 'trancherunningmid' : trancherunningmid, - 'trancherunningask' : trancherunningask, - 'index' : 'XO'}) + d.update( + { + "indexrefspread": convert_int(csvdict["Ref"]), + "trancheupfrontbid": trancheupfrontbid, + "trancheupfrontmid": trancheupfrontmid, + "trancheupfrontask": trancheupfrontask, + "trancherunningbid": trancherunningbid, + "trancherunningmid": trancherunningmid, + "trancherunningask": trancherunningask, + "index": "XO", + } + ) data.append(d) with engine.connect() as conn: for i, l in enumerate(data): |