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
|
from bbg_helpers import init_bbg_session, retrieve_data, BBG_IP
import pandas as pd
import datetime
from db import dbengine, dbconn
from dateutil.relativedelta import relativedelta
def get_universe():
engine = dbengine('corelogic')
r = engine.execute("SELECT cusip from map_cusip")
return set(c[0][:8] for c in r.fetchall() if c[0])
def get_bbg_data(cusips, universe):
end_date = datetime.datetime.utcnow()
start_date = end_date - relativedelta(years=1)
with init_bbg_session(BBG_IP) as session:
for cusip in cusips:
if cusip in universe:
security = "{}@TRAC Mtge".format(cusip)
yield (cusip, retrieve_data(session, security,
start_date=start_date,
end_date=end_date))
def insert_data(dfs, conn):
sql_str = "INSERT INTO trace_trades(cusip, condition_code, size, time, price)" \
" VALUES(%s, %s, %s, %s, %s) ON CONFLICT DO NOTHING"
for cusip, df in dfs:
if 'type' in df:
del df['type']
if 'conditionCodes' not in df:
df['conditionCodes'] = None
df = df.sort_index(axis=1)
cusip = cusip + calc_check_digit(cusip)
to_insert = [(cusip,) + r for r in df.itertuples(index=False)]
with conn.cursor() as c:
c.executemany(sql_str, to_insert)
conn.commit()
def get_cusips():
with open("/home/share/CorpCDOs/data/TRACE/ABS.csv") as fh:
for line in fh:
yield line.split()[0]
_alphabet = '0123456789ABCDEFGH JKLMN PQRSTUVWXYZ*@#'
def calc_check_digit(number):
"""Calculate the check digits for the number."""
# convert to numeric first, then sum individual digits
number = ''.join(
str((1, 2)[i % 2] * _alphabet.index(n)) for i, n in enumerate(number))
return str((10 - sum(int(n) for n in number)) % 10)
if __name__ == "__main__":
universe = get_universe()
cusips = get_cusips()
dfs = get_bbg_data(cusips, universe)
conn = dbconn('serenitasdb')
insert_data(dfs, conn)
conn.close()
|
