如何将Bloomberg API中的数据存储到Pandas数据帧中？

I recently started using Python so I could interact with the Bloomberg API, and I'm having some trouble storing the data into a Pandas dataframe (or a panel). I can get the output in the command prompt just fine, so that's not an issue.

我最近开始使用Python,因此我可以与Bloomberg API进行交互,而且我在将数据存储到Pandas数据帧(或面板)时遇到了一些麻烦。我可以在命令提示符中得到输出就好了,所以这不是问题。

A very similar question was asked here: Pandas wrapper for Bloomberg api?

这里提出了一个非常相似的问题:Bloomberg api的熊猫包装?

The referenced code in the accepted answer for that question is for the old API, however, and it doesn't work for the new open API. Apparently the user who asked the question was able to easily modify that code to work with the new API, but I'm used to having my hand held in R, and this is my first endeavor with Python.

然而,该问题的已接受答案中引用的代码是针对旧API的,并且它不适用于新的开放API。显然,提出问题的用户能够轻松修改该代码以使用新的API,但我习惯将手放在R中,这是我第一次使用Python。

Could some benevolent user show me how to get this data into Pandas? There is an example in the Python API (available here: http://www.openbloomberg.com/open-api/) called SimpleHistoryExample.py that I've been working with that I've included below. I believe I'll need to modify mostly around the 'while(True)' loop toward the end of the 'main()' function, but everything I've tried so far has had issues.

一些仁慈的用户能告诉我如何将这些数据输入熊猫吗?在Python API中有一个例子(在这里可以找到:http://www.openbloomberg.com/open-api/),我已经使用了一个名为SimpleHistoryExample.py的例子。我相信我需要在'main()'函数的末尾围绕'while(True)'循环进行修改,但到目前为止我尝试的所有内容都有问题。

Thanks in advance, and I hope this can be of help to anyone using Pandas for finance.

在此先感谢,我希望这对使用Pandas进行融资的任何人都有帮助。

# SimpleHistoryExample.py

import blpapi
from optparse import OptionParser


def parseCmdLine():
    parser = OptionParser(description="Retrieve reference data.")
    parser.add_option("-a",
                      "--ip",
                      dest="host",
                      help="server name or IP (default: %default)",
                      metavar="ipAddress",
                      default="localhost")
    parser.add_option("-p",
                      dest="port",
                      type="int",
                      help="server port (default: %default)",
                      metavar="tcpPort",
                      default=8194)

    (options, args) = parser.parse_args()

    return options


def main():
    options = parseCmdLine()

    # Fill SessionOptions
    sessionOptions = blpapi.SessionOptions()
    sessionOptions.setServerHost(options.host)
    sessionOptions.setServerPort(options.port)

    print "Connecting to %s:%s" % (options.host, options.port)
    # Create a Session
    session = blpapi.Session(sessionOptions)

    # Start a Session
    if not session.start():
        print "Failed to start session."
        return

    try:
        # Open service to get historical data from
        if not session.openService("//blp/refdata"):
            print "Failed to open //blp/refdata"
            return

        # Obtain previously opened service
        refDataService = session.getService("//blp/refdata")

        # Create and fill the request for the historical data
        request = refDataService.createRequest("HistoricalDataRequest")
        request.getElement("securities").appendValue("IBM US Equity")
        request.getElement("securities").appendValue("MSFT US Equity")
        request.getElement("fields").appendValue("PX_LAST")
        request.getElement("fields").appendValue("OPEN")
        request.set("periodicityAdjustment", "ACTUAL")
        request.set("periodicitySelection", "DAILY")
        request.set("startDate", "20061227")
        request.set("endDate", "20061231")
        request.set("maxDataPoints", 100)

        print "Sending Request:", request
        # Send the request
        session.sendRequest(request)

        # Process received events
        while(True):
            # We provide timeout to give the chance for Ctrl+C handling:
            ev = session.nextEvent(500)
            for msg in ev:
                print msg

            if ev.eventType() == blpapi.Event.RESPONSE:
                # Response completly received, so we could exit
                break
    finally:
        # Stop the session
        session.stop()

if __name__ == "__main__":
    print "SimpleHistoryExample"
    try:
        main()
    except KeyboardInterrupt:
        print "Ctrl+C pressed. Stopping..."

6 个解决方案

#1

I use tia (https://github.com/bpsmith/tia/blob/master/examples/datamgr.ipynb)

我用tia(https://github.com/bpsmith/tia/blob/master/examples/datamgr.ipynb)

It already downloads data as a panda dataframe from bloomberg. You can download history for multiple tickers in one single call and even download some bloombergs reference data (Central Bank date meetings, holidays for a certain country, etc)

它已经将数据作为来自bloomberg的熊猫数据框下载。您可以在一个电话中下载多个代码的历史记录,甚至可以下载一些bloombergs参考数据(*银行日期会议,某个国家/地区的假期等)

And you just install it with pip. This link is full of examples but to download historical data is as easy as:

你只需用pip安装它。此链接充满了示例,但下载历史数据非常简单:

import pandas as pd
import tia.bbg.datamgr as dm

mgr = dm.BbgDataManager()
sids = mgr['MSFT US EQUITY', 'IBM US EQUITY', 'CSCO US EQUITY']
df = sids.get_historical('PX_LAST', '1/1/2014', '11/12/2014')

and df is a pandas dataframe.

和df是一个熊猫数据帧。

Hope it helps

希望能帮助到你

#2

I've just published this which might help

我刚刚发布了这可能会有所帮助

http://github.com/alex314159/blpapiwrapper

It's basically not very intuitive to unpack the message, but this is what works for me, where strData is a list of bloomberg fields, for instance ['PX_LAST','PX_OPEN']:

解压缩消息基本上不是很直观,但这对我有用,其中strData是一个bloomberg字段列表,例如['PX_LAST','PX_OPEN']:

fieldDataArray = msg.getElement('securityData').getElement('fieldData')
size = fieldDataArray.numValues()
fieldDataList = [fieldDataArray.getValueAsElement(i) for i in range(0,size)]
outDates = [x.getElementAsDatetime('date') for x in fieldDataList]
output = pandas.DataFrame(index=outDates,columns=strData)
for strD in strData:
    outData = [x.getElementAsFloat(strD) for x in fieldDataList]
    output[strD] = outData
output.replace('#N/A History',pandas.np.nan,inplace=True)
output.index = output.index.to_datetime()
return output

#3

I've been using pybbg to do this sort of stuff. You can get it here:

我一直在使用pybbg做这种事情。你可以在这里得到它:

https://github.com/bpsmith/pybbg

Import the package and you can then do (this is in the source code, bbg.py file):

导入包然后你可以这样做(这是在源代码,bbg.py文件中):

banner('ReferenceDataRequest: single security, single field, frame response')
req = ReferenceDataRequest('msft us equity', 'px_last', response_type='frame')
print req.execute().response

The advantages:

Easy to use; minimal boilerplate, and parses indices and dates for you.

使用方便;最小的样板,并为您解析索引和日期。
It's blocking. Since you mention R, I assume you are using this in some type of an interactive environment, like IPython. So this is what you want , rather than having to mess around with callbacks.

它是*的。既然你提到R,我假设你在某种类型的交互式环境中使用它,比如IPython。所以这就是你想要的,而不是不得不乱用回调。
It can also do historical (i.e. price series), intraday and bulk data request (no tick data yet).

它还可以执行历史(即价格序列),日内和批量数据请求(还没有刻度数据)。

Disadvantages:

Only works in Windows, as far as I know (you must have BB workstationg installed and running).

据我所知,只能在Windows中运行(必须安装并运行BB工作站)。
Following on the above, it depends on the 32 bit OLE api for Python. It only works with the 32 bit version - so you will need 32 bit python and 32 bit OLE bindings

继上面的内容之后,它依赖于Python的32位OLE api。它只适用于32位版本 - 因此您将需要32位python和32位OLE绑定
There are some bugs. In my experience, when retrieving data for a number of instruments, it tends to hang IPython. Not sure what causes this.

有一些错误。根据我的经验,当检索多个乐器的数据时,它往往会挂起IPython。不确定是什么原因引起的。

Based on the last point, I would suggest that if you are getting large amounts of data, you retrieve and store these in an excel sheet (one instrument per sheet), and then import these. read_excel isn't efficient for doing this; you need to use the ExcelReader (?) object, and then iterate over the sheets. Otherwise, using read_excel will reopen the file each time you read a sheet; this can take ages.

基于最后一点,我建议如果您获得大量数据,则将其检索并存储在Excel工作表(每张工具一个工具)中,然后导入这些数据。 read_excel这样做效率不高;您需要使用ExcelReader(?)对象,然后迭代工作表。否则,每次阅读工作表时,使用read_excel将重新打开文件;这可能需要很长时间。

#4

Tia https://github.com/bpsmith/tia is the best I've found, and I've tried them all... It allows you to do:

Tia https://github.com/bpsmith/tia是我发现的最好的,我已经尝试过所有这些...它允许你这样做:

import pandas as pd
import datetime
import tia.bbg.datamgr as dm
mgr = dm.BbgDataManager()
sids = mgr['BAC US EQUITY', 'JPM US EQUITY']
df = sids.get_historical(['BEST_PX_BPS_RATIO','BEST_ROE'],
                         datetime.date(2013,1,1),
                         datetime.date(2013,2,1),
                         BEST_FPERIOD_OVERRIDE="1GY",
                         non_trading_day_fill_option="ALL_CALENDAR_DAYS",
                         non_trading_day_fill_method="PREVIOUS_VALUE")
print df

#and you'll probably want to carry on with something like this
df1=df.unstack(level=0).reset_index()
df1.columns = ('ticker','field','date','value')
df1.pivot_table(index=['date','ticker'],values='value',columns='field')
df1.pivot_table(index=['date','field'],values='value',columns='ticker')

The caching is nice too.

缓存也很好。

Both https://github.com/alex314159/blpapiwrapper and https://github.com/kyuni22/pybbg do the basic job (thanks guys!) but have trouble with multiple securities/fields as well as overrides which you will inevitably need.

https://github.com/alex314159/blpapiwrapper和https://github.com/kyuni22/pybbg都做了基本的工作(谢谢大家!)但是有多个证券/领域以及你不可避免需要覆盖的问题。

The one thing this https://github.com/kyuni22/pybbg has that tia doesn't have is bds(security, field).

这个https://github.com/kyuni22/pybbg有一件事就是tia没有bds(安全,字段)。

#5

You can also use pdblp for this (Disclaimer: I'm the author). There is a tutorial showing similar functionality available here https://matthewgilbert.github.io/pdblp/tutorial.html, the functionality could be achieved using something like

您也可以使用pdblp(免责声明:我是作者)。有一个教程显示类似的功能https://matthewgilbert.github.io/pdblp/tutorial.html,功能可以使用类似的东西实现

import pdblp
con = pdblp.BCon()
con.start()
con.bdh(['IBM US Equity', 'MSFT US Equity'], ['PX_LAST', 'OPEN'],
        '20061227', '20061231', elms=[("periodicityAdjustment", "ACTUAL")])

#6

A proper Bloomberg API for python now exists which does not use COM. It has all of the hooks to allow you to replicate the functionality of the Excel addin, with the obvious advantage of a proper programming language endpoint. The request and response objects are fairly poorly documented, and are quite obtuse. Still, the examples in the API are good, and some playing around using the inspect module and printing of response messages should get you up to speed. Sadly, the standard terminal licence only works on Windows. For *nix you will need a server licence (even more expensive). I have used it quite extensively.

现在存在适用于python的Bloomberg API,它不使用COM。它具有允许您复制Excel插件功能的所有钩子,具有适当的编程语言端点的明显优势。请求和响应对象的文档记录很差,而且非常迟钝。尽管如此,API中的示例仍然很好,有些使用检查模块和打印响应消息可以让您加快速度。遗憾的是,标准终端许可仅适用于Windows。对于* nix,您将需要服务器许可证(甚至更昂贵)。我已经广泛使用它了。

https://www.bloomberg.com/professional/support/api-library/

#1