Archive

Posts Tagged ‘Bloomberg Professional’

Advanced Company Screening in Bloomberg Professional

Students often enquire about creating a list of companies in a particular area…sector of the economy, or industry.

 

Equity Screening

 

The Equity Screening function [EQS <GO>] in Bloomberg is an obvious starting point. This allows a number of criteria to be specified, to build a search, step by step.

 

When ‘Country of Domicile’ is selected, the expanding menus on the left hand side of the screen give a breakdown from geographical regions to individual countries. However, it is actually faster to type in the country name in the search field, in this case, ‘United States’.

Click ‘Update’. This gives 12,455 companies.

 

Repeat for ‘Sectors’. Here, the expanding menus can come in useful, as you may be uncertain of the category names to enable a keyword search. A recent student enquiry requested US ‘Medical Equipment’ companies. This was located by expanding the options, starting with ‘Health Care’ – and then selected. Click ‘Update’ to incorporate ‘Medical Equipment’ into the search. This gives 202 companies.

 

Results

EQS Results Summary

 

Click on ‘See Results’ to list the companies:

 

List

EQS Company List

 

Whilst there is an icon at the top right of the screen to Export the results to another application, it can be more effective to save the list of companies through the ‘Actions – Save’  drop down menu. Enter a file name (‘US Medical Device Companies’ specified here) and click on ‘Save’.

 

Save

EQS: Actions – Save

 

Excel Add-In

 

To gain access to the results from the EQS search, for additional research, the Excel Add-In interface for Bloomberg provides a number of dedicated search options. The ‘Import Data’ feature is particularly useful. This gives a number of Wizard-type options, with standard screens allowing search selections to be entered. For example:

Import Data  –  Real-Time/Historical  >  Historical End of Day

 

Step 1: Create a List of Securities.

 

Import Data

Import Data search screen.

 

The ‘Select securities’ section allows the source to be specified from a drop menu of options. This includes Indices and EQS – Equity Screening. Once EQS is selected, the field below (Saved Screens) gives access to file names saved – in this case ‘US Medical Device Companies’ – normally at the bottom of the list. This loads the full list of companies to the ‘Available securities’ window. From here, the complete list can be selected (‘Add all’) and copied into the ‘Selected securities’ window, or a subset, then ‘Add’ – as is the case in the screen shot below.

 

List

Step 1: Security List

 

Click on the ‘Next’ button to proceed:

 

Step 2: Create List of Fields.

Whilst there are a series of expanding menus [New Fields, Analysis, Corporate Actions, Descriptive, Earnings Estimates, Fundamentals, Market Activity, Ratings] to reveal the data items available, it is actually more effective to search by keyword(s). To illustrate, ’employees’ has been used as a search term, to locate the ‘Number of Employees’ field. Once selected, a definition is provided below. Click ‘Add’ to copy into the ‘Selected fields’ window.

 

Field

Step 2: Field Selection

 

Click ‘Next’, for Date and Frequency options: Yearly, back to 2010 specified.

 

Step 3: Select Perodicity and Time Frame.

 

Dates

Step 3: Dates / Frequency

 

The next two steps (Currency, Pricing Defaults) are not relevant to this search, so click ‘Next’ to proceed to

Step 6: Layout Options.

 

Layout

Layout Options

 

Click on ‘Finish’ to display results – it may be necessary to widen columns.

 

Results

Results in Excel

 

Although a simple example, this could include many additional data items for each company, for which Excel is well suited.

 

Bloomberg Professional is available to current students and staff of The University of Manchester.

Company Financial Statements: Comparisons in Different Databases

Students are often confused when viewing company financial statements (Income Statement, Balance Sheet, Cash Flow Statement) in different databases. The reason being, that the values displayed, for the same company, often don’t match.

 

Commercial vs Educational Emphasis

This confusion is understandable. Surely values for the same company, for the same statement, should be identical? In explaining this apparent anomaly, the primary purpose of specialised financial databases needs to be appreciated. That is, to provide data/information to allow analysis of many types of securities – different classes of assets, such as Equities – or companies.

