diff options
Diffstat (limited to 'python/import_quotes.py')
| -rw-r--r-- | python/import_quotes.py | 52 |
1 files changed, 50 insertions, 2 deletions
diff --git a/python/import_quotes.py b/python/import_quotes.py index bac610b0..0e053dd4 100644 --- a/python/import_quotes.py +++ b/python/import_quotes.py @@ -69,6 +69,12 @@ def get_current_tickers(database, workdate): return get_markit_bbg_mapping(database, basketid_list, workdate) def insert_cds(database, workdate): + """insert Markit index quotes into the database + + :param database: psycopg2 connection to the database. + :param workdate: + """ + all_tickers, markit_bbg_mapping = get_current_tickers(database, workdate) filename = "cds eod {0:%Y%m%d}.csv".format(workdate) colnames = ['Upfront'+tenor for tenor in ['6m', '1y', '2y', '3y', '4y', '5y', '7y', '10y']] @@ -95,7 +101,13 @@ def insert_cds(database, workdate): database.commit() print(all_tickers-tickers_found) -def insert_index(engine, workdate): +def insert_index(engine, workdate=None): + """insert Markit index quotes into the database + + :param engine: sqlalchemy engine to the database + :param workdate: date. If None, we will try to reinsert all files + """ + basedir = os.path.join(root, 'Tranche_data', 'Composite_reports') filenames = [os.path.join(basedir, f) for f in os.listdir(basedir) if 'Indices' in f] @@ -107,7 +119,8 @@ def insert_index(engine, workdate): ext_cols = ['date', 'index', 'series', 'version', 'tenor'] + cols + \ ['adjcloseprice', 'adjmodelprice'] for f in filenames: - if datetime.datetime.fromtimestamp(os.path.getmtime(f)).date()==(workdate+BDay(1)).date(): + 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.rename(columns=colmapping, inplace=True) data.dropna(subset=['closeprice'], inplace=True) @@ -127,6 +140,41 @@ def insert_index(engine, workdate): data.reset_index(inplace=True) 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 + + :param engine: sqlalchemy engine to the database + :param workdate: date. If None, we will try to reinsert all files + """ + + basedir = os.path.join(root, 'Tranche_data', 'Composite_reports') + filenames = [os.path.join(basedir, f) for f in os.listdir(basedir) if f.startswith('Tranche Composites')] + index_version = pd.read_sql_table("index_version", engine, index_col='redindexcode') + for f in filenames: + if workdate is None or \ + datetime.datetime.fromtimestamp(os.path.getmtime(f)).date()==(workdate+BDay(1)).date(): + df = pd.read_csv(f, skiprows=2, parse_dates=['Date']) + df.rename(columns={'Date':'quotedate', + 'Index Term':'tenor', + 'Attachment':'attach', + 'Detachment':'detach', + 'Tranche Upfront Bid': 'upfront_bid', + 'Tranche Upfront Mid': 'upfront_mid', + 'Tranche Upfront Ask': 'upfront_ask', + 'Index Price Mid': 'index_price', + 'Tranche Spread Mid': 'tranche_spread', + 'Red Code':'redindexcode'}, inplace=True) + df.attach = df.attach *100 + df.detach = df.detach * 100 + df.tranche_spread = df.tranche_spread*10000 + df.tenor = df.tenor.str.lower() + 'r' + df.set_index('redindexcode', inplace=True) + df = df.join(index_version) + df = df.filter(['basketid', 'quotedate', 'tenor', 'attach', 'detach', + 'upfront_bid', 'upfront_ask', 'upfront_mid', + 'tranche_spread', 'index_price']) + df.to_sql('markit_tranche_quotes', engine, if_exists='append', index=False) + if __name__=="__main__": if len(sys.argv)>=2: workdate = datetime.datetime.strptime(sys.argv[1], "%Y-%m-%d") |