Welcome to Business Research Plus

27 July 2011 1 comment

Business Research Plus header 2015

From specialist databases to business literature, Business Research Plus provides advice and tips based on The University of Manchester Library Business Data Service resources and expertise.  See our About page for more details.

  • Library Research Plus provides expert insight from The University of Manchester Library’s Research Services for researchers in all disciplines including business.
  • My Learning Essentials, the Library’s award-winning skills progamme includes online resources with a wealth of useful tips on searching, referencing, writing to support personal and professional development.

Exploring our resources – try the Business and Management Resources page (Subject Guides),  scroll down to category or tag cloud in the right-hand column or search this blog (top right). For latest news see @UML_BDS 

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.

 

 

Finding ESG data in Datastream using the ASSET4 template

25 October 2016 Leave a comment

A detailed source of environmental, social and governance (ESG) data can be found in Thomson Reuters Datastream.

Instead of choosing the appropriate datatypes in a regular Datastream time series request, you can download the ASSET4 template. This is hard to find in the InfoBase support platform (replacement for Extranet) so I suggest you get it from the Datastream Excel add-in toolbar, although this method is not quite as easy as you would like.

Open Datastream in Excel the usual way (remember the desktop icon “DSSetup – Shortcut”). Click on the Datastream ribbon tab, go to the Request Tables group and click Sample Sheets. Tick the button ‘Equities : ESG ASSET4 – Sector Industry  analysis’ then click the Download button.

ASSET4 - find the template

Find the ASSEST4 template with the ‘Sample Sheets’ button and download it.

The Download & Open button does the same as the Download button, it doesn’t open the file.

ASSET4 template location

ASSET4 template downloads to a hidden folder.

The template takes the form of a macro-enabled Excel workbook and is downloaded to a hidden folder. Click on Start > Computer, click in the address bar and type the following:
C:\ProgramData\datastream\datastream advance\User

Note that ‘ProgramData’ is not the same as ‘Program Files’ or ‘Program Files (x86)’, it is a hidden folder. As you type the rest of the path, Windows should suggest auto-completions, which you can accept.

ASSET4 file location

ASSET4 file location, in the hidden C:\ProgramData folder.

Find downloaded ASSET4 (animated)

ASSET4 file location (animated)

From here, open the file and accept any warnings about enabling content.

ASSET4 intro

ASSET4 introduction

There are many sheets (tabs), starting with Home. Click on ESG Filters to select your search criteria. Choose (1) a sector or industry such as Telecommunications Services and (2) criteria across the environmental, social and corporate governance categories such as Water Use Total, Women Managers and Size of Board. When ready, click the Go button.

ASSET4 search criteria

ASSET4 search criteria

Wait a few minutes while the data is downloaded to the Data Table sheet.

ASSET4 data table

ASSET4 data table

The tables and charts of the many other sheets are automatically updated to reflect your search (except the Data Fields definitions sheet). For example, the Bottom 5’s sheet.

ASSET4 example results

ASSET4 example results

If you find a datatype you would like to use in a regular time series request, make a note of its code.

See also earlier post Environmental, Social and Governance (ESG) Data from July 2013.

Advanced support for using the Bloomberg Excel add-in

4 August 2016 1 comment

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.

 

Reshaping wide and long data in Stata

29 July 2016 Leave a comment
Reshape from long to wide via Stata

Reshape from long data (left) to wide data (right) via Stata

Downloading data from a platform like WRDS can give you the data you need but not in the layout you desire. This post shows you how to change from long data to wide data; that is, from a stacked column of companies to a column for each company, using date as unique identifier. It requires the use of Stata, available on PCs at the Library Finance Zone. (Version 14 was used in this test but it should work just as well on others.)

Preparation

Excel data (long)

Long data in Excel

In Excel, keep only the essential columns (you can add the others back later using VLOOKUP). In this example, some kind of ID (like PERMNO), Date and Return.The first row of the sheet contains the variable names. Save this file as type CSV (Comma delimited) *.csv, ignoring any warnings about features or multiple sheets. Close the file.

Stata import text delimited

Import text data delimited in Stata

Open Stata, click File > Import > Text data (delimited, *.csv). Click the Browse button to select your CSV file the click OK. You will see a command like the following was performed: import delimited P:\Reshape\long.csv. You can look at a summary of the data by typing list in the Command window.

Performing the command

