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 for a user profile:
- Log into Bloomberg from a desktop shortcut
- Open the desktop's Excel application
- From the PC's start menu, open the Bloomberg folder and select 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. See below for an example of a successful attempt.
- Lastly, close and re-launch Excel. Bloomberg will appear in the ribbon.
Working in Excel
Click Spreadsheet Builder to walk through steps to build a custom table in the current worksheet.
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
Errors
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>
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.
