Bloomberg Excel Add-in, formally known as Bloomberg API (Applications Program Interface), is a powerful tool that allows you to extract Bloomberg data directly to Excel spreadsheets.
Use the Excel Add-in when you want to customize your datasets or when you want to request for time-series data on multiple securities with multiple fields at one go.
Tip: The downloaded data may contain Bloomberg formulas. If you want to view the data on your own computer, try the Excel Copy and Paste Special as Values functions to copy the data to another worksheet.
If you are using the Excel plugin for the first time, you have do a one-time installation.
To install Bloomberg Excel plugin, close all Excel spreadsheets and follow this path: Start > All Programs > Bloomberg > Install Excel Add-In.
Once installed, the Bloomberg tab will appear in the Excel ribbon.
If there is an error in installation, run Start > All Programs > Bloomberg > Bloomberg API Diagnostics > Start & Repair, and try to install the add-in again.
Bloomberg has various pre-built templates to help users retrieve data. Below are some of the templates for your reference. Please open/use them on a Bloomberg terminal. Otherwise, figures would not be properly shown.
For more templates, type DAPI <GO>, and select 5) Sample Spreadsheet.
The Import Data Wizard provides a step-by-step guided process to assist you in finding the required data and bringing them into an Excel spreadsheet.
Using the Import Data Wizard to draw data from Bloomberg into a spreadsheet is easy and efficient. The macros in the wizard create Bloomberg formulas to deliver three main data types of your choice, which include:
For instance, you are looking for historical time series data. Here are the key steps to draw data using the Import Data Wizard:
|Step 1||Select Import Data from the Bloomberg Menu OR click the Import Data icon on the Bloomberg toolbar.|
|Step 2||In the Select data type window, click the second option: Historical End of Day.|
|Step 3||Select the security. If you have multiple or a list if securities, consider typing each ticker followed by the market sector in an Excel spreadsheet. With these tickers in Excel, the Import Data Wizard lets you choose "Import from a spreadsheet" to upload a list of tickers to the wizard at one go. This save you time on selecting each security one by one using the security search function in the wizard.|
|Step 4||Select data fields/variables.|
|Step 5||Select Period (calendar or fiscal Year) and Time Frame.|
|Step 6||Set Currency and other History Parameters.|
|Step 7||Set Pricing Parameters.|
|Step 8||Set Layout Options, including Field Orientation (vertical or horizontal) and Time Order (chronological or reverse).|
If you cannot find the data you want on the Bloomberg screen and the Import Data Wizard, try Bloomberg formulas.
Also, unlike the Import Data Wizard that requires you to go through the step-by-step process to retrieve data in specific layouts, Bloomberg formulas allow you to customize your data set and its layout in the way you want.
Bloomberg offers three formulas for different needs:
BDP (Bloomberg Data Point) is for static or real time current data. It returns data to a single cell in your Excel spreadsheet. This formula contains only one security and only one field.
Syntax: =BDP(security, field)
Example: =BDP("goog us equity","px last") retrieves the price of the last trade on Google Inc.
BDH (Bloomberg Data History) is for historical end of day and historical intraday data. It returns the historical data for a selected security/set of securities and timeframe.
Syntax: =BDH(security, field,start date [mm/dd/yyyy], end date [mm/dd/yyyy], optional arguments)
Example: =BDH("goog us equity","EBIT","1/1/2005","12/31/2009","per=cy","curr=USD") retrieves the annual Earnings Before Interest and Tax of Google Inc. from 1/1/2005 to 12/31/2009 in USD.
BDS (Bloomberg Data Set) returns informational bulk data or multi-cell descriptive data to your Excel spreadsheet.
Syntax: =BDS(security, field, optional arguments)
Example: =BDS("goog us equity","top_20_holders_public_filings") retrieves the current top 20 shareholders of Google Inc.
To explain the syntax further, the mandatory arguments to build your formulas are security and field for BDP and BDS formulas and additionally, start date and end date for BDH formulas. Security refers to any Bloomberg ticker.
To be more productive especially when you have a list of tickers, use cell referencing, so that you can drag and apply the same formula for one security to the other securities. Use it for other fields and dates too!
To look up for the fields to put into your formulas, we recommend 2 choices: Either go to FLDS <GO> to do a keyword search or click the Field Search icon on the Bloomberg toolbar. The Field Search icon on the Bloomberg toolbar is the same as the field search in the Import Data Wizard. If you still cannot find the fields you want, press Help key twice to consult with the Bloomberg Helpdesk.
Optional arguments for BDH formulas are values relating to currency, periodicity, date output format, spreadsheet direction/orientation, etc. As an example, in order to change to a common currency say US Dollar for a list of companies reporting in many different currencies, you can try this formula: =BDP("ock sp equity","historical_market_cap", "eqy_fund_crncy=usd", "fundamental_database_date=04/19/2010"). Note how the currency override function, ie. "eqy_fund_crncy=usd, is used in the formula. For a comprehensive list of optional arguments for BDH formulas, see Appendix A of the user guide to Bloomberg Formulas: Function Reference, pp. 12-14.
Tip: Function Builder guides you through on creating a Bloomberg formula to retrieve your needed data into an Excel spreadsheet. Click here for a graphical explanation of the Function Builder tool. Use it to familiarize yourself with the Bloomberg formulas!