Using Bloomberg Query Language in Excel
BQL stands for Bloomberg Query Language, which is the language used to perform analytics with and retrieve data from the Bloomberg Database. New functions have been added to the Bloomberg Excel Add-in that can be used to retrieve data using BQL. Like the Excel functions BDH and BDP, it’s possible to retrieve both current and historical data using BQL. Uniquely, BQL is also capable of performing complex tasks like screening, interval calculations, time series calculations, and supports the declaration of custom fields.A useful feature of BQL is the ability to manipulate data before retrieval. This includes arithmetic operations, rounding, finding the highest number in a set, etc. The advantage of performing calculations within BQL rather than in Excel is that it retrieves fewer pieces of information through the API and is therefore easier to process.
BQL Basics
There are two ways to perform a BQL query in Excel: the functions BQL and BQL.Query. Both functions are equally capable, but use different syntaxes. There are three types of inputs used when querying in BQL: a Universe, an Expression, and Parameters.
The universe is the subject of the query and is what the queried data describes. A universe can be a single security, a list of securities, an index, etc. For example, to query the most recent price of IBM, the universe would be “IBM US Equity”. To query the most recent prices of both IBM and Apple, the universe would be “[‘IBM US Equity’, ‘AAPL US Equity’]”.
An expression, also known as a field, is the type of data that will be retrieved. For example, when querying the most recent price of IBM, the expression used is “PX_LAST”. An expression can contain a single field, a combination of fields, and operations applied to the fields. For a complete list of fields available for use, see FLDS <GO>.
A parameter is an optional addition to a query that can further specify an expression. While querying the closing price of IBM on 12/13/2017, the date would be a parameter. In this case, the proper notation for this parameter would be “DATES=2017-12-13”.
BQL.Dates, BQL.Params, BQL.Expr, and BQL.List
Notably, BQL functions follow a different dating convention than Excel or other Bloomberg functions. For example, to be usable in BQL, the date 7/25/2017 must be converted into the string “2017-07-25”. This conversion may be completed automatically by inputting a regular date into the BQL.Date function.
The other functions, like BQL.Date, are useful for converting cell references in Excel into usable inputs for the BQL and BQL.Query functions. BQL.Params is used to convert inputs into a parameter, BQL.Expr is used to convert inputs into an expression, and BQL.List is used to convert a cell referenced range of securities into a list.
esg_score( )
An impartial assessment of a firm, fund, or security’s performance in relation to Environmental, Social, and Governance (ESG) concerns is called an ESG score. The many grading platforms that provide ESG ratings have varied evaluation standards, but they all fit into one or more of the E, S, or G categories.
ESG score schemes are often either sector- or industry-specific. Scoring systems tailored to individual industries evaluate issues that are considered significant by the sector as a whole. Commonly acknowledged elements that are significant to all industries, including diversity, equality and inclusion (DEI), human rights, and climate change, are often included in industry-agnostic ESG ratings.
After assigning a weight to each measurement criterion, ESG rating systems evaluate an organization’s performance in relation to each criterion. The total of the ratings for the criteria and the weightings assigned to the (proprietary) criteria determines an organization’s ultimate ESG score.
Business Question
Collect the ESG data, including BESG Environmental Pillar Score, Social Pillar Score, and Governance Pillar Score from the Bloomberg Terminal to an excel sheet using the BQL Excel add-in for all the firms from US and Australia.
ESG Trends in the US:
Growing Investor Focus: As a result of realizing the financial significance of sustainability considerations, investors are paying more attention to ESG performance.
Developments in Regulation: An increasing trend is the inclusion of ESG considerations in disclosure requirements by regulatory authorities. Businesses must supply more detailed information on their ESG policies.
Technology and Innovation: One of the main factors improving ESG performance is the combination of technology and innovation. Technology is a major facilitator of advancement in this field, from data-driven environmental impact assessments to sustainable supply chain management.
Stakeholder Activism: Businesses are coming under more and more pressure from a range of stakeholders, including as advocacy organizations, workers, and customers, to adopt a proactive approach to addressing ESG concerns.
Considerations for Global Supply Chains: As supply chains become more interconnected, local businesses as well as their foreign partners and suppliers are subject to heightened scrutiny about their ESG policies.
In the United States, understanding and taking into account environmental, social, and governance (ESG) factors is becoming more and more important for long-term company resilience and success. Businesses that successfully incorporate ESG concepts into their plans will be in a better position to manage risks, develop resilience, and provide long-term value for all parties involved.
ESG Trends in Australia:
In Australia, the significance of Environmental, Social, and Governance (ESG) aspects has grown as investors and companies realize how important it is to include sustainability into their decision-making. A company’s performance and influence in the domains of environmental stewardship, social responsibility, and corporate governance are assessed using a set of standards known as ESG.
Environmental (E): Australia emphasizes environmental sustainability heavily because of its varied ecosystems and particular environmental issues. The main issues include biodiversity, water conservation, renewable energy, and climate change. Businesses are under growing pressure to reveal their environmental effect and show that they are committed to lowering their carbon footprint. To encourage sustainable behaviors, the Australian government has also put in place a number of laws and programs, such as the Emissions Reduction Fund and the Renewable Energy Target.
Social (S): Human rights, diversity and inclusion, community involvement, labor practices, and product safety are just a few of the many variables that fall under the umbrella of social concerns. In Australia, the business sector is becoming more conscious of and committed to encouraging social responsibility. Businesses must interact with their local communities, promote diversity and inclusion, and exhibit fair labor standards. Corporate conduct is influenced by stakeholder expectations and legal obligations in various domains.
Governance (G): A crucial component of ESG is corporate governance, which makes sure businesses are run morally, openly, and with the interests of all stakeholders in mind. The Australian Securities Exchange (ASX) Corporate Governance Principles and Recommendations offer rules for listed firms, and the country has well-established standards for corporate governance. Important aspects of governance concerns include CEO salary, diversity on the board, shareholder activism, and anti-corruption initiatives.
Investor and Regulatory Landscape: Australia’s investors are beginning to understand the significance of including ESG considerations in their decision-making. Demand for ESG transparency and reporting is rising, and institutional investors in particular are incorporating ESG factors into their investing strategies. To promote accountability and transparency, regulatory organizations like the Australian Securities and Investments Commission (ASIC) and the Australian Securities Exchange (ASX) are actively striving to strengthen the criteria for ESG disclosure.
Challenges and Opportunities: Even while there has been progress, comprehensive ESG integration still faces obstacles. Standardized measurements and reporting systems are required, as some businesses may have trouble measuring and disclosing their ESG performance. On the other hand, this offers a chance for creativity and the creation of environmentally friendly company procedures.
Finally, it should be noted that ESG factors are becoming more and more important in Australia’s corporate environment. Businesses that take proactive measures to address governance, social, and environmental challenges are more likely to draw investors who support ethical and sustainable business practices in addition to complying with regulations. Australia has a great opportunity to be a major player in determining the direction of sustainable business as the world’s attention towards ESG continues to increase.
Data Collection
We collected the data by using Bloomberg Query Language (BQL) builder in EXCEL.
Collecting data for US based companies
Environmental_US Formula :
=@BQL("filter(#universe, #criteria1 and #criteria2)", "esg_score(dates=range(2015-01-01, 2022-12-31), fill=NA, per=Y, score_type=ENVIRONMENTAL).value, bics_level_1_sector_name().value", "#universe", A1, "#criteria1", A2, "#criteria2", A3, "mode=CACHED",/
SOCIAL_US Formula :
=@BQL("filter(#universe, #criteria1 and #criteria2)", "esg_score(dates=range(2015-01-01, 2022-12-31), fill=NA, per=Y, score_type=SOCIAL).value, bics_level_1_sector_name().value", "#universe", A1, "#criteria1", A2, "#criteria2", A3, "mode=CACHED",
GOVERNANCE_US Formula :
=@BQL("filter(#universe, #criteria1 and #criteria2)", "esg_score(dates=range(2015-01-01, 2022-12-31), fill=NA, per=Y, score_type=GOVERNANCE).value, bics_level_1_sector_name().value", "#universe", A1, "#criteria1", A2, "#criteria2", A3, "mode=CACHED",
Collecting data for Australia
Environmental_AU Formula :
=@BQL("filter(#universe, #criteria1 and #criteria2)", "esg_score(dates=range(2015-01-01, 2022-12-31), per=Y, score_type=ENVIRONMENTAL).value, bics_level_1_sector_name().value", "#universe", A1, "#criteria1", A2, "#criteria2", A3, "mode=CACHED",
Social_AU Formula :
=@BQL("filter(#universe, #criteria1 and #criteria2)", "esg_score(dates=range(2015-01-01, 2022-12-31), fill=NA, per=Y, score_type=SOCIAL).value, bics_level_1_sector_name().value", "#universe", A1, "#criteria1", A2, "#criteria2", A3, "mode=CACHED",
The preview loads data from bloomberg terminal
Governance_AU Formula :
=@BQL("filter(#universe, #criteria1 and #criteria2)", "esg_score(dates=range(2015-01-01, 2022-12-31), fill=NA, per=Y, score_type=GOVERNANCE).value, bics_level_1_sector_name().value", "#universe", A1, "#criteria1", A2, "#criteria2", A3, "mode=CACHED",
The preview loads data from bloomberg terminal
Reference links:
LibGuides: Bloomberg Professional Terminal: Bloomberg Excel Add-in. (n.d.). Retrieved from Bloomberg Excel Add-in – Bloomberg Professional Terminal – LibGuides at The World Bank/IMF Library Network