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

Bloomberg V3COM API wrapper update 2 for VBA

I am finally publishing some updates (hopefully also improvements) to the existing version for Bloomberg BCOM wrapper. If you are not familiar with the previous versions, you might want to take a look at these first.

http://mikejuniperhill.blogspot.fi/2013/05/bloomberg-v3com-api-wrapper-for-vba.html

http://mikejuniperhill.blogspot.fi/2013/06/bloomberg-v3com-api-wrapper-update-for.html

Three interface functions

With this updated version, I have now decided to break the class public interface function (previously getData function) into three separate functions. Handling all those different mandatory/optional input parameters for all different types of market data started to be a bit too messy operation to handle and public interface function mutated itself into a scary-looking monster. However, I still have not compromised the basic principle which says, that the wrapper is a compact one-module entity, which can be imported easily into your new VBA project. So, everything is (and hopefully will be) inside one class module. Anyway, to be more specific about the new public function interfaces, we have now three separate public functions for different types of data queries:

1. referenceData
2. bulkReferenceData
3. historicalData

I assume that you are familiar of these data query types already. If not, then read those previous posts. Now, let us investigate these function interfaces a bit to be able to understand, what has been changed.

For referenceData, the new class interface function has been defined to be the following:

Public Function referenceData(ByRef securities As Variant, _
    ByRef fields As Variant, _
    Optional ByRef overrideFields As Variant, _
    Optional ByRef overrideValues As Variant) As Variant

Needless to say, we still need to give arrays for securities (Bloomberg tickers with yellow key) and fields (Bloomberg field names).

Field override in Bloomberg

What is new here, is the override optionality. To implement an override to any field, we need to set up one array for override field names and another for override values. Excellent source for investigating possible override options for a field, is Bloomberg itself and its FLDS function. For example, you can test the override in your Bloomberg with the following commands:

IBM US Equity <GO>
FLDS <GO> 
write best eps into FLDS query input box and press ENTER
mouse-click BEST_EPS

You should now have a view for all the fields, which can be overriden for this specific field (BEST_EPS). Just for an example, if you select BEST_FPERIOD_OVERRIDE (default value = 1FY) to be 3FY, you can see that the value for BEST_EPS also changes. And so on. If you play with this FLDS for a while, you should become pretty comfortable with this override possibility in Bloomberg. Personally I have to give a credit for Bloomy people for giving out this function, since it is a really great tool, which truly increases your productivity. There is an example tester program given for reference data retrieving in the code section below, with and without override. If you already did not know, you can retrieve multiple fields for multiple securities, as that example program shows. 

Next, we have bulkReferenceData. The new class public interface function has been defined to be the following:

Public Function bulkReferenceData(ByRef securities As Variant, _
    ByRef fields As Variant, _
    Optional ByRef overrideFields As Variant, _
    Optional ByRef overrideValues As Variant) As Variant

Nothing else has been changed, except we have now override possibility also for this type of data retrieving. Using override follows exactly, what has been presented above for reference data. I guess that most of the people will retrieve option chains, bond chains or curve member chains from Bloomberg. If you are not familiar what kind of overrides you can have for a chain, use FLDS again (BOND_CHAIN, OPT_CHAIN, INDX_MEMBERS). There is an example tester program given for bulk reference data retrieving in the code section below, with and without override. It should be noted also, that you can also retrieve chains for multiple securities, as that example program shows. Because the function returns a multidimensional array, there might be some further labour needed for handling this array for empty items. However, if you are comfortable enough with VBA arrays, this should not be any tombstone for your project.

Finally, we have the trickiest one, historicalData. Function interface has been defined to be the following:

Public Function historicalData(ByRef securities As Variant, _
    ByRef fields As Variant, _
    ByVal startDate As Date, _
    ByVal endDate As Date, _
    Optional ByVal calendarCodeOverride As String, _
    Optional ByVal currencyCode As String, _
    Optional ByVal nonTradingDayFillOption As String, _
    Optional ByVal nonTradingDayFillMethod As String, _
    Optional ByVal periodicityAdjustment As String, _
    Optional ByVal periodicitySelection As String, _
    Optional ByVal maxDataPoints As Integer, _
    Optional ByVal pricingOption As String, _
    Optional ByRef overrideFields As Variant, _
    Optional ByRef overrideValues As Variant) As Variant

