Wednesday, June 26, 2013

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
'

21 comments:

  1. Dear Mikael,

    First of all, thanks for a great explanation of the Bloomberg API and for a great template for using it! It does work nicely.

    I know this is not a questionaire or Q&A forum, but I do try and ask a question to you and hope for an answer. The question concerns the bulk reference data:

    Bloomberg has a pre-defined function in Excel that can get all the (e.g.) discount factors from a certain curve using the "BCurveInt(..)" function, which (among other things), take in a curve-ID and interpolation dates and then returns a "bulk" of discount factors obtained from the given curve_ID (e.g. EUR.3m) and dates.

    Now, would it be possible using the above template code, to somehow obtain these discount factors? Or maybe even call this BCurve-function within VBA, and store all calculated discount factors into your "getData"-object (in your case 'r')?

    Thanks,
    Niklas

    ReplyDelete
  2. Hi Niklas,

    Thanks a lot about reading my blog. Glad to hear that wrapper works and makes hopefully your life easier. To be honest, I have not so familiar with this function you mentioned (BCurveInt). But, I can take a look at this scheme whenever I have some excess time at some point. I have already received a couple of re-development ideas what I have been testing at this moment. But, whenever I got something on this, I will let you know. Sounds pretty useful and interesting this BCurveInt function indeed.

    -Mikael

    ReplyDelete
    Replies
    1. Hi again,

      Yes, please do inform me if and when you find something!

      Thanks in advance,
      Niklas

      Delete
  3. Hi Mikael,

    I have a Bloomberg data question. If I send a reference data array that is 10,000 Securities by 10 Fields does bloomberg look at it as 1 data request or 100,000 data requests (10,000 * 10)? I know that bloomberg places monthly limits on how much data can be downloaded. Is there a maximum amount of securities and fields that can be sent to the bloomberg API as an array? Thanks for all your help. I really enjoy your blog.

    Chris

    ReplyDelete
  4. Hi Chris! I hope you and Mike dont mind me answering this :)
    If I am not wrong, Bloomberg views it as 100,000 unique hits because I once blew the limits haha

    ReplyDelete
  5. Hi, Mikael,

    Thank you for your code. But I have a problem with it. It gives me a type mismatch error when it comes to "r = b.getData(HISTORICAL_DATA, s, f...".

    Could you please help me on this. I nead to extract a "BOND_CHAIN"s from specific securities, like "IBM US". (Convert the excel formula =BDS("XXXX Corp", "BOND_CHAIN") into VBA). I am sure there must be a solution. Thank you. Eugene

    ReplyDelete
    Replies
    1. Sorry, It would be more about how to get conditional bulk data. Like the Excel formula: =BDS("xxxx Corp", "Bond_Chain", "Chain_Years_to_Maturity_Start=5"). That would be a real challenge. Would you have any ideas.

      PS. I was debugging your code during 1h today - It works great! Thank you for it !

      Delete
    2. You have to be more specific about this problem with historical data. Can you copy-paste your main program which is calling wrapper. We could take a look into that and see if there could be something. Type mismatch error sounds to me like you are having some inconsistent input parameters for wrapper interface function ..

      Just in case, I tested for getting bond chain using BULK REFERENCE DATA for some equity and it worked well for me. I compared my wrapper results against Bloomberg SRCH function. I need to take a look at this conditional chain issue ..

      Delete
    3. I actually tester this conditional chain issue now and it works. The thing is, that I need to update that wrapper to include some extra code we have been developing with 0.5. So, stay tuned ..

      Delete
    4. Regarding the above issue with - Convert the excel formula =BDS("XXXX Corp", "BOND_CHAIN") into VBA. could someone help me with the code please?
      Many thanks

      Delete
  6. Hello world. Sorry that I have not been able to answer any of your questions, but I have just been ultra busy with work-related issues and quite naturally, didn't even want to see any computer screen at evening ..

    0.5 - thanks for answering on my behalf. Yes, be careful with your data queries, since you are going to exceed it pretty quickly. Each data point you are retrieving from BCOM server, is going to get noted by Bloomberg. How much data can you take out from it, depends on the data limit what your company has bought for you. I have been exceeding my limit now two times. For this reason, when you are testing something with VBA wrapper, use small amount of data first.

    We (I and 0.5) have been testing wrapper together and naturally found a lot of issues what you are about to face when you are dealing with large set of data. We have been sort of a brainstroming how to proceed with the issues discovered. I will start to go through these wrapper-related issues as soon as I have some excess time for that. For the time being, just hang on.

    -Sincerely yours, Mike

    ReplyDelete
  7. Thank you Mikael. Can't wait for the second version of the wraper. I am really interested to find out what is the API function to be able to conditionally extract data.
    Best,
    Eugen

    ReplyDelete
    Replies
    1. Check out my latest posting, I have been implementing override functionality. I think that was the thing you were after?

      Delete
  8. The code works very good!! Helps a lot.

    Is there a way to receive bulk data in a synchronously way? For instance If I load 100 securities, the output is distorted. So s(50) does not represent r(50,*). Is this possible?

    Best, Jort

    ReplyDelete
  9. Well, I have identified this problem for historical data. As far as I know, there is a way to solve this problem. First you get data for all 100 securities. After this, you pick up one security to be "reference security" for dates. Then you loop through the whole data set (99 securities left) and fetch data for "reference security dates" for those 99 securities left. Then you also need to define a rule for missing data. For example, use previous value if security does not have observation for a give date, And so on .. so, even it should not be technically too challenging, for sure it means a lot of coding and debugging. I have been thinking to implement this kind of optionality for historical data, maybe for the next wrapper version..

    Thanks for comment and reading!

    -Mike

    ReplyDelete
  10. Thanks Mike, I've tried your approach and it worked that way!

    ReplyDelete
  11. Well, if you did all that, the hats off for you! :)
    Have you checked the latest update of wrapper? There is a way to overcome this problem ..

    -Mike

    ReplyDelete
  12. Hi Mikael,

    This is such a great tool, thank you for that, I tried it this afternoon. However, I have a remark concerning fields that return an array of array. I tried to use the field DVD_HIST_ALL which is of type BulkReferenceData (and contains declaration date, exdate and so on ...for a stock), and the wrapper only returns the first column of this array (so only return the Declaration date, but not the rest of the data). Do you think it's easy to fix?

    Thanks again for this great tool!

    LD.P

    ReplyDelete
  13. I'd make the following change to the getserverData_historical()

    If (bFields.HasElement("date")) Then 'bFieldArray(0))) Then
    '
    'Dim d As Variant: ReDim d(0 To bFields.NumElements - 1)
    'For j = 0 To bFields.NumElements - 1
    ' d(j) = bFields.GetElement(j).GetValue(0)
    'Next j
    Dim d As Variant: ReDim d(0 To UBound(bInputFieldArray) + 1)
    d(0) = bFields.GetElement("date").GetValue(0) 'Date
    For j = 1 To UBound(bInputFieldArray) + 1
    If (bFields.HasElement(bFieldArray(j - 1))) Then
    d(j) = bFields.GetElement(bFieldArray(j - 1)).GetValue(0)
    Else
    d(j) = "#N/A"
    End If
    Next j
    '
    bOutputArray(offsetNumber, i) = d
    End If

    ReplyDelete
  14. Hello Mike ! Thank you for all this code that you put online, but i have one question please. I want to know how do you print your data when you test you BCOM_wraper module please ? thank you

    ReplyDelete
  15. Hello Mike,

    Thank you very much for posting this code...it will be extremely useful for an upcoming project I will be working on. With respect to the reference data call, I was wondering if you had any updated code or could provide some guidance as to how/where within the code it could be modified in order to pass field overrides as part of the request. I have a need to request accrued interest values and being able to override the settlement date (and any other allowable fields) would be extremely helpful.

    Chris

    ReplyDelete