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
52
53
54
55
56
57
|
import os
from common import root
import csv
import datetime
from db import connmlpdb
import pdb
import re
workdate= datetime.date(2014, 7, 3)
def convert(x):
try:
return float(x[:-1])
except ValueError:
return None
sqlstr = "SELECT markit_ticker, markit_tier, cds_curve from index_members(%s, %s)"
d = {}
d2 = set([])
for index in ['HY9', 'HY10', 'HY15', 'HY17', 'HY19', 'IG9', 'IG19', 'IG21']:
spread=0.05 if 'HY' in index else 0.01
with connmlpdb.cursor() as c:
c.execute(sqlstr, (index, workdate))
for line in c:
d2.add((line['markit_ticker'], line['markit_tier']))
key = (line['markit_ticker'], line['markit_tier'], 'USD', 'XR', spread)
hykey = key[:-1]+(0.05,)
if hykey in d:
del d[hykey] ## we only keep the tightest quote
d[key] = line['cds_curve']
colnames = ['Upfront'+tenor for tenor in ['6m', '1y', '2y', '3y', '4y', '5y', '7y', '10y']]
sqlstr = "INSERT INTO cds_quotes(date, curve_ticker, upfrontbid, upfrontask," \
"runningbid, runningask, source, recovery) VALUES(%s, %s, %s, %s, %s, %s, %s, %s)"
filelist = [f for f in os.listdir(os.path.join(root, "Tranche_data", "CDS")) if f.endswith('csv')]
for f in filelist:
d3 = set([])
workdate = datetime.datetime.strptime(re.match("[^\d]*(\d*)", f).groups()[0], "%Y%m%d")
with connmlpdb.cursor() as c:
c.execute("DELETE from cds_quotes where date=%s", (workdate,))
with open(os.path.join(root, "Tranche_data", "CDS", f)) as fh:
csvreader = csv.DictReader(fh)
with connmlpdb.cursor() as c:
for line in csvreader:
d3.add((line['Ticker'], line['Tier']))
k = (line['Ticker'], line['Tier'], line['Ccy'], line['DocClause'], float(line['RunningCoupon']))
if k in d:
c.executemany(sqlstr,
[(workdate, t, convert(line[colnames[i]]), convert(line[colnames[i]]),
float(line['RunningCoupon'])*10000, float(line['RunningCoupon'])*10000, 'MKIT',
convert(line['RealRecovery'])/100) for i, t in enumerate(d[k])])
connmlpdb.commit()
print(workdate)
print(d2-d3)
|
