Hi,I try to find a way to get the same result than the BLPB function using VBA code.I want to get a Ticker list from an Indice. The excel formula is for example : =BLPB("SX5E INDEX","OPT CHAIN")I tried TickerLookup but no results... So is it possible to get the bulk information directly with VBA ?Thanks in advance for your help.

If I remeber correctly, it should be possible. Bloomberg has a decent help function for that has sample VBA code for various pursopses (bulk should be included). If my memory serves me right it's API <Go>, but I'm not sure. Something to do with API anyway. There you'll find what you're looking for.

Getting bulk information from Bloomberg via VBA requires a Variant array. Assuming you know the basics of VBA and how to get Bloomberg fields, here is a sample:Dim oBlp As BLP_DATA_CTRLLib.BlpDataDim ReqSecurities As VariantDim ReqFields As VariantDim vtResult As VariantSet oBlp = New BlpDataReqSecurities = Array("QQQQ Equity", "SX5E Index")' You can inspect:'Option Chain and'Option Chain With Expiration DatesReqFields = Array("OPT_CHAIN", "OPT_CHAIN_EXPIRE_DT")With oBlp .SubscriptionMode = ByRequest .Subscribe ReqSecurities, , 1, ReqFields, , , vtResultEnd With'Take a look at the array vtResult'vtResult(0,0)(n,0) has your option chain for QQQQ

Thanks a lot for the sample code, it's exactly what I'm looking for !!!Sincerely.

Ooops I thanked you too early I tried the method but I get no results.Here is my code :----------------------------------------Set oBlp = New BlpData ReqSecurities = Array("SX5E Index") ReqFields = Array("OPT_CHAIN") With oBlp .SubscriptionMode = ByRequest .Subscribe ReqSecurities, 1, ReqFields, , , Results:=vtResult .Subscribe End With If IsEmpty(vtResult) = True Then GoTo fin For i = 0 To UBound(vtResult, 1) res = vtResult(i, 0) MsgBox (res) Next i fin: Set oBlp = Nothing----------------------------------------vtResult is not empty but UBound(vtResult,1)=0 and UBound(vtResult,2)=0Normally, vtResult must contains one column (OPT_CHAIN) and several rows (Options List), so did I forget something ?

Try this and see what your results are in the Debug window:' Make certain you have a Reference to C:\blp\API\ActiveX\blpdatax.dllSub FindBloombergData() On Error GoTo Handler: Dim oBlp As BLP_DATA_CTRLLib.BlpData Dim ReqSecurities As Variant Dim ReqFields As Variant Dim vtResult As Variant Dim n As Integer Set oBlp = New BlpData ReqSecurities = Array("SX5E Index") ReqFields = Array("OPT_CHAIN") With oBlp .SubscriptionMode = ByRequest .Subscribe ReqSecurities, 3, ReqFields, , , vtResult End With Stop 'and look at the results in Local or Watch Window For n = 0 To UBound(vtResult(0, 0), 1) Debug.Print n, vtResult(0, 0)(n, 0) Next Handler: If Err.Number <> 0 Then MsgBox Err.Description, vbCritical End If Set oBlp = NothingEnd Sub

Yes !!This time it's OK !I didn't get correctly the results from vtResult and with your sample code I found where the problem was.So thank you very much for your great help !!!Sincerely,Henri.

The important thing to remember when getting bulk data from Bloomberg API is the structure of the data. To be certain, you will get a variant array. However, depending on which securities are in your securities array, and what types of analytics you have requested in your fields array, you may have a very deep tree-like Variant.In my example above, I had only one security and one field, but the result has n elements stored in a zero-based array as: vtResult(0, 0)(n, 0)In my experience, navigating this array is the more difficult task, but a little trial and error using the "Watch" window will help you locate the proper address of the element.

OK, I didn't know before your example the zero-based array, thanks again for your help and the advice to use Watch Window !

