Home > Forum Home > Valuing Investments and Businesses > Work with Excel and Bloomberg Share

Work with Excel and Bloomberg

Excel Help for Work With Excel And Bloomberg in Valuing Investments and Businesses


Forum TopicPost Reply Login

Work With Excel And Bloomberg

Rate this:
(3/5 from 1 vote)
ConfusedHi,

I am going to develop one investment software in which I need to download data from Bloomberg. I can download using 'Bloomberg menubar' on Bloomberg workstation, but I want to do this programmatically by using Bloomberg API and excel VBA. Can anyone help me in this issue?
 Posted by on
 
Replies - Displaying 1 to 10 of 21Order Replies By: Most Recent | Chronological | Highest Rated
Confused
Rate this:
(4/5 from 2 votes)
Hi,

Thanks for your reply. I've already started my development using BB's ActivieX control for VBA and for now I should do my development on BB installed/licensed machine. Then only I can download data from BB. Is this an only way to develop an application using the BB API?

Can I use the following code to contact BB from a non-BB licensed (my development) machine?

VBA Code:
ChNo = Application.DDEInitiate(app:="BLP", topic:="S")

BBQuery = " ' " & ISIN & Corp & ", [RTG_SP] ' "

BBList = Application.DDERequest(ChNo, BBQuery)

Thank you once again and looking forward to your reply.
 Posted by on
Shocked
Rate this:
(4/5 from 2 votes)
You can probably initiate the Bloomberg API within the VBA code and then make calls based on available functions with the API.  A full list of fields available for the Bloomberg API can be found at [http://about.bloomberg.com/software/info_api.html].

An example of initiating and using this once logged in to Bloomberg could be:

VBA Code:
ChNo = Application.DDEInitiate(app:="BLP", topic:="S")

BBQuery = " ' " & ISIN & Corp & ", [RTG_SP] ' "

BBList = Application.DDERequest(ChNo, BBQuery)

Alternatively, you could download the Bloomberg ActiveX control (available from [http://about.bloomberg.com/software/api_tools.html]) and interact with that from within your Excel VBA Code.
 Excel Business Forums Administrator
 Posted by on
Shocked
Rate this:
(4/5 from 2 votes)
I think that the issue here would be the Bloomberg security.  If you can login to Bloomberg from the remote machine then this may be possible.

One possibility would be to use VBA code to initiate the Bloomberg login window and then use a Send Keys command to enter the credentials.

VBA Code:
Sub BBlogin()
    Dim BBwindow As Long
    BBwindow = FindWindow(vbNullString, "1-BLOOMBERG")
    If BBwindow = 0 Then
        MsgBox "Error - cannot find Bloomberg login window."
        Exit Sub
    End If
    ShowWindow BBwindow, SW_SHOWNORMAL    
    Application.SendKeys "username", false
    Application.SendKeys "{TAB}", false
    Application.SendKeys "password", false
    Application.SendKeys "~", false
End Sub
 Excel Business Forums Administrator
 Posted by on
Happy
Rate this:
(3/5 from 1 vote)
You can use web queries in Excel to download market data from the Bloomberg web site, Yahoo Finance, and Google Finance.

Refer the post here for how to do this.
 Excel Business Forums Administrator
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
Hi Russell,

Sorry! After long time I come back to you. Thanks for your reply. Currently, I have just split my BB data downloading code in VBA from other part of my module because later on I can just add that Excel VBA file into my project.

Alangrn,

If you have a Bloomberg licenced machine, then you can download the documentation for VBA, .NET...etc from Bloomberg's help itself.
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
freddiemac,

If we use DDE,  can we still download BB data from non-BB computer by providing authentication information?

Also, can you tell me more about DDE and difference in using DDE and ActiveX Control? and, can we use excel vba to work with DDE?
    
Actually, I want to download large amount of historical data from BB and now I am using ActiveX object to do that. My concern here is that I would like to keep my program away from the BB computer. Is there any other suggestion on this?

Thanks in advance.
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
freddiemac,

I am something clear now. I think what I am doing now is right one.

Finally I  came to the conclusion that its better to forget about DDE and it always better to keep download code on BB machine.
   
I can download historical records just like you mentioned before, using subscription request methods. 

Thank you very much for ur help! it really helps me a lot.
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
Sorry I am not sure if this is the correct forum, but I am looking for courses on how to use Bloomberg with Excel VBA. I have been developing VBA application for many years but have never retrieved data from Bloomberg. Any resources will be appreciated.
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
Hi! If you want to download a large list of historical info the best way is to use ActiveX, asynchronous request, subscription by request. In my experince that is by far the fastest and most reliable way to do it. If you go to the WAPI<go> and then search for ad hoc examples for ActiveX then you will find a Xl-file called SimpleSynchronous. In the file you can see the diffence subscription modes. It is very easy to modify the code in that example.

I strongly advice against using DDE. When using ActiveX asynchronous request, subscription by request you can send a matrix to BB and get a matrix back ie you only have to ask one question to BB even though you want eg 10 diffenets info fields for 150 different securities. VBA usually hangs when you have a long loop which you have to have when using DDE. Thus DDE is by no means recommended (if you want to keep your job..). ActiveX is a bit more trick since you have a class module. However by using the example I reffered you to you only have to modify a bit in order for it to work so you really dont need to know much programming. Please let me know if I can further assit you! HTH
 Posted by on
Oops
Rate this:
(3/5 from 1 vote)
Oh I forgot. if you want to have the macro at another computer is a bit more tricky. I advise aganist it because it might work in theory but in realy life it will probably hang quite often. you can however connect 2 xls files to each other if they are on the same network. Note that this in practice requires ActiveX. DDE requires that you are logged in to the BB. Doing that from a distant is doable by using SendKeys (but it is only doable in theory since it will work 1 out of 10 times). Needless 2 say I advice against. ActiveX does not require that you are logged in. In sum if you are on the same network it is doable. However mind the legalities involved...
 Posted by on
 Displaying page 1 of 3 

Excel templates and solutions matched for Work with Excel and Bloomberg:

Solutions: OLAP Reporting Resource Management Construction Cost Estimation Employee Shift Scheduling Complete Trading System Financial VBA Code Password Recovery Business Planning Templates Index Numbering and Logging Real Time Stock Quotes Charting Tools Compare Files Worksheet Analysis Separate Data Video Tutorials Dimensional Analysis and Measurement Construction Schedule Templates Workbook Search File Navigator Merge Data Ranges Batch Printing Training Templates Six Sigma Templates Random Sampling Dashboard Training with Examples Work Efficiency Add-ins
Categories: Excel Productivity Time Saving