Note the large amount of optional parameters for historical data. If you are familiar with Bloomberg BDH function, you may notice, that these optional parameters above are exactly the same what are being used in that BDH function. Let us go through the optional parameters:

• calendarCodeOverride - Returns the data based on the calendar of the specified country, exchange or religion from CDR <GO>. Taking a two character calendar code null terminated string. This will cause the data to be aligned according to the calendar and including calendar holidays. This only applies to daily requests.
• currencyCode - Amends the value from local currency of the security to the desired currency. Currency of the ISO code. Eg. USD, GBP.
• nonTradingDayFillOption - Sets whether to include or exclude Non-Trading Days when no data is available. NON_TRADING_WEEKDAYS, ALL_CALENDAR_DAYS or ACTIVE_DAYS_ONLY (default).
• nonTradingDayFillMethod - Formats the type of data returned for non-trading days. PREVIOUS_VALUE (default) or NIL_VALUE.
• periodicityAdjustment - Sets the periodicity of the data. ACTUAL, CALENDAR (default) or FISCAL.
• periodicitySelection - DAILY (default), WEEKLY, MONTHLY, QUARTERLY, SEMI_ANNUALLY or YEARLY.
• maxDataPoints - The number of periods to download from the end date.  The response will contain up to X data points, where X is the integer specified. If the original data set is larger than X, the response will be a subset, containing the last X data points. Hence the first range of data points will be removed. Any positive integer.
• pricingOption - Sets quote to Price or Yield for a debt instrument. PRICING_OPTION_PRICE or PRICING_OPTION_YIELD (default for debt instrument).

These definitions for optional parameters given above, are copy-pasted from Bloomberg WAPI site. The best way to learn what kind of effect these parameters has on your result data, is just to play with parameters. There are also some override possibilities for historical data request and these are following the same principles as presented within the previous sections. If you want to know what kind of fields can be overriden for historical data retrieving, consult WAPI<GO> or contact Bloomberg help desk. To be honest, I am not so familiar with overriding fields for historical data.

Data array inconsistency for historical data - the problem

With this updated version for historical data, it is now possible to retrieve historical data also for multiple fields and multiple securities. However, one really annoying feature for this historical data retrieving for multiple securities is the fact, that the dates for different securities are not necessarily matching inside arrays. I mean, that for an array item n

• security A has a date of 12.8.2013 for the item
• security B has a date of 15.8.2013 for the item
• security C has a date of 13.8.2013 for the item 

Technically speaking, you could do a separate function for handling this problem. First you get raw data for all n securities. After this, you pick up one security to be "reference security" for dates. Then you loop through the whole data set (n-1 securities left) and fetch data for "reference security dates" for all those 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 "intellectually too challenging", for sure it means a lot of extra churning with your code. So, what to do? 

The solution

Because of all those ingenious optional parameters implemented, there is an elegant way to overcome this problem. We give the following optional parameters for wrapper when retrieving historical data

• nonTradingDayFillOption = ALL_CALENDAR_DAYS
• nonTradingDayFillMethod = PREVIOUS_VALUE.

There is an example tester program for retrieving historical data with these optional parameters implemented. It shows that by employing this method, your data arrays for different securities will be "date consistent" with each other. Make sure, that you have "Sheet1" existing in your Excel, since all tester programs are printing out the result data into this worksheet. Final note: do not forget to declare Bloomberg V3COM API library: VBA editor (ALT+F11) - Tools - References -  Bloomberg API COM 3.x Type Library.

Afterthoughts

The biggest improvement for this version has been the optionality for field value overrides. Second improvement has been the possibility for retrieving data for multiple securities (bulk reference and historical data) and multiple fields (historical data). I have tested example programs and they should be working correctly. If you observe anything unusual going on with wrapper, just leave a comment for me.

