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