# 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

{% tabs %}
{% tab title="Compare with BDP" %}

```python
# 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")
```

{% endtab %}

{% tab title="Multiple Ticker" %}

```python
# A list of tickers (separated by commas)
# The results of a screening
# Entire universes, filtered down to a subset.


Universe
=BQL("members('SPX Index')", "LAST(ZSCORE(DROPNA(PX_LAST(dates=range(-30d,0d)))))")
=BQL("filter(members('SPX Index'), LAST(ZSCORE(DROPNA(PX_LAST(dates=range(-30d,0d)))))>2)", 
     "LAST(ZSCORE(DROPNA(PX_LAST(dates=range(-30d,0d)))))")
     
     
```

{% endtab %}

{% tab title="Apply Function" %}

```python
=BQL("IBM US Equity", "PX_LAST")

# Date range of data ; exclude non-trading datas ; 
=BQL("IBM US Equity", "       PX_LAST(dates=range(-30d,0d))")
=BQL("IBM US Equity", "DROPNA(PX_LAST(dates=range(-30d,0d)))")

# Apply Aggregate function
=BQL("IBM US Equity", "        AVG(DROPNA(PX_LAST(dates=range(-30d,0d))))")
=BQL("IBM US Equity", "     ZSCORE(DROPNA(PX_LAST(dates=range(-30d,0d))))")
=BQL("IBM US Equity", "LAST(ZSCORE(DROPNA(PX_LAST(dates=range(-30d,0d)))))")
```

{% endtab %}

{% tab title="Example" %}

```python
=BQL("IBM US Equity","EBIT(FA_PERIOD_REFERENCE=RANGE(2012,2016))"
# This query will return 5 data points with (LTM) EBIT values for IBM for 2012, 2013, 2014, 2015 and 2016

=BQL("IBM US Equity","AVG(EBIT(FA_PERIOD_REFERENCE=RANGE(2010,2016)))")
#This query will return a single data point with the 5-year average EBIT value of IBM

=BQL("IBM US Equity","IS_EPS(FA_PERIOD_OFFSET=0)")
# This query will return the last 12-month (LTM) EPS actual value for IBM

=BQL("IBM US Equity","IS_EPS(FA_PERIOD_OFFSET=1,FA_ACT_EST_DATA=E,EST_SOURCE=BST)")
=BQL("IBM US Equity","IS_EPS(FA_PERIOD_OFFSET=1,FA_ACT_EST_DATA=E,EST_SOURCE=CGD)")
# Next 12-months from the latest reported fiscal period
# Estimates Data ; AE for both acutal and estimate
# Bloomberg Standard Consensus ; Company Guidance

=BQL("SIE GR Equity","EBIT(FA_PERIOD_REFERENCE=RANGE(2011,2020),FA_PERIOD_TYPE=A)")
# standing at the point 2016, we can have data from the past and in tehr future



```

{% endtab %}
{% endtabs %}

## 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?

{% tabs %}
{% tab title="From BQL" %}

```python
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)
```

{% endtab %}

{% tab title="Second Tab" %}

{% endtab %}
{% endtabs %}

## Data Type Example

### single-point data

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

### time-series data

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

### Actuals, Estimates, Guidance

```python
# 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)")
```

![](/files/-MC5YHeG_PpmR3hsUjfI)

### Calendarization of financial reporting calendar

```python
=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


```

![](/files/-MC5YOWTXIqV7wbPOHpx)

### blended data

```python

=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


```

![](/files/-MC5YUuMH-UyyiZR-L9t)

### Blended Trailing

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


```

![](/files/-MC5YanCxyMuq3LQf7fa)

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

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

![](/files/-MC5YfZq8oxe6l1k6QkL)

### Point-in-time data

```python
=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>`.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://michael-mao.gitbook.io/bloomberg/bql/bloomberg-query-language-bql.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.