Introduction to Bloomberg Query Language (BQL)
--
Note: this post has been replaced, please go to:
The Bloomberg Query Language (BQL) is a new API based on normalised, curated data, allowing you to perform custom calculations in the Bloomberg cloud. Data-intensive analyses that previously required downloading thousands of data points followed by extensive manipulation in Excel now just require one formula, therefore helping universities use less data.
1. Why use BQL?
Researchers may be familiar with downloading data in bulk in Excel using formulas such as BDP, BDH and BDS for data point, data history and data set respectively. You may have created these formulas either by using the function builder menus or by typing them out in full. These formulas give direct access to one or more data items, for one or more securities (tickers), for one or more points in time, by downloading the full set of data to your spreadsheet.
It can be problematic to download so much data in bulk when all you really need is the result of a calculation, such as the average price per sector of the FTSE 100 over the last 50 days. (In this example, you would have to download for all 101 securities the price for each day and sector code, that adds up to 5,151 data points when you only need one per sector). You might exceed daily or monthly download limits, meaning that Bloomberg cannot be used in Excel for that PC until the first day of the next calendar month. The download limits affect all users on that PC.
There is new approach, Bloomberg Query Language (BQL), thanks to extensive rewriting of code on Bloomberg’s servers and the development of this new language and functions. Currently, BQL offers access to equities, funds, fixed income, economics and portfolios.
BQL has solutions to make more efficient use and safer use of data in three particular ways:
- BQL can consolidate fields to make custom fields.
- BQL can reduce the number of tickers you need to use (for example, study sectors within an index instead of every member).
- BQL offers functions to aggregate or group fields (such as average or sum).
You may use the BQL Builder function in Excel or types out the formulas. In this post we will type out the formulas to better explain how they work. In practice, you may prefer to use the BQL Builder.
2. A little comparison between the old way and the new
At its simplest, a BQL function can look the same as a BDP (data point) function. We start with a short example of Apple Inc’s last price (the PX_LAST item). The functions require a security (ticker) then the data item.
- using BDP
=BDP("AAPL US Equity", "PX_LAST") - using BQL
=BQL("AAPL US Equity", "PX_LAST")
With BDP, if you want to get a second data item, price volume (total number of shares traded that day), you must write a new formula in a new cell. The With BQL, you can extend the list of data items in the one formula with a comma; this will give you an array of data.
- using BDP
=BDP("AAPL US Equity", "PX_LAST")
and=BDP("AAPL US Equity", "VOLUME") - using BQL
=BQL("AAPL US Equity", "PX_LAST,PX_VOLUME")
Please note: For BDP,
VOLUMEgives the value today so far andPX_VOLUMEgives yesterday’s close value. For BQL, thePX_VOLUMEitem gives the value today so far.
What if you want to look at percentage ratio of last price and price volume? BQL lets you perform a calculation on the Bloomberg server and download just the result.
- using BDP write another formula in another cell like
=B6*100/B7 - using BQL
=BQL("AAPL US Equity", "PX_LAST*100/PX_VOLUME")
BQL will download just the one result value instead of downloading many.
There are ‘override’ options you can apply to data items, such as currency. The overrides vary between data items, you can read about them in the terminal with the FLDS function. For BDP, you can only apply overrides at the end of the formula, for all data items. For BQL, you can also apply overrides to individual data item, using brackets:
=BQL("APPL US Equity", "PX_LAST(CURRENCY=EUR)*100/PX_VOLUME")
3. Writing a BQL request
There are two Excel formulas you can use for BQL requests: BQL and BQL.Query. The BQL formula has the simpler format, it has two parts:
- Who do you want to know about?
- What do you want to know about them?
We have seen this above by looking for the last price of Apple Inc. The ‘who’ part is one or more securities (tickers). The ‘what’ part is the data items.
Requests with one security
The ‘what’ part can become quite long. It is often a good idea to build up from something simple. In this example, we start with the last price of IBM Corp., then add functions one at a time:
=BQL("IBM US Equity", "PX_LAST")
Add a parameter to the data item to get a range of dates, last price from 30 days ago to today. Parameters written in parentheses apply to the data item to the left; the dates parameter lets you choose one or more dates, the range function accepts relative dates such as -30d for ‘30 days ago’:
=BQL("IBM US Equity", "PX_LAST(dates=range(-30d,0d))")
This gives us an array of last price data, but it includes the non-trading days with NA. We can exclude these cells by wrapping the data item in the DROPNA function:
=BQL("IBM US Equity", "DROPNA(PX_LAST(dates=range(-30d,0d)))")
We can apply an aggregate function to calculate a single figure from an array of date. The AVG function calculates the average (mean) value.
=BQL("IBM US Equity", "AVG(DROPNA(PX_LAST(dates=range(-30d,0d))))")
We could see where each price point lies within the range (the Z-Score) by using the ZSCORE function instead of the average:
=BQL("IBM US Equity", "ZSCORE(DROPNA(PX_LAST(dates=range(-30d,0d))))")
Finally, we could get just the latest Z-Score in the range using the LAST function:
=BQL("IBM US Equity", "LAST(ZSCORE(DROPNA(PX_LAST(dates=range(-30d,0d)))))")
Requests with more than one security
So far, we have used the BQL function with just one security. The BQL function can also work with multiple securities, including:
- A list of tickers (separated by commas),
- The results of a screening,
- Entire universes, filtered down to a subset.
If your query uses the members function to access an entire universe, it is essential that you start with a small subset when testing and building.
In this example, we use the members universe to request price data for all members of the S&P 500 index (‘SPX Index’). (The filter will come later.) We will use the price highest from the average in the last 30 days.
=BQL("members('SPX Index')", "LAST(ZSCORE(DROPNA(PX_LAST(dates=range(-30d,0d)))))")
This gives us an array of data, one entry for each member. We can wrap the members function in a filter to limit the results to just the members that meet a condition, such as greater than 2:
=BQL("filter(who,what>2)", "what")- Or, written out in full:
=BQL("filter(members('SPX Index'), LAST(ZSCORE(DROPNA(PX_LAST(dates=range(-30d,0d)))))>2)", "LAST(ZSCORE(DROPNA(PX_LAST(dates=range(-30d,0d)))))")
Writing Excel formulas this long can be difficult to write and read, and Excel has a limit of 255 characters per cell. We will look at a better way below.
4. Writing a BQL.Query request
You can construct a longer or more complicated query with the BQL.Query formula. It may look familiar if you have used SQL before. The BQL.Query formula has up to three parts:
let: Define some variables to use below (optional)get: What do you want to know?for: Who do you want to know about?
You can define a variable in the optional first part of the query. This will save you typing out the same bit of code several times, reducing the length of the query and avoiding mistakes.
Convert a BQL request to a BQL.Query request
We convert our previous example to a BQL.Query request, introducing a variable and making the request shorter.
A BQL request is easier for many people because it looks similar to a traditional BDS, BDH or BDP request. All BQL requests are converted to BQL.Query requests behind the scenes. You can view the converted query by adding a parameter to the end of the request (as a third argument):
=BQL("who", "what", "showQuery=True")- Or, written out in full:
=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")
This will display the query instead of the data. There are two parts get and for:
get(what) for(who)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 copy this query text and paste it into a new empty cell. (Use the ‘Paste Values’ function in Excel.) We can then clean up the query by replacing the repeated bit with a variable. Variables begin with a hash symbol and end with a semicolon. We now have three parts let, get, for:
let(#myvar=LAST(ZSCORE(DROPNA(PX_LAST(dates=range(-30d,0d)))));)
get(#myvar)
for(filter(members('SPX Index'), #myvar>2))
Notice that there are no commas between the let, get, for parts, and no quotes. We can add line breaks between the parts by pressing Alt + Enter, for aesthetic purposes. You must keep the whole query within one cell (unless you can use the CONCATENATE function).
We can call the BQL.Query function as follows, if the new query is in cell A3:
=BQL.Query("entire-query")or=BQL.Query(cell-ref)=BQL.Query(A3)
You will get an array of data with each variable in a column.
5. Getting support
The help pages for BQL can be found in the Bloomberg terminal by typing BQLX then the Enter key. They provide a ‘Getting Started’ page, ‘Excel Formula Reference’, and pages for each universe such as equities and funds.
The resources pages include links to video tutorials, fact sheets, tutorials and function reference tables.
