from sqlalchemy import Table, create_engine, MetaData from sqlalchemy.exc import IntegrityError, DataError import os import csv import datetime import logging logger = logging.getLogger(__name__) engine = create_engine('postgresql://serenitas_user@debian/serenitasdb') metadata = MetaData(bind = engine) 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: root_dir = '/home/share/CorpCDOs' quote_dir = os.path.join(root_dir, 'Tranche_data', 'Quotes') quotefiles = [f for f in os.listdir(quote_dir) if f.endswith('csv')] for quotefile in quotefiles: with open(os.path.join(quote_dir, quotefile)) 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>=28: 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() os.unlink(os.path.join(quote_dir, quotefile)) if __name__=="__main__": insert_quotes()