diff options
| -rw-r--r-- | python/position.py | 58 |
1 files changed, 46 insertions, 12 deletions
diff --git a/python/position.py b/python/position.py index 6a30fdf8..cc229136 100644 --- a/python/position.py +++ b/python/position.py @@ -2,19 +2,53 @@ from bbg_helpers import init_bbg_session, retreive_data, process_msgs import pandas as pd from sqlalchemy import create_engine from pandas.tseries.offsets import BDay +from pandas import bdate_range +import re +import os engine = create_engine('postgresql://dawn_user@debian/dawndb') -session = init_bbg_session('192.168.0.14', 8194) -fields = ["START_ACC_DT", "MTG_FACTOR_PAY_DT", "CUR_CPN", "INT_ACC", - "DAYS_ACC", "MTG_FACE_AMT", "MTG_FACTOR", "MTG_PREV_FACTOR", "MTG_FACTOR_PRINC_PAY", - "MTG_PRINC_LOSSES", "CRNCY"] +def get_bbg_numbers(workdate, fields): + positions = pd.read_sql_query("select * from list_positions(%s)", engine, params=(workdate.date(),)) + positions.loc[positions.identifier.str.endswith('_A'),'bbg_id'] = positions.identifier.str.slice(stop=9) + positions.loc[~positions.identifier.str.endswith('_A'),'bbg_id'] = positions.identifier.str.slice(stop=12) + sec = [s + " Mtge" if s!='XS0295516776' else s +" Corp" for s in positions.bbg_id.tolist()] + data = retreive_data(session, sec, fields, workdate) + df = process_msgs(data) + df = pd.DataFrame.from_dict(df, orient='index') + return df -workdate = pd.datetime(2015, 7, 24) -securities = pd.read_sql_query("select * from list_positions(%s)", engine, params=(workdate.date(),)) -securities.loc[securities.identifier.str.endswith('_A'),'bbg_id'] = securities.identifier.str.slice(stop=9) -securities.loc[~securities.identifier.str.endswith('_A'),'bbg_id'] = securities.identifier.str.slice(stop=12) -sec = [s + " Mtge" if s!='XS0295516776' else s +" Corp" for s in securities.bbg_id.tolist()] -data = retreive_data(session, sec, fields, workdate) -df = process_msgs(data, fields) -df.to_csv('pomme6.csv') +def get_list(workdate): + positions = pd.read_sql_query("select * from list_positions(%s)", engine, params=(workdate.date(),)) + positions.loc[positions.identifier.str.len() <= 11, 'cusip'] = positions.identifier.str.slice(stop=9) + positions.loc[positions.identifier.str.len() == 12, 'isin'] = positions.identifier + 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/share/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) + +if __name__=="__main__": + session = init_bbg_session('192.168.0.4', 8194) + fields = ["START_ACC_DT", "MTG_FACTOR_PAY_DT", "CUR_CPN", "INT_ACC", + "DAYS_ACC", "MTG_FACE_AMT", "MTG_FACTOR", "MTG_PREV_FACTOR", "MTG_FACTOR_PRINC_PAY", + "MTG_PRINC_LOSSES", "CRNCY"] + workdate = pd.datetime.today() + df = get_bbg_numbers(workdate, fields) + df.to_csv("positions_{0:%Y-%m-%d}.csv".format(workdate)) |