How do I store data from the Bloomberg API into a Pandas dataframe?

Question

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.

A very similar question was asked here: Pandas wrapper for 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.

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.

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

# 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..."

Solution 1

I use 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)

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

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.

Hope it helps

Solution 2

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

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

Solution 3

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']:

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

Solution 4

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

https://github.com/bpsmith/pybbg

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

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.

• 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).

• 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

• 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.

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.

Solution 5

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

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.

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

View more solutions

Comments

[Cody Gray]

The download link has gone dead, so I've gone ahead and removed it in response to a flag. If possible, and you get a chance, it would be great if you could find the new location and update your answer.

[rsc05]

I got the following error File "C:\Users\chehabr\AppData\Local\Continuum\anaconda3\lib\site‌​-packages\tia\bbg\v3‌​api.py", line 186 print 'unhandled event: %s' % evt.EventType ^ SyntaxError: Missing parentheses in call to 'print'. Did you mean print('unhandled event: %s' % evt.EventType)?

[D.L]

this answer is aged: it appears as though the tia module works with version 2.x but fails to work with python 3.x

[dsugasa]

Searching through the git hub issues, there is discussion about converting from Python 2 to Python 3. Additionally, github.com/PaulMest/tia has python 3 version with several recent updates.

[dsugasa]

xbbg also works well for BDS queries pypi.org/project/xbbg