Understanding Bloomberg data API has been a long-term project for me. After using BDP- and BDH-functions in Excel, I started my woodshedding with some copy-pasted examples from Bloomberg WAPI<GO> site. Then, I used to have this one huge monolithic, unmanageable and frightening black box module for all data retrieving. It was actually working fine, but I always had uncomfortable feeling in my stomach, when I was using that module. Finally, I wanted to gain some deeper understanding of this API and I spent a lot of time debugging code and investigating different objects. Today, I feel much more comfortable with Bloomberg API. So, what have I learned? Why all this self torture? After all, do I still have working black box solution for getting market data out from Bloomberg? Yes, but now I know exactly how and why it is working and if something goes wrong some day, I will have a pretty good idea what can go wrong and why, and how to fix it without spending my weekend with debugger. Plus, I have the skills to do some Bloomberg-related development for someone else.
Depending on programming background, learning Bloomberg data API can be at least a bit of challenging at first. You should be well familiar with the use of objects in VBA, handling multidimensional arrays and debugging tools. What I am presenting in this post, is an example of stand-alone VBA class for retrieving snapshot data from Bloomberg by using VBA wrapper. You can start to debug example code and try to replicate it on your own. By transcribing/copying code what someone else has created, I have learned a lot of things and also got some kind of intuition what all the objects involved are doing. You can use my example program first as a black box to "get the job done". Then later, you can start to take a look more closely with debugger how it is working and why.
So, the ultimate goal is that we will have a generic VBA Bloomberg data wrapper class, what we could use easily in any VBA projects to retrieve any type of data for a given ticker/field combination. This example class presented here can only retrieve snapshot data. However, from BBCOM server you can also get historical and bulk data. When you start to feel comfortable enough with V3COM API common objects and logic, you can study that stuff in WAPI<GO> site (Desktop v3 API) and extend your wrapper class so, that it can also retrieve historical and bulk data.
Simple step-by-step data retrieval example by using VBA wrapper is given here:
- In your VBA project, insert a new Class Module and name it to be "BBCOM_wrapper".
- Copy-paste the code below here under the tag BBCOM WRAPPER into a newly created VBA Class Module.
- Declare V3COM API (dll-file): VBA editor (press ALT+F11) --> Tools --> References --> Bloomberg API COM 3.X Type Library (this should be located here: C:\blp\API\blpapicom.dll. If it's not there, you should contact bloomberg and ask for their help)
- Copy-paste the code below here under the tag MAIN PROGRAM into a newly created VBA Standard Module.
- First we will create BBCOM_wrapper object, called b. The purpose of this object is to fetch market data from BBCOM server, based on tickers and fields we give for the object:
- We need to define variant array for securities and fields (note: array indexing starts at zero). For example, we want to get security name, bid and ask for USD/EUR and THB/USD currency. We have 2 securities:
securities(0) = "EUR Curncy"
securities(1) = "THB Curncy"
- And we have 3 data fields:
fields(0) = "SECURITY_NAME"
fields(1) = "PX_BID"
fields(2) = "PX_ASK"
- We have to define data structure, into which we copy the result what we get from BBCOM_wrapper object:
- BBCOM_wrapper object takes 2 arguments at its public interface (securities array, fields array):
- As a result (data), we now have data structure (variant array of arrays), having the following dimensions (0 to 1, 0 to 2) in other words: 2 securities, both having 3 different fields. Finally (since we want to honor all good programming principles) we destroy BBCOM_wrapper object:
Have a good day and I wish all the best for you in your journey to the deepness of Bloomberg API.