The key user groups are within business and commerce. However, there is sufficient useful content, to also be of value to educational establishments, and therefore students – but business users have priority.

For example, when seeking to gauge the relative performance of two or more companies, from different countries, with different accounting standards, there is a clear logic in being able to compare on a like-with-like basis. Therefore, when looking at a value in an Income Statement, for example, it would be clear which company had superior performance. To compare companies otherwise, the so-called apples with oranges analogy, lacks transparency and is ineffective.

Hence, the evolution of common methods for calculating accounting values in different databases, to ease comparisons between companies. Bloomberg Professional and Thomson ONE.com databases both reflect this need, but use different terminology. A look at a company example is instructive.

 

Bloomberg Professional – Financial Analysis [FA]

The Income Statement (‘I/S’) for Tesco PLC, a UK supermarket group, is set out below, using the Financial Analysis function, within Bloomberg Professional.

 

Income Statement - Adjusted

Bloomberg – Financial Analysis (Income Statement – Adjusted)

 

Note that the tab towards the top left: ‘Adjusted’, is highlighted in blue. This is the default view, meaning this is how data is normally displayed for financial statements in Bloomberg Professional. Therefore, companies from different countries can be compared on a like-with-like basis, when the same currency is selected (top right of screen). In the example above, this is set to GBP, or Great Britain Pounds.

Another option is also highlighted – the ‘As Reported’ tab. This selection would display values from the company’s official annual report and accounts.

 

Thomson ONE.com – Reuters Fundamentals

 

The Income Statement for Tesco PLC is displayed again, using Thomson ONE.com database: drop down menu selection:

Company Overviews – Fundamentals – Reuters Fundamentals

 

T1 Income Statement

Thomson ONE.com – Income Statement (Standardised)

 

The ‘Report Format’ option on the left, gives the choice of ‘Company Specific’ (official values – annual report and accounts) and ‘Standardised’.  When the ‘Standardised’ view has been selected and ‘Update View’ clicked on,  the drop down menu to choose a common currency become available, thus allowing like-with-like comparisons, between different companies.

 

Conclusions

The ability to quickly search for companies within databases such as Bloomberg Professional or Thomson ONE.com is an effective means to locate company financial statements and take advantage of like-with-like comparisons.

When viewing financial statements within databases, the realisation that a company’s annual report and accounts is the ultimate (official) source, represented by ‘As Reported’ in Bloomberg Professional and ‘Company Specific’ in Thomson ONE.com, can help to avoid confusion for students, in conducting company analysis.

 

Bloomberg Professional and Thomson ONE.com are databases available to current students and staff of The University of Manchester.

Top 10 Companies

A student recently asked the following: ‘how do I get a list of the top 10 companies in the pharmaceuticals area?’.

What ‘top 10’ means, or is meant to mean, is a little fuzzy…could this be ordering by turnover (sales), number of employees, earnings per share or other measure? Upon further questioning, the size of the company, represented by Market Capitalization, was deemed acceptable. In addition to this, the relative position of a specified company (Glaxosmithkline: GSK-LN) was queried.

 

Thomson ONE.com

One possible approach is to search for the specified company and see what options then present themselves. By typing the company name or ticker code (GSK-LN) in the search box at the top left of the screen, it is possible to select the appropriate option from those displayed. This gives a Company Overview screen to start.

 

Company Overview

Thomson ONE.com – Company Overview

 

By selecting the ‘Comparables’ screen, a number of related companies are displayed. To reach this screen, use the drop-down menus at the top of the page:

Company Views – Fundamentals – Comparables

 

GSK Related Companies

Thomson ONE.com – Comparables (GSK)

 

Whilst this gives a list of related entries in the sense that they are pharmaceutical companies, it is not clear if these represent the ‘top 10’. To do so, a measure would have to be targeted, such as Market Capitalization, using the ‘Equity Screener’ feature. This can be reached from:

Screening & Analysis – Companies – Company Screener

