Skip to Main Content
Go to Penn Libraries homepage   Go to Guides homepage

Bloomberg Help Guide

What can the API do?

Quickly download historical pricing, financial ratios, and more directly into an Excel spreadsheet.

  • You must be logged into Bloomberg at the time
  • Real-time pricing not available

Enabling the Bloomberg Add-In for Excel

To enable the Bloomberg Excel add-in:

  1. From the PC's start menu, find the Bloomberg application, right-click it, and select Open file location
  2. Run Install Office Add-Ins
  3. Click Install on the pop-up window
  4. A new pop-up window will appear listing which Microsoft programs have successfully installed the Bloomberg add-in. Typically, some will have errors. You will likely need to close the installation window and try again, repeating steps 3 & 4 above. Keep trying until it lists Excel, Word, and PowerPoint as successful.
  5. Log in to Bloomberg
  6. Open Excel

You will need to perform the above steps on each machine you use before Bloomberg will appear in Excel's menu.

Working in Excel

Click Spreadsheet Builder to walk through steps to build a custom table in the current worksheet.

Excel Bloomberg ribbon menu with Spreadsheet Builder identified

  • Functions populate dynamically based on the options you select in the wizard.
  • After completing the wizard, you can adjust functions and variables generated by the wizard by editing them directly in the worksheet.

The Bloomberg Desktop (Excel) API enables a suite of special functions (examples below), for retrieving Bloomberg data.

  • BDH - Bloomberg Data History retrieves historical values (end-of-day or intra-day).
    • Syntax: BDH(Security, Field, Start Date, End Date, [option])
  • BDP - Bloomberg Data Point retrieves current or real-time values.
    • Syntax: BDP(Security, Field, [option])
  • BDS - Bloomberg Data Set populates multiple cells with descriptive data for a security.
    • Syntax: BDS(Security, Field, [option])

Within a function's syntax,

  • Security consists of a Ticker and Market Sector (e.g.: PFE US EQUITY).
  • Field refers to a mnemonic (e.g.: PCT_GEO_OWNERSHIP) as listed in FLDS <GO>.

Combined, a function could appear as follows: BDS("PFE US EQUITY", "PCT_GEO_OWNERSHIP", "cols=2;rows=50").

Access step-by-step help building a Bloomberg function by selecting Bloomberg > Function Builder from Excel's ribbon menu.

Bloomberg Query Language is a separate approach that enables more-robust, SQL-like queries.

  • BQL syntax: BQL(Instrument, Function, Range)

Access examples and step-by-step help building a BQL function by selecting Bloomberg > BQL Builder from Excel's ribbon menu.

This method copies static data without calling the Bloomberg Desktop API:

  1. In the Bloomberg software, load a table.
  2. With the cursor at a corner of the table, click and drag, selecting the headers, labels, and values you want to copy. A dashed line may appear to identify the selected area. Selected content copies to the clipboard automatically.
  3. In Excel, select a cell and press Ctrl+V to paste the copied content. This ideally pastes each copied Bloomberg row into a separate Excel cell. If paste fills multiple Excel columns, you will need to perform additional column/content adjustments before continuing.
  4. Select the first column of pasted values.
  5. Select Data > Text to Columns.
  6. Select Fixed width.
  7. Continue the wizard as desired, or click Finish.

Not all tables can be copied. Consider emailing a screenshot using GRAB <GO>.


Access help and resources through the Bloomberg Terminal:

  • DAPI <GO> - Getting Started, tools, and more for the Desktop (Excel) API
    • Help <GO> - Help and further documentation for the DAPI
  • XLTP <GO> - Bloomberg's Excel spreadsheet templates
  • FLDS <GO> - Search for fields and corresponding mnemonics to use in queries

Error Codes

Excel API functions can return empty cells (if no results match your query) or unique error codes. See several common codes below:

  • #N/A Authorization - Your terminal or account does not have permission to pull the requested data.
  • #N/A Dly Lmt or #N/A Mth Lmt - Your terminal has exceeded the maximum daily or monthly security download requests.
  • #N/A Limit - You have exceeded the maximum of 3,500 concurrent real-time security subscriptions/hits across all open tables.
  • #N/A Unknown - An error occurred, and a more specific code is not available.

For additional Excel API error codes, descriptions, and troubleshooting, submit this command in the Bloomberg application:

LPHP DAPI:0:1 1489652 <GO>

Download Limits

Bloomberg limits the number of results users can download. Once a terminal has reached its daily or monthly limit, API data will remain unavailable until the next period. For your benefit and for others', please consider how you download data.

The following approaches may help:

  • Use FLDS <GO> in the Bloomberg interface to confirm the variables you need before accessing them via API.
  • Identify exactly what attributes (e.g.: date range; company size) your research requires. Apply screens and filters to download only the items that meet your criteria.
  • Close other workbooks that contain real-time queries.
  • Set Excel to calculate formulas manually (Formulas > Calculation Options > Manual). Recalculate one cell (F2), worksheet (Shift+F9), or workbook (F9) at a time.
    In the Excel Ribbon, a cursor selects Formulas then Calculation Options then Manual

Data Portability

Data obtained through the Bloomberg Desktop (Excel) API may not leave the local machine you used to access the Bloomberg service. Enter DAPI <GO> and see the Desktop API Guidelines section to learn more about this policy and its limited exceptions.

Lippincott Library Research Team

Profile Photo
Lippincott Library
Van Pelt-Dietrich Library Center
3420 Walnut Street
2nd Floor West
Social: Twitter Page


Penn Libraries Home Franklin Home
(215) 898-7555