diff options
| -rw-r--r-- | python/load_refentity.py | 33 | ||||
| -rw-r--r-- | python/markit_red.py | 32 |
2 files changed, 44 insertions, 21 deletions
diff --git a/python/load_refentity.py b/python/load_refentity.py index 837b5dfb..98387b6a 100644 --- a/python/load_refentity.py +++ b/python/load_refentity.py @@ -1,4 +1,7 @@ from db import dbconn +from pathlib import Path +import datetime +import re import lxml.etree as etree parser = etree.XMLParser(remove_blank_text=True) from psycopg2.extras import Json @@ -71,11 +74,15 @@ def insert_refobligation(fname): 'type', 'isconvert', 'isperp', 'coupontype', 'ccy', 'maturity', 'issuedate', 'coupon', 'isin', 'cusip', 'event'] - redpair_insert = ("INSERT INTO RedPairMapping VALUES({})". - format(",".join(["%s"] * len(names_redpair)))) - refobligation_insert = ("INSERT INTO RefObligation({}) VALUES({})". + redpair_insert = ("""INSERT INTO RedPairMapping VALUES({}) + ON CONFLICT(redpaircode) DO UPDATE SET {}""". + format(",".join(["%s"] * len(names_redpair)), + ",".join(f"{name}=EXCLUDED.{name}" for name in names_redpair[1:]))) + refobligation_insert = ("""INSERT INTO RefObligation({}) VALUES({}) + ON CONFLICT(obligationname) DO UPDATE SET {}""". format(",".join(names_refobligation), - ",".join(["%s"] * len(names_refobligation)))) + ",".join(["%s"] * len(names_refobligation)), + ",".join(f"{name}=EXCLUDED.{name}" for name in names_refobligation[1:]))) skipfirst = True with conn.cursor() as c: for child in tree.getroot(): @@ -95,8 +102,18 @@ def insert_refobligation(fname): c.execute(refobligation_insert, [d.get(name) for name in names_refobligation]) conn.commit() +def get_date(f): + m = re.search("(\d*)\.", f.name) + if m: + timestamp = int(m.groups(0)[0]) + return datetime.datetime.fromtimestamp(timestamp/1000) + if __name__ == "__main__": - #insert_refobligation('/home/serenitas/guillaume/V9 Red Obligation1503420865581.xml') - from pathlib import Path - base_dir = Path('/home/serenitas/CorpCDOs/Tranche_data/RED_reports') - insert_refentity((base_dir / 'V10 Red Entity Delta1503687306161.xml').as_posix()) + from markit_red import download_report + base_dir = Path('/home/serenitas/CorpCDOs/Tranche_data/RED_reports/Deltas') + for report in ['REDEntityDelta', 'REDObligationDelta']: + f = base_dir / download_report(report)[0] + if "Entity" in report: + insert_refentity(f.as_posix()) + else: + insert_refobligation(f.as_posix()) diff --git a/python/markit_red.py b/python/markit_red.py index 949c0578..23856894 100644 --- a/python/markit_red.py +++ b/python/markit_red.py @@ -7,10 +7,14 @@ from db import with_connection def request_payload(payload): r = requests.get('https://www.markit.com/export.jsp', params=payload) res = [] + path = os.path.join(os.environ['BASE_DIR'], "Tranche_data", "RED_reports") + if 'Delta' in payload['report']: + path = os.path.join(path, "Deltas") + 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")) + z.extract(f, path=path) res.append(f) return res @@ -20,14 +24,13 @@ def download_report(report): '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) + return request_payload(payload) else: - r += request_payload(payload) - return r + return request_payload(payload) @with_connection('serenitasdb') def update_redcodes(conn, fname): @@ -71,11 +74,14 @@ def update_redindices(fname): 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) + # 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) |