By entering ‘Pharmaceuticals’ within the Business Description field and ‘greater than or equal to 5000’ within the Latest Market Cap ($Mil) field [Stock & Earnings Criteria section], then clicking on the ‘Search’ button, this gives 66 results.

 

T1 Screening Search

Thomson ONE.com – Screening & Analysis search

 

Selecting the column title (‘Market Cap’) orders the companies by this field’s values. The Excel icon allows data export.

 

Search screen

Thomson ONE.com – Screening & Analysis

 

The data displayed within Excel:

 

Excel Export

Thomson ONE.com – Company List Exported to Excel

 

Glaxosmithkline appears at number 6 in the results. A particular concern with this list may relate to how precisely ‘Pharmaceuticals’ matches with companies which are so classified. For example, 3M, which appears at number 5 on the list, makes many varied products, and isn’t primarily thought of as a pharmaceutical company. Another alternative is to try features within other databases – for example, Bloomberg Professional.

The first function which suggests itself is ‘Relative Valuation’ (RV), which would seem to provide a similar role to ‘Comparables’ within Thomson ONE.com. Enter the following on the Command Line:

GSK LN  F8/Equity  RV

 

Relative Valuation

Bloomberg Professional – Relative Valuation

 

Another potential choice would be to use the Equity Screening function (EQS). This allows companies to be selected according to the Sectors (or sub-sectors) to which they are assigned. There are a number of other categories which can be used to refine a search, if so desired.

 

Sectors

Bloomberg Professional – EQS – Sectors (Pharmaceuticals)

 

By selecting ‘Pharmaceuticals’, then ‘Update’, the number of results are shown. In this case 1,146 companies:

 

EQS Results

Bloomberg Professional – EQS Results

 

By clicking on ‘Results’, a listing is displayed, sorted by the default data field – Market Cap(italization). The currency is not clear at first sight. However, by hovering the mouse pointer over the column title (Market Cap), a pop up box confirms details: Currency: GBP, representing Great Britain Pounds.

 

Pharmaceutical Companies

Bloomberg Professional – Pharmaceutical Companies

 

Glaxosmithkline appears at number 10 in this listing. Interestingly, 3M doesn’t appear within the top 21, reflecting the earlier comment on categorisation of companies in different databases.

 

So, Equity Screening within Bloomberg Professional and Screening & Analysis within Thomson ONE.com databases have provided a listing of companies sorted by Market Capitalization and represent a reasonable solution to what was initially a difficult question to address.

 

Thomson ONE.com (from Thomson Reuters) and Bloomberg Professional are available to current students and staff of The University of Manchester.

 

Related posts include:

Company Employee Data Using Different Databases: [24 February 2016],  and

Company and Industry Comparisons: [10 July 2015].

 

Help!

I am aware that the Beatles had a hit with Help!, but not the associated details, without looking it up.

Similarly with Bloomberg Professional, I may suspect that particular data is included, but not be sure, without looking it up. With a vast number of functions (10,000+) and many types of data available, this could be tricky. Fortunately, Bloomberg has an excellent Help system, including:

 

Autocomplete

You don’t need to know lots of codes to make progress. The ‘Autocomplete’ feature means you can just type a Company Name on the command line (top left of the screen) – Tesco PLC, for example (a UK supermarket group). This provides a list of results under Functions (not relevant here) and under Securities (relevant here).

SECURITIES

TSCO LN Equity                                   Tesco PLC (London)

 

This also includes the Ticker Code [TSCO LN], which stands for Tesco on the London Stock Exchange. You don’t need to know this code, you can just click on the entry to proceed.

 

Function Help

Behind every function, there is a Help screen enabling you to be become familiar with what it does and how it works. For example, the Help screen for the Financial Analysis [FA] function is shown below.

 

FA function Help

Financial Analysis Help

 

FA has the option to display financial statements (e.g. Income Statement) in a currency other than that in which the company reports its results. For Tesco PLC, that would be GBP, representing Great Britain Pounds. This could be changed to US Dollars (USD), to compare with another company based in the US.

