The aim of this tutorial is to demonstrate how to use the Bloomberg Excel Add-In in order find the present constituents of the S&P 500 index and download end of day price data for each.
After opening the Bloomberg terminal and Excel begin by:
- Type “SPX Index” into the command line and pressing Enter to load the index.
- Next, we will find the Bloomberg field that will show us the constituents of the index. Type FLDS in the command line to display a window that will allow us to search for fields associated with the SPX Index ticker. Search for “members” and click on “Show Bulk Data …” in the Value column. This will display a list of current tickers of the securities that compose the Index. Feel free to browse other fields; if you ever have trouble finding specific information in the terminal, press the Help key twice to bring up a chat with the Bloomberg Help Desk.
- Now, we will load the index member data into Excel. One could copy/paste from the terminal, but Bloomberg provides a mechanism to directly download and display data in Excel. We will be downloading bulk data (data that will take multiple rows/columns) and use Bloomberg’s BDS Excel function for the download. In cell A1, type the formula “=BDS(“SPX Index”,”INDX_MEMBERS”) where here the first argument of BDS is a Bloomberg ticker and the second is a field for which we would like to obtain data. After hitting Enter, slightly more than 500 tickers and associated exchange codes should display in column A.
- Next, we would like to obtain end of date prices for each stock that composes the S&P 500. First, we identify each security as an equity by typing the formula “=CONCAT(A1,” Equity”)” in cell B1 and then copying this formula down column B. Then, copy and paste by value the contents of Column B into Column C (see screenshot above).Now, we will obtain historical end of day price data for the a single security using Bloomberg’s historical data function BDH. Open up a new Excel sheet, and in cell A1, type: “=BDH(“APPL US Equity”, “PX_LAST”, “1/1/2015”, “12/31/2016″)” and press enter. You should see end of date prices displayed as in the screenshot below. Also, the formula you typed will self-update to add an additional argument similar to “cols=2;rows=504”.
- Now, we are in a position to obtain end of day price data for each security in the index. Copy the dates in Column A by value starting and paste into cell A2 in a new sheet. Then copy transpose paste the tickets into Cell B2 of the new sheet so that they are all in the first row. Finally, in cell B2, enter the formula “=BDH(B$1,”PX_LAST”,$A2,$A2)” and copy and paste this formula throughout the sheet. After waiting, you should see your sheet update with end of day prices for all S&P 500 stocks as in the below screenshot.
Exercise 1: Use the FLDS function to search for other besides price that you are interested in, e.g. daily volume and repeat the tutorial using that field.
Exercise 2: Try to replicate this tutorial using the Bloomberg Import Data tool. Specifically, in the Bloomberg tab in Excel, click the Import Data icon and then the historical data option. Search for securities that you would like to retrieve information for (or even load the security list we build in this tutorial from a .csv file), and follow prompts for the tool. Can you construct a similar end of date price dataset to the above?