1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
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)
|
