>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
BQL
A Bloomberg Professional Service O
f
fe
r
i
ng
BLOOMBERG FUNDAMENTALS IN BQL
A single function for queries of both single-point and time-series data
Ability to perform both data retrieval and transformations
Alignment of actuals, estimates and guidance data
In all three queries above, a single field (IS_EPS) can be used with the parameter FA_PERIOD_OFFSET specifying an offset
(which could be positive for future fiscal periods or negative for past fiscal periods) from the latest fiscal period reported by the
company (fiscal period 0).The parameter FA_ACT_EST_DATA determines whether actuals (A) or estimates (E) data is returned
and the parameter EST_SOURCE determines the source of the estimates data returned.
Requesting a time series of both actuals and estimates data requires a one query only. For example, this query will return annual
EBIT data (shown in EUR billions here) for Siemens for a period of 10 years in the past and in the future. In this case the
parameter FA_ACT_EST_DATA is set, by default, to "AE", i.e. Actuals data for reported and Estimates data for future periods.
=BQL("IBM US Equity","EBIT")
=BQL("IBM US Equity","EBIT(FA_PERIOD_REFERENCE=RANGE(2012,2016))"
This query will return
the latest (LTM) EBIT value for IBM
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)")
=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)")
This query will return the last 12-
month (LTM) EPS actual value for IBM
Next 12-months from
the latest reported
fiscal period
Estimates
Data
Bloomberg Standard Consensus
Company Guidance
Calendarization of financial reporting calendar
A new parameter for the fiscal year end allows the alignment of financial data of companies following different fiscal year ends for
their financial reporting. For example, the comps of Costco Wholesale have the following fiscal year end dates:
8.27
6.80
5.29
6.61
6.04
7.32
8.83
9.50
10.36
10.41
09/30/2011 09/30/2012 09/30/2013 09/30/2014 09/30/2015 09/30/2016 09/30/2017 09/30/2018 09/30/2019 09/30/2020
=BQL("SIE GR Equity","EBIT(FA_PERIOD_REFERENCE=RANGE(2011,2020),FA_PERIOD_TYPE=A)")
reported
Reported fiscal periods
Future fiscal periods
=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)")
Since the FA_PERIOD_YEAR_END parameter is
set to a calendar year ending on December 31,
than 2016 Q4 means a calendar quarter ending
on 12/31/2016. Therefore, 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)")
The FA_PERIOD_YEAR_END parameter
can also be set to follow the fiscal
calendar of the company, in which case
2016 Q4 means a quarter ending in
August 2016 for Costco, and the quarter
ending in January 2016 for its comps.
Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr
Costco Wholesale COST US Equity August
Wal-Mart Stores WMT US Equity January
Target TGT US Equity January
Dollar General DG US Equity January
Dollar Tree DLTR US Equity January
Five Below FIVE US Equity January
Big Lots BIG US Equity January
Dollarama DOL CN Equity January
Calendar Year 2016
Calendar Year 2017
COMPANY
TICKER
FISCAL YEAR END
2017 Q1
2017 Q2
2017 Q4
2017 Q4
2018 Q1
2018 Q1
2018 Q1
2018 Q1
2018 Q1
2018 Q1
2018 Q1
2017 Q4
2017 Q3
2017 Q3
2017 Q3
2017 Q3
2017 Q3
2017 Q4
2017 Q4
2017 Q4
2017 Q4
2016 Q4
2017 Q3
2017 Q3
Calendar
2016 Q4
New fiscal period types for blended data
While the calendarization of financial reporting calendar across companies usually works well for aligning fiscal periods of
companies that report on a quarterly basis, for companies reporting on a less frequent basis the misalignment of fiscal periods
can still be significant unless data is blended from different fiscal periods reported by the company to create a new blended fiscal
period which is a calculated by Bloomberg. For example, Vodafone's financial disclosure follows a fiscal year ending on March 31.
BQL Fundamentals also offers a Blended Trailing period type which dynamically links the period end date of the blended data to
an observation date (or as-of-date).
Fiscal Year 2015
Fiscal Year 2016
2014-03-31
2015-03-31 2016-03-31
Blended Annual 2015
2014-12-31
2015-12-31
25%
75%
=BQL("VOD LN Equity","EBIT(FA_PERIOD_TYPE=BA, FA_PERIOD_YEAR_END=C1231,FA_PERIOD_REFERENCE=2015)")
This query will return the EBIT data for a Blended Annual period ending on December 31, 2015, calculated as the
time-weighted average of the Vodafone's reported EBIT data from fiscal periods 2015A and 2016A, as shown above.
=BQL("VOD LN Equity","EBIT(AS_OF_DATE=2017-03-22,FA_PERIOD_TYPE=BT,FA_PERIOD_OFFSET=1 )")
Fiscal Year 2017 (E)
Fiscal Year 2018 (E)
2016-03-31
2018-03-31
2017-03-31
Blended Trailing
2018-03-22
3%
97%
2017-03-22
This query will return the EBIT data for a
Blended
Trailing period ending one year
forward (on 2018-03-22) from today (2017-03-
22) since the FA_PERIOD_OFFSET has been set
to 1. In this example, it will be calculated as a
time-weighted average of 3% of EBIT estimate
from 2017A and 97% of EBIT estimate from
2018A for Vodafone.
Requesting the same type of data as a time-
series is also possible by using the range()
function with the AS_OF_DATE,
as shown
below. Such query will return 10 daily data
points, with each daily data point representing
a blended trailing period ending exactly one
year forward from that day. At the same time,
the weights of 2017A estimate and 2018A
estimate will dynamically change every day.
=BQL("VOD LN Equity","EBIT(AS_OF_DATE= range(2017-03-12,2017-03-22),FA_PERIOD_TYPE=BT,FA_PERIOD_OFFSET=1
Another important period type that BQL fundamentals introduces is the latest-12-month period (LTM) which is also the default
period type for the FA_PERIOD_TYPE parameter (when not explicitly specified in the query). This period type eliminates the need
for using separate fields for, for example, IS_EPS for a fiscal semiannual period vs TRAILING_12_MONTH_EPS for a trailing
period. For example, the query below can be used to retrieve the LTM data for EPS for China Merchants Bank.
The LTM period type will automatically take into account the type of field that is being queried and, for example, for a field like
IS_EPS will return the sum of the last 4 fiscal quarters or 2 fiscal semiannuals, whereas for a field like BS_TOT_ASSET will return
the value from the latest fiscal quarter or the latest fiscal semiannual.
Point-in-time data
BQL Fundamentals offers full capability of retrieving point-in-time data, both as a
single data point as well as a time series of data via the AS_OF_DATE parameter.
This is critical for properly aligning market data (like price or market capitalization)
with fundamentals data. For example, the following query will return the price to
earnings ratio for Banco Bradesco:
Legacy Fundamentals vs BQL Fundamentals
The table below provides examples of common queries in Legacy Fundamentals functions BDP() and BDH() and how the same
queries can be run via BQL Fundamentals
Description of the query
Legacy Fundamentals
BQL Fundamentals
Get total assets data for IBM for the latest
quarter
=BDP("IBM US Equity",
"
BS_TOT_ASSET","
FUND_PER=Q")
=BQL("IBM US Equity","BS_TOT_ASSET","FA_PERIOD_TYPE=Q")
Get EPS data for Vodafone for the latest
semiannual period
=BDP("VOD LN Equity",
"
IS_EPS","
FUND_PER=S")
=BQL("VOD LN Equity","IS_EPS","FA_PERIOD_TYPE=S")
Get the latest LTM EPS data for
Vodafone
=BDP("IBM US
Equity","TRAIL_12M_EPS_AFTER_XO","FUND_PER=S")
=BQL("VOD LN Equity","IS_EPS","FA_PERIOD_TYPE=LTM")
Get the estimated EPS GAAP data for the
current fiscal year for Siemens
=BDP("SIE GR
Equity","BEST_EPS_GAAP","BEST_FPERIOD_OVERRIDE=1FY")
=BQL("SIE GR
Equity","IS_EPS","FA_PERIOD_TYPE=A","FA_PERIOD_OFFSET=1A")
Get the estimated EPS Adj data for next
fiscal year for Siemens
=BDP("SIE GR
Equity","BEST_EPS","BEST_FPERIOD_OVERRIDE=2FY")
=BQL("SIE GR
Equity","IS_EPS","FA_PERIOD_TYPE=A","FA_PERIOD_OFFSET=2A","
FA_ADJUSTED=Y")
Get the estimated EPS Adj data for the
next 12 months for Siemens
=BDP("SIE GR
Equity","BEST_EPS","BEST_FPERIOD_OVERRIDE=1TY")
=BQL("SIE GR
Equity","IS_EPS","FA_PERIOD_TYPE=LTM","FA_PERIOD_OFFSET=1
A","FA_ADJUSTED=Y")
Get the estimated EPS Adj data for the
next 24 months for Siemens
Not supported
=BQL("SIE GR
Equity","IS_EPS","FA_PERIOD_TYPE=LTM","FA_PERIOD_OFFSET=2
A","FA_ADJUSTED=Y")
Get the EPS GAAP guidance data for IBM
for fiscal year 2017
=BDP("IBM US
Equity","CEST_EPS_GAAP","BEST_FPERIOD_OVERRIDE=17Y"
=BQL("IBM US
Equity","IS_EPS","FA_PERIOD_TYPE=A","FA_PERIOD_REFERENCE=
2017","EST_SOURCE=CGD")
Get annual EPS Adj actual data for the
last 3 years and estimates data for the
next 2 years
Not supported as a single query. The following steps are needed:
1. For the actuals part, run the following query:
=BDH("IBM US EQUITY","IS_DIL_EPS_CONT_OPS","-2FY","-
0FY","FUND_PER=Y")
2. For the estimates part, the =BDH() function can't be used to obtain
fiscal period data for future fiscal period, but each individual data
point can be obtained through the =BDP() function:
=BQL("IBM US
Equity","IS_EPS","FA_PERIOD_TYPE=A","FA_PERIOD_OFFSET=RAN
GE(-2,2)","FA_ADJUSTED=Y")
2.38
2.43
2.46
2.41
2.42
2.43
6/30/2016 9/30/2016 12/31/2016 3/31/2017 6/30/2017 9/30/2017
Latest LTM period reported
=BQL("600036 CH Equity","IS_EPS(FA_PERIOD_TYPE=LTM,FA_PERIOD_OFFSET=range(-2Q,3Q))")
This query will return actuals data
for 3 reported LTM periods and
estimates
data for 3 future
periods, in quarterly offsets from
the latest reported LTM period.
=BQL("BBDC4 BZ Equity","PE_RATIO(AOD=range(2017-01-01,2017-03-22))")
Banco Bradesco reported the 2016-
12-31 earnings on 2017-02-02. The
PE_RATIO is calculated by using the
price of each day in the numerator
divided by the 2016-09-
30 LTM
earnings (until 2017-02-01) and the
2016-12-
31 LTM earnings (from
2017-02-02) in the denominator.
Data Point 1:
=BDP("IBM US
Equity","BEST_EPS","BEST_FPERIOD_OVERRIDE=1FY")
Data point 2:
=BDP("IBM US
Equity","BEST_EPS","BEST_FPERIOD_OVERRIDE=2FY")
3. Populate the appropriate cells in Excel with the formulas from step
1 and step 2 to create a single data stream
Get annual EPS Adj estimates data from
for the last 3 and for the next 2 years for
IBM
Not supported as a single query. Five separate queries are needed
for each of the data points requested:
Data point 1:
=BDP("IBM US
EQUITY","BEST_EPS","BEST_FPERIOD_OVERRIDE=-2FY")
…
Data point 5:
=BDP("IBM US
EQUITY","BEST_EPS","BEST_FPERIOD_OVERRIDE=2FY")
=BQL("IBM US
Equity","IS_EPS","FA_PERIOD_TYPE=A","FA_PERIOD_OFFSET=RAN
GE(-2,2)","FA_ADJUSTED=Y","FA_ACT_EST_DATA=E")
Get current year's EPS Adj estimates
data as of every day from December 1,
2016 to January 31, 2017 for IBM
=BDH("IBM US
EQUITY","BEST_EPS","12/01/2016","01/31/2017","BEST_FPERIOD_
OVERRIDE=1GY","DAYS=ALL","FILL=P")
Note: The "current year" will roll over on 12/31/2016 which is the
period end date of IBM's fiscal year
=BQL("IBM US
EQUITY","IS_EPS","FA_ADJUSTED=Y","FA_PERIOD_TYPE=A","FA_P
ERIOD_OFFSET=1A","AS_OF_DATE=RANGE(2016-12-01,2017-01-
31)","FILL=PREV")
Note: The "current year" will roll over on 01/19/2017 when IBM reported
the earnings for the fiscal year ending on 12/31/2016
Get the 1 year blended forward EPS Adj
estimates as of every day from December
1, 2016 to January 31, 2017 for IBM
=BDH("IBM US
EQUITY","BEST_EPS","12/01/2016","01/31/2017","BEST_FPERIOD_
OVERRIDE=1BF","DAYS=ALL","FILL=P")
=BQL("IBM US
EQUITY","IS_EPS","FA_ADJUSTED=Y","FA_PERIOD_TYPE=BT","FA_
PERIOD_OFFSET=1","AS_OF_DATE=RANGE(2016-12-01,2017-01-
31)","FILL=PREV")
Get the 1 year blended trailing EPS Adj
actuals data as of every day from
December 1, 2016 to January 31, 2017
for IBM
Not supported
=BQL("IBM US
EQUITY","IS_EPS","FA_ADJUSTED=Y","FA_PERIOD_TYPE=BT","AS_
OF_DATE=RANGE(2016-12-01,2017-01-31)","FILL=PREV")
Get the revisions history for the annual
EPS Adj estimates data for the current
year as of every day from December 1,
2016 to January 31, 2017 for IBM
=BDH("IBM US
EQUITY","BEST_EPS","12/01/2016","01/31/2017","BEST_FPERIOD_
OVERRIDE=1FY","DAYS=ALL","FILL=P")
=BQL("IBM US
EQUITY","IS_EPS","FA_ADJUSTED=Y","FA_PERIOD_TYPE=A","FA_P
ERIOD_OFFSET=1FA","AS_OF_DATE=RANGE(2016-12-01,2017-01-
31)","FILL=PREV")
Get the EPS Adj actuals LTM data as of
every day from December 1, 2016 to
January 31, 2017 for IBM
Not supported as a single query. A separate query is needed for
every day:
Data point 1:
=BDP("IBM US
EQUITY","T12M_DIL_EPS_CONT_OPS","FUNDAMENTAL_PUBLIC_
DATE=20161201")
…
Data point 62:
=BDP("IBM US
EQUITY","T12M_DIL_EPS_CONT_OPS","FUNDAMENTAL_PUBLIC_
DATE=20170131")
=BQL("IBM US
EQUITY","IS_EPS","FA_ADJUSTED=Y","AS_OF_DATE=RANGE(2016-
12-01,2017-01-31)","FILL=PREV")
Additional resources
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>.
CONTACT U
S
To
learn more
about
Bloomberg’s Fundamentals
Data, contact
your Bloomberg
account representative or press the <HELP>
key
twice on the
Bloomberg
Professional
®
service.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
bloomberg.com
B
E
I
J
IN
G
FRANKFURT
LONDON
NEW
Y
O
RK
SÃO PAULO
S
Y
DNEY
+86 10 6649
7500
+49 69 9204
1210
+44 20 7330
7500
+1 212 318
2000
+55 11 2395
9000
+61 2 9777
8600
DUB
A
I
HONG KONG
MUMBAI
SAN FRANCISCO
SINGAPORE
TOKYO
+971 4 364
1000
+852 2977
6000
+91 22 6120
3600
+1 415 912
2960
+65 6212
1000
+81 3 3201
8900