Bloomberg Query Language (BQL)

What is BQL

API based on normalised, curated data, allowing you to perform custom calculations in the Bloomberg cloud. It can perform data processing in the query to partially replace the function of Excel.

• consolidate fields to make custom fields.

• reduce the number of tickers you need to use (for example, study sectors).

• functions to aggregate or group fields (such as average or sum).

Why BQL

In this manner, it saves query data.

How to use BQL

BQL Builder ; BQL and BQL.Query.

BQL Format

Compare with BDP

# Comparison: Ability to extend query points, apply formula, override data items

=BDP("AAPL US Equity", "PX_LAST")
=BDP("AAPL US Equity", "VOLUME")


=BQL("AAPL US Equity", "PX_LAST, PX_VOLUME")
=BQL("AAPL US Equity", "PX_LAST*100/PX_VOLUME")
=BQL("APPL US Equity", "PX_LAST(CURRENCY=EUR)*100/PX_VOLUME")

Multiple Ticker

Apply Function

Example

BQL.Query

• let: Define some variables to use below (optional)

• get: What do you want to know?

• for: Who do you want to know about?

From BQL

Use BQL to show BQL.query
=BQL("filter(members('SPX Index'), LAST(ZSCORE(DROPNA(PX_LAST(dates=range(-30d,0d)))))>2)", 
     "LAST(ZSCORE(DROPNA(PX_LAST(dates=range(-30d,0d)))))", 
     "showQuery=True")
     
A sample of BQL.query
get( LAST(ZSCORE(DROPNA(PX_LAST(dates=range(-30d,0d)))))
     for (
          filter(
               members('SPX Index'), 
               LAST(ZSCORE(DROPNA(PX_LAST(dates=range(-30d,0d)))))>2
                )
         )
         

We can write in a more organized manner
let(#myvar=LAST(ZSCORE(DROPNA(PX_LAST(dates=range(-30d,0d))))) ;)
get(#myvar)
for(filter(members('SPX Index'), #myvar>2))

Call the BQL.Query
=BQL.Query("entire-query") or =BQL.Query(cell-ref)
=BQL.Query(A3)

Second Tab

Data Type Example

single-point data

=BQL("IBM US Equity","EBIT")

time-series data

=BQL("IBM US Equity","EBIT(FA_PERIOD_REFERENCE=RANGE(2012,2016))"

Actuals, Estimates, Guidance

# AE for both acutal and estimate

# actual: last 12-month (LTM) EPS actual value for IBM
=BQL("IBM US Equity","IS_EPS(FA_PERIOD_OFFSET=0)")

# estimates: # Next 12-months from the latest reported fiscal period ; Bloomberg Standard Consensus ;
=BQL("IBM US Equity","IS_EPS(FA_PERIOD_OFFSET=1,FA_ACT_EST_DATA=E,EST_SOURCE=BST)")

# estimates: # Next 12-months from the latest reported fiscal period ; Company Guidance
=BQL("IBM US Equity","IS_EPS(FA_PERIOD_OFFSET=1,FA_ACT_EST_DATA=E,EST_SOURCE=CGD)")

# a time series version
=BQL("SIE GR Equity","EBIT(FA_PERIOD_REFERENCE=RANGE(2011,2020),FA_PERIOD_TYPE=A)")

Calendarization of financial reporting calendar

=BQL("COST US Equity,WMT US Equity,TGT US Equity,DLTR US Equity,FIVE US Equity,BIG US Equity,DOL CN Equity",
     "IS_EPS(FA_PERIOD_REFERENCE=2016Q4,FA_PERIOD_YEAR_END=C1231)")
# FA_PERIOD_YEAR_END parameter is set to a calendar year ending on December 31: 2016-12-31 -> calendar quarter 2016-10-01 tto 2016-12-31
# the query above will return for each company the fiscal quarters (highlighted) that overlap the most with the calendar quarter 2016 Q4.

=BQL("COST US Equity,WMT US Equity,TGT US Equity,DLTR US Equity,FIVE US Equity,BIG US Equity,DOL CN Equity",
     "IS_EPS(FA_PERIOD_REFERENCE=2016Q4,FA_PERIOD_YEAR_END=F)")
# return the actual fiscal quarter for each company

blended data

=BQL("VOD LN Equity",
     "EBIT(FA_PERIOD_TYPE=BA, #  Blended Annual period
           FA_PERIOD_YEAR_END=C1231, # ending on December 31, 2015
           FA_PERIOD_REFERENCE=2015)"
    ) # time-weighted average

Blended Trailing

# current date 2017-03-31
=BQL("VOD LN Equity",
     "EBIT(AS_OF_DATE=2017-03-22,
           FA_PERIOD_TYPE=BT,
           FA_PERIOD_OFFSET=1 )"
    )

latest-12-month period (LTM): default for FA_PERIOD_TYPE parameter

=BQL("600036 CH Equity",
     "IS_EPS(FA_PERIOD_TYPE=LTM,
             FA_PERIOD_OFFSET=range(-2Q,3Q))"
    )

Point-in-time data

=BQL("BBDC4 BZ Equity",
     "PE_RATIO(AOD=range(2017-01-01,2017-03-22))")

Get Support

For a detailed overview of the Bloomberg Query Language syntax run HELP BQLX <GO> on the Bloomberg Terminal.

For a detailed overview of the BQL Fundamentals parameters, parameter default values and available parameter inputs consult the BQL Fundamentals Technical Documentation in the Brochures section on HELP BQLX <GO>.

For templates in Excel powered by BQL, run XLTP BQL <GO>.