In a training session looking at Bloomberg, a student asked the question: ‘What is the basis for the currency conversion in the example you demonstrated?’. Not knowing the answer, my first step was to press the green Help key at the top left of the Bloomberg keyboard, to display the Help screen for this function. This did not provide an answer. Therefore, I tried another Help feature, which is incredibly effective – the search engine.

 

Search Engine

By typing a few keywords on the command line and pressing the Help key, Bloomberg tries to understand the question being asked. In effect, it operates like a search engine. This typically provides about five answers, one of which is usually relevant – unlike a normal web-based search engine, delivering millions of results, but not necessarily what you are looking for.

To obtain an answer, I typed:  FA Currency Conversion  and pressed the Help key.

Help: Currency Conversion

Help: Currency Conversion

 

This produced three results, the second of which (shown above) provided a solution. Click on the title to display additional content.

 

Bloomberg Help Desk

Where you have a question for which an answer has not been forthcoming, using the above Help features, the final option is to contact the Bloomberg Help Desk. To do so, press the Help key twice. Students will see the display below.

Click on ‘Bloomberg Help Desk’ at the bottom of the screen.

 

Bloomberg Help Screen

Bloomberg Help

 

Next, type your email address and question, then click ‘Submit’, for a response within 24 hours.

Help Email

Help Desk Enquiry Screen

 

The Help system is a productive resource, saving you time, when looking up all things Bloomberg…financial data…economic data…appropriate functions for analysis.

Just don’t ask when Help! was released. [1965, for the Film, Single and Soundtrack Album]

Bond ISIN to company ISIN — EDSC manuals, tips & tricks

9 November 2016 Leave a comment

Suppose you created a list with bonds and the accompanying ISIN codes. You can’t use these ISIN codes to download information from the issuer of this bond. But here is way to use the ISIN codes you collected so far to find the ISIN code of the issuer An example: the ISIN code US44044KAA97 belongs […]

via Bond ISIN to company ISIN — EDSC manuals, tips & tricks

Bloomberg Essentials Training Program to Bloomberg Market Concepts

 

Bloomberg Essentials Training Program [BESS], better known as ‘Bloomberg Certification’, was discontinued at the end of August 2016. When entering this function code now, followed by the Enter/Go key, users are diverted to Bloomberg Market Concepts [BMC].

 

Bloomberg Market Concepts

BMC Function

 

BMC is an introductory Finance course, linked to about 70 Bloomberg functions – therefore different to BESS, which required study of content in Core (e.g. Getting Started, Bloomberg Excel Add-In) and Market Sector (e.g. Equity Essentials) areas. This provided a wider overview of Bloomberg functions and operation. Successful completion of the Core and at least one Market Sector examination resulted in a certificate.

Although a loss to keen students wishing to obtain this qualification, the content is still available within Bloomberg, from the Help pages. By typing HPL on the command line, followed by the Enter/Go key, this leads to the ‘Bloomberg Help and Learning’ pages. ‘Getting Started’ can be selected from the right hand menu.

 

Help Pages.

‘Bloomberg Help and Learning’.

 

This displays content previously available through the BESS function.

 

BESS content.

Getting Started content.

 

Other posts are available on Database Certification (mentioning BESS) and BMC.

 

 

Advanced support for using the Bloomberg Excel add-in

4 August 2016 2 comments

Introduction

An example Bloomberg Excel template

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. Where is the add-in? I don’t have it.
  2. Which ways can one use Bloomberg and Excel together?
  3. Where can I find the field codes?
  4. I don’t want to be restricted to Excel, can I use Java, C++, .NET, Python or R?
  5. Where can I get more help?

Where is the add-in? I don’t have it.

Install Bloomberg Excel add-in

  • 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

Bloomberg types of copy/export to Excel

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 (GP): Right-click on chart > Copy/Export 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

Excel template library

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<GO> function, and in Excel under Bloomberg > Explore > Template Library.

Excel import

Bloomberg Excel historical end of day

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

Bloomberg 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

Bloomberg Excel function builder manual edits

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:
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<GO> 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<GO> 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.]

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.