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()