diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/insert_tranche_quotes.py | 141 |
1 files changed, 113 insertions, 28 deletions
diff --git a/python/insert_tranche_quotes.py b/python/insert_tranche_quotes.py index 5166b89b..9e22a73b 100644 --- a/python/insert_tranche_quotes.py +++ b/python/insert_tranche_quotes.py @@ -1,7 +1,9 @@ from sqlalchemy import Table, create_engine, MetaData
+from sqlalchemy.exc import IntegrityError
import os
import csv
import datetime
+import pdb
engine = create_engine('postgresql://mlpdb_user:Serenitas1@debian/mlpdb')
metadata = MetaData(bind = engine)
@@ -9,37 +11,120 @@ 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, 'Scenarios', 'Calibration')) if 'tranches' in f]
-K = [0, 15, 25, 35, 100]
+quotefiles = [f for f in os.listdir(os.path.join(root_dir, 'Tranche_data', 'Quotes')) if '2' in f]
+
+def convert(x):
+ try:
+ return float(x)
+ except:
+ 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}
for quotefile in quotefiles:
- with open(os.path.join(root_dir, 'Scenarios', 'Calibration', quotefile)) as fh:
- quotedate = datetime.datetime.strptime(os.path.splitext(quotefile)[0].split("_")[-1], "%Y-%m-%d").date()
- series = 19 if "19" in quotefile else 21
- index = os.path.splitext(quotefile)[0].split("_")
- if quotedate < datetime.date(2013, 9, 23):
- continue
- if quotedate <= datetime.date(2014, 5, 21):
- version=1
- else:
- version=2
+ with open(os.path.join(root_dir, 'Tranche_data', 'Quotes', quotefile)) as fh:
reader = csv.DictReader(fh)
data = []
- for i, csvdict in enumerate(reader):
- d = {'quotedate' : datetime.datetime.strptime(csvdict['bidTime'], "%m/%d/%Y %I:%M:%S %p"),
- 'indexrefprice': csvdict['bidRefPrice'],
- 'indexrefspread': 500,
- 'tranchedelta': csvdict['bidDelta'],
- 'quotesource' : csvdict['AskContributorCode'],
- 'trancheupfront' : csvdict['Mid'],
- 'trancherunning' : float(csvdict['Coupon']) * 10000 if 'Coupon' in csvdict else 500,
- 'tenor' : '5yr',
- 'index' : 'HY',
- 'series': series,
- 'version': version,
- 'attach': K[i],
- 'detach': K[i+1]
- }
+ 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['Ticker'] == 'CDX-NAHY':
+ if csvdict['Price Bid']=='':
+ continue
+
+ if series==9:
+ version = 20
+ elif series==10:
+ version = 19
+
+ 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 csvdict['Ref']=='': #no delta quote maybe
+ continue
+ if series>=22:
+ continue
+ try:
+ trancheupfront = (float(csvdict['Upfront Bid'])+float(csvdict['Upfront Ask']))/2
+ except ValueError:
+ continue
+ d = {'quotedate' : timestamp,
+ 'indexrefspread': int(float(csvdict['Ref'])),
+ 'tranchedelta': convert(csvdict['Delta']),
+ 'quotesource' : csvdict['Source'],
+ 'trancheupfront' : trancheupfront,
+ 'trancherunning' : runningdict2[attach] if series==9 else runningdict1[attach],
+ 'tenor' : tenordict[csvdict['Tenor']],
+ 'index' : 'IG',
+ 'series': series,
+ 'version': version,
+ 'attach': attach,
+ 'detach': int(csvdict['Detach'])
+ }
+ 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
+ trancheupfront = (float(csvdict['Upfront Bid'])+float(csvdict['Upfront Ask']))/2
+ else:
+ trancherunning=(float(csvdict['Bid'])+float(csvdict['Ask']))/2
+ trancheupfront = 0
+
+ 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)
+
with engine.begin() as conn:
- conn.execute(ins, data)
+ for l in data:
+ with conn.begin() as t:
+ try:
+ conn.execute(ins, l)
+ except IntegrityError:
+ t.rollback()
|