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
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
|
import psycopg2
import os
import os.path
import datetime
from datetime import date
import csv
import pdb
import sys
import common
def convertToNone(s):
return None if s=="" else s
def sanitize_float(intex_float):
intex_float = intex_float.replace(",", "")
if "(" in intex_float:
intex_float = - float(intex_float[1:-1])
else:
try:
intex_float = float(intex_float)
except ValueError:
pdb.set_trace()
return intex_float
def upload_data(workdate, conn, cursor):
dealupdate = {}
for cusip_universe_file in os.listdir(os.path.join(common.root, "data", "Trinfo_" + workdate)):
with open( os.path.join(common.root, "data", "Trinfo_" + workdate, cusip_universe_file), "r") as fh:
dr = csv.DictReader(fh, dialect='excel-tab')
data = []
deals_to_update = []
for line in dr:
if "ISIN" not in line:
line['ISIN'] = None
if "," in line['Tranche']:
line["dealname"], line["tranche"] = line["Tranche"].split(",")
else:
continue
line["dealname"] = line["dealname"].lower()
dealname = line['dealname']
line = {k: convertToNone(v) for k, v in line.items()}
if dealname not in dealupdate:
cursor.execute("SELECT \"Latest Update\" FROM clo_universe " \
"WHERE dealname = %s ORDER BY \"Latest Update\" DESC", (dealname,))
dealupdate[dealname] = cursor.fetchone()[0]
sqlstring = "SELECT updatedate FROM latest_cusip_universe WHERE cusip = %s"
cursor.execute(sqlstring, (line['CUSIP'],))
curr_date = cursor.fetchone()
if not curr_date or curr_date[0] < dealupdate[dealname]:
if dealname not in deals_to_update:
deals_to_update.append(dealname)
line['updatedate'] = dealupdate[dealname]
for key in ['Curr Balance', 'Orig Balance', 'Orig Attachment Point',
'Curr Attachment Point', 'Orig Detachment Point',
'Curr Detachment Point', 'Factor', 'Coupon', 'Floater Spread']:
if line[key]:
line[key] = sanitize_float(line[key])
line[key] = convertToNone(line[key])
if "Curr Moody" not in line:
line['Curr Moody'] = line['Orig Moody']
sqlstring = "INSERT INTO cusip_universe(Cusip, ISIN, dealname, tranche, " \
"Coupon, Orig_Balance, Curr_Balance, Factor, Orig_Moody, Curr_Moody, " \
"Orig_Attach, Orig_Detach, Curr_Attach, Curr_Detach, Floater_Index, " \
"Spread, updatedate)" \
"VALUES (%(CUSIP)s, %(ISIN)s, %(dealname)s, %(tranche)s, %(Coupon)s, " \
"%(Orig Balance)s, %(Curr Balance)s, %(Factor)s, %(Orig Moody)s, %(Curr Moody)s, " \
"%(Orig Attachment Point)s, %(Orig Detachment Point)s, %(Curr Attachment Point)s," \
"%(Curr Detachment Point)s, %(Floater Index)s, %(Floater Spread)s, " \
"%(updatedate)s)"
try:
cursor.execute(sqlstring, line)
except psycopg2.DataError:
pdb.set_trace()
print("uploaded: {0}".format(line['CUSIP']))
conn.commit()
for dealname in deals_to_update:
try:
cursor.execute("SELECT p_cusip, p_curr_subordination, "\
"p_curr_thickness from et_deal_subordination(%s)",
(dealname,))
except psycopg2.DataError:
pdb.set_trace()
data = cursor.fetchall()
data = [ (t[1], t[2], t[0], dealupdate[dealname]) for t in data]
cursor.executemany("UPDATE cusip_universe SET subordination = %s, "
"thickness = %s WHERE cusip = %s AND "
"updatedate = %s", data)
conn.commit()
if __name__=="__main__":
if len(sys.argv) > 1:
workdate = sys.argv[1]
else:
workdate = str(datetime.date.today())
upload_data(workdate, common.conn, common.cursor)
common.cursor.close()
common.conn.close()
|
