彭博数据...为什么Application.WorksheetFunction.BDH不起作用? [英] Bloomberg Data... Why doesn't Application.WorksheetFunction.BDH work?

问题描述

我正在尝试将彭博历史数据直接导入VBA中的数组:

 将昏暗的数据作为变体股票代码="UKGRABIQ指数"start_date ="1998年12月31日"end_date ="2015/3/31"数据= Application.WorksheetFunction.BDH(股票代码，"PX_LAST"，开始日期，结束日期，"ARRAY = TRUE")范围("A1"，"A66").值=数据 

最终目标是能够对数据进行很多操作，而不仅仅是使其出现在工作表中.

预先感谢您的帮助.

解决方案

BDH()函数由Bloomberg加载项提供:它不是本机Excel Worksheet函数，因此Application.WorksheetFunction集合不会有它.

您仍然可以或多或少地使用BDH:Application.Evaluate()方法可以完成您对Application.WorksheetFunction的尝试.

这里是文档: https://msdn.microsoft.com/zh-cn/library/office/ff193019.aspx

但是，我建议您探索VBA可用的ActiveX对象，而不是尝试在VBA代码中模拟工作表计算.

您需要转到VBA IDE菜单，工具>引用...，并浏览到Bloomberg Active-X数据控件，该控件通常在C:\ blp \ API \ ActiveX \ blpdatax.dll中

此代码段应该可以工作，但是我尚未在当前计算机上对其进行过测试:如果出现任何错误，请探索对代码，字段和日期使用或删除数组.

Dim arrData作为变体将objDataX变暗为BLP_DATA_CTRLLib.BlpData 
将objDataX设置为BLP_DATA_CTRLLib.BlpData 
arrData = objDataX.BLPGetHistoricalData(Array(ticker)，"PX_LAST"，start_date，end_date)

您需要检查返回的数组:您并非总是从彭博社那里获得简单的2D网格"，有时它是嵌套数组的结构.

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.

解决方案

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.

这篇关于彭博数据...为什么Application.WorksheetFunction.BDH不起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！