4 files changed, 47 insertions, 4 deletions

diff --git a/python/bbg_helpers.py b/python/bbg_helpers.py
index c63b0730..6c524aae 100644
--- a/python/bbg_helpers.py
+++ b/python/bbg_helpers.py
@@ -58,7 +58,11 @@ def event_loop(session, request):
 
 def get_pythonvalue(e):
     if e.datatype() in [blpapi.DataType.DATE, blpapi.DataType.DATETIME]:
-        return pd.to_datetime(e.getValue())
+        t = e.getValue()
+        if isinstance(t, datetime.time):
+            return t
+        else:
+            return pd.to_datetime(t)
     elif e.datatype() == blpapi.DataType.ENUMERATION:
         return e.getValueAsString()
     else:
@@ -136,7 +140,7 @@ def retrieve_data(session, securities, fields=[], overrides={},
         else:
             request.set("security", securities)
     else:
-        if isinstance(securities, list):
+        if hasattr(securities, '__iter__'):
             for security in securities:
                 request.append("securities", security)
         else:
diff --git a/python/markit/import_quotes.py b/python/markit/import_quotes.py
index 2b7ef57c..560d683c 100644
--- a/python/markit/import_quotes.py
+++ b/python/markit/import_quotes.py
@@ -6,6 +6,7 @@ import pandas as pd
 import os
 
 from collections import defaultdict
+from itertools import chain
 from pandas.tseries.offsets import BDay
 
 logger = logging.getLogger(__name__)
@@ -58,6 +59,14 @@ def get_markit_bbg_mapping(database, basketid_list, workdate):
     database.commit()
     return (all_tickers, markit_bbg_mapping)
 
+def get_bbg_tickers(database, basketid_list, workdate):
+    with database.cursor() as c:
+        c.execute("SELECT cds_curve FROM historical_cds_issuers(%s) " \
+                  "WHERE index_list && %s",
+                  (workdate, list(basketid_list)))
+        yield from chain.from_iterable(e[0] for e in c)
+    database.commit()
+
 def get_basketids(database, index_list, workdate):
     with database.cursor() as c:
         for index in index_list:
diff --git a/python/test_bbg_cds_quotes.py b/python/test_bbg_cds_quotes.py
new file mode 100644
index 00000000..74cd1aeb
--- /dev/null
+++ b/python/test_bbg_cds_quotes.py
@@ -0,0 +1,30 @@
+from bbg_helpers import BBG_IP, init_bbg_session, retrieve_data
+from markit.import_quotes import get_index_list, get_basketids, get_bbg_tickers
+from db import dbconn
+import datetime
+
+workdate = datetime.date.today()
+serenitasdb = dbconn('serenitasdb')
+index_list = get_index_list(serenitasdb, workdate)
+basket_ids = get_basketids(serenitasdb, index_list, workdate)
+bbg_tickers = set(get_bbg_tickers(serenitasdb, basket_ids, workdate))
+
+securities = [f'{ticker} Curncy' for ticker in bbg_tickers]
+fields = ['UPFRONT_RUNNING_SPREAD', 'UPFRONT_BID', 'UPFRONT_ASK', 'CDS_RECOVERY_RT']
+
+r = {}
+with init_bbg_session(BBG_IP) as session:
+    for pcs in ['CBGN', 'CMAN']: #, 'CMAN', 'MSG1']:
+        r[pcs] = retrieve_data(session, securities, fields, overrides={'PRICING_SOURCE': pcs})
+
+sql_str = f"INSERT INTO cds_quotes VALUES({','.join(['%s'] * 8)})"
+
+def build_tuple(k, v, workdate, source):
+    return (workdate, k.split()[0], v.get('UPFRONT_BID'),
+            v.get('UPFRONT_ASK'), v.get('UPFRONT_RUNNING_SPREAD'),
+            v.get('UPFRONT_RUNNING_SPREAD'), source, v.get('CDS_RECOVERY_RT'))
+
+for pcs in ['CBGN', 'CMAN']:
+    with serenitasdb.cursor() as c:
+        c.executemany(sql_str, [build_tuple(k, v, workdate, pcs) for k, v in r[pcs].items()])
+    serenitasdb.commit()
diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql
index 2660eab1..5b6f6990 100644
--- a/sql/serenitasdb.sql
+++ b/sql/serenitasdb.sql
@@ -197,7 +197,7 @@ CREATE TABLE IF NOT EXISTS RedPairMapping(
 CREATE TYPE curr AS ENUM('USD', 'EUR', 'JPY', 'GBP', 'CAD');

 CREATE TYPE sen AS ENUM('Senior', 'Subordinated');

 CREATE TYPE tier AS ENUM('SNRFOR', 'SECDOM', 'SUBLT2', 'PREFT1', 'JRSUBUT2');

-CREATE TYPE bbgSource AS ENUM('MSG1', 'CBIN', 'CBGN', 'MKIT');

+CREATE TYPE bbgSource AS ENUM('MSG1', 'CBIN', 'CBGN', 'MKIT', 'CMAN');

 CREATE TYPE DocClause AS ENUM('No Restructuring', 'Modified Modified Restructurin', 'Full Restructuring');

 CREATE TYPE tenor AS ENUM('6mo', '1yr', '2yr', '3yr', '4yr', '5yr', '7yr', '10yr');

 CREATE TYPE index_type AS ENUM('IG', 'HY', 'EU', 'LCDX', 'XO');

@@ -252,7 +252,7 @@ CREATE TABLE IF NOT EXISTS cds_quotes(
        RunningAsk float,

        Source bbgSource,

        Recovery float,

-       PRIMARY KEY(curve_ticker, Date));

+       PRIMARY KEY(curve_ticker, Date, Source));

 

 GRANT ALL ON cds_quotes TO serenitas_user;

 CREATE INDEX IF NOT EXISTS cds_quotes_date_index ON cds_quotes(date);