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
|
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')
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
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))
|
