1 files changed, 51 insertions, 0 deletions
diff --git a/python/insert_composite_quotes.py b/python/insert_composite_quotes.py
new file mode 100644
index 00000000..027cd42e
--- /dev/null
+++ b/python/insert_composite_quotes.py
@@ -0,0 +1,51 @@
+from sqlalchemy import Table, create_engine, MetaData
+import os
+import csv
+import datetime
+
+##script to load quotes from markit composite quotes csv file
+engine = create_engine('postgresql://mlpdb_user:Serenitas1@debian/mlpdb')
+metadata = MetaData(bind = engine)
+quotes = Table('tranche_quotes', metadata, autoload = True)
+ins = quotes.insert()
+
+root_dir = '/home/share/CorpCDOs'
+
+def convert(x):
+    try:
+        return float(x)
+    except:
+        return None
+
+tenordict = {'3Y': '3yr', '5Y': '5yr', '7Y':'7yr', '10Y':'10yr'}
+
+with open(os.path.join(root_dir, 'Tranche_data', 'CDXNAIG Tranches.csv')) as fh:
+    reader = csv.DictReader(fh)
+    data = []
+    for csvdict in reader:
+        timestamp =  datetime.datetime.strptime(csvdict['Date'], "%m/%d/%Y")
+        if timestamp==datetime.datetime(2010, 7, 5):
+            continue
+        attach = int(float(csvdict['Attachment'])*100)
+        detach = int(float(csvdict['Detachment'])*100)
+        series = int(csvdict['Index Series'])
+        version = int(csvdict['Index Version'])
+
+        d = {'quotedate' : timestamp,
+             'indexrefprice': float(csvdict['Index Price Mid'])*100,
+             'indexrefspread': 80 if series==9 else 100,
+             'quotesource' : 'MKIT',
+             'trancheupfront' : convert(csvdict['Tranche Upfront Mid']),
+             'trancherunning' : convert(csvdict['Tranche Spread Mid']),
+             'tenor' : tenordict[csvdict['Index Term']],
+             'index' : 'IG',
+             'series': series,
+             'version': version,
+             'attach': attach,
+             'detach': detach,
+             'corratdetachment': convert(csvdict['Base Correlation'])
+             }
+        data.append(d)
+
+with engine.begin() as conn:
+    conn.execute(ins, data)