diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/handle_default.py | 57 |
1 files changed, 33 insertions, 24 deletions
diff --git a/python/handle_default.py b/python/handle_default.py index c61f071f..a47854c3 100644 --- a/python/handle_default.py +++ b/python/handle_default.py @@ -2,19 +2,27 @@ from db import serenitas_pool import datetime from sys import argv -def affected_indices(company_id, conn): - """returns the list of indices containing company_id""" +def get_recovery(company_id: int, seniority: str, conn): + with conn.cursor() as c: + c.execute("SELECT recovery * 100, auction_date FROM defaulted " + "WHERE id=%s AND seniority=%s", + (company_id, seniority)) + return c.fetchone() +def affected_indices(company_id: int, seniority: str, conn): + """returns the list of indices containing company_id""" + sqlstr = ("SELECT b.*, a.curr_weight*b.indexfactor AS orig_weight " + "FROM basket_constituents_current a " + "JOIN (SELECT * FROM index_version WHERE lastdate='infinity') b " + "USING (basketid) " + "WHERE company_id=%s AND seniority=%s") + print(sqlstr) with conn.cursor() as c: - sqlstr = """SELECT * FROM - (SELECT unnest(index_list) AS basketid FROM bbg_issuers WHERE company_id=%s) a - JOIN index_version USING (basketid) - WHERE lastdate='infinity'""" - c.execute(sqlstr, (company_id,)) + c.execute(sqlstr, (company_id, seniority)) recordslist = c.fetchall() return recordslist -def create_newindices(recordslist, lastdate, nissuers, conn): +def create_newindices(recordslist, recovery, lastdate, conn): """create the new indices versions and update the old""" insertstr = """INSERT INTO index_version(Index, Series, Version, IndexFactor, CumulativeLoss, lastdate) Values(%(index)s, %(series)s, %(version)s, %(indexfactor)s, @@ -25,9 +33,9 @@ def create_newindices(recordslist, lastdate, nissuers, conn): with conn.cursor() as c: newids = {} for r in recordslist: - r['indexfactor'] -= 100/nissuers + r['indexfactor'] -= r['orig_weight'] r['version'] += 1 - r['cumulativeloss'] += (100-recovery)/nissuers + r['cumulativeloss'] += (100-recovery) * r['orig_weight'] r['lastdate'] = 'infinity' c.execute(insertstr, r) newids[r['basketid']] = c.fetchone()[0] @@ -36,12 +44,14 @@ def create_newindices(recordslist, lastdate, nissuers, conn): conn.commit() return newids -def update_indexmembers(newids, company_id, conn): +def update_indexmembers(newids, company_id, seniority, conn): with conn.cursor() as c: for oldid, newid in newids.items(): - c.execute("""update bbg_issuers set index_list=index_list||%s where company_id in - (select company_id from bbg_issuers where %s=Any(index_list) - and company_id != %s)""", (newid, oldid, company_id)) + c.execute("INSERT INTO basket_constituents " + "(SELECT company_id, seniority, %s, weight " + "FROM basket_constituents " + "WHERE basket_id=%s AND NOT (company_id=%s AND seniority=%s))", + (newid, oldid, company_id, seniority)) conn.commit() def update_redcodes(index_type, conn): @@ -69,16 +79,15 @@ def update_redcodes(index_type, conn): if __name__=="__main__": if len(argv) == 1: print("""Usage: - python handle_default.py <company_id> <date> <recovery> <n_issuers> + python handle_default.py <company_id> <seniority> For instance: - python handle_default.py 210065 2015-02-19 15.875 100""") + python handle_default.py 210065 Senior""") else: - conn = serenitas_pool.getconn(__name__) + conn = serenitas_pool.getconn() company_id = int(argv[1]) - lastdate = datetime.datetime.strptime(argv[2], "%Y-%m-%d") - recovery = float(argv[3]) - n_issuers = float(argv[4]) - recordslist = affected_indices(company_id, serenitasdb) - newids = create_newindices(recordslist, lastdate, n_issuers, serenitasdb) - update_indexmembers(newids, company_id, serenitasdb) - serenitas_pool.putconn(conn, __name__) + seniority = argv[2] + recovery, lastdate = get_recovery(company_id, seniority, conn) + recordslist = affected_indices(company_id, seniority, conn) + newids = create_newindices(recordslist, recovery, lastdate, conn) + update_indexmembers(newids, company_id, seniority, conn) + serenitas_pool.putconn(conn,) |