I need help with feeding live information into Access from Bloomberg. I use API in Excel all the time. I have recently taken a high level class in Access to become more familiar with it, however, I am still somewhat shaky in VBA. I know that I can link Excel spreadsheets to Objects in Access, so I can see that as a temporary solution. I would really like to feed the information directly into access through ActiveX controls maybe? Any ideas? I have roughly gone through the WAPI screen but at first glance it is a bit over my head. Can someone point me in the right direction? Thanks! Jessica

this is a really great topic... I am getting lots of parallel use out of Bloom by putting an Access data broker between B and the office network - i.e. requests fed to Access, Access feeds to B-API, B-API feeds to Access, user queries from Access... and everybody in the office can see my db - you get the picture. Plus, Access forms are a better way to set up an event-driven environment rather than slapping buttons onto a sheet, and as you say, if you have offline analytics, you just stuff the spreadsheet into Access and pass the params through.After all this talk about 'Who likes Excel/VBA' I must admit that I really prefer Access to Excel in many ways.That said, the way I have my API is in Excel, who calls Access - it's a bad way to do it and I did it because I didn't have Access installed on that machine at the time (funny how you can call the database from Excel but you can't pull up the Access app). To do it right, you just add the B-API refs to your Access project in the right places... sort of depends on how you're setting things up. Anyway, no reason why it shouldn't be doable - gonna go through all that in a week or two myself.

Interesting .. thanks for your feedback. Is the Access Data Broker, as you referred to, a form that you set up in Access? How did you know the VBA programming for these events? Thanks, Jessia

Jessica, have you had any luck? This is a task I try to come back to every 3 months when I can't sleep. Some thoughts:(1) The Copperfield Approach a. dumping a query with all the relevant blp arguments to an Excel spreadsheet via DoCmd.TransferToSpreadsheet b. automation in Excel to "write" the blp function for each record in a cell adjacent to the record c. Inserting a DoEvents and a pause to let DDE link all the data d. close and save the Excel file e. back in Access, create a new link table to the saved file f. create an update query with relevant primary keys and update the "price" field with the "bloomberg price" field in the new linked table g. run the update query h. destroy the link table i. destroy the update queryThis is all speculation:(2) I think you can reference the bloomberg API through Windows and open a DDEConnection. I think the context is something like DDEConnect("winblp",TICKER,SECTOR...) I'm trying to track down the API programmer's guide at the moment.(3) I think you can also open an Excel spreadsheet in memory (not visible), load blp.xla and blph.xla and then run the function as ExcelObject.Application.Run("blp","|M!IBM EQUITY... I'm using ellipses b/c I've never gotten it to work, but the error codes seem to suggest that Access is at least reaching the function

Since I posted last, I actually had to spend some time building a little direct Bloomberg-to-Access hardware... The thing has been pretty successful, useful, and a hell of a lot faster than fiddling around with Excel. Setup is like this:1) Set up a form with the Blp activex control on there and some other stuff to monitor the connection2) I am actually feeding some parametrized queries into a table in the database from some other sources. So, I have a table which represents a queue of queries to make. Some of these queries are recurring (i.e. 'check the bond price every 10 mins') so they don't ever leave the queue. Anyway, you query the queue for bbg queries you should be making at the current moment, and then process them (see next step).3) You use the standard BLP mechanism for processing the queries. I.e. given the security ID and a list of fields you want to look at, you attach a number to the request and send it through to the activex control.4) When the control has a response to the query, it fires an event which is indexed by the request ID number. You can match that with any other info you already had when you made the call, and populate a record in a table.Believe it or not, this can be done fairly easily in VBA, and it is efficient enough that you can even build equity index monitors that are not exactly real-time but no more than 60 seconds late. I don't happen to have the need for near-real-time vol surfaces or anything like that but I have considered building such a thing. You can also put some junk on your form so that you can click and get other related info, so it's a nice framework. Overnight you can archive your daily data for future research. What's not to like about all that?!?! I think it's been going for a couple of months now, totally reliable. Anyway, what you need to know in order to do it yourself is:1) Access in general2) Event-driven stuff for the BLP controland that's about it.

I cannot believe I agonized over this. It was too easy.Ok no more Copperfield. This is not hand waving...(1) Add a reference to Bloomberg Data Type Library from VB(2) Next, create a function to go grab Bloomberg DataFunction GetBLB(secTicker As String, secTag As String, secMnemonic As String) Dim objCtl As BLP_DATA_CTRLLib.BlpData Set objCtl = New BlpData objCtl.Subscribe secTicker & " " & secTag, 1, secMnemonic, Results:=vtResults GetBLB = (vtResults(0, 0))End Function(3) Now, you need a subroutine to cycle through some records. I have a query which grabs all my public holdings and gets the 10 day moving average:(Note, you also need the DAO library for this:Sub UpdatePricing() Dim db As Database Dim recset As Recordset Dim sTick As String Dim sTag As String Dim sField As String Set db = CurrentDb Set recset = db.OpenRecordset("qry_PublicHoldings") With recset .MoveFirst Do While Not .EOF .Edit sTick = .Fields("Ticker") sTag = .Fields("EqTag") sField = .Fields("FieldTag") .Fields("CurPrice") = _ GetBLB(sTick, sTag, sField) .Update .MoveNext Loop End WithEnd Sub

It sounds pretty easy... i am going to try that vba code tomorrow. Thanks for your help! I'll let you know how it goes.Jessica

Well, if you want to understand how the Bloomberg API works, there are some very good examples in VBA, VC++ and even an example in VBA that feeds a database (which is what you want) provided by Bloomberg.Just type API <GO> under BloombergThen SDK <GO>Then choose menu #11Download then the activeX datacontrol (much easier if you know nothing about that stuff than the C API)What is really cool then is to understand how the component works on an asynchronous way.You can then fire requests and get the results when they are available without blocking your whole application.You can even get real time data directly under VB/VC interface.Hope it may help

krQuote...user queries from Access... and everybody in the office can see my db - you get the picture. Surely this is contravening the Bloomberg data licence?

surely it is indeedjust as surely as the monthly fee for individual terminals is highway robbery

The models I use are situated at the Bloomberg terminal and remain there. I think if I were to query and implement the data in a real-time intranet environment then I would need to upgrade to the data-license package, but somewhere in their API text...hold on..."... In addition, there is a facility to get historical data in tick-by-tick detail going back 50 days, and daily prices going back many years. There are three programming interfaces available: C API, DDE Server and ActiveX Controls.No matter which interface you use, you should stay aware at all times of the legal limits on distribution of API data. No Bloomberg data may be distributed, in any form, to computers that are not licensed Bloomberg workstations. Please consult your company's legal department or your Bloomberg Account Manager for more information on this."Thanks for raising the point.Ryan

Compliance with the license can be "cheaply" obtained with a $500/month low end subscription to the Bloomberg Data Licensing Service. They have a feature in the contract whereby they make it legal to slurp all the data you want off a terminal and send it anywhere.

Hi,my code isdim BlpObj as BLP_DATA_CTRLLib.BlpDataset BLP=new BlpDataTicker="INDU"Tag="Index"fields="150|Fundamentals - Balance Sheet|0| |0CC2|Equity Securities|BS_EQY_SEC|260|12|2"fýeldsarr=split(fields,"|")BlpObj.Subscribe Ticker & " " & Tag,1,fieldsarrand I have the errorRun-Time error '-2142467259(80004005)':#N/A Fldanyone knows what this error isThank you

Fishbo.I think the problem is that you have to pay approx. 50 cent per data entry.Regards.

oooopsthank you