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
|
from bbg_helpers import init_bbg_session, retreive_data, process_msgs
from sqlalchemy import create_engine, MetaData, Table, bindparam
import numpy as np
import pandas as pd
engine = create_engine('postgresql://et_user@debian/ET')
metadata = MetaData(bind = engine)
session = init_bbg_session('192.168.0.14', 8194)
fields_update = ["LN_ISSUE_STATUS", "AMT_OUTSTANDING", "PX_LAST","LAST_UPDATE_DT",
"LN_CURRENT_MARGIN", "DEFAULTED", "DEFAULT_DATE",
"CALLED", "CALLED_DT", "PRICING_SOURCE"]
# append securities to request
cusips = pd.read_sql_query("select id_bb_unique, substring(id_bb_unique from 3) as cusip from bloomberg_corp_ref " \
"where (status is Null or status not in ('REFINANCED','RETIRED', 'REPLACED')) "\
"and not called", engine, index_col='cusip')
securities = ["{0} Corp".format(cusip) for cusip in cusips.index]
data = retreive_data(session, securities, fields_update)
df = process_msgs(data, fields_update)
df.security = df.security.str.slice(0,9)
df.set_index(['security'], inplace=True)
df['ID_BB_UNIQUE'] = cusips['id_bb_unique'].values
df.reset_index(inplace=True)
bloomberg_corp_ref = Table('bloomberg_corp_ref', metadata, autoload=True)
stmt = bloomberg_corp_ref.update().\
where(bloomberg_corp_ref.c.id_bb_unique == bindparam('ID_BB_UNIQUE')).\
values(defaulted = bindparam('DEFAULTED'),
default_date = bindparam('DEFAULT_DATE'),
called = bindparam('CALLED'),
called_date = bindparam('CALLED_DT'),
status = bindparam('LN_ISSUE_STATUS'))
conn = engine.connect()
conn.execute(stmt,df[['DEFAULTED', 'DEFAULT_DATE', 'CALLED',
'CALLED_DT', 'LN_ISSUE_STATUS', 'ID_BB_UNIQUE']].to_dict('records'))
currentdata = pd.read_sql_query("SELECT id_bb_unique, pricingdate from bloomberg_corp",
engine,
parse_dates=["pricingdate"],
index_col=['id_bb_unique', 'pricingdate'])
#no need to insert empty prices
df.dropna(subset=['PX_LAST', 'LAST_UPDATE_DT'], inplace=True)
df.set_index(['ID_BB_UNIQUE', 'LAST_UPDATE_DT'], inplace=True)
df = df.ix[df.index.difference(currentdata.index)]
df.index.names = ['ID_BB_UNIQUE', 'LAST_UPDATE_DT']
df.reset_index(inplace=True)
to_insert = df[['ID_BB_UNIQUE','LAST_UPDATE_DT','PX_LAST','LN_CURRENT_MARGIN',
'AMT_OUTSTANDING', 'PRICING_SOURCE']]
to_insert.columns = ['id_bb_unique', 'pricingdate', 'price', 'loan_margin',
'amount_outstanding', 'source']
to_insert.to_sql('bloomberg_corp', engine, if_exists='append', index=False)
|
