Be Careful of Data Obtained From Bloomberg using RTD or BDP Functions
Hire me to supercharge your Hadoop and Spark projects
I help businesses improve their return on investment from big data projects. I do everything from software architecture to staff training. Learn More
The RTD / BDP functions provide real-time data from bloomberg directly to an excel sheet. However if you’re subscribing to fields which Bloomberg hasn’t designated to be ‘real-time enabled’ you have to manually refresh bloomberg data for it to have any effect.
Let’s look at an example
FUT_CTD_PX (Cheapest to deliver price) is a static FIELD for a real-time VALUE. IE, if you check in the BB terminal this value will be constantly changing, but excel sees it as a static field.
To get the CTD price for a future we can use the following formula:
=BDP(“SOME FUTURE’S TICKER”, “FUT_CTD_PX”)
This field will never update, it will stay static with it’s initial value. If the spreadsheet has been open for 5 days, it’s the price from 5 days ago which will be displayed.
Microsoft’s RTD server model is event driven, in that the data-source notifies the RTD server when a new value is available and that value is picked up by a spreadsheet when excel calls it’s RTD.RefreshData method (which it does every 2 seconds by default). In our example, Bloomberg’s data-source never notifies the RTD server of a change in value for any of it’s STATIC fields, and they therefore remain the same indefinitely.
This also explains why calling RTD.RefreshData from VBA code has no effect, as the RTD server has no new value to pass forwards.
The only REAL solution is for Bloomberg to provide such key fields as real-time. Static fields should be reserved for values which do not change on a regular basis, such as instrument name, maturity date, ISIN, and so forth.
As a manual solution, Bloomberg does provide a Excel menu containing data-refresh commands. Clicking ‘refresh entire workbook’ will eventually update all static values (it can take up to 10 seconds), but only for the =BDP formula. If you’re using =RTD you’ll have to re-open your workbook or delete and re-type the formulas.
“Can I do this Programatically?” I hear you cry…. Why yes you can!
It’s not a graceful solution by any means but you can use one of the following to activate the respective bloomberg command:
be sure to wrap any use of these in good error checking to avoid Excel getting mad at the user.
Application.Run ("bloombergui.xla!RefreshData") [Default] Application.Run ("bloombergui.xla!RefreshCurrentSelection") Application.Run ("bloombergui.xla!RefreshEntireWorksheet") Application.Run ("bloombergui.xla!RefreshEntireWorkbook") Application.Run ("bloombergui.xla!RefreshAllWorkbooks")
It’s important to be aware that some bloomberg fields are not real-time. If you’re using one of these fields for a key calculation you’ll have to make sure that you manually refresh regularly, or that you have integrated a crude automation into your code.