My personal Thank You this time goes to Faizal from Singapore. As I have been gradually working with this wrapper, he has been giving me 1) some proper motivation to work, 2) valuable suggestions and comments, and 3) extremely valuable help for testing this wrapper with real-life data sets.

Anyway, have a great start for autumn and thanks for reading my blog. I hope you got something to make your life a bit easier when working with Bloomberg market data in VBA.

-Mike

' VBA standard module
Option Explicit
'
Private b As BCOM_wrapper
Private r As Variant
Private s() As String
Private f() As String
Private overrideFields() As String
Private overrideValues() As String
'
Sub tester_referenceData()
    '
    ' create wrapper object
    Set b = New BCOM_wrapper
    '
    ' create 3 securities and 4 fields
    ReDim s(0 To 2): s(0) = "GS US Equity": s(1) = "DBK GR Equity": s(2) = "JPM US Equity"
    ReDim f(0 To 3): f(0) = "SECURITY_NAME": f(1) = "BEST_EPS": f(2) = "BEST_PE_RATIO": f(3) = "BEST_DIV_YLD"
    '
    ' retrieve result from wrapper into array and print
    r = b.referenceData(s, f)
    printReferenceData r
    '
    ' create 1 override for fields
    ReDim overrideFields(0 To 0): overrideFields(0) = "BEST_FPERIOD_OVERRIDE"
    ReDim overrideValues(0 To 0): overrideValues(0) = "3FY"
    '
    ' retrieve result from wrapper into array and print
    r = b.referenceData(s, f, overrideFields, overrideValues)
    printReferenceData r
    '
    ' release wrapper object
    Set b = Nothing
End Sub
'
Sub tester_bulkReferenceData()
    '
    ' create wrapper object
    Set b = New BCOM_wrapper
    '
    ' create 3 securities and 1 fields
    ReDim s(0 To 2): s(0) = "GS US Equity": s(1) = "DBK GR Equity": s(2) = "JPM US Equity"
    ReDim f(0 To 0): f(0) = "BOND_CHAIN"
    '
    ' retrieve result from wrapper into array and print
    r = b.bulkReferenceData(s, f)
    printBulkReferenceData r
    '
    ' create 2 overrides for chain
    ReDim overrideFields(0 To 1): overrideFields(0) = "CHAIN_CURRENCY": overrideFields(1) = "CHAIN_COUPON_TYPE"
    ReDim overrideValues(0 To 1): overrideValues(0) = "JPY": overrideValues(1) = "FLOATING"
    '
    ' retrieve result from wrapper into array and print
    r = b.bulkReferenceData(s, f, overrideFields, overrideValues)
    printBulkReferenceData r
    '
    ' release wrapper object
    Set b = Nothing
End Sub
'
Sub tester_historicalData()
    '
    ' create wrapper object
    Set b = New BCOM_wrapper
    '
    ' create 3 securities and 4 fields
    ReDim s(0 To 2): s(0) = "GS US Equity": s(1) = "DBK GR Equity": s(2) = "JPM US Equity"
    ReDim f(0 To 3): f(0) = "PX_OPEN": f(1) = "PX_LOW": f(2) = "PX_HIGH": f(3) = "PX_LAST"
    '
    ' retrieve result from wrapper into array
    r = b.historicalData(s, f, CDate("21.8.2008"), CDate("21.8.2013"), , , "ALL_CALENDAR_DAYS", "PREVIOUS_VALUE")
    printHistoricalData r
    '
    ' release wrapper object
    Set b = Nothing
End Sub
'
Private Function printReferenceData(ByRef data As Variant)
    '
    Dim rng As Range: Set rng = Sheets("Sheet1").Range("A1")
    rng.CurrentRegion.ClearContents
    Dim i As Long, j As Long
    '
    On Error Resume Next
    For i = 0 To UBound(data, 1)
        For j = 0 To UBound(data, 2)
            rng(i + 1, j + 1) = data(i, j)
        Next j
    Next i
