Bloomberg Data... Why doesn't Application.WorksheetFunction.BDH work?

Question

I am trying to import Bloomberg historical data directly into an array in VBA:

Dim data As Variant

ticker = "UKGRABIQ Index"
start_date = "12/31/1998"
end_date = "3/31/2015"

data = Application.WorksheetFunction.BDH(ticker, "PX_LAST", start_date, end_date, "ARRAY=TRUE")

Range("A1", "A66").Value = data

The end goal is to be able to do a lot of manipulation on that data, not to just have it appear in the worksheet.

Thanks in advance for the help.

Answer 1

The BDH() function is provided by the Bloomberg Add-ins: it isn't a native Excel Worksheet function, so the Application.WorksheetFunction collection won't have it.

You can still use BDH that way, more or less: the Application.Evaluate() method does what you're trying to do with Application.WorksheetFunction.

Here's the documentation: https://msdn.microsoft.com/en-us/library/office/ff193019.aspx

However, I would recommend that you explore the ActiveX objects available to VBA, rather than attempting to emulate a worksheet calculation in VBA code.

You'll need to go to the VBA IDE menu, Tools > References... and browse for the Bloomberg Active-X data control, which is usually in C:\blp\API\ActiveX\blpdatax.dll

This code snippet should work, but I haven't tested it on my current machine: explore the use (or removal) of arrays for the tickers, fields and dates if you get any errors.

Dim arrData As Variant
Dim objDataX As BLP_DATA_CTRLLib.BlpData

Set objDataX As BLP_DATA_CTRLLib.BlpData

arrData = objDataX.BLPGetHistoricalData( Array(ticker), "PX_LAST", start_date, end_date )

You'll need to inspect the returned array: you don't always get a simple 2D 'grid' from Bloomberg, it's sometimes a structure of nested arrays.