Hi,I already have some intermediate experience with regards to Excel VBA, but I still struggle with the basics when it comes to requesting Bloomberg data within Excel VBA. The Bloomberg helpdesk seemingly only knows the answer "pls refer to WAPI [GO]", while this is unfortunately far too technical as a non-full-time programmer and non-native speaker...I am looking for help with regards to a very simple problem: the macro should take a certain date and a security ticker out of a worksheet and should provide me in a different cell (only) with the closing price of that specific security on that particular date.I already found the post by Andrew (see below) and tried to modify it, however I have already a problem at the very beginning. When I start the macro, the error "user-defined..." pops up. I assume the reason is that I have to introduce the blpdata-Object first before I can use it. Is there anybody who could help me with a step-by-step "for dummies" explanation what kind of preparations have to be made in order to use the blpdata-Object?Best regardsTobiasQuoteOriginally posted by: AndrewGetting bulk information from Bloomberg via VBA requires a Variant array. Assuming you know the basics of VBA and how to get Bloomberg fields, here is a sample:Dim oBlp As BLP_DATA_CTRLLib.BlpDataDim ReqSecurities As VariantDim ReqFields As VariantDim vtResult As VariantSet oBlp = New BlpDataReqSecurities = Array("QQQQ Equity", "SX5E Index")' You can inspect:'Option Chain and'Option Chain With Expiration DatesReqFields = Array("OPT_CHAIN", "OPT_CHAIN_EXPIRE_DT")With oBlp .SubscriptionMode = ByRequest .Subscribe ReqSecurities, , 1, ReqFields, , , vtResultEnd With'Take a look at the array vtResult'vtResult(0,0)(n,0) has your option chain for QQQQ

hi this thread has helpped me lots but i have a further issue in that i need to provide override fields and values?? how do i do that efficently for lots of records.I have a handful of Corprtate ISIN's but for each one i have to retrieve hundreds of rows with different settings for the overrides??i am able to get the data row by row but that seems ineffient and is very slow. Am i able to provide all the data at once for all of the override combinations for a particular ISIN.hope that makes sense!jj

Hi I hope someone can help me i am trying to find the most effiecient way of populating the attached screenshot of an excel spreadsheet with Bloomberg data using the subscribe method of the ActiveX control.basically i want to submit the data in column A as the fields and the data in cells B3:J3 as the fields and then the data in L3 to Q17 as the overrides.then i want to drop the results into B4:J17.Thanks in advance for any assistance on this!

tobias28,What does your VBA function look like and which line is causing the error?Some things to remember, (a) you must run your functions and workbooks on a machine that has Bloomberg installed, (b) you need a reference to the Bloomberg data object, as in Dim oBlp As BLP_DATA_CTRLLib.BlpData, and (c) requests are written to a Variant array which may occupy many dimensions.

Hi, This is my first day in this forum. I'have a problem:I want to write in VBA (Acces) the following operation that I use within an Excell Cell=Blp("BEST_BRK_PERIOD_OVERRIDE","BEST EPS", ":0FY")In VBA I usually use objSnap.GetData "IBM US Equity", Array("LAST_PRICE","BEST EPS"), , , 10000, vtResultI Do not know where to put ":0Fy" in order to get the same results (in VBA) as I get using the BLP functionin Excell.I have tried also settingobjBloomberg.Periodicity = bbFiscalAnnually or bbCalendarAnnuallyStartDate = "01.01.05"EndDate = "01.01.07" objSnap.GetData "IBM US Equity", Array("BEST_BRK_PERIOD_OVERRIDE","BEST EPS"), , , 10000, vtResultand I do not get the value I need. I do not know If I was clear enough. Have any of you already used the method GetData (Security As Variant, Fields As Variant, [OverrideFields As Variant], [Overrides As Variant], timeoutInMilliseconds As Long, Results AsVariant) ?I would appreciate any help. I'm building an archive from Bloomberg and I can not go furtherBest regardsedgar

Hi Andrew,This is my frist day in this forum. I am familiar with basic V programming for Excel macros but I am trying run Bloomberg API fro the first time using VB. I am doing this on a machine which has Bloomberg...This is the code snippet I took from the examples in Bloomberg API documentation. However when I try to compile this in excel, it gives an error in the first line saying that I need to define a user-defined type. Could you please help?Dim WithEvents objBloomberg As BLP_DATA_CTRLLib.BlpDataSub BLPGetHistoricalDataTest() Set objBloomberg = New BlpData vtResults = objBloomberg.BLPGetHistoricalData(Array("MSFT US Equity", "Dell US Equity"), Array("PX_LAST", "PX_Volume"), Date - 30) For nDate = LBound(vtResults, 1) To UBound(vtResults, 1) MsgBox "Microsoft P/V on" & Format(vtResults(nDate, 0, 0), "Short Date") & "was" & vtResults(nDate, 1, 1) & "/" & vtResults(nDate, 1, 2) Next nDateEnd Sub