End Function
'
Private Function printBulkReferenceData(ByRef data As Variant)
    '
    Dim rng As Range: Set rng = Sheets("Sheet1").Range("A1")
    rng.CurrentRegion.ClearContents
    Dim i As Long, j As Long
    '
    On Error Resume Next
    For i = 0 To UBound(data, 1)
        For j = 0 To UBound(data, 2)
            rng(j + 1, i + 1) = data(i, j)
        Next j
    Next i
End Function
'
Private Function printHistoricalData(ByRef data As Variant)
    '
    Dim rng As Range: Set rng = Sheets("Sheet1").Range("A1")
    rng.CurrentRegion.ClearContents
    Dim i As Long, j As Long, k As Long: k = 1
    '
    On Error Resume Next
    For i = 0 To UBound(data, 1)
        For j = 0 To UBound(data, 2)
            rng(j + 1, i + k) = data(i, j)(0)
            rng(j + 1, i + k + 1) = data(i, j)(1)
            rng(j + 1, i + k + 2) = data(i, j)(2)
            rng(j + 1, i + k + 3) = data(i, j)(3)
        Next j
        '
        k = k + 3
    Next i
End Function
'

' 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 String
Private bInputFieldArray() As String
Private bOutputArray As Variant
Private bOverrideFieldArray() As String
Private bOverrideValueArray() As String
'
' 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
Private bOverrides As blpapicomLib2.element
Private bOverrideArray() As blpapicomLib2.element
'
' class non-object data members
Private bStartDate As String
Private bEndDate As String
Private bRequestType As ENUM_REQUEST_TYPE
Private nSecurities As Long
Private nSecurity As Long
Private bCalendarCodeOverride As String
Private bCurrencyCode As String
Private bNonTradingDayFillOption As String
Private bNonTradingDayFillMethod As String
Private bPeriodicityAdjustment As String
Private bPeriodicitySelection As String
Private bMaxDataPoints As Integer
Private bPricingOption As String
'
Public Function referenceData(ByRef securities As Variant, _
    ByRef fields As Variant, _
    Optional ByRef overrideFields As Variant, _
    Optional ByRef overrideValues As Variant) As Variant
    '
    ' mandatory user input parameters
    bRequestType = REFERENCE_DATA
    bInputSecurityArray = securities
    bInputFieldArray = fields
    '
    ' field names and values for overrides
    If Not (VBA.IsMissing(overrideFields)) Then bOverrideFieldArray = overrideFields
    If Not (VBA.IsMissing(overrideValues)) Then bOverrideValueArray = overrideValues
    '
    processDataRequest
    referenceData = bOutputArray
End Function
'
Public Function bulkReferenceData(ByRef securities As Variant, _
    ByRef fields As Variant, _
    Optional ByRef overrideFields As Variant, _
    Optional ByRef overrideValues As Variant) As Variant
    '
    ' mandatory user input parameters
    bRequestType = BULK_REFERENCE_DATA
    bInputSecurityArray = securities
    bInputFieldArray = fields
    '
    ' field names and values for overrides
    If Not (VBA.IsMissing(overrideFields)) Then bOverrideFieldArray = overrideFields
    If Not (VBA.IsMissing(overrideValues)) Then bOverrideValueArray = overrideValues
    '
    processDataRequest
    bulkReferenceData = bOutputArray
