Wednesday, May 22, 2013

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
'

31 comments:

  1. Hi, thanks for giving us this snippet. However I modified this into those for Historical data but the codes crashed. I have been trying to find out how to grab data out for a ticker with a specific date

    ReplyDelete
  2. I think having this blog is fantastic. I happen to come across it when I was searching how to solve this problem I have. It would be wonderful if you could reply to me :)

    Thanks so much Mikael!

    ReplyDelete
  3. Thanks for reading my blog and I am delighted to hear that someone is getting something out from it. I am having one week off from desk unfortunately, but back there next week. I could take a look at that part of the code, which retrieves historical data from BBCOM server. I could create one update post to opening up this topic later that week at some evening after work. Hopefully this is not some extremely urgent issue for you at the moment.

    I have noted myself too, that BBCOM API is extremely sensitive and crashes very easily and moreover, doing so without any warnings. So, remember to press save button a lot when you are working with the code.

    At least, that example code for retrieving snapshot data SHOULD be working without any problems.

    -Mikael

    ReplyDelete
  4. Thanks Mikael! This blog is very useful for my work :)
    No worries. Next week is ok because in the meantime I will try to resolve the problem. I know the part i get a crash is whenever i move from calling the class then it moves into the module in the class and when it reaches the part where it reads the object it crashes. Ok I might just be rambling. Lets discuss this next week! Enjoy your break!

    ReplyDelete
  5. For 0.5

    Check my latest blog update for BB Wrapper
    http://mikejuniperhill.blogspot.fi/2013/06/bloomberg-v3com-api-wrapper-update-for.html

    -Mike

    ReplyDelete
  6. Hi Mike! Thanks so much for this. I seem to have some difficutly with the .getData function. I get type mismatch when I call that function. Let me try to see whats the issue! If I cant I might need to ask you again. Once again thank you so much.. this is a great progress for me :)

    ReplyDelete
  7. Hmm .. some possible solutions for your problem:

    1) Create a reference to Bloomberg API COM 3.5 Type Library (VBA editor (press ALT+F11) --> Tools --> References --> Bloomberg API COM 3.5 Type Library)?

    2) Set that API class name to be "BCOM_wrapper"?

    3) What about the data types what you are FEEDING to getData function? getData(ENUM_REQUEST_TYPE, Variant, Variant, String, Optional String, Optional Date, Optional Date) - we must deliver exactly these data types.

    4) What about the data structure, into which you will RETURN THE RESULT from that getData function? My tester program I have set the result to be : Private r As Variant


    -Mike

    ReplyDelete
  8. type mismatch - sounds like some inconsistency with required/given data type in your program. I advice you to check that all parameters going into getData function are correct, and that result in which the function returns result array is x as variant.

    I suppose you can run that tester program in here without errors:
    http://mikejuniperhill.blogspot.fi/2013/06/bloomberg-v3com-api-wrapper-update-for.html

    -Mike

    ReplyDelete
  9. Hey Mike, I managed to find out whats causing the issue. Now working to modify the way I generate the values per a specific date I want. The issue of typedata is due to the date format. Managed to get around it by declaring the date as DATE.
    Thanks so much!

    ReplyDelete
  10. Great! That was exactly what I suspected.

    Well, have a great time with that wrapper and good luck. Check out my LinkedIn profile and connect (http://fi.linkedin.com/in/katajamaki). Have a nice summer.

    -Mike

    ReplyDelete
  11. Hey Mike so sorry to disturb you on this again, I ran for a list of 1000 stocks and the program crashes. It works for 3 tickers but not more.. i get this error message pure virtual function call..

    ReplyDelete
  12. it starts off with range out of bounds and then it crashes. I discovered it crashed in the catch function... hmmm

    ReplyDelete
  13. The problem was the following: there are some stocks, which do not have any time-series available for some field. Output array inside wrapper class is being re-dimensioned for each of the security, based on the number of data points found for a time-series in concern. Now, if there is not any data points found (nItems = 0), re-dimensioning fails and the program crashes (since this procedure is made while being connected with BBCOM server). Now, I corrected this problem by creating immediate exit point from method, if number of data points is zero.

    ReplyDelete
  14. Oh thats amazing. But Mike how did you see that. i cant even track the error. It just crashed..

    ReplyDelete
  15. Well, I did not actually saw anything. I investigated that Excel sheet you sent me (250 stock tickers) first, by using plain BDP function to get that PE_RATIO for each of these 250 stocks. I noticed, that for 7 stocks (if I remember correctly) there were no such value available (PE_RATIO). That rang the bell immediately. My hypothesis was now, that this (no values available for time-series) is the source for error. After this, I created a test case, taking just 2 stock tickers having #NA for BDP-function and debugged program inside BCOM_wrapper (Yes, experiencing all those "C++ pure virtual function call" error messages and re-opening Excel after each crash). There (method getServerData_historical) I noticed, that output array redimensioning will fail if the number of itmes in time-series is zero.

    Like I mentioned, I corrected this problem by creating immediate exit point from method, if number of data points is zero. Plus a couple of other small fixes. I updated my recent posting with these corrections also.

    About crashings: the program will crash with no hope of any recovery, if you terminate the program or there is going to be runtime error, while being inside EVENT in wrapper class. For this reason, debugging Bloomberg API is a bit tricky activity.

    -Mike

    ReplyDelete
  16. Hi Mike,

    Your code works great. I have encountered a problem with the following Bloomberg Field: "PX_ROUND_LOT_SIZE". For IBM US EQUITY the value should be 100 (This is what bloomberg excel returns using BDP(IBM US EQUITY,PX_ROUND_LOT_SIZE). The error message that i receive is Run Time Error 458 "Variable uses an automation not supported in Visual Basic". Thanks for your help.

    Chris

    ReplyDelete
  17. Chris, take a look at the update in the newest version of BCOM wrapper. Should be working now. There is also explanation of this problem.

    -Mike

    ReplyDelete
  18. Hi Mikael,

    Thanks a lot for posting this! Can we use Bloomberg's internal functions such as MCAL and DES in coding?
    Thanks a lot in advance.
    Fazil

    ReplyDelete
  19. Short answer is not with wrapper, since it only handles market data. There is some interesting things what you can do with DDE, for example:

    http://mikejuniperhill.blogspot.fi/2013/05/printing-bloomberg-historical-price.html

    ReplyDelete
  20. Hi, I used session.Start but couldn't get it work. Can anyone please tell me what's wrong? bbcomm.exe was running. Thanks in advance.

    ReplyDelete
    Replies
    1. It worked. Sorry about that

      Delete
  21. Hi Mikael,
    Your post is really helpful. I have a question about beqsrequest. When handling the response, I used
    securities = message.GetElement("securityData") and got run-time error 5 - invalid procedure call or argument. I can't see what's wrong. It worked for "responseError". Could you please help? And any other elements of beqs response message other than responseError and securityData? What about elements of the beqsrequest?
    Thanks a lot in advance.

    ReplyDelete
    Replies
    1. I get the same error... Does anyone know how to solve this?

      Delete
  22. Hi Mikael,

    Thank you very much for your post, it is really helpful. Thank you for sharing ! I use your code to create a function which perform a request on 30 stocks:
    r = b.historicalData(s, f, CDate(DateFrom), CDate(DateTo), , , "ALL_CALENDAR_DAYS", "PREVIOUS_VALUE")

    s is an array of 30 ISINs and f="CUR_MKT_CAP"

    When i execute this function, Excel freezes until the request is finished. Is it inherent to the BB Api or do you think it would be possible to change this behaviour ?

    many thanks for your help !

    ReplyDelete
  23. Dear Mikael,
    Thank's a lot for this amazing code. I've spent some time incorporating some functionalities in our VBA/MATLAB code using Bloomberg API, but recently got stuck with field overrides. By analysing your code, everything became crystal clear!!!

    Thanks a lot,

    Marcin

    ReplyDelete
  24. Hi Mr Mikael,
    Thank you so much for the codes.
    I try to modify your code to get documentation files of the field. However, it doesn't work out, I am amateur on programming as well as on finance. Here is the link:

    http://1drv.ms/1Hv7Xml

    Again, thank you so much in advance.

    Nhat

    ReplyDelete
  25. Hello,
    I'm using your BBCOM wrapper for few years now and it's a great job.
    I updated my access to 2016 and once I try to call BBG it crashes immediately. Any idea of what is going on?

    ReplyDelete
  26. My 2 cents : update to this (http://mikejuniperhill.blogspot.fi/2013/08/bloomberg-v3com-api-wrapper-update-2.html) and then check if your machine is having required DLL file available (referencing in VBA editor : Bloomberg API COM 3.x). I have tested this version already with Excel 2016 and it is working without problems.

    ReplyDelete
  27. For me, this sounds exactly like missing DLL case.

    ReplyDelete
  28. Hi - after reinstalling the terminal I have a "Can't find a project or library" error and I can see that VBA now has a reference to API COM3.5 vs 3.0 before. Is the code compatible with API COM 3.5 or is there an issue with that latest version? Thanks for the help

    ReplyDelete