diff options
Diffstat (limited to 'python/task_server/insert_tranche_quotes.py')
| -rw-r--r-- | python/task_server/insert_tranche_quotes.py | 413 |
1 files changed, 207 insertions, 206 deletions
diff --git a/python/task_server/insert_tranche_quotes.py b/python/task_server/insert_tranche_quotes.py index f16d76c6..5ece93f1 100644 --- a/python/task_server/insert_tranche_quotes.py +++ b/python/task_server/insert_tranche_quotes.py @@ -1,206 +1,207 @@ -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:
- quote_dir = os.path.join(os.environ['BASE_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()
+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: + quote_dir = os.path.join(os.environ['BASE_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 >= 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() + + os.unlink(os.path.join(quote_dir, quotefile)) + +if __name__=="__main__": + insert_quotes() |