Guides: Bloomberg Help Guide: API/Excel

Business FAQ

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:

From the PC's start menu, find the Bloomberg application, right-click it, and select Open file location
Run Install Office Add-Ins
Click Install on the pop-up window
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.
Log in to Bloomberg
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:

In the Bloomberg software, load a table.
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.
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.
Select the first column of pasted values.
Select Data > Text to Columns.
Select Fixed width.
Continue the wizard as desired, or click Finish.

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

Help

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.

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

Lippincott Library

Email Me

Contact:

Van Pelt-Dietrich Library Center
3420 Walnut Street
2nd Floor West

215-898-5924

Bloomberg Help Guide