def get_worldscope_list_qaids(self, qaid, item, freq):
seccodes = []
regions = []
series_dict_g = dict()
series_dict_us = dict()
for q in qaid:
[seccode, region] = QADirect.get_sec_code_from_id(self.conn,q)
seccodes.append(seccode)
regions.append(region)
#build dict of seccodes and qaid for series dict creation below
seccode_dict = dict(zip(seccodes,qaid))
if 'G' in regions:
n_regions = np.array(regions)
ii = np.where(n_regions == 'G')[0]
qaids = list(seccodes[ii])
for q in qaids:
series_dict_g[q] = self.get_worldscope_region(q, item, freq,'G')
elif 'US' in regions:
indices = [i for i, x in enumerate(regions) if x == "US"]
#pull out seccodes which match US
s_codes = [seccodes[i] for i in indices]
for s in s_codes:
series_dict_us[seccode_dict[s]] = self.get_worldscope_region(s, item, freq,'US')
series_dict_us.update(series_dict_g)
return series_dict_us
def get_pricing_item(self, qaid, query):
#temp variable for switching below
item = query
try:
[seccode, region] = QADirect.get_sec_code_from_id(self.conn, qaid)
except QADNotFound as e:
print "Can't Find QAId: ", e.value
return
if item == 'close':
item = 'Close_'
elif item == 'open':
item = 'Open_'
if query in ['volume', 'consolvol', 'mosttrdvol']:
adjuster = '/CumAdjFactor'
else:
adjuster = "*CumAdjFactor * case when priceunit = 'E+02' then 100 else 1 end"
if region == 'US':
sql = '''select marketdate, ''' + item + adjuster + ''', q.ISOCurrCode
from Ds2PrimQtPrc q join secmapx m on
m.vencode = q.infocode and m.ventype = 33 and rank = 1
join ds2Adj a on a.infocode = q.infocode
and q.marketdate between adjdate and isnull(endadjdate, '2079-06-06') and AdjType = 2
join ds2exchqtinfo e on e.infocode = q.infocode
and e.startdate < q.marketdate and q.exchintcode = e.exchintcode
where m.seccode = ?'''
elif region == 'G':
sql = '''select marketdate, ''' + item + adjuster + ''', q.ISOCurrCode
from Ds2PrimQtPrc q join gsecmapx m on
m.vencode = q.infocode and m.ventype = 33 and rank = 1
join ds2Adj a on a.infocode = q.infocode
and q.marketdate between adjdate and isnull(endadjdate, '2079-06-06') and AdjType = 2
join ds2exchqtinfo e on e.infocode = q.infocode
and e.startdate < q.marketdate and q.exchintcode = e.exchintcode
where m.seccode = ?'''
cursor = self.conn.cursor()
cursor.execute(sql, seccode)
rows = cursor.fetchall()
s = QADirect.rows_to_series(rows)
#build dictionary to return
pricingitem_dict = dict()
pricingitem_dict['ds.' + str(query)] = s
return pricingitem_dict
def get_pricing_item(self, qaid, query):
#temp variable for switching below
item = query
try:
[seccode, region] = QADirect.get_sec_code_from_id(self.conn,qaid)
except QADNotFound as e:
print "Can't Find QAId: ", e.value
return
if item == 'close':
item = 'Close_'
elif item == 'open':
item = 'Open_'
if query in ['volume','consolvol', 'mosttrdvol']:
adjuster = '/CumAdjFactor'
else:
adjuster = "*CumAdjFactor * case when priceunit = 'E+02' then 100 else 1 end"
if region == 'US':
sql = '''select marketdate, ''' + item + adjuster + ''', q.ISOCurrCode
from Ds2PrimQtPrc q join secmapx m on
m.vencode = q.infocode and m.ventype = 33 and rank = 1
join ds2Adj a on a.infocode = q.infocode
and q.marketdate between adjdate and isnull(endadjdate, '2079-06-06') and AdjType = 2
join ds2exchqtinfo e on e.infocode = q.infocode
and e.startdate < q.marketdate and q.exchintcode = e.exchintcode
where m.seccode = ?'''
elif region == 'G':
sql = '''select marketdate, ''' + item + adjuster + ''', q.ISOCurrCode
from Ds2PrimQtPrc q join gsecmapx m on
m.vencode = q.infocode and m.ventype = 33 and rank = 1
join ds2Adj a on a.infocode = q.infocode
and q.marketdate between adjdate and isnull(endadjdate, '2079-06-06') and AdjType = 2
join ds2exchqtinfo e on e.infocode = q.infocode
and e.startdate < q.marketdate and q.exchintcode = e.exchintcode
where m.seccode = ?'''
cursor = self.conn.cursor()
cursor.execute(sql, seccode)
rows = cursor.fetchall()
s = QADirect.rows_to_series(rows)
#build dictionary to return
pricingitem_dict = dict()
pricingitem_dict['ds.'+str(query)] = s
return pricingitem_dict
def get_total_return(self, qaid):
[seccode, region] = QADirect.get_sec_code_from_id(self.conn, qaid)
if region == 'US':
sql = '''select marketdate, ri
from Ds2PrimQtRI q join secmapx m on
m.vencode = q.infocode and m.ventype = 33 and rank = 1
where m.seccode = ?'''
elif region == 'G':
sql = '''select marketdate, ri
from Ds2PrimQtRI q join gsecmapx m on
m.vencode = q.infocode and m.ventype = 33 and rank = 1
where m.seccode = ?'''
cursor = self.conn.cursor()
cursor.execute(sql, seccode)
rows = cursor.fetchall()
s = QADirect.rows_to_series(rows)
#build dictionary to return
totalreturn_dict = dict()
totalreturn_dict['ds.totalreturn'] = s
return totalreturn_dict
def get_total_return(self, qaid):
[seccode, region] = QADirect.get_sec_code_from_id(self.conn,qaid)
if region == 'US':
sql = '''select marketdate, ri
from Ds2PrimQtRI q join secmapx m on
m.vencode = q.infocode and m.ventype = 33 and rank = 1
where m.seccode = ?'''
elif region == 'G':
sql = '''select marketdate, ri
from Ds2PrimQtRI q join gsecmapx m on
m.vencode = q.infocode and m.ventype = 33 and rank = 1
where m.seccode = ?'''
cursor = self.conn.cursor()
cursor.execute(sql, seccode)
rows = cursor.fetchall()
s = QADirect.rows_to_series(rows)
#build dictionary to return
totalreturn_dict = dict()
totalreturn_dict['ds.totalreturn'] = s
return totalreturn_dict
def get_pricing_item_list(self, qaid, query):
[seccode, region] = QADirect.get_sec_code_from_id(self.conn, qaid)
if region == 'US':
sql = '''select marketdate, ''' + ' ,'.join(
query
) + '''*CumAdjFactor * case when priceunit = 'E+02' then 100 else 1 end, q.ISOCurrCode
from Ds2PrimQtPrc q join secmapx m on
m.vencode = q.infocode and m.ventype = 33 and rank = 1
join ds2Adj a on a.infocode = q.infocode
and q.marketdate between adjdate and isnull(endadjdate, '2079-06-06') and AdjType = 2
join ds2exchqtinfo e on e.infocode = q.infocode
and e.startdate < q.marketdate and q.exchintcode = e.exchintcode
where m.seccode = ?'''
elif region == 'G':
sql = '''select marketdate, ''' + ' ,'.join(
query
) + '''*CumAdjFactor * case when priceunit = 'E+02' then 100 else 1 end, q.ISOCurrCode
from Ds2PrimQtPrc q join gsecmapx m on
m.vencode = q.infocode and m.ventype = 33 and rank = 1
join ds2Adj a on a.infocode = q.infocode
and q.marketdate between adjdate and isnull(endadjdate, '2079-06-06') and AdjType = 2
join ds2exchqtinfo e on e.infocode = q.infocode
and e.startdate < q.marketdate and q.exchintcode = e.exchintcode
where m.seccode = ?'''
cursor = self.conn.cursor()
cursor.execute(sql, seccode)
rows = cursor.fetchdictarray()
df = pd.DataFrame(rows)
#Close_ column not listed
df = df.rename(columns={'': 'close'})
df = df.rename(columns={'Open_': 'open'})
df = df.set_index('marketdate')
#build dictionary to return
pricingitem_dict = dict()
pricingitem_dict['ds.' + 'ohlc'] = df
return pricingitem_dict
def get_worldscope_currency(self, qaid):
[seccode, region] = QADirect.get_sec_code_from_id(self.conn,qaid)
if region == 'US':
sql = '''select isoused
from wscurr c join wsinfo i on c.natcode = i.country
join secmapx m on m.vencode = i.code and m.ventype = 10 and rank = 1
where seccode = ?'''
elif region == 'G':
sql = '''select isoused
from wscurr c join wsinfo i on c.natcode = i.country
join gsecmapx m on m.vencode = i.code and m.ventype = 10 and rank = 1
where seccode = ?'''
cursor = self.conn.cursor()
cursor.execute(sql, seccode)
rows = cursor.fetchall()
return rows[0][0]
def get_worldscope(self, qaid, item, freq):
[seccode, region] = QADirect.get_sec_code_from_id(self.conn,qaid)
if region == 'US':
sql = '''select d.year_, d.seq, d.date_, value_, f.date_ from wsndata d
join secmapx m on m.ventype = 10 and m.vencode = d.code and rank = 1
left outer join wsfye f on f.code = d.code and f.year_ = d.year_
where m.seccode = ? and item = ? and freq = ?'''
elif region == 'G':
sql = '''select d.year_, d.seq, d.date_, value_, f.date_ from wsndata d
join gsecmapx m on m.ventype = 10 and m.vencode = d.code and rank = 1
left outer join wsfye f on f.code = d.code and f.year_ = d.year_
where m.seccode = ? and item = ? and freq = ?'''
cursor = self.conn.cursor()
cursor.execute(sql, seccode, item, freq)
rows = cursor.fetchall()
val = dict()
if freq == 'Q':
for row in rows:
if row[2] is None and row[4] is not None:
if row[1] == 4:
row[2] = row[4]
elif row[1] == 3:
row[2] = row[4] + relativedelta(months=-3)
elif row[1] == 2:
row[2] = row[4] + relativedelta(months=-6)
elif row[1] == 1:
row[2] = row[4] + relativedelta(months=-9)
lastfy = row[4]
elif row[2] is None and row[4] is None:
row[2] = lastfy + relativedelta(months=3*row[1])
val[row[2]] = row[3]
elif freq == 'A':
for row in rows:
if row[2] is None and row[4] is not None:
if row[1] == 1:
row[2] = row[4]
val[row[2]] = row[3]
#build dictionary to return
series_dict = dict()
series_dict['ws.'+str(item)] = pd.Series(val)
return series_dict
def get_pricing_item_list(self, qaid, query):
[seccode, region] = QADirect.get_sec_code_from_id(self.conn,qaid)
if region == 'US':
sql = '''select marketdate, ''' + ' ,'.join(query) + '''*CumAdjFactor * case when priceunit = 'E+02' then 100 else 1 end, q.ISOCurrCode
from Ds2PrimQtPrc q join secmapx m on
m.vencode = q.infocode and m.ventype = 33 and rank = 1
join ds2Adj a on a.infocode = q.infocode
and q.marketdate between adjdate and isnull(endadjdate, '2079-06-06') and AdjType = 2
join ds2exchqtinfo e on e.infocode = q.infocode
and e.startdate < q.marketdate and q.exchintcode = e.exchintcode
where m.seccode = ?'''
elif region == 'G':
sql = '''select marketdate, ''' + ' ,'.join(query) + '''*CumAdjFactor * case when priceunit = 'E+02' then 100 else 1 end, q.ISOCurrCode
from Ds2PrimQtPrc q join gsecmapx m on
m.vencode = q.infocode and m.ventype = 33 and rank = 1
join ds2Adj a on a.infocode = q.infocode
and q.marketdate between adjdate and isnull(endadjdate, '2079-06-06') and AdjType = 2
join ds2exchqtinfo e on e.infocode = q.infocode
and e.startdate < q.marketdate and q.exchintcode = e.exchintcode
where m.seccode = ?'''
cursor = self.conn.cursor()
cursor.execute(sql, seccode)
rows = cursor.fetchdictarray()
df = pd.DataFrame(rows)
#Close_ column not listed
df = df.rename(columns={'':'close'})
df = df.rename(columns={'Open_':'open'})
df = df.set_index('marketdate')
#build dictionary to return
pricingitem_dict = dict()
pricingitem_dict['ds.'+'ohlc'] = df
return pricingitem_dict
def get_worldscope_list_items(self, qaid, meas_list, freq):
[seccode, region] = QADirect.get_sec_code_from_id(self.conn,qaid)
if region == 'US':
sql = '''select d.year_, d.seq, d.date_, value_, f.date_ from wsndata d
join secmapx m on m.ventype = 10 and m.vencode = d.code and rank = 1
left outer join wsfye f on f.code = d.code and f.year_ = d.year_
where m.seccode = ? and item in (%s) and freq = ?'''
elif region == 'G':
sql = '''select d.year_, d.seq, d.date_, value_, f.date_ from wsndata d
join secmapx m on m.ventype = 10 and m.vencode = d.code and rank = 1
left outer join wsfye f on f.code = d.code and f.year_ = d.year_
where m.seccode = ? and item in (%s) and freq = ?'''
#fill sql with ? to be replaced by list of qaid, measurements and
#freqency
measures = ', '.join('?' for meas in meas_list)
sql = sql % measures
sql_items = copy.deepcopy(meas_list)
cursor = self.conn.cursor()
#prepend with security code of qaid
sql_items.insert(0,seccode)
sql_items.append(freq)
#define cursor for query
cursor.execute(sql, sql_items)
#query returned as big list. need to pull out individual
#measurments change to fetchdictarray for more verbose indexing
rows = cursor.fetchall()
#parse items based on inflection of date
date = rows[0][0]
qaid_pts = []
series_dict = dict()
val = dict()
i = 0
if freq == 'Q':
for row in rows:
#date montonically increasing
if row[0] >= date:
date = row[0]
#inflection of date. create series and store in dictionary
else:
#modify key for consistency with API usage
#+1 offset for prepended security id
series_dict['ws.'+str(sql_items[i+1])] = pd.Series(val)
i += 1
val = dict()
date = row[0]
if row[2] is None and row[4] is not None:
if row[1] == 4:
row[2] = row[4]
elif row[1] == 3:
row[2] = row[4] + relativedelta(months=-3)
elif row[1] == 2:
row[2] = row[4] + relativedelta(months=-6)
elif row[1] == 1:
row[2] = row[4] + relativedelta(months=-9)
lastfy = row[4]
elif row[2] is None and row[4] is None:
row[2] = lastfy + relativedelta(months=3*row[1])
val[row[2]] = row[3]
elif freq == 'A':
for row in rows:
if row[2] is None and row[4] is not None:
if row[1] == 1:
row[2] = row[4]
val[row[2]] = row[3]
series_dict['ws.'+str(sql_items[i+1])] = pd.Series(val)
return series_dict
