from sqlalchemy import Table, create_engine, MetaData from sqlalchemy.exc import IntegrityError, DataError, SAWarning from serenitas.utils.env import BASE_DIR import csv import datetime import logging import warnings logger = logging.getLogger(__name__) 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) ins = quotes.insert() def convert(x): if x: try: return float(x) except ValueError: logger.info("couldn't convert {0} to float".format(x)) else: return None def convert_int(x): if x: try: return int(float(x)) except ValueError: logger.info("couldn't convert {0} to int".format(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} def insert_quotes(year=2016, quote_dir=None): if not quote_dir: 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: reader = csv.DictReader(fh) data = [] 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) break except ValueError: logger.error("%s: Date in the wrong format", csvdict["Date"]) continue else: 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") # 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"]], } except KeyError: continue if csvdict["Ticker"] == "CDX-NAHY": trancheupfrontbid = convert(csvdict["Price Bid"]) trancheupfrontask = convert(csvdict["Price Ask"]) try: 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", } ) 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"]) try: trancheupfrontmid = (trancheupfrontbid + trancheupfrontask) / 2 except TypeError: trancheupfrontmid = None if series >= 25: running = 100 else: 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", } ) except KeyError: continue 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"]) try: trancheupfrontmid = ( trancheupfrontbid + trancheupfrontask ) / 2 except TypeError: trancheupfrontmid = None else: trancherunningbid = convert(csvdict["Bid"]) trancherunningask = convert(csvdict["Ask"]) try: trancherunningmid = ( trancherunningbid + trancherunningask ) / 2 except TypeError: trancherunning = None 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"]) try: trancheupfrontmid = ( trancheupfrontbid + trancheupfrontask ) / 2 except TypeError: trancheupfrontmid = None else: trancherunningbid = convert(csvdict["Bid"]) trancherunningask = convert(csvdict["Ask"]) try: trancherunningmid = ( trancherunningbid + trancherunningask ) / 2 except TypeError: trancherunning = 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": "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"]) try: trancheupfrontmid = ( trancheupfrontbid + trancheupfrontask ) / 2 except TypeError: trancheupfrontmid = None trancherunningbid = trancherunningask = trancherunningmid = 500 else: trancherunningbid = convert(csvdict["Bid"]) trancherunningask = convert(csvdict["Ask"]) try: 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", } ) data.append(d) with engine.connect() as conn: for i, l in enumerate(data): with conn.begin() as t: try: conn.execute(ins, l) except (IntegrityError, DataError) as e: logging.debug(e.orig) t.rollback() quotefile.unlink() if __name__ == "__main__": insert_quotes()