Business Research Plus Research expertise from the Business Data Service of The University of Manchester Library Home About Help with this site Type text to search here... Home > Business Databases, company information > Advanced support for using the Bloomberg Excel add-in
Advanced support for using the Bloomberg Excel add-in 4 August 2016 Phil Reed Leave a comment Go to comments
Introduction
This post is a collection of frequently and infrequently asked questions about the Bloomberg Professional API, most specifically the Bloomberg Excel Add-in. For the most part, it should be considered for experts, so please don’t be disheartened if it is too advanced for you; it’s effectively everything I know about the topic. The questions are as follows. 1. 2. 3. 4. 5.
Where is the add-in? I don’t have it. Which ways can one use Bloomberg and Excel together? Where can I find the field codes? I don’t want to be restricted to Excel, can I use Java, C++, .NET, Python or R? Where can I get more help?
Where is the add-in? I don’t have it.
If the ribbon tab labelled “Bloomberg” is not showing in Excel, close it then click on Start > All Programs > Bloomberg > Install Office Add-Ins. Follow the instructions, but be prepared to run the little install program a few times, perhaps opening and closing Excel and running it again.
Which ways can one use Bloomberg and Excel together? Terminal copy or export
Some of the ways to get data from Bloomberg to Excel (remember to work at the terminal though!). Top-left (FA): Red menu > Output > Excel > Current Template (data is live). Top-right (G Options > Copy Data to Clipboard (then paste in Excel). Bottom-left (MEMB): Red menu > Output > Excel (data is fixed). Bottom-right (CHNG): Not possible to export report data. Various functions in the Bloomberg terminal let you get the on-screen data into Excel in different ways. Look for commands such as “Copy data to clipboard”, “Output > Excel” or similar, by clicking on the red menu bar (Actions) or right-clicking on charts or data. Sometimes the action will download and open a new Excel document, either with the data written in directly or loading later via the Bloomberg API. Sometimes the data is copied to the clipboard for you to paste into a worksheet of your choice. Remember that a sheet which contains Bloomberg formulas to load live data may not load on a PC without Bloomberg unless you save as CSV or copy/paste-as-values. (This is also true for all the Excel options below.) Excel templates
Bloomberg template library, browsed in Excel. Could also use XLTP function in terminal. Usually most useful if you are looking up one company, bond, exchange rate or commodity, a Bloomberg template will give you a detailed Excel workbook filled with data and visualisations that are updated live from Bloomberg via the Excel API. Amber coloured fields are editable, often to change the company, country, sector, date or other variable. (See the first image in the post.) The templates can be found in the terminal with the XLTP function, and in Excel under Bloomberg > Explore > Template Library. Excel import
Use the Import Data menu in Excel to get historical end of data and other data. A commonly used feature that is described in our Bloomberg Workbook (available in the Bloomberg Suite and at the Precinct Library) is the Historical End of Day wizard. In Excel, click Bloomberg > Import > Import Data > Real-Time / Historical > Historical End of Day. The wizard will let you type security identifiers or select from a common index, then choose your data types and data range. It will then produce the results in the cell you selected. Excel function builder
Build a function from scratch. The formula in cell B1 is =BDP(“AAPL US EQUITY”, “INDUSTRY_SECTOR”) and the value is Technology. If you want a little more control, use the function builder, found in Bloomberg > Create > Function Builder. This more advanced tool will expose the Bloomberg API to you, starting by asking you to choose one of three major Bloomberg Functions: BDP: (Bloomberg Data Point) Import a single data point of current data. BDH: (Bloomberg Data History) returns the historical data for a selected security. BDS: (Bloomberg Data Series) imports a set of bulk data such as peers. For your chosen function, you will be asked to type in a security (such as “AAPL US EQUITY”), a field (such as “INDUSTRY_SECTOR” or “PX_LAST”) , and dates (depending on the function). The tool will suggest auto-completion if you don’t happen to know the exact security or field code. It will only suggest valid responses. You can add optional extra parameters such as orientation=H|V, currency, or “array=True” which puts all the output data into one cell instead many rows/columns (requires array formulas afterwards). Note that row and column counts will be added as extra parameters automatically after the formula has called. The security, field and dates can be written into the formula or referenced from other cells. Notice that the security ID needs to end with what kind of entity it is, so equities end “EQUITY”, bonds end “CORP” etc. Excel manual function creation
In the formula in cell B1, by replacing the security ID with a cell reference A1, you can then copy the formula down or across. Once you have used the function builder, you will have a working formula that you may wish to copy out for each of your many securities, fields or dates. If you use a cell reference for this variable, you may copy the formula across or down. For example, you can have a list of security identifiers in column A {AAPL US EQUITY, IBM US EQUITY, VOD LN EQUITY, …} and the formula in column B =BDP(A1, “INDUSTRY_SECTOR”) and copy down the formula in column B. What if your formula produces data in two dimensions and you need to leave a gap between each call, for example with amendment history of bonds? I have addressed that problem by writing a Python script to prepare the formulas and spacing. Assuming noblanks.txt is a file with one security ID per line (without the “CORP” bit) and withblanks.txt is our output: 1 2 3 4 5 6 7 8
fin = open('noblanks.txt') fout = open('withblanks.txt', 'w') for line in fin: id = line.rstrip() fout.write(id + "\t=BDS(\"" + id + "Corp\",\"AMENDMENT_HISTORY\",\"cols=3;rows=100\")" fout.write('\t\n' * 100) fin.close() fout.close()
The generated file is tab-delimited and can be opened in Excel for Bloomberg to action. The first column is the security ID (without the “CORP” bit), the second column contains the formulas, and there are 100 blank rows between each Bloomberg call to ensure enough space. There is room to improve this approach!
Where can I find the field codes? The mnemonic codes for each field are not the same as in the Bloomberg terminal but can be looked up using the FLDS function. The auto-complete feature of the function builder in Excel is a good alternative.
I don’t want to be restricted to Excel, can I use Java, C++, .NET, Python or R? In theory, yes, that is possible, although you will need technical support and all the necessary development environment to be set up on a Bloomberg terminal. In summary, the APIv3 needs be installed (from the WAPI function) which provides the necessary libraries for Java, C, C++ and .NET. To use R (or RStudio), you’ll need to connect via Java (with the standard rJava library and Rbbg library from the http://r.findata.org/ repository). Python connects via the C library. This is too advanced to be part of the regular Business Data Service, sorry! Last year, I worked with PhD candidate Ali Bayat, and we got the following R script working from RStudio with R 3.1.3 and Java 8u22. [Thank you, Ali.] 1 2 3 4 5 6
install.packages("rJava") install.packages("Rbbg", repos = "http://r.findata.org/ library("rJava") library("Rbbg") conn <- blpConnect() bdp(conn, "AMZN US Equity", "NAME")
Where can I get more help? As mentioned, our white binder Bloomberg Workbook (at the Bloomberg Suite and Precinct Library) describes how to use the historical end of day import from Excel. There is help throughout the Excel add-in (look for white question mark in a blue circle icons). The templates all have a help sheet (coloured green tab). Remember to press the F1 key at any terminal function to get context-sensitive support.
Report this ad
Report this ad
Share this: Email
Print
More
Tweet
Related
CEO compensation - which database is best?
Extending your Excel worksheet formulas via Python, for Capital IQ
Financial Database Certification - Updated
Categories: Business Databases, company information Tags: Bloomberg Professional, Excel Comments (1) Trackbacks (1) Leave a comment Trackback 1. craig 4 August 2016 at 5:22 pm Reply Besides excel one can also use MATLAB. One needs the regular license and also an add-on license for the Datafeed Toolbox. It’s great for intraday data collection. 1. 17 March 2017 at 2:39 pm Extending your Excel worksheet formulas via Python, for Capital IQ | Business Research Plus
Leave a Reply Enter your comment here...
Finding ESG data in Datastream using the ASSET4 template Reshaping wide and long data in Stata RSS feed
Email Subscription Enter your email address to subscribe and receive notifications of new posts by email. Join 1,209 other followers Enter your email address Sign me up!
Referencing tips
Categories (category cloud below) Select Category
Recent Posts Dissertation Research 13 new ESG Scores released on Datastream Updated conditional statements feature in WRDS Getting a linking table from Compustat via WRDS Cloud using SSH and SAS Company Screening in ‘Capital IQ’ Converting Company Identifiers for Quoted Companies
Top Posts Risk Free Rate for UK and US Referencing databases (Bloomberg, Datastream etc.) Where can I find historical rates for U.S. treasury bills, notes, and bonds? Risk Free Rates on Datastream Company Identifiers in Datastream. Total Return on Bloomberg CEO compensation - which database is best? Total shareholder return (share price return) CUSIP - 6 digit cusip to 9 digit cusip / permno / gvkey Risk Free Rate and Fama French factors
UML.BDS tweets There will be no Research Consultation this Tuesday, 24 October. Apologies for any inconvenience caused. The next session will be Thursday. 5 months ago Dissertation Research bizlib247.wordpress.com/2017/10/19/dis… https://t.co/uSkDRor2ni 5 months ago 13 new ESG Scores released on Datastream bizlib247.wordpress.com/2017/10/10/13-… https://t.co/7hmjDn6IOj 5 months ago Updated conditional statements feature in WRDS bizlib247.wordpress.com/2017/10/04/upd… https://t.co/bHe1ryDEZM 5 months ago Getting a linking table from Compustat via WRDS Cloud using SSH and SAS bizlib247.wordpress.com/2017/06/27/get… https://t.co/nQJtPuetAq 9 months ago
Blogroll Aberconway Library (Cardiff) Academic Trends (INSEAD) BizKnowledge Watch from IESE BizResearch blog from LBS Business & Economics Information Solutions (Warwick Library) Corporate Law and Governance Databaser: on financial databases Datapoints from Lippincott Library (Wharton, UPENN) Erasmus Data Service Center (Erasmus University Rotterdam) Judge Business Info (Cambridge) LSE Library Blog Ohio University Business Blog PGR Doc Blog (Humanities Researcher Development @ University of Manchester) Research Financial (Vrije Universiteit Amsterdam)
Apps
Business Databases
Business Libraries Careers company
information Data Analysis Economic data FAQ updates General Business Library guides Management literature MBS - EDL and PL New Books News & Feedback off-campus Referencing Uncategorized UoM Research Updates Video demos
Tag Cloud ABI Inform accounting annual reports Apps
Bloomberg Professional bonds Business authentication Bankscope
BoardEx
Source Premier business_news Capital IQ careers Compustat country_intelligence credit ratings CRSP CSMAR CUSIP data databases
Datastream
e-journals Emerald endnote eScholar Eventus Excel Factiva FactSet ExcelConnect Fame Fast Answers migration Financial Times Global Financial Data (GFD) GMID Google scholar Harvard IBES import e-books
iPad Apps IPO Journal Citation Reports journal ranking KeyNote London Stock Exchange LSPD marketing market_analysis
mergers_and_aquisitions Microsoft online training Orbis PI Navigator reference regional_data research risk free rate SAS Scopus screening SDC SDC Platinum SPSS Stata Statistics thesis Thomson ONE
ThomsonONE.com Thomson One
Banker Thomson Research Web of Knowledge world development indicators Worldscope
WRDS Eddie Davies Library - Flickr
More Photos
Business Research Plus Stats 817,950 hits August 2016 M T W T F S S 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 « Jul
Oct »
Pages – testing About Fast Answers (FAQ) Guides Help with this site
Report this ad
Top Create a free website or blog at WordPress.com.
