diff options
Diffstat (limited to 'python/markit')
| -rw-r--r-- | python/markit/import_quotes.py | 71 |
1 files changed, 48 insertions, 23 deletions
diff --git a/python/markit/import_quotes.py b/python/markit/import_quotes.py index edcfce0b..5bdd289c 100644 --- a/python/markit/import_quotes.py +++ b/python/markit/import_quotes.py @@ -19,7 +19,7 @@ def convert(x): def get_index_list(database, workdate): with database.cursor() as c: - c.execute("SELECT distinct index, series FROM index_maturity " \ + c.execute("SELECT distinct index, series FROM index_maturity " "WHERE issue_date IS NOT NULL and issue_date <= %s + 10", (workdate,)) for index, series in c: @@ -42,7 +42,7 @@ def get_markit_bbg_mapping(database, basketid_list, workdate): markit_bbg_mapping = defaultdict(set) all_tickers = set([]) with database.cursor() as c: - c.execute("SELECT markit_ticker, markit_tier, spread, currency, cds_curve, " \ + c.execute("SELECT markit_ticker, markit_tier, spread, currency, cds_curve, " " doc_clause FROM historical_cds_issuers(%s) where index_list && %s", (workdate, list(basketid_list))) for line in c: @@ -110,6 +110,14 @@ def insert_cds(database, workdate): database.commit() logger.warning('missing_quotes for {0}'.format(all_tickers-tickers_found)) +def get_date(f): + with open(f) as fh: + next(fh) + next(fh) + next(fh) + date = next(fh).split(",", 1)[0][1:-1] + return datetime.datetime.strptime(date, "%d-%b-%y").date() + def insert_index(engine, workdate=None): """insert Markit index quotes into the database @@ -120,32 +128,49 @@ def insert_index(engine, workdate=None): basedir = os.path.join(os.environ['BASE_DIR'], 'Tranche_data', 'Composite_reports') filenames = [os.path.join(basedir, f) for f in os.listdir(basedir) if 'Indices' in f] - name_mapping = {"CDXNAHY":"HY", "CDXNAIG":"IG",'iTraxx Eur': "EU", 'iTraxx Eur Xover': "XO"} + name_mapping = {"CDXNAHY": "HY", + "CDXNAIG": "IG", + 'iTraxx Eur': "EU", + 'iTraxx Eur Xover': "XO"} cols = ['closeprice', 'closespread', 'modelprice', 'modelspread'] - colmapping={'Date':'date', 'Name': 'index', 'Series': 'series', 'Version': 'version', - 'Term': 'tenor', 'Composite Price': 'closeprice', 'Composite Spread': 'closespread', + colmapping={'Date': 'date', 'Name': 'index', 'Series': 'series', + 'Version': 'version', 'Term': 'tenor', + 'Composite Price': 'closeprice', + 'Composite Spread': 'closespread', 'Model Price': 'modelprice', 'Model Spread': 'modelspread'} ext_cols = ['date', 'index', 'series', 'version', 'tenor'] + cols + \ ['adjcloseprice', 'adjmodelprice'] + + dates_to_files = {} + for f in filenames: + d = get_date(f) + if d in dates_to_files: + dates_to_files[d].append(f) + else: + dates_to_files[d] = [f] + + if workdate is None: + filenames = dates_to_files[max(dates_to_files.keys())] + else: + filenames = dates_to_files[workdate] + for f in filenames: - if workdate is None or \ - datetime.datetime.fromtimestamp(os.path.getmtime(f)).date()==(workdate+BDay(1)).date(): - data = pd.read_csv(f, skiprows=2, parse_dates=[0,7], engine='python') - data = data.rename(columns=colmapping) - data.dropna(subset=['closeprice'], inplace=True) - for col in cols: - data[col] = data[col].str.replace('%', '').astype('float') - data['tenor'] = data['tenor'].apply(lambda x: x.lower()+'r') - data['index'] = data['index'].apply(lambda x: name_mapping[x] if x in name_mapping else np.NaN) - data = data.dropna(subset=['index']) - data['closespread'] *= 100 - data['modelspread'] *= 100 - ## we renumbered the version for HY9, 10 and 11 - data.loc[data.series.isin([9, 10, 11]) & (data.index=='HY'),'version'] -= 3 - data['adjcloseprice'] = data['closeprice'] - data['adjmodelprice'] = data['modelprice'] - data = data.groupby(['index', 'series', 'tenor', 'date'], as_index=False).last() - data[ext_cols].to_sql('index_quotes', engine, if_exists='append', index=False) + data = pd.read_csv(f, skiprows=2, parse_dates=[0, 7], engine='python') + data = data.rename(columns=colmapping) + data.dropna(subset=['closeprice'], inplace=True) + for col in cols: + data[col] = data[col].str.replace('%', '').astype('float') + data['tenor'] = data['tenor'].apply(lambda x: x.lower()+'r') + data['index'] = data['index'].apply(lambda x: name_mapping[x] if x in name_mapping else np.NaN) + data = data.dropna(subset=['index']) + data['closespread'] *= 100 + data['modelspread'] *= 100 + ## we renumbered the version for HY9, 10 and 11 + data.loc[data.series.isin([9, 10, 11]) & (data.index=='HY'), 'version'] -= 3 + data['adjcloseprice'] = data['closeprice'] + data['adjmodelprice'] = data['modelprice'] + data = data.groupby(['index', 'series', 'tenor', 'date'], as_index=False).last() + data[ext_cols].to_sql('index_quotes', engine, if_exists='append', index=False) def insert_tranche(engine, workdate=None): """insert Markit index quotes into the database |