diff options
Diffstat (limited to 'python/externalmarksbackfill.py')
| -rw-r--r-- | python/externalmarksbackfill.py | 41 |
1 files changed, 40 insertions, 1 deletions
diff --git a/python/externalmarksbackfill.py b/python/externalmarksbackfill.py index 10b607b7..64c8c81e 100644 --- a/python/externalmarksbackfill.py +++ b/python/externalmarksbackfill.py @@ -3,7 +3,9 @@ import os import re import psycopg2 import datetime +import bbg_helpers from db import dbconn +from db import dbengine from pickle import dumps from sqlalchemy import create_engine from itertools import chain @@ -45,12 +47,48 @@ def runSingleFill(f): finally: conn.commit() +def get_CUSIPs(): + conn = dbconn('dawndb') + sql_str = "select distinct cusip from bonds where asset_class = 'Subprime'" + df = pd.read_sql_query(sql_str, dbengine('dawndb')) + return df + +def get_BVAL(): + BBG_IP = ['192.168.9.65'] + bbgstartdate = pd.datetime(2013, 1, 1) + hist_securities = get_CUSIPs()['cusip']+ ' Mtge' + hist_fields = ['PX_LAST'] + + with bbg_helpers.init_bbg_session(BBG_IP) as session: + hist_data = bbg_helpers.retrieve_data(session, hist_securities, hist_fields, start_date=bbgstartdate) + + hist_data1 = pd.concat(hist_data, axis = 1) + hist_data1 = hist_data1.resample('1m', closed= 'right').last() + hist_data1 = hist_data1.drop(pd.Timestamp('2017-02-28')).stack(level = -2) + hist_data1['source'] = 'BVAL' + hist_data1 = hist_data1.reset_index().rename(columns = {'level_1':'identifier', 'PX_LAST': 'mark'}) + hist_data1.identifier = hist_data1.identifier.str[:9] + return hist_data1 + +def pop_BVAL_to_database(df): + conn = dbconn('dawndb') + sql_str = "INSERT INTO external_marks VALUES(%(identifier)s, %(date)s, %(mark)s, %(source)s)" + with conn.cursor() as c: + for r in df.to_dict(orient='record'): + try: + c.execute(sql_str, r) + except (psycopg2.DataError, psycopg2.IntegrityError) as detail: + print(detail) + conn.rollback() + finally: + conn.commit() + def get_globs(): basedir = '/home/serenitas/Daily' globs = [iglob(os.path.join(basedir, year, "{}_*/{}*/ReviewedPack*.xlsx".format(year, year))) - for year in ['2015', '2016']] + for year in ['2015', '2016', '2017']] for year in ['2013', '2014']: globs.append(iglob(os.path.join(basedir, year, @@ -58,6 +96,7 @@ def get_globs(): return chain.from_iterable(globs) settings = { + 'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170101.20170131.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), 'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20161201.20161231.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), 'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20161101.20161130.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), 'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20161001.20161031.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), |