Bloomberg V3COM API wrapper update for VBA

In one of my previous posts, I presented one possible approach for creating a wrapper for Bloomberg API snapshot data http://mikejuniperhill.blogspot.fi/2013/05/bloomberg-v3com-api-wrapper-for-vba.html

In this post, I am updating that implementation for creating that Bloomberg API wrapper, but now enabling data retrieving process for

1. Reference data (Ex. previous close bid for EUR Curncy)
2. Bulk reference data (Ex. all curve members for USD swap curve - retrieving ISIN codes + yellow key)
3. Historical data (Ex. daily close prices for EONIA Index between 31.5 - 24.6.2013)

In our tester program, we have data retrieving examples for all of these three different categories.

• First, we retrieve curve members for four different swap curves (bulk reference data).
• Then, we retrieve security name and previous close value for first members (overnight index) for each of the four curves (reference/snapshot data).
• Finally, we retrieve daily historical data between 31.5.2013 - 24.6.2013 for overnight index for each of the four curves (historical data). Note, that historical data retrieves date/value-pairs.

In that tester program below, the result data from Bloomberg server is returned into a variant array. You should investigate the content of this array in your VB editor (Locals window), to get some idea about how the data is packed in that result array. Needless to say, you have to know how to handle multidimensional arrays, to be able to "unpack" data from that result array.

The idea of this wrapper class is to serve as a simple black box interface into BBCOM server to retrieve any type of desired market data. Design is, that there is no design at all. The functionality of Bloomberg API can safely assumed to be quite stable, so I made a decision to keep everything inside one monolithic class for the maximum user convinience reasons. By that way, it is now very easy to import this one wrapper class into a new VBA project and just start to use it. Remember to create reference to Bloomberg API COM 3.5 Type Library in your VB editor before using this class. 

If there is anything unusual going on with this, just let me know. For the time being, I will collect all the possible fixes and updates into this posting. I hope this helps in your working with Bloomberg-related market data. Have a nice day.
- Mike

UPDATES

8.7.2013
Most of the data types from BCOM API are converted implicitly into corresponding VBA data types without any problems, such as

BLPAPI_CHAR = VBA String
BLPAPI_DATE = VBA Date
BLPAPI_FLOAT64 = VBA Double
BLPAPI_STRING = VBA String

However, BCOM data type BLPAPI_INT32 was causing runtime error 458 (Variable uses an automation not supported in Visual Basic). I assume this data type to be Unsigned Integer. Problem has been fixed by converting this data type explicitly into VBA Long data type. You can investigate Datatype property for bFieldValue object in wrapper method getServerData_reference.

' VBA standard module
Option Explicit
'
Private b As BCOM_wrapper
Private r As Variant
Private s() As Variant
Private f() As Variant
'
Sub tester()
    '
    Set b = New BCOM_wrapper
    '
    ' EXAMPLE 1 - retrieve bulk data (curve members)
    ' create security array for four curves ID
    ReDim s(0 To 3)
    s(0) = "YCSW0023 Index" 'USD swap curve
    s(1) = "YCSW0045 Index" 'EUR swap curve
    s(2) = "YCSW0004 Index" ' CAD swap curve
    s(3) = "YCSW0018 Index" ' ZAR swap curve
    ' redim field array
    ReDim f(0 To 0)
    f(0) = "INDX_MEMBERS" 'curve members
    r = b.getData(BULK_REFERENCE_DATA, s, f) ' <---- investigate array content
    '
    '
    '
    ' EXAMPLE 2 - retrieve snapshot/reference data (security name and previous close)
    ReDim s(0 To 3)
    s(0) = r(0, 0) 'USD overnight
    s(1) = r(1, 0) 'EUR overnight
    s(2) = r(2, 0) 'CAD overnight
    s(3) = r(3, 0) 'ZAR overnight
    ' redim field array
    ReDim f(0 To 1)
    f(0) = "SECURITY_NAME"
    f(1) = "PX_CLOSE_1D"
    r = b.getData(REFERENCE_DATA, s, f) ' <---- investigate array content
    '
    '
    '
    ' EXAMPLE 3 - retrieve daily historical data for all overnight indices between 31.5 - 24.6
    ReDim f(0 To 0)
    f(0) = "PX_CLOSE_1D"
    r = b.getData(HISTORICAL_DATA, s, f, "CALENDAR", "DAILY", CDate("31.5.2013"), CDate("24.6.2013")) ' <---- investigate array content
    '
    '
    '
    ' UPDATE 08.07.2013 -  (BCOM server returns data type BLPAPI_INT32, which will be converted into VBA Long data type)
    ' EXAMPLE 4 - retrieve current ask size for IBM US Equity
    ReDim s(0 To 0): s(0) = "IBM US Equity"
    f(0) = "ASK_SIZE"
    r = b.getData(REFERENCE_DATA, s, f) ' <---- investigate array content
    Debug.Print r(0, 0)
    '
    '
    ' release object
    Set b = Nothing
