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
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
|
import csv
from lxml import etree
import requests, io, zipfile, shutil
import os
from db import with_connection
def request_payload(payload):
r = requests.get('https://www.markit.com/export.jsp', params=payload)
res = []
with zipfile.ZipFile(io.BytesIO(r.content)) as z:
for f in z.namelist():
if f.endswith("xml"):
z.extract(f, path=os.path.join(os.environ['BASE_DIR'], "Tranche_data", "RED_reports"))
res.append(f)
return res
def download_report(report):
version = 10 if 'Entity' in report else 9
payload = {'user': 'GuillaumeHorel',
'password': 'password',
'version': version,
'report': report}
r = []
if report in ['CredIndexAnnex', 'CredIndexAnnexSplit']:
for family in ['CDX', 'ITRAXX-EUROPE']:
payload.update({'family': family})
r += request_payload(payload)
else:
r += request_payload(payload)
return r
@with_connection('serenitasdb')
def update_redcodes(conn, fname):
with open(os.path.join(os.environ['BASE_DIR'], "Tranche_data", "RED_reports", fname)) as fh:
et = etree.parse(fh)
r = []
for indextype in ['HY', 'IG', 'EU', 'XO']:
if indextype in ['HY', 'IG']:
subfamily = 'CDX.NA.{0}'.format(indextype)
elif indextype in ['EU']:
subfamily = 'iTraxx Europe'
elif indextype in ['XO']:
subfamily = 'iTraxx Europe Crossover'
for index in et.xpath("./index/indexsubfamily[text()='{0}']/..".format(subfamily)):
r.append([indextype] + [index.find(tag).text for tag in \
['series', 'version', 'redindexcode', 'indexfactor']])
sqlstr = """UPDATE index_version SET redindexcode=%s
where index=%s and series=%s and version=%s and abs(indexfactor-%s)<0.001"""
with conn.cursor() as c:
c.executemany(sqlstr, [tuple([e[3], e[0], e[1], e[2], float(e[4])*100]) for e in r])
conn.commit()
def update_redindices(fname):
basedir = os.path.join(os.environ['BASE_DIR'], "Tranche_Data", "RED_reports")
with open(os.path.join(basedir, fname)) as fh:
e = etree.parse(fh)
root = e.getroot()
headers = ['referenceentity', 'redentitycode', 'role', 'redpaircode', 'jurisdiction',
'tier','pairiscurrent', 'pairvalidto', 'pairvalidfrom', 'ticker',
'ispreferred', 'isdatransactiontype', 'docclause','recorddate',
'publiccomments','weight']
for c in root.findall('index'):
names = [c.find(tag).text for tag in ['indexsubfamily', 'series', 'version']]
with open( os.path.join(basedir, "{0}.{1}.V{2}.csv".format(*names)), "w") as fh2:
csvwriter = csv.DictWriter(fh2, fieldnames=headers)
csvwriter.writeheader()
data = []
for constituent in c.findall('.//originalconstituent'):
data.append({l.tag: l.text for l in constituent})
data = sorted(data, key=lambda x: x['referenceentity'])
csvwriter.writerows(data)
if __name__=="__main__":
report_list = ['REDEntity', 'REDObligation', 'REDEntityDelta', 'REDObligationDelta',
'REDObligationPreferred', 'CredIndexAnnex', 'CredIndexAnnexSplit',
'REDIndexCodes']
fname = download_report("REDIndexCodes")
update_redcodes(fname[0])
f1, f2 = download_report("CredIndexAnnex")
update_redindices(f1)
update_redindices(f2)
|
