Using VBA BCOM wrapper to retrieve Bloomberg surface data

I always feel a bit guilty about not responding to my readers, who might be having issues with Bloomberg API stuff. This has been clearly the most popular topic on this blog. I have been planning to release a couple of new postings concerning Bloomberg market data API, based on some new things what I have learned, while working and doing things for other people.

One such an issue was also asked by one of my blog readers a couple of months ago:

"Lets say I want to request a 6x10 FX volatility surface. I usually use BDP functions with override fields. I tried the same with your code and all I managed to do is one request per maturity/strike, which means 60 separate requests. It works but it's really time consuming. Do you have any advice for a one request code, that could allow me to build one array with all my values?"

In a nutshell, the user wants to retrieve Bloomberg volatility surface into Excel with BCOM API in a single request. With Bloomberg curves, such as USD swaps curve, you have Bloomberg ID for that curve and you can retrieve all members of that curve by using Bulk reference request and field name INDX_MEMBERS to retrieve all securities within that curve. However, for volatility surfaces, there is no such scheme available and all you have, is N amount of individual security tickers.

This means, that for a single BCOM wrapper query, we have to read all those securities from some source into a one-dimensional array. Alternatively, if we would like to process those tickers one by one (I assume the reader did this), the time elapsed for such query will be intolerable. Why? Because for each query iteration, BCOM wrapper is opening connection and starting session with BCOM server. BCOM wrapper was not originally meant to be used in repetitive data queries because of this reason.

Now, back to our original problem (60 separate requests, intolerable processing time) which is not BCOM wrapper issue, but a consequence caused by the way we might handle the input data. Let us find a way to handle this input data (tickers) in a way, which enables us to create a single request for BCOM server.

STEP ONE: Bloomberg surface data in VCUB

I have been using Bloomberg VCUB function to get volatility surface data from Bloomberg. For example, Cap volatility surface matrix for SEK currency is presented in the picture below.

STEP TWO: Excel data range configurations

Now, let us retrieve tickers first. In this screen, go to Actions - Export to Excel - Tickers. CopyPaste
Cap market tickers into a new Excel workbook (blue area in the picture below).


























Blue area has all security tickers what we just downloaded from VCUB. Pink area will be filled with values retrieved from Bloomberg with BCOM wrapper for each security ticker. Define Excel range name for the blue area as "_input" and Excel range name for the pink area as "_output".

STEP THREE: VBA program

Next, add new standard VBA module into your VB editor and copyPaste the following program.

Option Explicit
'
Private Declare Function GetTickCount Lib "kernel32.dll" () As Long
Private startTime As Long
'
Private r_input As Range ' input range for Bloomberg tickers
Private r_output As Range ' output range for Bloomberg data
Private matrix As Variant ' input tickers matrix
Private result As Variant ' bloomberg results
'
Public Sub tester()
    '
    ' start counter
    startTime = GetTickCount()
    '
    ' set range for input matrix in Excel and read values into variant array
    Set r_input = Sheets("Sheet2").Range("_input")
    matrix = r_input.Value2
    '
    ' export variant array content into 2-dim array of strings
    ' and define Bloomberg field (only one!) to be retrieved
    Dim s() As String: s = matrixToSecurities(matrix)
    Dim f() As String: ReDim f(0 To 0): f(0) = "PX_MID"
    '
    ' create BCOM instance and retrieve market data
    Dim b As New BCOM_wrapper
    result = b.referenceData(s, f)
    '
    writeMatrixToRange result, Sheets("Sheet2").Range("_output")
    '
    ' stop counter and release BCOM object
    Debug.Print ((GetTickCount - startTime) / 1000)
    Set b = Nothing
End Sub
'
Private Function matrixToSecurities(ByRef matrix As Variant) As String()
    '
    ' read values from variant array into 1-dim array
    Dim nRows As Integer: nRows = UBound(matrix, 1)
    Dim nCols As Integer: nCols = UBound(matrix, 2)
    Dim nSecurities As Integer: nSecurities = (nRows * nCols)
    Dim s() As String: ReDim s(0 To (nSecurities - 1))
    Dim i As Integer, j As Integer, securityCounter As Integer
    '
    For i = 1 To nCols
        For j = 1 To nRows
            '
            ' yellow key is hard-coded here
            s(securityCounter) = VBA.Trim(matrix(j, i)) & " Curncy"
            securityCounter = securityCounter + 1
        Next j
    Next i
    matrixToSecurities = s
End Function
'
Public Function writeMatrixToRange( _
    ByRef m As Variant, _
    ByRef r As Range)
    '
    ' clear output range and write values from result matrix
    ' take into account rows and columns of 'original matrix'
    ' for this we use the information of matrix output range
    ' having the same dimensions as input matrix range
    Dim nRows As Integer: nRows = r.Rows.Count
    Dim nCols As Integer: nCols = r.Columns.Count
    r.ClearContents
    Dim i As Integer, j As Integer, securityCounter As Integer
    '
    For i = 1 To nCols
        For j = 1 To nRows
            r(j, i) = m(securityCounter, 0)
            securityCounter = securityCounter + 1
        Next j
    Next i
End Function
'

STEP FOUR: test run

Remember also to include BCOM wrapper into your program. Follow all instructions given in here. When we run this example program, the result should be the following.

If we cross check the corresponding values for SEK Cap volatilities (PX_MID) with Bloomberg BDP Excel worksheet functions, we should get pretty much the same values what we will get from BCOM server with wrapper. Now, if you would like to change your volatility feed (different currency or instrument type), just import new tickers from Bloomberg VCUB into your Excel, define new input range ("_input") and new output range ("_output") and run the program. In VBA program, define correct Bloomberg field name. At the moment, this has been hard-coded to be PX_MID. That's basically all you have to do.

I am not saying that this is absolutely the best way for handling this data, but it is working well and it is efficient enough, even there are some additional twists needed with data input and output. I ran 10 separate data retrieving processes with BCOM wrapper and calculated average of those processing times. For those 70 SEK tickers, processing time (from reading data to writing data) was 1.5 seconds and for example, for USD Cap volatility data (255 tickers), average processing time was 2.2 seconds.

Thanks for reading.

-Mike