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
|
from sqlalchemy import Table, create_engine, MetaData
import os
import csv
import datetime
import pdb
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'
quotefiles = [f for f in os.listdir(os.path.join(root_dir, 'Scenarios', 'Calibration')) if 'tranches' in f]
K = [0, 15, 25, 35, 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(2014, 6, 6) or series!=19:
continue
if quotedate <= datetime.date(2014, 5, 21):
version=1
else:
version=2
reader = csv.DictReader(fh)
data = []
for i, csvdict in enumerate(reader):
timestamp = datetime.datetime.strptime(csvdict['bidTime'], "%m/%d/%Y %H:%M:%S %p")
d = {'quotedate' : timestamp,
'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]
}
data.append(d)
with engine.begin() as conn:
conn.execute(ins, data)
|
