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:
- referenceData
- bulkReferenceData
- historicalData
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).
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
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.
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 '
Your welcome Mike! It helped me alot as well! :)
ReplyDeleteThanks! :)
ReplyDeleteWow!!! It's like getting a cool present for the Christmas :)
ReplyDeleteThanks a lot Mike !!!!
Some errors I've met:
Private bOverrideFieldArray() As String
Private bOverrideValueArray() As String
should be defined as Variant, else: type mismatch error.
In case there is a list of products > 10 the output will be in a wrong order as it BBG will take the values starting with 1 first, then 2, etc. The order will be 1, 11, 12.. 2, 21 etc.. However, this is easy to correct.
That's for the moment. Once again, thank's a lot Mike. You did a great job !
Hi Eugen, and thanks for your comments.
ReplyDeleteSounds interesting this case with those override arrays. Works fine for me without any errors. So, you actually started to run those tester programs and it was throwing that type mismatch error?
With that second case, I am not sure whether I understood what was the problem - sorry :) Hmm .. Can you describe this case in a bit more detailed level. What type of data you wanted to get? What securities and fields and so on .. That would be helpful for me and next week I can create test case and debug that to see where's the problem..
Otherwise, have a great weekend and thanks for following my blog!
-Mike
Eugen, tester program were working well for me without any errors when I ran it at my Bloomberg this morning. However, due to being consistent, I changed one thing:
ReplyDeleteAll input arrays (except result) are now defined as strings, since we are using only strings within these arrays. However, for result array we do not know the exact data type beforehand ..
' private data structures defined in wrapper
Private bInputSecurityArray() As String
Private bInputFieldArray() As String
Private bOutputArray As Variant
Private bOverrideFieldArray() As String
Private bOverrideValueArray() As String
.. and hereby in our tester program, all arrays (except result) are defined as strings:
Private r As Variant
Private s() As String
Private f() As String
Private overrideFields() As String
Private overrideValues() As String
If you look at the function interfaces in wrapper, you see that all the input arrays are taken in as variant. When we assign string array to variant, the result will be variant/string array. So this assignment is valid. Now, there is a reason why these override field and value arrays are not defined as strings in function interfaces: you can not have array as an OPTIONAL argument in function interface.
-Mike
Hi Mike,
ReplyDeleteI'm not sure if this has something to do with the wrapper, but I got some strange behavour in the output.
For instance, when I do in Excel BDP("ADBE US Equity", Security_typ) it will result in common stock. If I use the wrapper with s(0) = "/isin/US00724F1012", it wont generate any output. If I try a regular other ISIN it works with the wrapper. If I use the ticker into the wrapper s(0) = "ADBE US Equity", it will generate a output.
Not sure if this has anything todo with the wrapper, but just wanted to share it..
besides this the wrapper works very good!!
thanks, jort
Hi there,
ReplyDeleteThe following program prints out "Common Stock" two times:
Sub tester_referenceData2()
'
' create wrapper object
Set b = New BCOM_wrapper
'
' create security and 1 field
ReDim s(0 To 0): s(0) = "/isin/US00724F1012"
ReDim f(0 To 0): f(0) = "SECURITY_TYP"
'
' retrieve result from wrapper into array and print
r = b.referenceData(s, f)
Debug.Print r(0, 0)
'
s(0) = "ADBE US Equity"
' retrieve result from wrapper into array and print
r = b.referenceData(s, f)
Debug.Print r(0, 0)
'
' release wrapper object
Set b = Nothing
End Sub
'
For BDP function I will get result when using "ADBE US Equity", but Invalid security error if I am using "/isin/US00724F1012".
-Mike
Hi Mike, Yes, you are correct.
ReplyDeleteI did not know that all letters should be capital letters. I used US00724f1012, with the lower case 'f'. That didn't work.
Stupid me, sorry for this:)
Well, I actually did not know that was this the source of error (lowercase alphabet in ticker).. initially I suspected that you might have some other issue with your program. Anyway, always good to confirm that the program is working as expected.
ReplyDeleteThanks for your participation here.
-Mike
Hi, Mike! I run across you wonderful blog via google search, it seems you are doing some great works. Please keep it up. I have not been able to play with the wrapper yet, but one thing i do not fully understand from the post is - can it download historical intraday data? I have some bloomberg api examples which are provided with the terminal of that, but they are not really efficient, you need to rework them to make thing work properly anyway...
ReplyDeleteHi Mike,
ReplyDeleteThanks again for the awesome code! Still got a question regarding bulk data. For instance; GS US Equity with BOND_CHAIN. If you do in excel bds("GS US Equity","BOND_CHAIN") you will receive two fiels as an output (bloombergid and tickCpnMty). Is it also possible to retreive the output TickCpnMty (the second field from the output)? If I use the code I only receive the bloombergIDs.
I copy paste the part of the historical data, and this also worked for the bulk data:
ReplyDeleteDim d As Variant: ReDim d(0 To bDataPoint.NumElements - 1)
For j = 0 To bDataPoint.NumElements - 1
d(j) = bDataPoint.GetElement(j).GetValue(0)
Next j
bOutputArray(nSecurity - 1, i) = d
good job, I was looking on how to solve it too.
DeleteGreat ! This is working fine.
DeleteNevertheless, if I request several fields containing BulkData of 2 columns, I get back the result of only 1 field.
i.e : For
s(0) = "/isin/ES0305033005 "
f(0) ="MTG_HIST_COLLAT_BAL"
f(1) ="MTG_HIST_NUM_LOANS"
overrideFields(0) ="COLLATERAL_DATA_SOURCE"
overrideValues(0) = "2"
r = b.bulkReferenceData(s, f, overrideFields, overrideValues)
r will only contains first fields bulk values. Any idea why ?
Hi guys. Try this (working to grab all columns from BDS function).
DeletePS, it doesn't work very well with multiple securities. I'm not sure why, had no time to explore the code enought. In case of multiple securities (most of times, I guess), use this code doing 'multiple requests'. Sorry about that.
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))
'
' <<< edited code STARTS here >>>
'
ReDim bOutputArray(0 To bFieldValue.GetValue(0).NumElements - 1, 0 To bFieldValue.numValues - 1)
'
For i = 0 To bFieldValue.numValues - 1
Set bDataPoint = bFieldValue.GetValue(i)
For j = 0 To bDataPoint.NumElements - 1
bOutputArray(j, i) = bDataPoint.GetElement(j).Value
Next j
Next i
'
' <<< edited code ENDS here >>>
'
End If
Loop
End Function
Mikael, incredible piece of work! It's a state of art bloomberg API code! Thank you so much for sharing it!!
Hi Mikael,
ReplyDeleteThanks for this wrapper, a great help!
I'm having a runtime error with the overrides when calling the historical data function. Just added the following lines to your example:
' create 1 override for the historical data
ReDim overrideFields(0 To 0): overrideFields(0) = "adjustmentSplit"
ReDim overrideValues(0 To 0): overrideValues(0) = "TRUE"
'
' retrieve result from wrapper into array with overrides
r = b.historicalData(s, f, CDate("21/8/2008"), CDate("21/8/2013"), , , "ALL_CALENDAR_DAYS", "PREVIOUS_VALUE", , , , , overrideFields, overrideValues)
Any idea what could be causing that?
Thanks for your help
Flo
Hi,
DeleteI do have the same issue, especially when I want to get intraday data doing such thing like
ReDim overrideFields(0 To 2): overrideFields(0) = "IntrRw": overrideFields(1) = "Size": overrideFields(2) = "Type": ' overrideFields(3) = "Points"
ReDim overrideValues(0 To 2): overrideValues(0) = "TRUE": overrideValues(1) = "H": overrideValues(2) = "H": 'overrideValues(3) = "1"
Any idea??
it would be of great help!
THank
Mikael,
ReplyDeleteI have been working on a more "elegant" way to grab data from Bloomberg into a excel spreadsheet than using the well known BDP and BDH functions and after several weeks of research (of course I'm not working fulltime on this project, since I have other concerns in my work environment), your last three posts about it are the most beautiful and cristal clear material I have found so far. I've already put your first method to the test and it works like a charm. Now I'm finishing to analyze this last update and I'm glad I could finally find a good solution to my problem.
Please don't stop sharing your knowledge with us! Good job with your blog, it is already on my Favorites tab.
Thanks, and sorry for any mispelled words and mistakes, English is not my mother language.
I've been reading your articles about Bloomberg requests and I have to say many thanks to you. I'm a junior analyst and I'm starting working on, I should say, basics. I have a question about the override fields. 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 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?
ReplyDeleteIs it possible to apply the different set of overrides per security?
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteMikael,
ReplyDeleteVery good piece of work, liked the generic handling of the problem.
Best,
Great piece of code and tremendously helpful.
ReplyDeleteGot everything working except referencedata requests when including overrides.
Without overrides the reference requests work very well.
I attempt to retrieve international equity related estimates (for example BEST_SALES) in USD.
Below the statement which results in a type mismatch error. But, also I doubt that it is the proper override syntax.
r = b.referenceData(s, f, "Currency", "USD")
Could you please help me finding the proper syntax to incorporate a “USD” override?
Martin
Hi Martin, I had the same error when i ran the code, but i was able to fix it by changing the date syntax in CDATE formula. Currently it is something like CDATE("31.04.14"), you have to change it to american standards like CDATE("04/31/14").
ReplyDeleteHi Mikael,
ReplyDeleteI was looking through your VBA code but i was not sure how to add option1 field into the program. For example, I have a BDP formula that checks the correlation.. =BDP("EURUSD BGNL Curncy","CORR_COEF", "BETA_OVERRIDE_REL_INDEX=DKKUSD Curncy"). I couldn't figure out where i would add the beta parameter. Do you know of any examples that would overcome this issue? thanks for your help
Hi,
ReplyDeleteFirst of all thanks for your awesome work however when I implement everything works fine except for the hirstorical data. I always get the same error:
Invalid procedure call or argument
What can be the cause of this issue?
thanks for your help
Hi Mikael. This is truly an awsome piece of development code. EXCELLENT!
ReplyDeleteI just found one typo error in your code that was preventing the code from running smoothly for one parameters. It is for the OPTION_PRICING parameter in historical request:
There is an additional space after pricingOption that has to be removed in the sendRequest() line bRequest.Set "pricingOption ". See below:
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
*******ERROR IS HERE
If (bPricingOption <> "") Then bRequest.Set "pricingOption ", bPricingOption
***********ERROR ENDS HERE
***********CORRECTION STARTS HERE
If (bPricingOption <> "") Then bRequest.Set "pricingOption", bPricingOption
***********CORRECTION ENDS HERE
'
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
Thanks again for your work!!!
Marcin Brynda
Hi Marcin. Excellent work done. I am sure, you have spent a moment and two with your program, before finding this bug :) My apologies for it.
Delete-Mike
Wrapper is still pretty far from being reliable and .. in many ways, I'd like to get back on the horse and start to think again, how to fix all reported bugs and to make it better to use. It has been working pretty well for my own very simple needs, but as I can see, the other users are finding all kinds of strange bugs pretty much all the time.
ReplyDeleteAnd that makes me feel bad, since I have not been having a time or interest to dig deeper with all those reported bugs. Moreover, the fact that the only place to really test any related code is at my desk - since I do not have Bloomberg at home - is a restricting factor on any development work on this front. So, until I will get back on that horse, you have to keep your head above the water. Thanks for reporting bugs and sorry I have not been able to help you. Maybe some day, I will find some extra energy to think what could be the next step in evolution concerning this wrapper.
Hey Mikael,
ReplyDeleteDo you have created wrapper class using mktdata instead of refdata ?
In my project, mktdata subscription is used to fetch blpdata, historical data and bulkdata using old method, ie bloombeg data control library. As I migrated to excel 2013, this library is no more supported. So now I want to achieve the same using COM library but not getting more information in the examples downloaded from bloomberg site.
I am not quite sure what you mean, but .. the wrapper presented here, is able to request historical data and snapshot data (market data, say, close price for 10Y Bund future, etc) and other related reference data (tick size for the contract, etc). However, wrapper is not supporting real-time feeding. Is this the case you are referring to?
DeleteYes Mikael, I am referring to real time feeding. Have you created wrapper for the same ?
DeleteUnfortunately, I have not.
DeleteHi Mikael,
ReplyDeleteThanks for this wrapper, a great job!
I need to download a Dividend History - All (DV030), but the bulk data contains 7 columns. How can i print all the columns, your vba code download just the first.
Thank you very much for your help..
Antonio
Hi Mikael,
ReplyDeleteThanks for your posts and the wrapper. They are excellent!
I am trying to get the yield for several bonds. The thing is I want to override the prices. I have adopted your tester as follows but it keeps returning the default yields instead of the override yields. Below are two bonds where I want to price the yields overrided with my prices: 2 bonds, 2 sets of prices per bond (bid/ask). Any ideas? Any insights appreciated. thank you
Sub tester_referenceData()
'
' create wrapper object
Set b = New BCOM_wrapper
'
' create 3 securities and 4 fields
ReDim s(0 To 1): s(0) = "USY9896RAB79 CORP": s(1) = "XS1488414464 CORP"
ReDim f(0 To 0): f(0) = "YAS_BOND_YLD" ': f(1) = "YAS_BOND_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) = "YAS_BOND_PX" ': overrideFields(1) = "YAS_BOND_PX"
ReDim overrideValues(0 To 3): overrideValues(0) = "93.75": overrideValues(1) = "94.5": overrideValues(2) = "99": overrideValues(3) = "99.5"
'
' retrieve result from wrapper into array and print
r = b.referenceData(s, f, overrideFields, overrideValues)
printReferenceData r
'
' release wrapper object
Set b = Not
Is there a way to do the equivalent of this =BDS("M US Equity", "ERN_ANN_DT_AND_PER", "endcol=2")? In other words, return two columns of bulk data, one being the values the other being the YYYY:Q# label?
ReplyDeleteand the answer is: Debug.Print bDataPoint.GetElement(1).Value '<- the name of the item, i.e. 2017:Q2
DeleteFabulous work, thanks ! When I am using this with historical data over a short time frame, sometimes some fields have no data because there has been no update in the Bloom data. In such cases carrying over the previous values does not work & I get no value at all, but also the "columns" in the output array get "mixed up" because if a field has no data the corresponding column is simply "deleted". So e.g. field number 4 will appear in column 2, etc. Is there a way to solve this, e.g. with NAs for missing values ? Thanks
ReplyDeleteHi Mikael,
ReplyDeleteGreat wrapper!
I'm having difficulties extracting data for several of my securities.
My security ticker is "RTY Index". I'm trying to retrieve the LAST_PRICE in multiple currencies so I have to use historicalData
When I use the function referenceData, it works but it does not with the historicalData function.
f(0) = "SECURITY_NAME"
f(1) = "LAST_PRICE"
s(0) = "RTY Index"
r = b.referenceData(s, f)
x = b.historicalData(s, f, CDate("2017-06-01"), CDate("2017-06-12"))
Can you help me out?
Thanks a lot!
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
ReplyDeleteMikael
ReplyDeleteI came across this wonderful API through some google deep dive. Thank you very much for taking time to work on this. There were a couple of issues that I worked around with. This is for someone who has the same issues.
1. Wrapper calls the date-text conversion module only from the historical data subroutine. let's say you're calling worksheets with MBS cusips sorted by months in a year so you can pull up specific prepay/factor information for a specific month. If you use a settle date or collat as of date override and reference to a date field you will not get the factor info for the relevant date. I suggest using the actual bbrg dates as overridevalues within your reference data subroutine before you call the wrapper. I modified the date fields for all subroutines calling the wrapper so that the wrapper never calls the date conversion module. It was simpler and cleaner, since you will be accessing the same worksheets again and again.
2.when you are calling securities from a worksheet into an array directly, please make sure you convert them to a one dimensional string array with option base 0 syntax. otherwise, you will get an error and even if you convert by cycling through you array, you will lose the first security if you didn't redim Mikael's arrays named s(), f(), overridefields() and overridevalues()
3.if you have a lot of securities dumping the entire boutputarray saves time, especially for the historical data subroutine. But make sure you've increased the second dimension by expanding it to 4 columns before dumping the output.
Thanks once again, Mikael. Great stuff!
I am happy to hear that you have found this to be useful. Your suggestions are highly appreciated!
Delete-Mike
Subscript out of range error ; If (ubound(boverrideFiekdArray)<>ubound(boverrideFiekdArray)), missing variant, context bbcom.referencedata
ReplyDeleteit doesnt work anymore for me :(
ReplyDeleteThank You and I have a keen supply: Whole House Renovation Cost Calculator house renovation shows
ReplyDelete