Thursday, May 30, 2013

Printing Bloomberg historical price screen with DDE

Sometimes we need to have printed paper documentations for Bloomberg pricing sources used for valuations for each and every security in our bond books. Now, imagine that there are a few hundred bonds out there and you should print these screenshots one by one: by manually inserting every required parameter in Bloomberg screen and pressing print. Frankly said, this would be more than time-expensive and tedious task, and definitely not something I would like to be involved with. However, I have to be involved. How could I outsource this process for a machine?

Fortunately, there is one approach what you can use in VBA: Dynamic Data Exchange (DDE). DDE has its problems and in general, I try to avoid using it. However, it has its rare moments exactly for things like this. Instead of doing this process manually, I have a program which reads a list of required parameters and then prints out price documentations one by one by using DDE.

The point in my example given below is, that you will get the basic idea how you could use DDE for this particular type of problem. Then, you can work for developing your own solution. You can copy-paste the following program into a new VBA Standard Module.

Note: for this program you need to have active Bloomberg licence in your machine.


Option Explicit
'
Private Const CONST_APP As String = "WINBLP"
Private Const CONST_TOPIC As String = "BBK"
'
Private channel As Long
Private executionString As String
'
Public Sub tester()
    '
    On Error GoTo errHandler
    '
    channel = DDEInitiate(CONST_APP, CONST_TOPIC)
    '
    Dim executionString As String: executionString = createExecutionString("DE0001102317", "CBBT", "D", "B")
    DDEExecute channel, executionString
    '
    DDETerminate channel
    Exit Sub
    
errHandler:
    MsgBox "Undefined error", vbCritical, "Error"
    Exit Sub
End Sub
 
Private Function createExecutionString(ByVal isinCode As String, _
ByVal provider As String, ByVal period As String, ByVal quoteType As String) As String
    '
    Dim executionString As String
    executionString = "<BLP-1><HOME>ID " & isinCode & "<GO>" & _
                      "HP<GO><TABR>" & _
                      provider & "<TABR>" & _
                      "<TABR><TABR><TABR><TABR>" & _
                      "<TABR><TABR><TABR>" & _
                      period & quoteType & "<GO>" & _
                      "<PRINT>"
    '
    createExecutionString = executionString
End Function
'

As you can see, the beef is inside createExecutionString function which creates executionString for DDEExecute function. When you open any Bloomberg function (say, SWPM<GO> for example), you can set up input values for all required argument fields by

1) clicking a field active with your mouse and then writing a value into a field
2) moving with keyboard TAB and then writing a value into a field

As you can see in createExecutionString function, it uses the latter approach by moving with keyboard TAB and then writing a value into a field. If you run that test program, you will see that it actually does all of this: moving with cursor in fields and writing a value. It's like that macro what you can record in your Bloomberg. 

Anyway, I hope that you would find this post to be helpful and it could help you to solve your problem. Have a great start for June!
-Mike

2 comments:

  1. Hello Mikael,
    I tweaked the above program a bit to get a screenshot of the 'GP' screen for a desired equity ticker and specified time period using the command instead of at the end of execution string. Even though this works perfectly fine, I am not able to automate the part where we need to type the "file name", "file type" , change directory and press to save the file in a desired format (like Picture1.jpg)
    I believe this is not related to Bloomberg commands but the "Save Screen As" window (using the windows api)
    I tried simulating key press using the SendKeys function, but it does not work.
    I would highly appreciate if you can guide me to a probable solution.
    Thanks.

    ReplyDelete
  2. hi Mikael,
    the code above is working well
    but can I get two isin values in a single paper,so that I can save the paper?
    any response...!

    ReplyDelete