How to refresh/load RTD Bloomberg function (BDH) in excel in vba

Question

I would like to know if there's a way in VBA code forcing the bloomberg functions (In spreadsheet) to update its value( Any BDH functions)

Targeting Developers have faced similar issue/ have Bloomberg terminal

What have I tried--

Application.RTD.RefreshData
Application.RTD.throttleInterval = 0
Application.CalculateFull

The BDH function do not reload themselve.

The only way I can refresh them now is : I click the "Refresh WorkBook" Button on the Ribbon of the BloomBerg add-in.

Since the Bloomberg Add-in is locked in VBE, I cannot find out the necessary code. Am I missing any Bloomberg Reference? Can any Bloomberg expert/user point me in the right direction? Thanks.

Sanson

How are you calling the Bloomberg functions? Unless you are using 'Live' Bloomberg fields, then Bloomberg caches the data. Clicking the Ribbon button clears the cache and then reloads the data. Using the Bloomberg API, you'll need to do the same (something like RefreshAllStaticData()). – Tenaille

@creamyegg Hi, thanks for replying. So I can call "RefreshAllStaticData()" in vba act as the click event on the ribbon button "REfresh workbook" ? Also, where can I find those Info? Is there a link to vba bloomberg reference like that? Thanks in advance – Sanson

yes but you'll need a reference to the Bloomberg API. Take a look at the examples on the Bloomberg API page (I can't remember the name of the page) on Bloomberg. It should give you some direction there. – Tenaille

Sorry, but can I have a link to the VBA Bloomberg API I need to install? So I can install it. There are so many API on bloomberg's site. – Sanson

Sorry, I don't know the name of the Reference off hand and I don't have access to Bloomberg any more. If you have the Bloomberg Excel add-in, you'll already have it so it'll be listed in the references dialog. – Tenaille

Thanks, but the add-in are locked so I can't have any access to me. Anyway, thanks – Sanson

I have checked WAPI<GO> and asked bloomberg support, they said Macro are not supported by Bloomberg. Oh well. – Sanson

Can give it a try but no promises :) where can i download the add-in which you are referring to? – Anacrusis

@SiddharthRout Thanks for your generous help, but I am targeting developer/program that has faced this problem before. Because as far as I know, you need to have a Bloomberg terminal in order to have the add-in itself. Thanks again. – Sanson

@SiddharthRout I can also use help on cracking the password-protected xla file. Thanks <--- – Sanson

Sorry I do not help with Password cracking :) – Anacrusis

Answer 1

I did a searching of the keyword "refresh" in the xla by opening it in notepad. Found the following targets:

RefreshAllWorkbooks
blpmain.xla!RefreshAllStaticData
blpmain.xla!'RefreshWorkbookFundamentalsData
blp.xla!IsBlpRefreshAvailable

I tried them out one by one, the first 2 works by calling:

Application.run "RefreshAllWorkbooks"
Application.run "RefreshAllStaticData"

But not calling them alone ( I guess it's because I somehow can call protected PUBLIC procedure using Application.run)

RefreshAllWorkbooks

or

RefreshAllStaticData

Thanks for all the help

Sanson

Also an interesting alternative: #7661471 but your approach seems better (assuming they don't change their code too often). – Georg

@Georg That's a solution. But considering I am dealing with ~ 200 BDH functions in different sheets. I won't want to clearContents all the affected sheets and search every cell for BDH functions. OP's original approach was close, just drop the blpmain.xla part it will work – Sanson

Answer 2

I recently received this answer from bbg chat. I think this is what we are all looking for...

bbg helpdesk: Normally we don''t provide help on VBA on the Help Desk but I have found the below. You can use the following VBA commands to refresh BDx() formulas:

Refresh based on default option setting: Application.Run "RefreshData"  
Refresh current selection: Application.Run "RefreshCurrentSelection"
Refresh current worksheet: Application.Run "RefreshEntireWorksheet"
Refresh current workbook: Application.Run "RefreshEntireWorkbook"
Refresh all workbooks: Application.Run "RefreshAllWorkbooks"

Note: When using VBA macros to refresh Bloomberg formulas, the formulas cannot complete requesting data while the macro that triggered the refresh is running. You must use Application.OnTime() to schedule a second function to run after the sub-routine that triggered the refresh exits. The following code snippet demonstrates the VBA code to refresh all workbooks, followed by a 10 second delay before calling the processSheet sub-routine:

    Sub refreshSheet()
        Application.Run "RefreshEntireWorksheet"  
        Application.OnTime (Now + TimeValue("00:00:10")), "processSheet"
    End Sub


    Sub processSheet()
        ' perform processing here
    End Sub

Controversial

Answer 3

I've found that changing something in the BDH formula would cause a refresh. Find and replace the = sign would do the tick.

Public Sub Recalc()
    Dim ws As Worksheet, FormulaCells As Range, c As Range
    Application.Calculation = xlCalculationManual
    For Each ws In ThisWorkbook.Worksheets
        On Error Resume Next
        ws.Activate
        Set FormulaCells = ws.UsedRange.SpecialCells(xlCellTypeFormulas).Cells
        If Err = 0 Then
            For Each c In FormulaCells
                c.Formula = Replace(c.Formula, "=", "=")
            Next 'c
        Else
            Err.Clear
        End If
    Next 'ws
    Application.Calculation = xlCalculationAutomatic
End Sub

Jonell

Answer 4

I have never managed to do what you ask for. The only reliable way I have found to get up-to-date data is by calling the API directly from VBA with BLP_DATA_CTRLLib.BlpData, waiting for the answer, and putting the result into a sheet.

With regards to opening password protected VBA code, a google or stackoverflow search should give you your answer.

Georg

Just FYI :) meta.stackexchange.com/questions/137086/… – Anacrusis

LOL :) I understand but do you think Bloomberg would approve it? Imagine if you created an Add-In and password protect it, would you be ok with it if someone discusses on SO on how to crack it ;) ... Just a thought.... – Anacrusis

Thanks, I think your solution is the standard way of calling Bloomberg API from Excel. My problem is my user has generated hunderds of BDH functions on the excel. If I use your method, a generic way is to overide bloomberg's UDF? I found the solution not by cracking the .xla file. I just open notepad to edit the xla file, and search for all the "Refresh" keyword. Found out both "RefreshAllStaticData" and "RefreshAllWorkbooks" works! Thanks anyway. I will give you the bounty in 6 hours (SO restriction) @SiddharthRout thanks for your help too, see my answer above – Sanson

@Larry: I wanted to help and would have helped but didn't know where to download the add-in from and hence I asked you where to download it from but then had to step back when you asked me about cracking the password-protected xla file. Glad you got your query solved :) – Anacrusis

Btw, it would be best if you can kindly include a html or link to "How to install the API", "Hello World" example of the API – Sanson

@Sanson I don't think I deserve the bounty on this one to be honest. As to "how to install the API": install Bloomberg and the API comes with it. And for Hello World examples, the best place is WAPI <Go> (only available to subscriber, from the terminal). – Georg

Answer 5

This works for me:

WS.Select
WS.Range("A5").Select 'the cell that contains the BDH function
Application.Run "RefreshCurrentSelection"

Shafer