from bbg_helpers import init_bbg_session, retrieve_data, BBG_IP import pandas as pd from sqlalchemy import create_engine from pandas.tseries.offsets import BDay from pandas import bdate_range import re import os import logging import sys def get_list(engine, workdate=None, asset_class=None, include_unsettled=True): if workdate: positions = pd.read_sql_query("select identifier, bbg_type from list_positions(%s, %s, %s)", engine, params=(workdate.date(), asset_class, include_unsettled)) positions.loc[positions.identifier.str.len() <= 11, 'cusip'] = positions.identifier.str.slice(stop=9) positions.loc[positions.identifier.str.len() == 12, 'isin'] = positions.identifier else: positions = pd.read_sql_table("securities", engine) positions['bbg_id'] = positions.cusip.where(positions.cusip.notnull(), positions['isin']) + \ ' ' + positions.bbg_type positions.set_index('bbg_id', inplace=True) return positions def get_list_range(engine, begin, end, asset_class=None): begin = pd.Timestamp(begin).date() end = pd.Timestamp(end).date() positions = pd.read_sql_query("select identifier, bbg_type, strategy from list_positions_range(%s, %s, %s)", engine, params=(begin, end, asset_class)) positions.loc[positions.identifier.str.len() <= 11, 'cusip'] = positions.identifier.str.slice(stop=9) positions.loc[positions.identifier.str.len() == 12, 'isin'] = positions.identifier positions['bbg_id'] = positions.cusip.where(positions.cusip.notnull(), positions['isin']) + \ ' ' + positions.bbg_type positions.set_index('bbg_id', inplace=True) return positions def backpopulate_marks(begin_str='2015-01-15', end_str='2015-07-15'): pattern = re.compile("\d{4}-\d{2}-\d{2}") list_of_daily_folder = (fullpath for (fullpath, _, _) in os.walk('/home/serenitas/Daily') if pattern.match(os.path.basename(fullpath))) list_of_bdays = bdate_range(start=begin_str, end=end_str) for path in list_of_daily_folder: date = pd.to_datetime(os.path.basename(path)) if date in list_of_bdays: marks_file = [f for f in os.listdir(path) if f.startswith("securitiesNpv")] if marks_file: marks_file.sort(key=lambda x:x[13:], reverse=True) #sort by lexicographic order which is what we want since we use ISO dates marks = pd.read_csv(os.path.join(path, marks_file[0])) positions = get_list(pd.to_datetime(date)) positions = positions.merge(marks, left_on='identifier', right_on='IDENTIFIER') positions.drop(['IDENTIFIER', 'last_settle_date'], axis=1, inplace=True) positions['date'] = date positions.rename(columns={'Price': 'price'}, inplace=True) positions = positions.drop_duplicates() positions.to_sql('position', engine, if_exists='append', index=False) def update_securities(engine, session, workdate): field = {'Corp': 'PREV_CPN_DT', 'Mtge': 'START_ACC_DT'} securities = get_list(engine) securities = securities[securities.paid_down.isnull()] conn = engine.raw_connection() data = retrieve_data(session, securities.index.tolist(), ['PREV_CPN_DT', 'START_ACC_DT', 'CUR_CPN', 'CPN_ASOF_DT']) data = pd.DataFrame.from_dict(data, orient='index') data = data[data.CPN_ASOF_DT.isnull() |(data.CPN_ASOF_DT<=workdate)] m = securities.merge(data, left_index=True, right_index=True) with conn.cursor() as c: for r in m.to_dict('records'): if r[field[r['bbg_type']]] < workdate: c.execute("UPDATE securities SET start_accrued_date=%({0})s " ",coupon=%(CUR_CPN)s WHERE identifier=%(identifier)s".format(field[r['bbg_type']]), r) conn.commit() def init_fx(session, engine, startdate): currencies = ['EURUSD', 'CADUSD'] securities = [c + ' Curncy' for c in currencies] data = retrieve_data(session, securities, ['PX_LAST'], start_date=startdate) data = data['EURUSD Curncy'].merge(data['CADUSD Curncy'], left_on='date', right_on='date') data.rename(columns={'PX_LAST_x': 'eurusd', 'PX_LAST_y': 'cadusd'}, inplace=True) data.to_sql('fx', engine, index=False, if_exists='append') def update_fx(engine, session, currencies): securities = [c + ' Curncy' for c in currencies] data = retrieve_data(session, securities, ['FIXED_CLOSING_PRICE_NY', 'PX_CLOSE_DT']) colnames = ['date'] values = [] for k, v in data.items(): currency_pair = k.split(' ')[0].lower() colnames.append(currency_pair) values.append(v['FIXED_CLOSING_PRICE_NY']) values = [v['PX_CLOSE_DT']] + values sqlstr = 'INSERT INTO fx({0}) VALUES({1}) ON CONFLICT DO NOTHING'.format( ",".join(colnames), ",".join(["%s"]*len(values))) conn = engine.raw_connection() with conn.cursor() as c: c.execute(sqlstr, values) conn.commit() def populate_cashflow_history(engine, session, workdate=None): securities = get_list(engine, workdate) conn = engine.raw_connection() data = retrieve_data(session, securities.index.tolist(), ['HIST_CASH_FLOW', 'MTG_HIST_CPN', 'FLT_CPN_HIST', 'HIST_INTEREST_DISTRIBUTED']) fixed_coupons = {'XS0306416982 Mtge': 7.62, '91927RAD1 Mtge': 6.77} for k, v in data.items(): if 'HIST_CASH_FLOW' in v: to_insert = v['HIST_CASH_FLOW'].merge(v['MTG_HIST_CPN'], how='left', left_on='Payment Date', right_on='Payment Date') to_insert.rename(columns={'Coupon_y': 'coupon', 'Interest': 'interest', 'Payment Date': 'date', 'Principal Balance': 'principal_bal', 'Principal Paid': 'principal'}, inplace=True) to_insert.drop(['Period Number', 'Coupon_x'], axis=1, inplace=True) elif 'FLT_CPN_HIST' in v: to_insert = v['FLT_CPN_HIST'] to_insert.rename(columns={'Coupon Rate': 'coupon', 'Accrual Start Date': 'date'}, inplace=True) to_insert.coupon = to_insert.coupon.shift(1) elif 'HIST_INTEREST_DISTRIBUTED' in v: to_insert = v['HIST_INTEREST_DISTRIBUTED'] to_insert.rename(columns={'Interest': 'interest', 'Historical Date': 'date'}, inplace=True) if k in fixed_coupons: to_insert['coupon'] = fixed_coupons[k] else: #damn you XS0299146992 ! continue else: logging.error("No cashflows for the given security") identifier = securities.loc[k,'identifier'] to_insert['identifier'] = identifier with conn.cursor() as c: c.execute("DELETE FROM cashflow_history WHERE identifier=%s", (identifier,)) conn.commit() to_insert.to_sql('cashflow_history', engine, if_exists='append', index=False) with conn.cursor() as c: c.execute("REFRESH MATERIALIZED VIEW factors_history") conn.commit() if __name__=="__main__": engine = create_engine('postgresql://dawn_user@debian/dawndb') if len(sys.argv)>1: workdate = pd.Timestamp(sys.argv[1]) else: workdate = pd.datetime.today() with init_bbg_session(BBG_IP) as session: update_securities(engine, session, workdate) populate_cashflow_history(engine, session, workdate) update_fx(engine, session, ['EURUSD', 'CADUSD']) # with init_bbg_session(BBG_IP) as session: # init_fx(session, engine, pd.datetime(2013, 1, 1))