aboutsummaryrefslogtreecommitdiffstats
path: root/python/insert_tranche_quotes.py
diff options
context:
space:
mode:
Diffstat (limited to 'python/insert_tranche_quotes.py')
-rw-r--r--python/insert_tranche_quotes.py200
1 files changed, 104 insertions, 96 deletions
diff --git a/python/insert_tranche_quotes.py b/python/insert_tranche_quotes.py
index e3325171..575136e0 100644
--- a/python/insert_tranche_quotes.py
+++ b/python/insert_tranche_quotes.py
@@ -10,9 +10,6 @@ metadata = MetaData(bind = engine)
quotes = Table('tranche_quotes', metadata, autoload = True)
ins = quotes.insert()
-root_dir = '/home/share/CorpCDOs'
-quotefiles = [f for f in os.listdir(os.path.join(root_dir, 'Tranche_data', 'Quotes')) if 'csv' in f]
-
def convert(x):
try:
return float(x)
@@ -23,109 +20,120 @@ 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}
-for quotefile in quotefiles:
- with open(os.path.join(root_dir, 'Tranche_data', 'Quotes', quotefile)) as fh:
- reader = csv.DictReader(fh)
- data = []
- for csvdict in reader:
- try:
- timestamp = datetime.datetime.strptime(csvdict['Date'], "%d %b %H:%M")
- timestamp = timestamp.replace(year=2014)
- except ValueError:
- pdb.set_trace()
-
- attach = int(csvdict['Attach'])
- series = int(csvdict['Series'])
- version = int(csvdict['Version'])
- if csvdict['Ref']=='': #no delta quote maybe
- continue
+def insert_quotes():
+ 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 'csv' in f]
+ 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 %H:%M")
+ timestamp = timestamp.replace(year=2014)
+ except ValueError:
+ pdb.set_trace()
- if csvdict['Ticker'] == 'CDX-NAHY':
- if csvdict['Price Bid']=='':
+ attach = int(csvdict['Attach'])
+ series = int(csvdict['Series'])
+ version = int(csvdict['Version'])
+ if csvdict['Ref']=='': #no delta quote maybe
continue
- if series==9:
- version = 20
- elif series==10:
- version = 19
-
+ #common values to all indices
d = {'quotedate' : timestamp,
- 'indexrefprice': float(csvdict['Ref']),
- 'indexrefspread': 375 if series==9 else 500,
- 'tranchedelta': convert(csvdict['Delta']),
- 'quotesource' : csvdict['Source'],
- 'trancheupfront' : (float(csvdict['Price Bid'])+float(csvdict['Price Ask']))/2,
- 'trancherunning' : 0 if series in [9, 10] and attach==10 else 500,
- 'tenor' : tenordict[csvdict['Tenor']],
- 'index' : 'HY',
- 'series': series,
- 'version': version,
- 'attach': attach,
- 'detach': int(csvdict['Detach'])
- }
- elif csvdict['Ticker'] == 'CDX-NAIG':
-
- if series>=22:
- continue
- try:
- trancheupfront = (float(csvdict['Upfront Bid'])+float(csvdict['Upfront Ask']))/2
- except ValueError:
- trancheupfront = None
- d = {'quotedate' : timestamp,
- 'indexrefspread': int(float(csvdict['Ref'])),
'tranchedelta': convert(csvdict['Delta']),
'quotesource' : csvdict['Source'],
- 'trancheupfront' : trancheupfront,
- 'trancherunning' : runningdict2[attach] if series<19 else runningdict1[attach],
- 'tenor' : tenordict[csvdict['Tenor']],
- 'index' : 'IG',
'series': series,
'version': version,
'attach': attach,
- 'detach': int(csvdict['Detach'])
+ 'detach': int(csvdict['Detach']),
+ 'tenor': tenordict[csvdict['Tenor']]
}
- elif csvdict['Ticker'] == 'ITRAXX-Europe':
- if series==9:
- if attach<=6:
- trancherunning = 500 if attach<=3 else 300
- trancheupfront = (float(csvdict['Upfront Bid'])+float(csvdict['Upfront Ask']))/2
- else:
- trancherunning=(float(csvdict['Bid'])+float(csvdict['Ask']))/2
- trancheupfront=0
- if series==19:
- if attach<=3:
- trancherunning = 500
- trancheupfront = (float(csvdict['Upfront Bid'])+float(csvdict['Upfront Ask']))/2
- else:
- trancherunning=(float(csvdict['Bid'])+float(csvdict['Ask']))/2
- trancheupfront = 0
- if series==21:
- if attach<=3:
- trancherunning =100
+
+ if csvdict['Ticker'] == 'CDX-NAHY':
+ if csvdict['Price Bid']=='':
+ continue
+
+ if series==9:
+ version = 20
+ elif series==10:
+ version = 19
+
+ d.update({'indexrefprice': float(csvdict['Ref']),
+ 'indexrefspread': 375 if series==9 else 500,
+ 'trancheupfront' : (float(csvdict['Price Bid'])+float(csvdict['Price Ask']))/2,
+ 'trancherunning' : 0 if series in [9, 10] and attach==10 else 500,
+ 'index': 'HY'
+ })
+
+ elif csvdict['Ticker'] == 'CDX-NAIG':
+
+ if series>=22:
+ continue
+ try:
trancheupfront = (float(csvdict['Upfront Bid'])+float(csvdict['Upfront Ask']))/2
- else:
- trancherunning=(float(csvdict['Bid'])+float(csvdict['Ask']))/2
- trancheupfront = 0
+ except ValueError:
+ trancheupfront = None
+ d.update({
+ 'indexrefspread': int(float(csvdict['Ref'])),
+ 'trancheupfront' : trancheupfront,
+ 'trancherunning' : runningdict2[attach] if series<19 else runningdict1[attach],
+ 'index' : 'IG'})
- d = {'quotedate' : timestamp,
- 'indexrefspread': int(float(csvdict['Ref'])),
- 'tranchedelta': convert(csvdict['Delta']),
- 'quotesource' : csvdict['Source'],
- 'trancheupfront' : trancheupfront,
- 'trancherunning' : trancherunning,
- 'tenor' : tenordict[csvdict['Tenor']],
- 'index' : 'EU',
- 'series': series,
- 'version': version,
- 'attach': attach,
- 'detach': int(csvdict['Detach'])
- }
- data.append(d)
+ elif csvdict['Ticker'] == 'ITRAXX-Europe':
+ if series==9:
+ if attach<=6:
+ trancherunning = 500 if attach<=3 else 300
+ try:
+ trancheupfront = (float(csvdict['Upfront Bid'])+float(csvdict['Upfront Ask']))/2
+ except ValueError:
+ trancheupfront = None
+ else:
+ try:
+ trancherunning=(float(csvdict['Bid'])+float(csvdict['Ask']))/2
+ except ValueError:
+ trancherunning = None
+ trancheupfront=0
+ if series==19:
+ if attach<=3:
+ trancherunning = 500
+ trancheupfront = (float(csvdict['Upfront Bid'])+float(csvdict['Upfront Ask']))/2
+ else:
+ try:
+ trancherunning=(float(csvdict['Bid'])+float(csvdict['Ask']))/2
+ except ValueError:
+ trancherunning=(float(csvdict['Upfront Bid'])+float(csvdict['Upfront Ask']))/2
+ trancheupfront = 0
+ if series==21:
+ if attach<=3:
+ trancherunning =100
+ try:
+ trancheupfront = (float(csvdict['Upfront Bid'])+float(csvdict['Upfront Ask']))/2
+ except ValueError:
+ trancheupfront = None
+ else:
+ trancherunning=(float(csvdict['Bid'])+float(csvdict['Ask']))/2
+ trancheupfront = 0
- with engine.begin() as conn:
- for l in data:
- with conn.begin() as t:
- try:
- conn.execute(ins, l)
- except IntegrityError:
- t.rollback()
+ d.update({'indexrefspread': int(float(csvdict['Ref'])),
+ 'trancheupfront' : trancheupfront,
+ 'trancherunning' : trancherunning,
+ 'index' : 'EU',
+ })
+ 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 as e:
+ print(e.orig)
+ t.rollback()
+
+ os.unlink(os.path.join(quote_dir, quotefile))
+
+if __name__=="__main__":
+ insert_quotes()
generated by cgit v1.2.3-70-g09d2 (git 2.52.0) at 2025-12-24 18:37:37 +0000