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

![](https://2822872041-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC4puujNFesy_jp7F37%2F-MC5P3tfzuLTRFb5Qqyu%2F-MC5YHeG_PpmR3hsUjfI%2Fimage.png?alt=media\&token=a7c58b8a-c27b-4bce-a511-e61aade1bcb7)

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


```

![](https://2822872041-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC4puujNFesy_jp7F37%2F-MC5P3tfzuLTRFb5Qqyu%2F-MC5YOWTXIqV7wbPOHpx%2Fimage.png?alt=media\&token=7d682371-505f-4fb1-bb1e-0e0c77b348f5)

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


```

![](https://2822872041-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC4puujNFesy_jp7F37%2F-MC5P3tfzuLTRFb5Qqyu%2F-MC5YUuMH-UyyiZR-L9t%2Fimage.png?alt=media\&token=d78142b0-6ab0-4e4e-8e74-4d37820e0cf7)

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


```

![](https://2822872041-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC4puujNFesy_jp7F37%2F-MC5P3tfzuLTRFb5Qqyu%2F-MC5YanCxyMuq3LQf7fa%2Fimage.png?alt=media\&token=ceef4d64-a630-48cb-bece-0b39c395e2e6)

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

![](https://2822872041-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC4puujNFesy_jp7F37%2F-MC5P3tfzuLTRFb5Qqyu%2F-MC5YfZq8oxe6l1k6QkL%2Fimage.png?alt=media\&token=35380cda-4261-408b-ba5a-44337348e375)

### 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>`.