Using Bloomberg in VBA

Do you ever have the need to access a Bloomberg function from VBA? Let's say you're writing a VBA dialog box, and you need access to a bit of market data. Sure, you can write a formula using BLP and read it off the sheet, but that's rather inflexible.

The way to do it is by using the Bloomberg.Data COM Control.

Public Sub SimpleBBTest()
    Dim objDataControl As Object
    Set objDataControl = CreateObject("Bloomberg.Data")
    If objDataControl Is Nothing Then
        Debug.Print "Cannot instantiate " _
               & "BLP_DATA_CTRLLib.BlpData"
        Exit Sub
    End If
    Dim arrayFields, vtResult
    arrayFields = Array("PX_LAST")
    objDataControl.subscribe "222388209 CUSIP Pfd", _
         1, arrayFields, Nothing, Nothing, vtResult, False
    Debug.Print "The current price for CFC B Pfd is " _
         & vtResult(0, 0)
End Sub

Run the code,
and you'll see the following in the Immediate window:

The current price for CFC B Pfd is 15.9199

Please note that both input and output to the Bloomberg Data Control's subscribe funtion takes arrays of strings.

And what if you need to use Bloomberg's over-rides?

Public Sub BloombergVbaTest()
    Dim objDataControl As Object
    Set objDataControl = CreateObject("Bloomberg.Data")
    If objDataControl Is Nothing Then
        Debug.Print "Cannot instantiate BLP_DATA_CTRLLib.BlpData"
        Exit Sub
    End If
    Dim arrayFields, overrideFields, overrideValues, vtResult
    arrayFields = Array("CDS_FLAT_SPREAD")
    overrideFields = Array("SW_EFF_DT", _
        "MATURITY", "SW_SPREAD", "CDS_QUOTED_PRICE", "CDS_RR")
    Dim upfront As Double, upfrontStr As String
    upfront = 43
    upfrontStr = "" & (100 - upfront)
    overrideValues = Array("20081224", _
        "20140320", "500", upfrontStr)
    objDataControl.subscribe "SPV6037W Corp cusip", _
        1, arrayFields, overrideFields, _
        overrideValues, vtResult, False
    Debug.Print "The flat rate equivalent " _
        & "for RDN with 43% upfront and 500 bps running is " _
        & vtResult(0, 0)
End Sub

This should give you something like this:

The flat rate equivalent for RDN with 43% upfront and 500 bps running is 2689.178

Why this blog?

Why did I create this blog? Because I have been using Excel and its related technologies for a very long time--since Excel 3.0 on Windows 3.x, and still run into problems.

Of course, one can search for Excel related articles on the Internet, but they are inevitably of the simple variety, such as how to use VLOOKUP et al.

There needs to be a resource for really advanced Excel users. I don't mean just how do I do something in VBA. I mean more difficult problems, such as... How do I use the Bloomberg API from Excel VBA? How do I create CLR hosted COM controls that are Excel dispatch automation compatible? How can I pass arrays of structures from VBA to managed code?

Many times I've thought--there should be a book on this. I suppose I could write a book, but I've decided to write a blog instead. So here it is...