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
|
from sqlalchemy import create_engine
import pandas as pd
from db import conn
import numpy as np
from bbg_helpers import init_bbg_session, retreive_data, process_msgs
engine = create_engine('postgresql://et_user@debian/ET')
session = init_bbg_session('192.168.1.108', 8194)
all_fields = ["ISSUE_DT", "LN_ISSUE_STATUS", "ID_CUSIP", "ID_BB_UNIQUE",
"SECURITY_TYP", "AMT_OUTSTANDING", "PX_LAST","LAST_UPDATE_DT",
"ISSUER", "MATURITY","CPN","CPN_TYP", "CPN_FREQ","FLT_SPREAD",
"LIBOR_FLOOR","LN_CURRENT_MARGIN", "LN_TRANCHE_SIZE", "AMT_ISSUED",
"LN_COVENANT_LITE","SECOND_LIEN_INDICATOR","DEFAULTED", "DEFAULT_DATE",
"CALLED", "CALLED_DT", "PRICING_SOURCE"]
# append securities to request
currentdata = pd.read_sql_query("select id_bb_unique, substring(id_bb_unique from 3) as cusip " \
"from bloomberg_corp_ref", engine, index_col='cusip')
mapping = pd.read_csv("/home/share/CorpCDOs/data/bbg_loanxid.csv", index_col=0)
mapping = mapping.ix[mapping.index.difference(currentdata.index)]
securities = ["{0} Corp".format(cusip) for cusip in mapping.index]
data = retreive_data(session, securities, all_fields)
df = process_msgs(data, all_fields)
df.security = df.security.str.slice(0,9)
df.set_index('security', inplace=True)
df['loanxid'] = mapping['loanxid']
df.reset_index(inplace=True)
sqlstr = "INSERT INTO bloomberg_corp_ref VALUES({0})".format(",".join(["%s"]*20))
with conn.cursor() as c:
for i in range(df.shape[0]):
issue_size = df.iloc[i]['LN_TRANCHE_SIZE']
if np.isnan(issue_size):
issue_size = df.iloc[i]['AMT_ISSUED']
if np.isnan(issue_size):
issue_size = None
try:
c.execute(sqlstr,
(df.iloc[i]['ID_BB_UNIQUE'], df.iloc[i]['ID_CUSIP'], df.iloc[i]['ISSUER'],
df.iloc[i]['MATURITY'], df.iloc[i]['CPN'], df.iloc[i]['CPN_TYP'],
df.iloc[i]['CPN_FREQ'], df.iloc[i]['FLT_SPREAD'], df.iloc[i]['LIBOR_FLOOR'],
issue_size, df.iloc[i]["LN_COVENANT_LITE"], df.iloc[i]["SECOND_LIEN_INDICATOR"],
df.iloc[i]["SECURITY_TYP"], df.iloc[i]["ISSUE_DT"], df.iloc[i]["DEFAULTED"],
df.iloc[i]["DEFAULT_DATE"], df.iloc[i]["CALLED"], df.iloc[i]["CALLED_DT"],
df.iloc[i]["LN_ISSUE_STATUS"], [df.iloc[i]['loanxid']]))
conn.commit()
except IntegrityError:
conn.rollback()
sqlstr = "INSERT INTO bloomberg_corp VALUES(%s, %s, %s, %s, %s, %s)"
with conn.cursor() as c:
for i in range(df.shape[0]):
margin = df.iloc[i]['LN_CURRENT_MARGIN']
if np.isnan(margin):
margin = None
amt_outstanding = df.iloc[i]['AMT_OUTSTANDING']
if np.isnan(amt_outstanding):
amt_outstanding = None
try:
c.execute(sqlstr, (df.iloc[i]['ID_BB_UNIQUE'], df.iloc[i]['LAST_UPDATE_DT'],
df.iloc[i]['PX_LAST'], margin, amt_outstanding,
df.iloc[i]['PRICING_SOURCE']))
conn.commit()
except IntegrityError:
conn.rollback()
conn.close()
|
