1 files changed, 64 insertions, 34 deletions
diff --git a/python/markit_red.py b/python/markit_red.py
index ff01b622..c2461b9a 100644
--- a/python/markit_red.py
+++ b/python/markit_red.py
@@ -3,45 +3,75 @@ from lxml import etree
 import requests, io, zipfile, shutil
 from common import root
 import os
+from db import serenitasdb
 
-user = 'GuillaumeHorel'
-password = 'password'
-
-
-for report in ['REDEntity', 'REDObligation', 'REDEntityDelta', 'REDObligationDelta',
-               'REDObligationPreferred', 'CredIndexAnnex', 'CredIndexAnnexSplit',
-               'REDIndexCodes']:
-    payload = {'user': user,
-               'password': password,
-               'report':report,
-               'version':9}
-    if report in ['CredIndexAnnex', 'CredIndexAnnexSplit']:
-        #payload['family']='CDX'
-        payload['family'] = 'ITRAXX-EUROPE'
+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 "xml" in f:
+            if f.endswith("xml"):
                 z.extract(f, path=os.path.join(root, "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
+
+def update_redcodes(fname):
+    with open(os.path.join(root, "Tranche_data", "RED_reports", fname)) as fh:
+        et = etree.parse(fh)
+    r = []
+    for indextype in ['HY', 'IG', 'EU']:
+        if indextype in ['HY', 'IG']:
+            subfamily = 'CDX.NA.{0}'.format(indextype)
+        else:
+            subfamily = 'iTraxx Europe'
+        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 indexfactor=%s"""
+    with serenitasdb.cursor() as c:
+        c.executemany(sqlstr, [tuple([e[3], e[0], e[1], e[2], float(e[4])*100]) for e in r])
+    serenitasdb.commit()
 
-basedir = os.path.join(root, "Tranche_Data", "Red")
-fh = open(os.path.join(root, "Tranche_Data", "Red_reports", "V9 Red Indices1416860143432.xml"))
-e = etree.parse(fh)
-root = e.getroot()
+def update_redindices(fname):
+    basedir = os.path.join(root, "Tranche_Data", "Red")
+    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) 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)
 
-for c in root.findall('index'):
-    with open( os.path.join(basedir, "{0}.{1}.V{2}.csv".format(c.find('indexsubfamily').text,
-                                                               c.find('series').text,
-                                                               c.find('version').text)), "w") as fh2:
-        headers = ['referenceentity', 'redentitycode', 'role', 'redpaircode', 'jurisdiction',
-                   'tier','pairiscurrent', 'pairvalidto', 'pairvalidfrom', 'ticker',
-                   'ispreferred', 'isdatransactiontype', 'docclause','recorddate','publiccomments','weight']
-        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)
-fh.close()
+if __name__=="__main__":
+    report_list = ['REDEntity', 'REDObligation', 'REDEntityDelta', 'REDObligationDelta',
+                   'REDObligationPreferred', 'CredIndexAnnex', 'CredIndexAnnexSplit',
+                   'REDIndexCodes']
+    fname = download_report("REDIndexCodes")
+    #update_redcodes(fname)