End Sub
'
'
'
'
' VBA Class module (Name=BCOM_wrapper)
Option Explicit
'
' public enumerator for request type
Public Enum ENUM_REQUEST_TYPE
    REFERENCE_DATA = 1
    HISTORICAL_DATA = 2
    BULK_REFERENCE_DATA = 3
End Enum
'
' constants
Private Const CONST_SERVICE_TYPE As String = "//blp/refdata"
Private Const CONST_REQUEST_TYPE_REFERENCE As String = "ReferenceDataRequest"
Private Const CONST_REQUEST_TYPE_BULK_REFERENCE As String = "ReferenceDataRequest"
Private Const CONST_REQUEST_TYPE_HISTORICAL As String = "HistoricalDataRequest"
'
' 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
Private bDataPoint As blpapicomLib2.Element
'
' class non-object data members
Private bRequestType As ENUM_REQUEST_TYPE
Private bNumberOfDataPoints As Long
Private bCalendarType As String
Private bFrequency As String
Private bMaxDataPoints As Long
Private bStartDate As String
Private bEndDate As String
Private nSecurities As Long
Private nSecurity As Long
'
Public Function getData(ByVal requestType As ENUM_REQUEST_TYPE, _
ByRef securities() As Variant, ByRef fields() As Variant, _
Optional ByVal calendarType As String, Optional ByVal dataFrequency As String, _
Optional ByVal startDate As Date, Optional ByVal endDate As Date) As Variant()
    '
    bRequestType = requestType
    bInputSecurityArray = securities
    bInputFieldArray = fields
    '
    If (bRequestType = ENUM_REQUEST_TYPE.HISTORICAL_DATA) Then
        '
        bCalendarType = calendarType
        bFrequency = dataFrequency
        '
        If ((startDate = CDate(0)) Or (endDate = CDate(0))) Then _
            Err.Raise vbObjectError, "Bloomberg API", "Input parameters missing for historical data query"
        bStartDate = convertDateToBloombergString(startDate)
        bEndDate = convertDateToBloombergString(endDate)
    End If
    '
    openSession
    sendRequest
    catchServerEvent
    releaseObjects
    getData = bOutputArray
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()
    '
    Select Case bRequestType
        Case ENUM_REQUEST_TYPE.HISTORICAL_DATA
            ReDim bOutputArray(0 To UBound(bInputSecurityArray, 1), 0 To 0)
            Set bRequest = bService.CreateRequest(CONST_REQUEST_TYPE_HISTORICAL)
            bRequest.Set "periodicityAdjustment", bCalendarType
            bRequest.Set "periodicitySelection", bFrequency
            bRequest.Set "startDate", bStartDate
            bRequest.Set "endDate", bEndDate
            '
        Case ENUM_REQUEST_TYPE.REFERENCE_DATA
            Dim nSecurities As Long: nSecurities = UBound(bInputSecurityArray)
            Dim nFields As Long: nFields = UBound(bInputFieldArray)
            ReDim bOutputArray(0 To nSecurities, 0 To nFields)
            '
            Set bRequest = bService.CreateRequest(CONST_REQUEST_TYPE_REFERENCE)
            '
        Case ENUM_REQUEST_TYPE.BULK_REFERENCE_DATA
            ReDim bOutputArray(0 To UBound(bInputSecurityArray, 1), 0 To 0)
            Set bRequest = bService.CreateRequest(CONST_REQUEST_TYPE_BULK_REFERENCE)
            '
    End Select
    '
    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 catchServerEvent()
    '
    Dim bExit As Boolean
    Do While (bExit = False)
        Set bEvent = bSession.NextEvent
        If (bEvent.EventType = PARTIAL_RESPONSE Or bEvent.EventType = RESPONSE) Then
            '
            Select Case bRequestType
                Case ENUM_REQUEST_TYPE.REFERENCE_DATA: getServerData_reference
                Case ENUM_REQUEST_TYPE.HISTORICAL_DATA: getServerData_historical
                Case ENUM_REQUEST_TYPE.BULK_REFERENCE_DATA: getServerData_bulkReference
            End Select
            '
            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, i As Long, j 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))
                    '
                    If (bFieldValue.DataType = BLPAPI_INT32) Then
                        bOutputArray(offsetNumber, j) = VBA.CLng(bFieldValue.Value)
                    Else
                        bOutputArray(offsetNumber, j) = bFieldValue.Value
                    End If
                End If
            Next j
        Next i
    Loop
