diff options
Diffstat (limited to 'python/position.py')
| -rw-r--r-- | python/position.py | 77 |
1 files changed, 52 insertions, 25 deletions
diff --git a/python/position.py b/python/position.py index 15c4f0c7..f2dc4f8e 100644 --- a/python/position.py +++ b/python/position.py @@ -6,24 +6,23 @@ from pandas import bdate_range import re import os -engine = create_engine('postgresql://dawn_user@debian/dawndb') +def get_list(workdate, asset_class=None, include_unsettled=True): + positions = pd.read_sql_query("select * 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 + return positions -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) - positions.bbg_id = positions.bbg_id + ' ' + positions.bbg_type +def get_bbg_numbers(session, workdate, fields): + positions = get_list(workdate) + positions['bbg_id'] = positions.cusip.where(positions.cusip.notnull(), positions['isin']) + \ + ' ' + positions.bbg_type data = retreive_data(session, positions.bbg_id.tolist(), fields, workdate) df = process_msgs(data) df = pd.DataFrame.from_dict(df, orient='index') return positions.merge(df, left_on='bbg_id', right_index=True) -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') @@ -44,18 +43,46 @@ def backpopulate_marks(begin_str='2015-01-15', end_str='2015-07-15'): positions = positions.drop_duplicates() positions.to_sql('position', engine, if_exists='append', index=False) +def update_securities(session, fields): + securities = pd.read_sql_table("securities", engine) + securities['bbg_id'] = securities.cusip.where(securities.cusip.notnull(), securities['isin']) + \ + ' ' + securities.bbg_type + data = retreive_data(session, securities.bbg_id.tolist(), fields) + df = process_msgs(data) + df = pd.DataFrame.from_dict(df, orient='index') + return securities.merge(df, left_on='bbg_id', right_index=True) + +def populate_cashflow_history(session, conn, workdate): + securities = get_list(workdate) + securities['bbg_id'] = securities.cusip.where(securities.cusip.notnull(), securities['isin']) + \ + ' ' + securities.bbg_type + securities.set_index('bbg_id', inplace=True) + data = retreive_data(session, securities.index.tolist(), ['HIST_CASH_FLOW']) + df = process_msgs(data) + for k, v in df.items(): + to_insert = v.get('HIST_CASH_FLOW') + if to_insert is not None: + identifier = securities.loc[k,'identifier'] + to_insert['identifier'] = identifier + print(identifier) + with conn.cursor() as c: + c.execute("DELETE FROM cashflow_history WHERE identifier=%s", (identifier,)) + conn.commit() + to_insert.rename(columns={'Coupon': 'coupon', + 'Interest': 'interest', + 'Payment Date': 'date', + 'Principal Balance': 'principal_bal', + 'Principal Paid': 'principal'}, inplace=True) + to_insert[['identifier', 'date', 'principal_bal', 'principal', + 'interest','coupon']].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__": - session = init_bbg_session('192.168.0.4', 8194) - fields = ["START_ACC_DT", "MTG_FACTOR_PAY_DT", "CUR_CPN", "INT_ACC", - "DAYS_ACC", "MTG_FACTOR", "MTG_PREV_FACTOR", "MTG_FACTOR_PRINC_PAY", - "MTG_PRINC_LOSSES"] + engine = create_engine('postgresql://dawn_user@debian/dawndb') workdate = pd.datetime.today() - df = get_bbg_numbers(workdate, fields) - # df.to_csv("../../positions_daily/positions_{0:%Y-%m-%d}.csv".format(workdate)) - # df.rename(columns={'CUR_CPN': 'coupon', - # 'MTG_FACTOR': 'factor', - # 'price': - # conn = engine.raw_connection() - # with conn.cursor() as c: - # c.executemany("UPDATE position SET WHERE identifier= %(identifier)s - # with engine.raw_connenction.cursor() as c: + conn = engine.raw_connection() + with init_bbg_session('192.168.0.4', 8194) as session: + populate_cashflow_history(session, conn, workdate) |