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.

If you have an excel file over 10mb in size excel could become unstable and it will take forever to save, backup, or pretty much do anything with. It will also use significantly more memory, degrading the performance of your machine as a whole.

I've worked in several offices dealing with excel files pushing 30mb. The reality is that these files need only be around 3 or 4mb. Regardless of how many rows of data you may be storing.

Here are my key tips to reducing the size of your excel file:

1. Remove pictures

Shadows, images, and drawings all increase file size, drawing objects created by adding 3d effects or shadows have the worst effect as they're just stored as (large) pictures. If you've added shadows to some cells, remove them.

Coloring cells also has a negative effect on file size, although more limited.

2. Delete unwanted Rows

This sounds silly, but it has a massive effect on file size.

Here's a really simple example:
I've created a workbook with 3 sheets, each with 1000 records covering 18 columns. if I write some simple text to cell 65000 in each sheet the file size jumps by 500kb.

Excel sometimes seems to extend the lower boundry of worksheets without any real reason, so if you're working with large files I suggest you check to see if there are reams of unused rows.
I've reduced the size of one particular file from 24mb to 1.5mb using only this tactic.

This also leads me on to my third point

3. Don't let macro's loop over every cell in a row / column

There are plenty of macros that will perform an assessment on every single cell in a particular column, all the way down to row 65k

Not only will this make it take about 20 minutes to run, but it can easily trick excel into thinking that it needs to save the contents of every single row (see point 2).
Here's a simple loop which will only iterate over cells if they're occupied (your data will have to be in a proper table-structure):

dim usefulCell as Range 

set usefulCell = SampleSheet.Range(col1_header_range_name)

Do until usefulCell.Value = ""

	'Do some stuff here, for example:

	CustomerObject.Name = usefulcell.Value

	CustomerObject.Address = usefulcell.offset(0, 1).Value

	CustomerObject.Age = usefulcell.offset(0, 2).Value

	'etc

	set usefulCell = usefulCell.offset(1, 0)

Loop

4. Don't do things twice

Don't have more formulae than you have to. If you're relying on a specific calculation multiple times, only calculate it once, then refer to that calculation in subsequent cells.

Don't replicate data, there's no point copying huge wads of data from one worksheet to another, reorganize so that you only need it in one place

Use static values wherever possible: If data comes from some external source, milk that source to the max. Anything you are calculating which could be imported SHOULD be imported, calculations increase file size and reduce performance.

I hope these tips help to make your excel user experience a better one. Please email me with any questions or comments.

Range.Offset[a, b].value , when using VBA is a great way to add values to adjacent cells without having to actually select every cell you write to (which is very slow as it requires the UI). When I started using VSTO I could only find the Offset method attached to the strongly typed Named_Range object.

Luckily, it does exist for a regular range, except the method is called get_Offset (obviously).

So there you have it. Sounds simple, but this has been frustrating me for a couple of weeks now.

rangeObject.get_Offset[1, 2];
 

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 retreiving 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