End Function
'
Private Function getServerData_bulkReference()
    '
    Set bIterator = bEvent.CreateMessageIterator
    nSecurity = nSecurity + 1
    '
    Do While (bIterator.Next)
        Set bIteratorData = bIterator.Message
        Set bSecurities = bIteratorData.GetElement("securityData")
        Dim offsetNumber As Long, i As Long, j As Long
        Dim nSecurities As Long: nSecurities = bSecurities.Count
        '
        Set bSecurity = bSecurities.GetValue(0)
        Set bSecurityField = bSecurity.GetElement("fieldData")
        '
        If (bSecurityField.HasElement(bInputFieldArray(0))) Then
            Set bFieldValue = bSecurityField.GetElement(bInputFieldArray(0))
            '
            If ((bFieldValue.NumValues - 1) > UBound(bOutputArray, 2)) Then _
                ReDim Preserve bOutputArray(0 To UBound(bOutputArray, 1), 0 To bFieldValue.NumValues - 1)
            '
            For i = 0 To bFieldValue.NumValues - 1
                Set bDataPoint = bFieldValue.GetValue(i)
                bOutputArray(nSecurity - 1, i) = bDataPoint.GetElement(0).Value
            Next i
        End If
    Loop
End Function
'
Private Function getServerData_historical()
    '
    Set bIterator = bEvent.CreateMessageIterator
    Do While (bIterator.Next)
        Set bIteratorData = bIterator.Message
        Set bSecurities = bIteratorData.GetElement("securityData")
        Dim nSecurities As Long: nSecurities = bSecurityArray.Count
        Set bSecurityField = bSecurities.GetElement("fieldData")
        Dim nItems As Long, offsetNumber As Long, nFields As Long, i As Long, j As Long
        nItems = bSecurityField.NumValues
        If (nItems = 0) Then Exit Function
        If ((nItems > UBound(bOutputArray, 2))) Then _
            ReDim Preserve bOutputArray(0 To nSecurities - 1, 0 To nItems - 1)
        '
        Set bSequenceNumber = bSecurities.GetElement("sequenceNumber")
        offsetNumber = CInt(bSequenceNumber.Value)
        '
        If (bSecurityField.Count > 0) Then
            For i = 0 To (nItems - 1)
                '
                If (bSecurityField.Count > i) Then
                    Set bFields = bSecurityField.GetValue(i)
                    If (bFields.HasElement(bFieldArray(0))) Then
                        '
                        Dim d(0 To 1) As Variant
                        d(0) = bFields.GetElement(0).GetValue(0)
                        d(1) = bFields.GetElement(1).GetValue(0)
                        bOutputArray(offsetNumber, i) = d
                    End If
                End If
            Next i
        End If
    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
'
Private Function convertDateToBloombergString(ByVal d As Date) As String
    '
    ' convert date data type into string format YYYYMMDD
    Dim dayString As String: dayString = VBA.CStr(VBA.Day(d)): If (VBA.Day(d) < 10) Then dayString = "0" + dayString
    Dim MonthString As String: MonthString = VBA.CStr(VBA.Month(d)): If (VBA.Month(d) < 10) Then MonthString = "0" + MonthString
    Dim yearString As String: yearString = VBA.Year(d)
    convertDateToBloombergString = yearString + MonthString + dayString
End Function
'
Private Function getMax(ByVal a As Long, ByVal b As Long) As Long
    '
    getMax = a: If (b > a) Then getMax = b
End Function
'