diff options
| -rw-r--r-- | python/import_quotes.py (renamed from python/import_cds_quotes.py) | 28 |
1 files changed, 27 insertions, 1 deletions
diff --git a/python/import_cds_quotes.py b/python/import_quotes.py index a5328b21..d6c80475 100644 --- a/python/import_cds_quotes.py +++ b/python/import_quotes.py @@ -5,7 +5,8 @@ import datetime from db import serenitasdb import re, sys from pandas.tseries.offsets import BDay -import pdb +import pandas as pd +import numpy as np def convert(x): try: @@ -84,6 +85,29 @@ def insert_cds_single(database, workdate, bbgtickers, mkt_tuple): database.rollback() database.commit() +def insert_index(database, workdate): + 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] + + name_mapping = {"CDXNAHY":"HY", "CDXNAIG":"IG",'ITraxx Eur': "EU"} + sqlstr = "INSERT INTO index_quotes VALUES(%s, %s, %s, %s, %s, %s, %s)" + + for f in filenames: + if datetime.datetime.fromtimestamp(os.path.getctime(f)).date()==workdate+BDay(1): + data = pd.read_csv(f, skiprows=2, parse_dates=[0,7]) + data = data.dropna() + data[['Composite Price', 'Composite Spread']] = data[['Composite Price', 'Composite Spread']].applymap(lambda x: float(x[:-1]) if x.endswith('%') else x) + data['Term']=data['Term'].apply(lambda x: x.lower()+'r') + data['Name'] = data['Name'].apply(lambda x: name_mapping[x] if x in name_mapping else np.NaN) + data = data.dropna() + data = data.set_index('Name', drop=False) + data['Composite Spread'] = data['Composite Spread']*100 + toinsert = [tuple(r) for r in data[["Date", "Name", "Series", "Version", "Term", + "Composite Price", "Composite Spread"]].values] + with database.cursor() as c: + c.executemany(sqlstr, toinsert) + database.commit() + if __name__=="__main__": if len(sys.argv)>=2: workdate = datetime.datetime.strptime(sys.argv[1], "%Y-%m-%d") @@ -91,6 +115,8 @@ if __name__=="__main__": workdate = datetime.datetime.today()-BDay(1) workdate = workdate.date() insert_cds(serenitasdb, workdate) + insert_index(serenitasdb, workdate) + serenitasdb.close() ## backpopulate single ticker # bbgtickers = ['CT675194bis', 'CT406651bis', 'CT406655bis', 'CX404662bis', 'CX404666bis', # 'CX404670bis', 'CX404678bis', 'CX404690bis'] |