End Function
'
Public Function historicalData(ByRef securities As Variant, _
    ByRef fields As Variant, _
    ByVal startDate As Date, _
    ByVal endDate As Date, _
    Optional ByVal calendarCodeOverride As String, _
    Optional ByVal currencyCode As String, _
    Optional ByVal nonTradingDayFillOption As String, _
    Optional ByVal nonTradingDayFillMethod As String, _
    Optional ByVal periodicityAdjustment As String, _
    Optional ByVal periodicitySelection As String, _
    Optional ByVal maxDataPoints As Integer, _
    Optional ByVal pricingOption As String, _
    Optional ByRef overrideFields As Variant, _
    Optional ByRef overrideValues As Variant) As Variant
    '
    ' mandatory user input parameters
    bRequestType = HISTORICAL_DATA
    bInputSecurityArray = securities
    bInputFieldArray = fields
    bStartDate = startDate
    bEndDate = endDate
    '
    ' checks and conversions for user-defined dates
    If ((startDate = CDate(0)) Or (endDate = CDate(0))) Then _
        Err.Raise vbObjectError, "Bloomberg API", "Date parameters missing for historical data query"
    '
    If (startDate > endDate) Then _
        Err.Raise vbObjectError, "Bloomberg API", "Incorrect date parameters for historical data query"
    '
    bStartDate = convertDateToBloombergString(startDate)
    bEndDate = convertDateToBloombergString(endDate)
    '
    ' optional user input parameters
    bCalendarCodeOverride = calendarCodeOverride
    bCurrencyCode = currencyCode
    bNonTradingDayFillOption = nonTradingDayFillOption
    bNonTradingDayFillMethod = nonTradingDayFillMethod
    bPeriodicityAdjustment = periodicityAdjustment
    bPeriodicitySelection = periodicitySelection
    bMaxDataPoints = maxDataPoints
    bPricingOption = pricingOption
    '
    ' field names and values for overrides
    If Not (VBA.IsMissing(overrideFields)) Then bOverrideFieldArray = overrideFields
    If Not (VBA.IsMissing(overrideValues)) Then bOverrideValueArray = overrideValues
    '
    processDataRequest
    historicalData = bOutputArray
End Function
'
Private Function processDataRequest()
    '
    openSession
    sendRequest
    catchServerEvent
    releaseObjects
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)
            '
            ' set mandatory user input parameter
            bRequest.Set "startDate", bStartDate
            bRequest.Set "endDate", bEndDate
            '
            ' set optional user input parameter
            If (bNonTradingDayFillOption <> "") Then bRequest.Set "nonTradingDayFillOption", bNonTradingDayFillOption
            If (bNonTradingDayFillMethod <> "") Then bRequest.Set "nonTradingDayFillMethod", bNonTradingDayFillMethod
            If (bPeriodicityAdjustment <> "") Then bRequest.Set "periodicityAdjustment", bPeriodicityAdjustment
            If (bPeriodicitySelection <> "") Then bRequest.Set "periodicitySelection", bPeriodicitySelection
            If (bCalendarCodeOverride <> "") Then bRequest.Set "calendarCodeOverride", bCalendarCodeOverride
            If (bCurrencyCode <> "") Then bRequest.Set "currency", bCurrencyCode
            If (bMaxDataPoints <> 0) Then bRequest.Set "maxDataPoints", bMaxDataPoints
            If (bPricingOption <> "") Then bRequest.Set "pricingOption", bPricingOption
            '
        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
    setOverrides
    bSession.sendRequest bRequest
End Function
'
Private Function setOverrides()
    '
    On Error GoTo errorHandler
    '
    If (UBound(bOverrideFieldArray) <> UBound(bOverrideValueArray)) Then Exit Function
    Set bOverrides = bRequest.GetElement("overrides")
    '
    ReDim bOverrideArray(LBound(bOverrideFieldArray) To UBound(bOverrideFieldArray))
    Dim i As Integer
    For i = 0 To UBound(bOverrideFieldArray)
        '
        If ((Len(bOverrideFieldArray(i)) > 0) And (Len(bOverrideValueArray(i)) > 0)) Then
            '
            Set bOverrideArray(i) = bOverrides.AppendElment()
            bOverrideArray(i).SetElement "fieldId", bOverrideFieldArray(i)
            bOverrideArray(i).SetElement "value", bOverrideValueArray(i)
        End If
    Next i
    Exit Function
    '
errorHandler:
    Exit Function
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))
                    bOutputArray(offsetNumber, j) = bFieldValue.Value
                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 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
                        '
                        bOutputArray(offsetNumber, i) = d
                    End If
                End If
            Next i
        End If
    Loop
End Function
'
Private Function releaseObjects()
    '
    nSecurity = 0
    Set bDataPoint = Nothing
    Set bFieldValue = Nothing
    Set bSequenceNumber = Nothing
    Set bSecurityField = Nothing
    Set bSecurityName = Nothing
    Set bSecurity = Nothing
    Set bOverrides = 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
    '
    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
'

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
'