The reshape command in Stata works in two modes.

  • Wide: instead of repeating dates and companies in long columns, create a new column for return for each company so that there is only one row for each date. This is also called long-to-wide. (You could instead have a column for each year with one row per company.)
  • Long: the reverse process, also called wide-to-long.

Here we are going from wide-to-long, with Date as the unique variable (i). We will lose the current Return column. Each new column will be Return for each company ID (j), with a name that is Return followed by that ID, such as Return100500, Return222622. The command to type in the Command window is:

reshape wide return, i(date) j(id)
Stata reshape

Reshape in Stata (click to enlarge)

After this is typed, you will get a summary of what just happened. If the text is in red, an error occurred, often if there are other variables that were not mentioned in the reshape command. You can list more than one variable to be reshaped (the command might then be reshape wide return volume price, i(date) j(id) for example).

Again, you can type the list command to see the data.

Finishing

Stata export

Export to Excel from Stata

You probably wish to export the data to Excel format to continue with your work. To do this, click File > Export > Data to Excel spreadsheet (*.xls, *.xlsx). Click the Save as button to choose where you wish to save to, and if you want older (pre-2007) or current Excel format. Tick the box “Save variable names to first row in Excel file” and press OK. You will see a command generated like this:  export excel using “P:\Reshape\reshaped.xlsx”, firstrow(variables)

Excel results

Now you have wide data in Excel!

You can now open the new file in Excel to see the reshaped data set.

More information

The Institute for Digital Education and Research at UCLA have two excellent example pages, one for reshaping long-to-wide and one for reshaping wide-to-long.

 

 

Categories: Data Analysis Tags: , ,

Use pairs in one VLOOKUP with historical exchange rates from Datastream

20 July 2016 Leave a comment

One of the most useful Excel functions I help students with is VLOOKUP – extremely useful to automatically bring in data from one table to another. If you have company accounts data from Compustat Global (via WRDS) for multiple companies in multiple currencies, you will need to bring in the change rate for each currency to show every field in US Dollars.

A B C D E
1 Company Currency Total Assets Local Exchange rate Total Assets USD
2 Co. X AUD 100 =VLOOKUP(B2, currencies, 2, FALSE) =C2*D2
3 Co. Y PLN 400 =VLOOKUP(B3, currencies, 2, FALSE) =C3*D3

Where ‘currencies’ is a named range elsewhere in the workbook:

From Currency Rate
AUD 1.3158
PLN 3.95685
USD 1

The VLOOKUP command looks for the currency code in the first column and returns the rate from the second column (use ‘FALSE’ to ensure an exact match). The conversion is simply a multiplication. (Note that you can convert from USD to USD at a rate of 1.0.) But what about making sure that the exchange rate is correct for the date of the data, and what if you have data from multiple years?

Performing VLOOKUP when matching a pair of variables

Thanks to Professor Marie Dutordoir for suggesting this kind of technique!

Usually you will want to look at accounts data over several years and for several companies. If you are working with several currencies then you will need a more complicated solution to this VLOOKUP exchange rate table, considering a day/month/year for each rate-currency pair. You do not need a second lookup table or other Excel functions, you just need to have another variable of date and a variable of currency-date pairs.

Global company accounts data

Global company accounts data. Given column G (currency) and column H (year), create new column F (map = currency&year).

The new column F (mapping) is formed from currency and year, so F2 contains “=G2&H2” (see image above). The ‘currencies’ named range now begins with a different first column produced in the same way (see image below). A VLOOKUP always matches on the first column of its range, and now this one pulls the rate from the fourth column, so rate is “=VLOOKUP(F2, currencies, 4, FALSE)” for row 2.

In the ‘currencies’ named range, I have combined the exchange rate calculation and lookup into one formula (column K). Repeat this for each data type that you wish to convert the currency for. Remember to have an entry for USD at rate 1 for each year.

paired-vlookup-lookup

Stacked historical exchange rates in a named range ‘currencies’. Created column A (map) from B&C.

This example only has the average exchange rate for each year, you may wish to extend this approach for monthly or even daily rates. If you do this, you will need to take extra care with the format of the mapping cells, but this exceeds the scope of the post.

Where to find historical exchange rates?

Use the Datastream Navigator to find exchange rates

Use the Datastream Navigator to find exchange rates. Start by searching one category (Exchange Rates), limit it “To Currency: United States Dollar”, then use the search box for the source currency.