This is a work in progress. I will keep updating.

Intro

This post won’t exhaustively explain BQL; it’ll cover the things I wish I had known when I started.

BQL is a declarative query language that can be written in a few different ways. I recommend learning the query language itself, which will allow you to easily take the same query and run it in different places:

• Excel**
• BQuant Enterprise
• BQuant Desktop
• and, open a helpdesk ticket**

* In Excel, it’s as easy as =@BQL.QUERY("get(name) for(members('SPX Index'))"). The query itself get(name) for(members('SPX Index')) can be run in BQuant with no changes.

** Keep it simple. If it’s a BQL question, then isolate the BQL and send just the relevant BQL. No reason to include Python code.

Shape of BQL

BQL has up to five clauses.

• let(): Allows you to predefine and reuse a variable
• get(): Defines what data you want to retrieve
• for(): The universe to retrieve data from
• with(): Parameters to pass to the fields.
• preferences(): Additional settings

let, with and preferences are optional. But every query must have get and for clauses.

Write one query, not many

Write this:

get(sales_rev_turn)
for('IBM US Equity')
with(fiscal_period_offset=range(-3Q, 0Q))

Not four separate queries:

get(sales_rev_turn)
for('IBM US Equity')
with(fiscal_period_offset=0Q)
;
get(sales_rev_turn)
for('IBM US Equity')
with(fiscal_period_offset=-1Q)
;
get(sales_rev_turn)
for('IBM US Equity')
with(fiscal_period_offset=-2Q)
;
get(sales_rev_turn)
for('IBM US Equity')
with(fiscal_period_offset=-3Q)

Members of an Index

import bql
bql_svc = bql.Service()
query = """
    get(sales_rev_turn)
    for(members('SPX Index'))
    with(fpo=range(-9Q, 0Q), fpt=Q, fill=prev)
    preferences(addcols=all)
"""
response = bql_svc.execute(query)

df = bql.combined_df(response)
df

Other Useful Universes

Peers

get(
    px_last
)
for(
    peers('IBM US Equity', type='BLOOMBERG_BEST_FIT')
)

There is also type=‘CUSTOM’ which uses whatever you last selected in Terminal. This is weird (it’s non-deterministic) and will cause different results for different users.

Port Portfolios

get(
    id().positions as #position,
    id().weights as #weight
)
for(
    members('<portfolioid>', type=PORT)
)
preferences(
    addcols=all
)

TranslateSymbols

Many Universe queries will return local tickers. Converting these to a fundamental or composite ticker is needed for certain queries.

get(
    px_volume
)
for(
    translateSymbols(members('SPX index'), targetidtype='fundamentalticker')
)

SRCH Queries

BQL doesn’t have all the fields, especially for fixed income, that DAPI/BDP has. For many queries, such as Bond screening, you must first define a screen in SRCH<go>. In this example, I’ve already created a screen called “my_bond_screen”.

get(
    AMT_OUTSTANDING(currency=USD)
    )
for(
    SCREENRESULTS(type='srch',screen_name='my_bond_screen')
)

Alternative Data ALTD<go>

Data available in ALTD is also available in BQL.

• Example: get(bloomberg.transactions.observed_sales) for('ANF US Equity')

Others

I’ll fill in some other examples later, but a few categories of Universe functions:

Universes:

• bondsuniv, debtuniv, equitiesuniv, fundsuniv, municipalsuniv
• port: returns members of pre-defined portfolio
• countries: get(id) for(COUNTRIES(region='ASIA'))

* The “univ” functions often run into limit problems: you have to carefully craft / narrow your queries for your query to not timeout, which makes them frustrating to work with. One tip is to narrow it to PRIMARY and ACTIVE securities.

Axes and Curves

• curvemembers: get(SPREAD()) for(CURVEMEMBERS('YCGT0001 Index'))

Expanders:

• members: Provides members of a given security, such as get(name) for(members('SPX Index'))
• holdings: Holdings of a fund (if available), such as get(name) for(holdings('QQQ US Equity'))
• bonds, loans, debt, municipals: Debt instruments for a given security
• futures, options: futures/options for a given security

Helpers:

• translatesymbols: Used for translating between local and fundamental tickers
• relativeindex
• equitypricingticker
• union: combine two different universes, such as union(members('SPX Index'), 'holdings('QQQ US Equity'))
• intersect: returns the intersection (members of both sets) of two universes: intersection(members('SPX Index'), 'holdings('QQQ US Equity'))
• setdiff: difference between two universes
• filter: Applies a filter to a given universe, such as filter(members('SPX Index'), cur_mkt_cap > 10B)

CDEs

CDE (custom data editor) allows you to edit and store information inside Bloomberg.

In BQL, you can query a CDE field as a regular field. You’ll likely want “fill=prev” to pull the most recent value.

get(
    UD_MYFIELD(fill=prev) as #myfield
)
for(
   'IBM US Equity'
)

Updating CDEs

There’s the bqcde library to update CDEs. The BQuant Help Center has thorough documentation on it. One good use for this is to generate a signal in BQuant and publish the results as a CDE. This may be scheduled to run automatically.

Cash Dividends

get(
    CASH_DIVS(effective_date=range(-3Y, 0D))
)
for(
    'IBM US Equity'
)

Segments

get(
    segment_name
) 
for(
    segments('IBM US Equity', type=REPORTED, hierarchy='product', level=all)
)

Economic Data

get(
    GDP(period_type=A, period_offset=range(-10Y, -2FY))
) 
for(
    'US Country'
)

Other tips

group() to avoid “Response for … is too large”

BQL has a limit on the number of “series” it returns.

While this will fail with an error: “BQL ERROR: Error: Response for px_last is too large. Apply filter() / group() to reduce the size.”

import bql
bql_svc = bql.Service()
query = """
    get(px_last)
    for(options('SPX Index'))
    with(fill=prev)
    preferences(addcols=all)"""
response = bql_svc.execute(query)
df = bql.combined_df(response)
df

This will work:

import bql
bql_svc = bql.Service()
query = """
    get(group(px_last))
    for(options('SPX Index'))
    with(fill=prev)
    preferences(addcols=all)
"""
response = bql_svc.execute(query)

df = bql.combined_df(response)
df

Wish List

A few thoughts on things I’d like to see:

• More reliable “univ” (equitiesuniv, bonduniv, etc) queries with fewer limits. I frequently hit: BQL_SYSTEM_ERROR: Request exceeded processing time limit. errors.
• No limits on number of series returned in a query, to avoid needed to group() to push everything into one series.
• Documentation around some of the lesser known BQL features, like currencycheck, toscalar/toscalarlist, applypreferences, aligndatesby, or even mode=cached
• A better bql.combined_df function in the Python library
• Release notes / blog / whatever for knowing when BQL is changed. bqlx<go> has great content, but doesn’t capture some of the nitty gritty changes. This isn’t a specific complain about BQL, rather how challenging it can be keeping track of changes in Bloomberg.
• And, of course, parity with BDP/BDH/BDS: different is OK, but it’s a real challenge when we hit a field only available in the “legacy” APIs.