As the snappy title implies, the following items I consider to be useful pieces of information when working with Bloomberg.

1. Don't store any data retrieved via the Bloomberg Client Terminal API

Don't put it in a database, pass it around on excel sheets, use it as a data source for internal systems, or anything like this at all. Doing so will land you in a lot of trouble with Bloomberg for breaking your license agreement, something they take very seriously (can you say 'lawsuit'?)

2. Get end users to upgrade their aging terminal software to take advantage of Bloomberg's real-time Excel formulas.

If your users are still using spreadsheets filled with =BLP() formulas then a huge portion of CPU power on their machines is dedicated to continually refreshing 'static' data. Bloomberg had to use a series of macros and elaborate hacks to simulate real-time data feeds to excel before Microsoft included support for real-time data streaming ( =RTD() ).

Newer versions of Bloomberg software include the =BDP() function which is simply a nice wrapper for Excel's build-in data streaming functionality. Using these new formulas will significantly improve the performance of Excel, and the PC as a whole. Plus sheets with the legacy =BLP formula will still work if you are not going to be able to migrate them.

3. Get a Bloomberg Developer License

Bloomberg offers a special cut-down license for IT professionals who work a lot with the Bloomberg Client Terminal API. This provides you with a limited amount of data through the API and some basic terminal software functionality. It's far from the ideal development environment (I hit my daily data limit by about 11am normally), but it's good enough to make sure what you're doing actually works.

It costs around 1/3 as much as a 'real' terminal license, so you can save a lot of dough.

4. Really understand what fields you're looking at.

Get yourself on a bloomberg terminal, navigate to the main page of an instrument (use "GOOG EQUITY" for a good example) then type "FLDS". You can now search for any field you want and see what value is assigned to it for the current instrument.

If you don't have access to the terminal (hi to fellow developers with restricted licenses) just go to the bloomberg menu in excel and use the 'field search' functionality. It basically does the same thing, except you don't get instrument values, merely a text description of the field.

5. You can use the API for 2 or 3 Days after last logging-in to the Terminal

So you closed your bloomberg Terminal window? Never mind, you can still get data via the API! This data connection stays active until a few days have passed or you log on somewhere else (if you've got an anywhere license).

This means you don't have to have a Bloomberg loaded to use bloomberg-enriched Excel sheets or applications using the API, saving memory, desktop space, and processing power.

6. The bloomberg buttons map to the function keys

Don't put up with a clunky Bloomberg Keyboard if you don't have to, as a developer you can just use your regular keyboard and ask Bloomberg to send you some keyboard stickers. These stickers go around your keys to show you the special bloomberg keys.

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.

Solutions

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:

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")

be sure to wrap any use of these in good error checking to avoid Excel getting mad at the user.

Summary

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.

I have found whilst working in financial institutions that I am continually called upon to integrate Bloomberg market data into spreadsheets using VBA in Excel. While the Bloomberg API returns data in an array, I tend to work with recordsets when dealing with large sets of data. Because of this I have some standard code which I reuse in all my Bloomberg projects to fairly efficiently convert bloomberg results arrays into recordsets.

ADODB.Recordsets have advantages over arrays for storing larger volumes of data, such as in built sorting and searching methods, and while converting an array into a recordset may seem like a waste of time it can make working with large volumes of Bloomberg data a lot easier.

Implementing this function is very simple, you simply loop through the array and write the relevent values to the recordset after jigging about with the format of the original array.

Here is the code complete with an example of retrieving data from the Bloomberg API:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Function: BBArrayToRecordset
' Required Reference to: Microsoft ActiveX DataObjects
' Author: Matthew Rathbone
'
' Description: This function takes a results array as output by the Bloomberg API and converts it
' into a ADODB.recordset
'
' Copyright: Matthew Rathbone: Free for commercial and non-commercial use.
' Links to www.matthewrathbone.com are always appreciated
''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function BBArrayToRecordset(fieldList As Variant, dataArray As Variant) As ADODB.Recordset

    'declare our return object
    Dim returnSet As ADODB.Recordset
   
    ' string to hold our list of fields
    Dim fieldString As String
   
    'string to hold our list of records
    Dim recordString As String
   
    Dim rDataArray As Variant
    ReDim rDataArray(0 To UBound(dataArray, 2))
   
    'convert the fields array to a string
    fieldString = Strings.Join(fieldList, ", ")
   
    'construct our return object
    Set returnSet = New ADODB.Recordset
   
    ' lets make our recordset! Looping and adding fields
    For i = 0 To UBound(fieldList, 1)
        returnSet.Fields.Append fieldList(i), adVarChar, 500
    Next
   
    'open the recordset
    returnSet.Open
        ' lets fill our recordset!
        For i = 0 To UBound(dataArray, 1)
            For ii = 0 To UBound(dataArray, 2)
                rDataArray(ii) = dataArray(i, ii)
            Next
            returnSet.AddNew fieldList, rDataArray
        Next
       
   
    'return the recordset
    Set BBArrayToRecordset = returnSet
   
End Function

'Example of it's use
'THIS EXAMPLE REQUIRES THE BLOOMBERG DATA TYPE LIBRARY REFERENCE
Public Sub exampleExecution()

    Dim oBlp                    As BlpData
    Dim aResult                 As Variant
    Dim aTickerList             As Variant
    Dim aFieldList              As Variant
    Dim exampleRecordset        As ADODB.Recordset
   
    'defining the securities we want data for
    ReDim aTickerList(0 To 1)
    aTickerList(0) = "AMGN EQUITY"
    aTickerList(1) = "GOOG EQUITY"
   
    'defining the fields we want to retrieve
    ReDim aFieldList(0 To 1)
    aFieldList(0) = "NAME"
    aFieldList(1) = "PX_CLOSE"
   
        'initialize our bloomberg data object
        Set oBlp = New BlpData
       
        'we want a single subscription to bloomberg (will not fire events ongoing instead populates our result array)
        With oBlp
            .Timeout = 40000
            .SubscriptionMode = ByRequest
            .Subscribe aTickerList, 3, aFieldList, , , aResult
        End With
   
    Set oBlp = Nothing
    Set exampleRecordset = BBArrayToRecordset(aFieldList, aResult)
       
   
End Sub

Link to the actual code module