elmama
​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


Accessing Bloomberg Financial data via EXCEL/VBA 


When I was first tasked with accessing Bloomberg data via EXCEL/VBA it was pretty tough going. There wasn't much to go on. Even a trawl through the Net didn't reveal much - still doesn't in fact. Proper real-life examples are thin on the ground but I'm going to give you one here. Incidentally if anyone working for a financial institution is reading this article and needs some help on this subject my rates are very reasonable - just email at oracle_tips@hotmail.com for a chat. Anyway here goes. What the example below will do is for a given index eg CAC40 it will retrieve each index member and print its SEDOL code, closing price and weight within the index on an EXCEL spreadsheet.  Useful for fund managers or anyone who wants to track and reconcile their own indices against the official numbers. I am assuming that your Bloomberg/EXCEL connection is up and running OK.

Here's the code.

Dim XOBJ As BLP_DATA_CTRLLib.BlpData
Dim VTRESULT As Variant
Dim SECFIELDS As Variant
Dim str As String

index_name = "CAC"

quitstr = "QUITTING - APPLICATION TIMED OUT"

'
'APPLICATION QUITS IF THERE IS 12 MINUTES OF INACTIVITY
'
Application.OnTime Now + TimeValue("00:12:00"), "quitexcel"


'
'Clear the sheet
'
Cells.Select
Selection.Clear
Range("A1").Select

Cells(1, 6) = "Last run date/time =  " & Date & " " & Time
Cells(1, 8) = "=now()"
Cells(1, 9) = index_name

Set XOBJ = New BlpData

' Set up the field(s) we want in an array
arrayfields = Array("indx_mweight")


On Error GoTo Q

'
' Timeout after two minutes without reply from Bloomberg
'
XOBJ.Timeout = 120000

XOBJ.Subscribe index_name & " index", 1, arrayfields, Results:=VTRESULT

If IsEmpty(VTRESULT) Then
    quitstr = "QUITTING AT STAGE 1"
    quitexcel
End If


nr_members = UBound(VTRESULT(0, 0)) + 1

I = 0
For X = 1 To nr_members
    Cells(X, 1) = VTRESULT(0, 0)(I, 0)
    Cells(X, 2) = VTRESULT(0, 0)(I, 1)
    I = I + 1
    Cells(2, 6) = "GETTING INDEX MEMBER/WEIGHT " & X & " OF " & nr_members
Next X


arrayfields = Array("id_sedol1", "px_yest_close")

ReDim SECFIELDS(nr_members)

For X = 1 To nr_members
    SECFIELDS(X) = Cells(X, 1) & " Equity"
Next X

I = 0
'
' Timeout after 6 minutes of inactivity from Bloomberg
'
XOBJ.Timeout = 240000

XOBJ.Subscribe SECFIELDS, 1, arrayfields, Results:=tom

If IsEmpty(tom) Then
    quitstr = "QUITTING AT STAGE 2"
    quitexcel
End If


For X = 1 To nr_members
    Cells(X, 3) = tom(I, 0)
    Cells(X, 4) = tom(I, 1)
    I = I + 1
    Cells(3, 6) = "GETTING sedol/price " & X & " OF " & nr_members
Next X


saveexcel

End
Q:
quitstr = "QUITTING - BLOOMBERG TIMED OUT"
quitexcel
End Sub

Sub quitexcel()
        Cells(13, 6) = quitstr
        ActiveWorkbook.Saved = True
        Application.DisplayAlerts = False
        Application.Quit
End Sub


Sub saveexcel()
    Cells(12, 6) = "SAVING"
    ActiveWorkbook.SaveAs "c:\cac.csv", xlCSV
    quitsr = "QUITTING - OK"
    quitexcel
End Sub