diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/import_quotes.py | 55 |
1 files changed, 29 insertions, 26 deletions
diff --git a/python/import_quotes.py b/python/import_quotes.py index cff077d2..67362aab 100644 --- a/python/import_quotes.py +++ b/python/import_quotes.py @@ -8,6 +8,7 @@ from pandas.tseries.offsets import BDay import pandas as pd import numpy as np import psycopg2 +from sqlalchemy import create_engine def convert(x): try: @@ -15,7 +16,7 @@ def convert(x): except ValueError: return None -index_list = ['HY9', 'HY10', 'HY15', 'HY17', 'HY19', 'HY21', 'HY22', 'HY23' +index_list = ['HY9', 'HY10', 'HY15', 'HY17', 'HY19', 'HY21', 'HY22', 'HY23', 'IG9', 'IG19', 'IG21', 'IG22', 'IG23', 'XO22'] def doc_clause_from_index(index, date): @@ -100,29 +101,30 @@ 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, %s, %s, %s, %s)" - cols = ['Composite Price', 'Composite Spread', 'Model Price', 'Model Spread'] + 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', + 'Model Price': 'modelprice', 'Model Spread': 'modelspread'} + 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(): data = pd.read_csv(f, skiprows=2, parse_dates=[0,7]) - data.dropna(subset=['Composite Price'], inplace=True) + data.rename(columns=colmapping, inplace=True) + data.dropna(subset=['closeprice'], inplace=True) data[cols] = data[cols].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(subset=['Name']) - data.set_index('Name', drop=False, inplace=True) - data['Composite Spread'] = data['Composite Spread']*100 - data['Model Spread'] = data['Model Spread']*100 + 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.set_index('index', drop=False, inplace=True) + data['closespread'] *= 100 + data['modelspread'] *= 100 ## we renumbered the version for HY9, 10 and 11 - data.loc[data.Series.isin([9, 10, 11]) & (data.Name=='HY'),'Version'] -= 3 - data['adjcloseprice']=data['Composite Price'] - data['adjmodelprice']=data['Model Price'] - cols += ['adjcloseprice', 'adjmodelprice'] - toinsert = [tuple(r) for r in data[["Date", "Name", "Series", "Version", "Term"]+cols].values] - with database.cursor() as c: - c.executemany(sqlstr, toinsert) - database.commit() + data.loc[data.series.isin([9, 10, 11]) & (data.index=='HY'),'version'] -= 3 + data['adjcloseprice'] = data['closeprice'] + data['adjmodelprice'] = data['modelprice'] + data[ext_cols].to_sql('index_quotes', engine, if_exists='append', index=False) if __name__=="__main__": if len(sys.argv)>=2: @@ -130,8 +132,9 @@ if __name__=="__main__": else: workdate = datetime.datetime.today()-BDay(1) workdate = workdate.date() - #insert_cds(serenitasdb, workdate) - # insert_index(serenitasdb, workdate) + engine = create_engine('postgresql://serenitas_user:Serenitas1@debian/serenitasdb') + insert_cds(serenitasdb, workdate) + insert_index(engine, workdate) ## backpopulate single ticker # company_id = 16632863 # with serenitasdb.cursor() as c: @@ -149,9 +152,9 @@ if __name__=="__main__": # mkt_tuple = (tup[1], tup[2]) + ('XR', '0.05', 'USD') # insert_cds_single(serenitasdb, workdate, bbg_tickers, mkt_tuple) # serenitasdb.close() - for f in os.listdir(os.path.join(root, "Tranche_data", "CDS")): - if f.endswith("csv"): - workdate = datetime.datetime.strptime(f.split(" ")[2].split(".")[0], "%Y%m%d") - workdate = workdate.date() - insert_cds(serenitasdb, workdate) + # for f in os.listdir(os.path.join(root, "Tranche_data", "CDS")): + # if f.endswith("csv"): + # workdate = datetime.datetime.strptime(f.split(" ")[2].split(".")[0], "%Y%m%d") + # workdate = workdate.date() + # insert_cds(serenitasdb, workdate) serenitasdb.close() |