def _get_data(self, universe, bql_factors):
"""
Retrieve data model based on the universe and the BQL items
Inputs:
- universe (bqlItem object): bql universe item object to request
factor data on. Eg. bq.univ.members() or bq.univ.equitiesuniv() or list()
- bql_factors (dict): bql data item object to request
data on. eg. bq.data.pe_ratio() or bq.data.px_last().std()
"""
# request data to BQL
try:
self._debug_query = bql.Request(universe, bql_factors)
r = self._bq.execute(bql.Request(universe, bql_factors))
# store the whole dataset in data
data = pd.DataFrame()
# capture each dataset from the response
# by using the keys of the bql_factors
for k in bql_factors.keys():
temp = r.get(k).df()[k]
# transform the series type to float when applicable
if k in set(self._float_fields_only):
temp.dropna().astype('float64')
data = pd.concat([data, temp], join='outer', axis=1)
except Exception as e:
_logger.error('Error while fetching data ({})'.format(e))
data = pd.DataFrame()
return data
def get_data(analysis_universe, factors, parameters):
factor_dict = OrderedDict()
weights_df = pd.DataFrame()
for x, y in factor_model[factors].items():
factor_dict[x] = y['expression']
weights_df = weights_df.append(
pd.DataFrame({
'name': [x],
'weights': [y['weights']]
}))
# Create the request
weights_df = weights_df.set_index('name')
request = bql.Request(universe.get(analysis_universe),
factor_dict,
with_params=parameters)
# Execute the request object.
response = bq.execute(request)
# Return a combined DataFrame.
response_df_concat = pd.concat([item.df() for item in response], axis=1)
# Calculate aggregate score and then create a percent rank
output_df = response_df_concat[list(factor_dict)].fillna(value=0)
output_df['Factor Score'] = output_df.dot(weights_df)
output_df['% Rank'] = output_df['Factor Score'].rank(pct=True).fillna(
value=0)
return output_df
def get_single_field_request(connection,
universe,
field,
field_name,
with_params=None,
preferences=None):
if with_params is None:
with_params = {}
if preferences is None:
preferences = {}
request = bql.Request(universe, {field_name: field},
with_params=with_params,
preferences=preferences)
responses = connection.execute(request)
return responses[0].df()
def get_score_data(connection,
universe,
fields,
with_params=None,
preferences=None):
if with_params is None:
with_params = {}
if preferences is None:
preferences = {}
request = bql.Request(universe,
fields,
with_params=with_params,
preferences=preferences)
responses = connection.execute(request)
df = pd.DataFrame({
response.name: response.df()[response.name]
for response in responses
})[[f for f in fields.keys()]]
return df.applymap(format_floats)
def _get_history(self, ids, field_name, history_period='-1y'):
# get the field in bql_item
bql_item = self._build_factors(user_selection=field_name)
try:
r = self._bq.execute(
bql.Request(ids,
bql_item,
with_params={'start': history_period}))
# store the whole dataset in data
data = r.single().df()
# pivot the data for better display
data = data.reset_index().pivot_table(index='DATE',
columns='ID',
values=field_name)
except Exception as e:
_logger.error('Error while fetching timeseries ({})'.format(e))
data = pd.DataFrame()
return data
# Import the BQL library
import bql
# Instantiate an object to interface with the BQL service
bq = bql.Service()
# Define the a data field
price = bq.data.px_last()
# Use the members function to define the universe
# as all members of the INDU Index
univ = bq.univ.members('INDU Index')
# Define the request
request = bql.Request(univ, price)
# Execute the request
response = bq.execute(request)
# Display the response in a DataFrame
response[0].df()
#String Interface Example (Fixed Income)
# Import the BQL library
import bql
# Instantiate an object to interface with the BQL service
bq = bql.Service()
#Object Model Example
# Import the BQL library
import bql
# Instantiate an object to interface with the BQL service
bq = bql.Service()
# Define the data fields
px_last = bq.data.px_last()
date_range = bq.func.range('-10D','0D')
px_avg = bq.data.px_last(start='-10D').avg()
# Define the universe
univ = bq.univ.members('INDU Index')
# Define the if clause to return the last traded price if the last trade
# is higher than the trailing 10D average price,
# otherwise return the text 'Below 10D Average'
return_if = bq.func.if_((px_last > px_avg), px_last, 'Below 10D Average')
# Execute the request
request = bql.Request(univ, return_if)
response = bq.execute(request)
# Display the response in a DataFrame
response[0].df()
bq = bql.Service()
# Define the request string and fill Value
request = "get(PX_LAST(dates=RANGE(2018-01-01,2018-01-10), fill='prev')) for (['EBAY US Equity'])"
# Execute the request
response = bq.execute(request)
# Display the response in a DataFrame
response[0].df()
#Object Model Example
# Import the BQL library
import bql
# Instantiate an object to interface with the BQL service
bq = bql.Service()
# Define the request object and the fill value
date_range = bq.func.range('2018-01-01', '2018-01-10')
field = bq.data.px_last(dates=date_range, fill='next')
ticker = 'EBAY US Equity'
# Execute the request
request = bql.Request(ticker, field)
response = bq.execute(request)
# Display the response in a DataFrame
response[0].df()
# Create the database connection
bq = bql.Service()
# Define a list of securities for the request universe
universe = ['IBM US Equity', 'AAPL US Equity']
# Define the date range for the request
date_range = bq.func.range('2015-01-01', '2015-12-31')
# Define a data item for the last price field,
# passing the date range and frequency parameters
price = bq.data.px_last(dates=date_range, frq="D")
# Generate the request using the security universe and data item
request = bql.Request(universe, price)
# Execute the request
response = bq.execute(request)
# Convert the response to a DataFrame and
# display the first five rows to check the results
dataframe = response[0].df()
dataframe.head(5)
# Pass a dictionary to the rename() function
# to modify the default column names
dataframe.rename(columns={
"DATE":
"Date",
"CURRENCY":
# Import the BQL library import bql # Instantiate an object to interface with the BQL service bq = bql.Service() # Define a variable for the security security = "AAPL US Equity" # Define a data item for the last price field # In this example, we're not passing any arguments last = bq.data.px_last() # Generate the request using the sercurity variable and data item request = bql.Request(security, last) # Execute the request response = bq.execute(request) # Display the response in a DataFrame response[0].df() #Example 2: Requesting Multiple Data Items #=========================================================================== # Import the BQL library import bql # Instantiate an object to interface with the BQL service bq = bql.Service()
request = "get(DROPNA(PX_LAST(dates=RANGE(2018-01-01,2018-01-10)))) for(['IBM US Equity'])"
# Execute the request
response = bq.execute(request)
# Display the response in a data frame
response[0].df()
#Object Model Example
# Import the BQL library
import bql
# Instantiate an object to interface with the BQL service
bq = bql.Service()
# Define the date range
date_range = bq.func.range('2018-01-01', '2018-01-10')
# Define the request ticker, start, and end date
px_last = bq.data.px_last(dates=date_range)
px_last_dropped_na = bq.func.dropna(px_last)
ticker = 'IBM US Equity'
# Execute the request
request = bql.Request(ticker, px_last_dropped_na)
response = bq.execute(request)
# Display the response in a data frame
response[0].df()
fa_period_type='LTM', currency='GBP')
# Define a composite BQL data item with each BQL Item component of the 2-year average ROE
bq_avg_roe_2y = (bq.data.return_com_eqy(fa_period_offset='-1') +
bq.data.return_com_eqy(fa_period_offset='-2')) / 2
# Define a BQL data item for trailing twelve-month operating cash flow and total assets
cash_from_oper = bq.data.cf_cash_from_oper(fa_period_type='LTM')
tot_asset = bq.data.bs_tot_asset(fa_period_type='LTM')
# Define the factor by using the defined data items
bq_cash_per_asset = cash_from_oper / tot_asset
# Request the number of the index members using BQL count function
id_count = bq.data.id().group().count()
bq_res = bq.execute(bql.Request(bq_univ, {'COUNT': id_count}))
num_of_members = bq_res[0].df()['COUNT']
# Define the threshold rank value which is equvalent to the percentile rank of 80%
threshold_percentage = 0.80
threshold_rank = int(threshold_percentage * num_of_members)
# Define a function to calculate the rank of BQL item
def rank_func(factor):
return factor.group().znav().rank().ungroup().applyPreferences(
Currencycheck="ignore")
# Define the rank of the factor by using the custom rank function
factor_ranks = [
# Instantiate an object to interface with the BQL service
bq = bql.Service()
# Define a variable for the security universe
univ = ['WMT US Equity', 'PG US Equity', 'KO US Equity']
# Define a data item for fcf yield
fcf_yield = bq.data.free_cash_flow_yield(fill='prev')
# Define a data item for average fcf yield for the group
#=======================================================================
#avg_fcf_yield = bq.data.free_cash_flow_yield(fill='prev').group().avg()
# Generate the request using the universe variable and data item
request = bql.Request(univ, fcf_yield)
# Execute the request
response = bq.execute(request)
# Display the response in a DataFrame
response[0].df()
#Grouping by Sector
'''
univ = bq.univ.members('INDU Index')
# Define a data item for the grouping field
grouping_item = bq.data.gics_sector_name()
response = bq.execute(request)
# Display the response in a DataFrame
response[0].df()
#Object Model Example
# Import the BQL library
import bql
# Instantiate an object to interface with the BQL service
bq = bql.Service()
#Set the Field
price = bq.data.px_last()
# Set the universe and the function
univ = bq.univ.members('INDU Index')
# Define and apply the filter criteria to only return results
# that have a PX_LAST value greater than or equal to 100
criteria = bq.data.px_last() >= 100
filtered_univ = bq.univ.filter(univ, criteria)
# Execute the request
request = bql.Request(filtered_univ, price)
response = bq.execute(request)
# Display the response in a DataFrame
response[0].df()
import bql
bq = bql.Servive()
time_range = bq.func.range('2017-01-01','2019-08-08')
security = ['ALI PM Equity']
last = bq.data.px_last(dates=time_range)
high = bq.data.px_high(dates=time_range)
low =bq.data.px_low(dates=time_range)
request = bql.Request(security,[last,high,low])
response = bq.execute(request)
df = response[0].df()
df = df.dropna()
df['ALI PRICES'] = df['PX_LAST()dates=RANGE(2017-01-01,2019-08-08)']
del df['PX_LAST()dates=RANGE(2017-01-01,2019-08-08)']
df.head()
security_sm = ['SMPH PM Equity']
df_sm['SMPH PRICES'] = df_sm['PX_LAST()dates=RANGE(2017-01-01,2019-08-08)']
del df_sm['PX_LAST()dates=RANGE(2017-01-01,2019-08-08)']
df_sm.head()
bq = bql.Service()
# Define the request string with the relative dates
request = "get(PX_LAST(dates=RANGE(-2Y,0D),per='Q')) for (['VOD LN Equity'])"
# Execute the request
response = bq.execute(request)
# Display the response in a DataFrame
response[0].df()
#Object Model Example
# Import the BQL library
import bql
# Instantiate an object to interface with the BQL service
bq = bql.Service()
# Define the request object with the relative date
date_range = bq.func.range('-2Y', '0D')
last = bq.data.px_last(dates=date_range, per='Q')
ticker = 'VOD LN Equity'
# Execute the request
request = bql.Request(ticker, last)
response = bq.execute(request)
# Display the response in a DataFrame
response[0].df()
response = bq.execute(request)
# Display the response in a data frame
response[0].df()
#Object Model Example
# Import the BQL library
import bql
# Instantiate an object to interface with the BQL service
bq = bql.Service()
# Define the field: PX_LAST the start and end dates
date_range = bq.func.range('2016-01-01', '2017-12-31')
px_last = bq.data.px_last(dates=date_range)
# Define the universe
univ = bq.univ.members('INDU Index')
# Define the request object to return the lowest value of px_last
# for the specified date range for the members of the INDU Index
func_max = bq.func.min(px_last)
# Execute the request
request = bql.Request(univ, func_max)
response = bq.execute(request)
# Display the response in a data frame
response[0].df()
#The Object Model
#===========================================
# The python model pulling out the 20-day average price for SP500 members
# Define px (i.e. price) as a price series for last 20 days
px = bq.data.px_last(dates=bq.fumc.range('-20d', '0d'))
# Define the avg_px by applying function avg to the time series px
avg_px = bq.func.avg(px)
# Define the universe as index members of SP500
index_memb = bq.univ.members('SPX Index')
# Create the request
request = bql.Request(index_memb, {'avg_px': avg_px}, with_params={'currency': 'USD'})
# Execute the request
response = bq.execute(request)
# combined_df os a utility function to convert response object into panda's DataFrame
data = bql.combined_df(response)
# The String Interface
#===========================================
# The query pulling out the 20-day average price for SP500 members
q_str = """
let(#avg_px = avg(px_last(dates=range(-20d, 0d)));)
get(#avg_px)
# Execute the request response = bq.execute(request) # Display the response in a DataFrame response[0].df() #Object Model Example # Import the BQL library import bql # Instantiate an object to interface with the BQL service bq = bql.Service() # define the data fields and use mathematical operators # to calculate the bid/ask spread ask = bq.data.px_ask() bid = bq.data.px_bid() spread = ask - bid # Define the security tickers = 'GE US Equity' # Execute the request request = bql.Request(tickers, spread) response = bq.execute(request) # Display the response in a DataFrame response[0].df()
sector = bq.data.gics_sector_name() pe_ratio = bq.data.pe_ratio() sector_avg_pe_ratio = pe_ratio.groupavg(sector) criteria_3 = pe_ratio < sector_avg_pe_ratio #*********************Create a list of the criteria using the and_() function***************************** criteria_list = bq.func.and_(criteria_1,criteria_2).and_(criteria_3) # Define the filtered universe filtered_univ = bq.univ.filter(start_univ, criteria_list) # Define a data item to return with the security results mkt_cap = bq.data.cur_mkt_cap(currency='USD') # Generate the request using the filtered universe and data item request = bql.Request(filtered_univ, mkt_cap) # Execute the request response = bq.execute(request) # Display the response in a DataFrame response[0].df() #Example B: Filtering an Issuer's Bonds # Import the BQL library import bql # Instantiate an object to interface with the BQL service bq = bql.Service()