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:
For referenceData, the new class interface function has been defined to be the following:
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>
write best eps into FLDS query input box and press ENTER
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:
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:
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
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.
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.