Skip to main content

NYU Libraries logo

Bloomberg Guide: Excel API & BQL

Learn how to access and use Bloomberg at NYU Libraries.

API Down at Bobst Library

ALERT: The Excel API is currently down on Bobst Library's Bloomberg terminal. Contact lib-business@nyu.edu for alternatives if you do not have access to the Bloomberg Excel API at another location.

Getting Started with the API

The Bloomberg Desktop API is a suite of Excel tools that allows you to query Bloomberg's underlying data for further custom analysis. Use the DAPI command to access the API's support documentation, including video tutorials.

Data downloaded using the API should not, under any circumstances, be removed from the Bloomberg terminal (i.e. local PC). Only your analysis of the data can be removed. Review the "Desktop API Guidelines" document (via the DAPI command) to ensure that you are in full compliance with Bloomberg's rules.  If these restrictions do not match your use case, you must use another data source such as Datastream or WRDS. If you have questions about whether you are in compliance, please email us by using the Ask A Librarian Form and selecting "Business" as your subject.

Installing the Excel API Add-In

At Bobst Library, every time a user logs on to Windows they must install the Excel add-in on their account. Watch a video of these steps here (requires flash).

  1. Click the start menu icon in the lower-left corner of Windows.
  2. Under "B" select the "Bloomberg" folder.
  3. In the resulting drop down, select "Install Office Add-Ins".
  4. Follow the prompts for the "Bloomberg" tab to appear in Excel.

To confirm if other terminals have the Excel add-in, open Excel. If the add-in is installed, there will be a "Bloomberg" tab.

Before You Use the API: Understanding Data Limits

Data accessed through the Bloomberg terminals, including the Excel API, is subject to daily and monthly download limits. The limits are associated with each terminal, not your personal account, and cannot be reset. Be judicious when downloading so as to avoid reaching the limits, especially during busy times of year when Bloomberg assignments are due.

How do I tell if a terminal has reached its data limit?

There is no direct way to view how much of the data limit has been used at a terminal. When a terminal's data limit has been reached, you will receive error messages in Excel or no results. In some cases, an API query may come back incomplete. In the terminal software, data in spreadsheets may also not display.

How do I report when the data limit has been reached?

Alert our staff by emailing us by using the Ask A Librarian Form and selecting "Business" as your subject. We will place a sign on the terminal.

When does the data limit reset?

The daily data limit resets at midnight and the monthly data limit resets at midnight on the last day of the month. These limits are controlled by Bloomberg: library staff cannot reset the limit and Bloomberg staff will not reset the limit under any circumstances.

How can I reduce or optimize my data consumption to prevent reaching the limit?

  • Carefully build API queries to only download what you need. The Equity Screener command (EQS) is particularly useful for this task.
  • Use Bloomberg Query Language (BQL), which helps reduce data consumption.
  • If necessary, perform very large API queries during slower times of year (summer, breaks) as opposed to when Bloomberg assignments are due (midterms, finals).
  • Perform very large queries during the last few days of the month to reduce the impact of reaching data limits on other users.
  • Consider using alternative financial data sources that allow very large queries: Datastream, WRDS.

This terminal is maxed out and I still need to do my Bloomberg assignment. What should I do?

  • The only solution is to move to another terminal whose data limit has not yet been reached.
  • Consult the strategies above when making new API queries.
  • If you have an assignment due that requires Bloomberg data, but all the Bobst terminals are exhausted, we can contact your instructor: reach out to the library staff by emailing us by using the Ask A Librarian Form and selecting "Business" as your subject.

Template Library (XLTP)

Use the XLTP command to access hundreds of visually appealing Excel templates that allow you to quickly perform custom analysis. Templates are organized by market sector, role (trader, analyst, broker, etc.), and region. Templates require the Bloomberg Excel API to display and update data.

Screenshot of the template library interface.

New: Bloomberg Query Language (BQL)

BQL is a new, more powerful API based on normalized, curated, point-in-time data that allows you to perform aggregation, screening, calculations, and other analysis on Bloomberg's servers.

Benefits of BQL

  1. Customizable: Go beyond the terminal's built-in functions and build your own custom outputs. 
  2. Fast: Reduces the number and complexity of steps compared to the old Bloomberg API. 
  3. Reduces data usage: Performing analysis and calculations in the cloud means you only download the data you need (as opposed to all the raw data necessary for an analysis), making it much less likely that you will hit the daily and monthly Bloomberg data download limits.
  4. No programming required: Leverage what you know of Excel--no need to learn R or another programming language / software environment. 

How do I get started with BQL? 

  • BQLX: Support documentation. In addition to an Excel Formula References (formulas for the two query methods, cell referencing, display parameters, helper formulas), Bloomberg supplies "Getting Started" guides - including video tutorials, spreadsheets, and cheat sheets - for equities, funds, fixed income, economics, and portfolios.
  • NI FFM BQL: Bloomberg "Functions for the Market" case studies featuring the practical application of BQL. 
  • "BQL Builder": Found in the Excel ribbon (includes sample queries).

 

Owen Minde of Bloomberg introduces BQL on stage at Bloomberg for Education NYC 2019.