Bloomberg V3COM API wrapper for VBA

Note, that there is an updated version available for this Bloomberg wrapper. You can find it from here: New Bloomberg V3COM API wrapper

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.

Main program walkthrough: 

• 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:

         Dim b As New BBCOM_wrapper

• 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:

         Dim securities(0 To 1) As Variant
         securities(0) = "EUR Curncy"
         securities(1) = "THB Curncy" 

• And we have 3 data fields:

         Dim fields(0 To 2) As Variant
         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:

         Dim data As Variant  

• BBCOM_wrapper object takes 2 arguments at its public interface (securities array, fields array):

         data = b.getData(securities, fields) 

• 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:

         Set b = Nothing 
  
Have a good day and I wish all the best for you in your journey to the deepness of Bloomberg API.
-Mike

'TAG: BBCOM WRAPPER
Option Explicit
'
' private constants for BCOM API
Private Const CONST_SERVICE_TYPE As String = "//blp/refdata"
Private Const CONST_REQUEST_TYPE_REFERENCE As String = "ReferenceDataRequest"
'
' private data structures
Private bInputSecurityArray() As Variant
Private bInputFieldArray() As Variant
Private bOutputArray() As Variant
'
' BCOM objects
Private bSession As blpapicomLib2.Session
Private bService As blpapicomLib2.Service
Private bRequest As blpapicomLib2.REQUEST
Private bSecurityArray As blpapicomLib2.Element
Private bFieldArray As blpapicomLib2.Element
Private bEvent As blpapicomLib2.Event
Private bIterator As blpapicomLib2.MessageIterator
Private bIteratorData As blpapicomLib2.Message
Private bSecurities As blpapicomLib2.Element
Private bSecurity As blpapicomLib2.Element
Private bSecurityName As blpapicomLib2.Element
Private bSecurityField As blpapicomLib2.Element
Private bFieldValue As blpapicomLib2.Element
Private bSequenceNumber As blpapicomLib2.Element
Private bFields As blpapicomLib2.Element
Private bField As blpapicomLib2.Element
'
Public Function getData(securities() As Variant, fields() As Variant) As Variant()
    '
    ' this is the public interface function
    bInputSecurityArray = securities
    bInputFieldArray = fields
    redimOutputArray
    '
    openSession
    sendRequest
    catchServerEvent
    releaseObjects
    getData = bOutputArray
End Function
'
Private Function redimOutputArray()
    '
    Dim nSecurities As Long, nFields As Long
    nSecurities = UBound(bInputSecurityArray)
    nFields = UBound(bInputFieldArray)
    ReDim bOutputArray(0 To nSecurities, 0 To nFields)
End Function
'
Private Function openSession()
    '
    Set bSession = New blpapicomLib2.Session
    bSession.Start
    bSession.OpenService CONST_SERVICE_TYPE
    Set bService = bSession.GetService(CONST_SERVICE_TYPE)
End Function
'
Private Function sendRequest()
    '
    Set bRequest = bService.CreateRequest(CONST_REQUEST_TYPE_REFERENCE)
    Set bSecurityArray = bRequest.GetElement("securities")
    Set bFieldArray = bRequest.GetElement("fields")
    appendRequestItems
    bSession.sendRequest bRequest
End Function
'
Private Function appendRequestItems()
    '
    Dim nSecurities As Long: nSecurities = UBound(bInputSecurityArray)
    Dim nFields As Long: nFields = UBound(bInputFieldArray)
    Dim i As Long
    Dim nItems As Integer: nItems = getMax(nSecurities, nFields)
    For i = 0 To nItems
        If (i <= nSecurities) Then bSecurityArray.AppendValue CStr(bInputSecurityArray(i))
        If (i <= nFields) Then bFieldArray.AppendValue CStr(bInputFieldArray(i))
    Next i
End Function
'
Private Function getMax(a As Long, b As Long) As Long
    '
    getMax = a
    If (b > a) Then getMax = b
End Function
'
Private Function catchServerEvent()
    '
    Dim bExit As Boolean
    Do While (bExit = False)
        Set bEvent = bSession.NextEvent
        If (bEvent.EventType = PARTIAL_RESPONSE Or bEvent.EventType = RESPONSE) Then
            getServerData_reference
            If (bEvent.EventType = RESPONSE) Then bExit = True
        End If
    Loop
End Function
'
Private Function getServerData_reference()
    '
    Set bIterator = bEvent.CreateMessageIterator
    '
    Do While (bIterator.Next)
        '
        Set bIteratorData = bIterator.Message
        Set bSecurities = bIteratorData.GetElement("securityData")
        Dim offsetNumber As Long, nFields As Long, i As Long, j As Long
        Dim nSecurities As Long: nSecurities = bSecurities.Count
        '
        For i = 0 To (nSecurities - 1)
            '
            Set bSecurity = bSecurities.GetValue(i)
            Set bSecurityName = bSecurity.GetElement("security")
            Set bSecurityField = bSecurity.GetElement("fieldData")
            Set bSequenceNumber = bSecurity.GetElement("sequenceNumber")
            offsetNumber = CInt(bSequenceNumber.value)
            '
            For j = 0 To UBound(bInputFieldArray)
                If (bSecurityField.HasElement(bInputFieldArray(j))) Then
                    Set bFieldValue = bSecurityField.GetElement(bInputFieldArray(j))
                    bOutputArray(offsetNumber, j) = bFieldValue.value
                End If
            Next j
        Next i
    Loop
End Function
'
Private Function releaseObjects()
    '
    Set bFieldValue = Nothing
    Set bSequenceNumber = Nothing
    Set bSecurityField = Nothing
    Set bSecurityName = Nothing
    Set bSecurity = Nothing
    Set bSecurities = Nothing
    Set bIteratorData = Nothing
    Set bIterator = Nothing
    Set bEvent = Nothing
    Set bFieldArray = Nothing
    Set bSecurityArray = Nothing
    Set bRequest = Nothing
    Set bService = Nothing
    bSession.Stop
    Set bSession = Nothing
End Function
'
'TAG: MAIN PROGRAM
Sub tester()
    '
    Dim b As New BBCOM_wrapper
    Dim securities(0 To 1) As Variant: securities(0) = "EUR Curncy": securities(1) = "THB Curncy"
    Dim fields(0 To 2) As Variant: fields(0) = "SECURITY_NAME": fields(1) = "PX_BID": fields(2) = "PX_ASK"
    Dim data As Variant: data = b.getData(securities, fields)
    Set b = Nothing
End Sub
'