Tip #2001-10: Continually Collect Bloomberg Tick Data
How do I automatically update the BLPI function to continually collect tick data in Bloomberg?
In their Excel Tools, Bloomberg offers a BLPI function that allows you to retrieve historical intra-day data. For example, with the BLPI function you can collect the Last Price and aggregate volume for a specific instrument for one-minute intervals throughout the trading session. However this function does not update automatically and was not designed for collecting continuous tick data.
However, with some macrocode, you can easily collect Bloomberg tick data. We have created a mini-app, which collects the last price and trade size for an equity instrument every time real-time data is updated. The mini-app uses Bloomberg real-time data. You can turn tick collection on or off simply by clicking a button. The macro, which runs when tick collection is turned on, uses the Visual Basic for Applications OnData property. The OnData property is triggered when DDE or OLE-linked data are updated. In our application, when the price and trade size are updated, OnData runs a macro that collects the data in a time series database. In other words, each time a DDE link is updated, the value in the cells containing the real-time data is added to a the database.
To make the application more interesting, we created a chart which displays the price data. The chart updates automatically when new data is added. In addition we used Excel's MIN and MAX function to display the high and low for the price and the trade size data being collected. We used Excel's MATCH and OFFSET functions to display the time at which the high and the lows occurred.
You can edit Bloomberg's real-time data formula to collect price ticks on just about any instrument available. The steps to change the data being collected are listed in the comment in cell C3. The macrocode is not hidden and is well documented. We have received many questions in the WillowTalk Forum on how to run a macro when real-time data is updated. Reviewing the macrocode in this mini-app should show you the technique on how to use VBA's OnData property.
If you are a Bloomberg subscriber, select Yes
to update the links, when you open the workbook and you will be able
to use the application to collect tick data.
If you are not a Bloomberg subscriber, just select No
when asked to update links to information in another workbook. You
will be able to review the macrocode and the Excel functions to see
how it works.
Contact us if you would like to employ our services to help you enhance this model or help you design financial models or customized applications using Bloomberg, Bridge, Factset or Reuters data.
|
