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
82
83
|
from db import dbconn
import datetime
from sys import argv
def affected_indices(company_id, conn):
"""returns the list of indices containing company_id"""
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,))
recordslist = c.fetchall()
return recordslist
def create_newindices(recordslist, lastdate, nissuers, 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,
%(cumulativeloss)s, %(lastdate)s) RETURNING basketid"""
updatestr = "UPDATE index_version SET lastdate=%s WHERE basketid=%s"
with conn.cursor() as c:
newids = {}
for r in recordslist:
r['indexfactor'] -= 100/nissuers
r['version'] += 1
r['cumulativeloss'] += (100-recovery)/nissuers
r['lastdate'] = 'infinity'
c.execute(insertstr, r)
newids[r['basketid']] = c.fetchone()[0]
for oldid in newids.keys():
c.execute(updatestr, (lastdate, oldid))
conn.commit()
return newids
def update_indexmembers(newids, company_id, 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))
conn.commit()
def update_redcodes(index_type):
if index_type == 'HY':
index_subfamily = 'CDX.NA.HY'
elif index_type == 'IG':
index_subfamily = 'CDX.NA.IG'
elif index_type == 'EU':
index_subfamily = 'iTraxx Europe'
elif index_type == 'XO':
index_subfamily = 'iTraxx Europe Crossover'
with conn.cursor() as c:
c.execute(
"UPDATE index_version SET redindexcode=index_version_markit.redindexcode "
"FROM index_version_markit WHERE index_version.series=index_version_markit.series "
"AND index_version.version=index_version_markit.version "
"AND index_version.index=%s "
"AND index_version_markit.indexsubfamily=%s"
"AND index_version.redindexcode IS NULL",
(index_type, index_subfamily))
conn.commit()
if __name__=="__main__":
if len(argv) == 1:
print("""Usage:
python handle_default.py <company_id> <date> <recovery> <n_issuers>
For instance:
python handle_default.py 210065 2015-02-19 15.875 100""")
else:
with dbconn('serenitasdb') as serenitasdb:
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)
|
