diff options
Diffstat (limited to 'python/markit_red.py')
| -rw-r--r-- | python/markit_red.py | 47 |
1 files changed, 25 insertions, 22 deletions
diff --git a/python/markit_red.py b/python/markit_red.py index 23856894..e10381ba 100644 --- a/python/markit_red.py +++ b/python/markit_red.py @@ -2,7 +2,8 @@ import csv from lxml import etree import requests, io, zipfile, shutil import os -from db import with_connection +from db import with_connection, dbengine +import pandas as pd def request_payload(payload): r = requests.get('https://www.markit.com/export.jsp', params=payload) @@ -36,22 +37,24 @@ def download_report(report): 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() + data_version = [] + data_maturity = [] + for index in et.iter('index'): + temp = {c.tag: c.text for c in index if c.tag not in ['terms', 'paymentfrequency']} + data_version.append(temp) + for term in index.iter('term'): + d = {'redindexcode': temp['redindexcode']} + d.update({c.tag: c.text for c in term}) + data_maturity.append(d) + + df_maturity = pd.DataFrame(data_maturity) + df_version = pd.DataFrame(data_version) + df_version['activeversion'] = df_version['activeversion'].map({'Y': True, None: False}) + df_maturity.tenor = df_maturity['tenor'].map(lambda s: s.lower() + 'r') + df_maturity.coupon = (pd.to_numeric(df_maturity.coupon) * 10000).astype(int) + serenitas_engine = dbengine('serenitasdb') + df_version.to_sql("index_version_markit", serenitas_engine, index=False, if_exists='append') + df_maturity.to_sql("index_maturity_markit", serenitas_engine, index=False, if_exists='append') def update_redindices(fname): basedir = os.path.join(os.environ['BASE_DIR'], "Tranche_data", "RED_reports") @@ -74,14 +77,14 @@ def update_redindices(fname): csvwriter.writerows(data) if __name__=="__main__": - # fname = download_report("REDIndexCodes") - # update_redcodes(fname[0]) + fname = download_report("REDIndexCodes") + update_redcodes(fname[0]) # f1, f2 = download_report("CredIndexAnnex") # update_redindices(f1) # update_redindices(f2) # report_list = ['REDEntity', 'REDObligation', 'REDObligationPreferred'] # for report in report_list: # download_report(report) - report_list = ['REDEntityDelta', 'REDObligationDelta'] - for report in report_list: - fname = download_report(report) + # report_list = ['REDEntityDelta', 'REDObligationDelta'] + # for report in report_list: + # fname = download_report(report) |