diff options
Diffstat (limited to 'python/insert_tranche_quotes.py')
| -rw-r--r-- | python/insert_tranche_quotes.py | 188 |
1 files changed, 0 insertions, 188 deletions
diff --git a/python/insert_tranche_quotes.py b/python/insert_tranche_quotes.py deleted file mode 100644 index 251d60bf..00000000 --- a/python/insert_tranche_quotes.py +++ /dev/null @@ -1,188 +0,0 @@ -from sqlalchemy import Table, create_engine, MetaData
-from sqlalchemy.exc import IntegrityError, DataError
-import os
-import csv
-import datetime
-import logging
-
-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):
- try:
- return float(x)
- except ValueError:
- 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=2015, 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:
- try:
- timestamp = datetime.datetime.strptime(csvdict['Date'], "%d %b %Y %H:%M")
- except ValueError:
- logging.error("%s: Date in the wrong format", csvdict['Date'])
- return
-
- attach = int(csvdict['Attach'])
- series = int(csvdict['Series'])
- version = int(csvdict['Version'])
- if csvdict['Ref']=='': #no delta quote maybe
- continue
-
- #common values to all indices
- try:
- d = {'quotedate' : timestamp,
- 'tranchedelta': convert(csvdict['Delta']),
- 'quotesource' : csvdict['Source'],
- 'series': series,
- 'version': version,
- 'attach': attach,
- 'detach': int(csvdict['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': float(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>=26:
- 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': int(float(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]:
- 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': int(float(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': int(float(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()
|