Skip to Content

Automated Bloomberg data scraping without an API

Posted on 6 mins read

Imagine that - hypothetically - you have access to a Bloomberg terminal along with the Excel Add-In but you don’t have access to the python / R / C API and you want to programatically extract Equity data from the service. What’s the best way to do it?

Problem Specification

For a given index, we want to get a list of constituents of that index for each date in a date range. For a given constituent, we want to get values for a range of metrics for a range of dates.

For example, I want to get a list of the constituents of the S&P 500 for each end of month for the last 10 years, and for each ticker that appears at least once I want to get the open, close, low, high and volume of the ticker.

Use the Excel Add-In

The Bloomberg Excel Add-In is eminently hackable. We will use two functions from the Bloomberg Excel Add-In.

Getting lists of constituents

To get constituents, we use =bds(). This stands for Bloomberg Data Set. It is used to pull… well, data sets. We can use it, specifically, to get the constituents of an index for a particular date by using the following form

    =bds("SPX Index","INDEX_MWEIGHT_HIST","END_DATE_OVERRIDE","20110101","cols=2;rows=165")

where

SPX Index is the index name
INDEX_MWEIGHT_HIST is the dataset we are calling
END_DATE_OVERRIDE tells the function that we want a historical date, not the latest date
20110101 is the date we want

This will return a list of constituents for the S&P 500 on the 1 Jan 2011.

Now, we can’t just use this as our master ticker list. Why? Survivorship bias. We need to ask for the constituent list for a range of dates, because stocks drop in and out of the list all the time. How do we eliminate survivorship bias? Well, the first step is to repeat this bds() call for each date in our analysis. In order to avoid hitting Bloomberg’s servers too hard, let’s just stick to end of months. So for each month in our analysis, we will obtain a list of constituents of the S&P 500. To do this, we simply change the "20110101" field to a different date over and over again.

Saving each constituent date to a separate csv file

I’m not going to give you the VBA code. BUT I can outline the logic for you. You want to write a VBA sub (or multiple subs) that -

  1. Starts at the top of a list of end-of-month dates
  2. Moves one cell down to the next date
  3. Copies that date
  4. Pastes it in an appropriate cell so that the bds() function above uses that new date
  5. Waits until the bds() call returns updated data for the new date
  6. Saves the worksheet with the new bds() data to a custom-named .csv file in a CONSTITUENTS folder.
  7. Returns to step 2 in a loop intil
  8. The end of the date range is reached

Combining the lists of constituents

Ok, so now if you’ve done this right, you have lots of csv files in a CONSTITUENTS folder. Each file is for a single date, and in the file there is a list of S&P 500 constituents for a particular date. Now we want to write a sub that -

  1. Creates a new csv file
  2. For each file in the CONSTITUENTS folder a. Open the file b. Copy the contents of the file c. Append it to the end of the new csv file d. Close the file
  3. Take only unique values from the new csv file
  4. Save that list of unique values to a MASTER TICKER LIST.csvfile.

Getting stock-level data

You should have a MASTER TICKER LIST.csv file which lists every unique ticker that appears in your historical index constituent lists. We can use this list to automate the querying the Bloomberg Excel Add-In for ticker-level data.

To get historical data for a ticker with the Add-In, we use the =bdh() (Bloomberg Data History) function. An example of the usage would be

=bdh("GFI SJ Equity","PX_LAST"","1960-01-01","2017-12-31","FX=ZAR","Dir=V","Dts=S","Sort=D","Quote=C","QtTyp=Y","Days=T","Per=cd","DtFmt=D","UseDPDF=Y")

where

GFI SJ Equity is the ticker
PX_LAST is the data field (last price in this case)
1960-01-01 is the start date
2017-12-31 is the end date
FX-ZAR is the currency you want it reported in
Per=cd is periodicity (daily)

This will return daily last price for GFI SJ Equity from 1960 to 2017. We can put many more fields into the data field option - just point that argument to an array of several fields in your worksheet.

How do you know which fields to ask for? Well, that’s a bit of trial and error - but explore the Bloomberg wizard in Excel to get a feel for what the mnemonics are.

Now that we have the basic form of a =bdh() query, we can use that with our MASTER TICKER LIST.csv to automatically pull the same data for all the stocks to ever lappear on a particular index. How?

Well, use Import Data to import the MASTER TICKER LIST.csv data to your workbook.

Then, build a VBA sub that does the following:

  1. Refresh the .csv data pull
  2. Start at the top of the list
  3. Copy the selected value to an appropriate cell so that the =bdh function uses that ticker for its query
  4. Wait for the query to complete
  5. Save that completed query as an appropriately named csv to an appropriate folder
  6. Move one cell down the ticker list
  7. Repeat (3-6) until the end of the list is reached.

Putting it all together

If you’ve built it correctly, you will have one Excel workbook with 3 macros, and two folders.

Macro 1 will save all constituents in a specified index to a CONSTITUENTS folder.

Macro 2 will combine all the contents of the CONSTITUENTS folder into a single ticker list and save it as a MASTER TICKER LIST.csv

Macro 3 will import the MASTER TICKER LIST and pull pre-specified Bloomberg data for each ticker on the list and save it to a TICKER DATA folder.

You can now point your python or R environment to the .csv folders and build the data frames necessary for deeper analysis.

Some gotchas

Look out for -

  1. Waiting for Bloomberg query to complete - if you simply wait or pause the VBA sub, the query pauses as well. You need to kill the sub and call it again when the wuery is complete
  2. Make sure that your currencies are consistent! Especially important for global data
  3. A company’s market data ticker may be different from its fundamental ticker, so you need to devise a way to pull market data from the exchange of your choosing and then pull fundamental data from the appropriate fundamental dicker for that stock.
  4. Decide on your overall folder structure beforehand otherwise it’s going to get messy.

Disclaimer

I am assuming that the above workflow does not violate any Bloomberg ToS and that I am allowed to share it with the public. If this is incorrect, please email me and let me know and I will take it down.