bloomberg ALLQ function to excel?

[signhere]

Hi would appreciate advice on this

what we are doing now manually is for each bond, use the ALLQ function in bloomberg to obtain all current broker quotes at a particular point in time. Can we somehow use an excel function/vba to obtain the ALLQ info (in particular Firm name, Bid Price, Time) and export them to excel automatically?

have tried using blp in excel but have trouble finding the correct field name

great if anyone can help cheers

 

[GammaJammer]

I don't know a better way than BLP personally. Reuters is better for dragging to excel - you can see the fields etc a lot more easily. But having said that I do usually manage to find the fields in the end. Persevere (and if you really want to feel pain, call the helpdesk).

GJ

 

[Martinghoul]

Best thing to do is to ask BBG direct... Personally, I don't know of a way to do it myself, not to get the quotes anyways.

 

[A Dashing Blade]

On the ALLQ page, you'll see the broker mnemonics, UBSX, ING etc etc

Isin@UBSX will get you access to that brokers data

so, in pure spreaddy formula terms . . .

=BDP(Isin@Broker, "BID") . . .(DON'T use BLP, that's old DDE technology, BDP is much better -although far from perfect for hard-core excel stuff)


In vba, you have to asynchronously subscribe to the Isin@Broker ticker for each field you wanna retrieve(BID, ASK, BID_SIZE etc)

You get a data event fired off when a data packet is received (ie a single Isin@Broker / Field) which you can then process

WAPI <GO> will give you all the code samples you need

wrt all possible BBG fields (including the undocumented ones), import the .tbl files in your BBG directory (eg C\prog files\blp\API\*.tbl )into Excel.

 

[GammaJammer]

try FLDS <GO> as well - that's quite helpful imho. Just found.....

 

[Martinghoul]

Sweeet, guys, this is goodly knowledge here! Thanks!

 

[GammaJammer]

See - it's not all trolling and flaming, you @*(()*&*(^*%&%*

 

[Martinghoul]

Never did I suggest otherwise, you flaming troll GJ !

 

[GammaJammer]

I reckon, as a hit ratio, if I get one thread like this a month (i.e. where I actually learn something) it's worth it for a free website.

 

[signhere]

Thank you all for the responses which make me feel this is gonna work out at the end

A Dashing Blade, I've been trying the function and sorry if this is a silly question but am i meant to put in literally ISIN@broker? say for this bond First Gen Corporation CB 2.5% 11 Feb 2013 whose ISIN is XS0341201373 and broker EXA, I put in =BDP("XS0341201373@EXA","BID"), it shows the error
#N/A Invalid Security, is this the rite syntax?

again appreciate any help meanwhile i'll keep trying

 

[GammaJammer]

doesn't look like it to me (although can't remember off the top of my head what the various errore messages mean precisely - there are a few).

One way to check is to get something you know would work and get that updating in realtime, then play around with the ticker and see what error message you get. Crude, but effective.

 

[Martinghoul]

The syntax to use is this:
=BDP("XS0341201373@EXA Corp","BID")

 

[GammaJammer]

doh - yeah instrument type usually helps.....

 

[Martinghoul]

But muchas gracias to Blade for pointing out that this is possible... I am but his humble disciple.

 

[A Dashing Blade]

Apols, just got in . . .
Martinghoul's syntax is obviously correct

BID_SIZE_TDY
BID
ASK
ASK_SIZE_TDY
TIME

are the relevent fields to use,

Note that TIME is documented as "The time of the most recent update to the "Last Price" field if the today's session is opened. The date of the most recent update if it happened during previous session. The time is linked to the Terminals default set on TZDF<GO>."
ie it doesn't tell you what (if anything) has changed, merely that the Firm has reprinted its prices.

 

[A Dashing Blade]

Taking this concept a step further, if you do this programatically accross a number of brokers (typically I would only use those who are autoex enabled) , it is certainly possible to produce a "touch price" screen (ie showing best bid/ask price/yield/broker/size) for a bond portfolio iyswim.

 

[signhere]

thank you thank you problem solved!!!
have learned heaps my hats off to you guys

 

[GammaJammer]

Nice to see a thread that does exactly what it says on the tin.....

 

[A Dashing Blade]

Nice to see a thread that does exactly what it says on the tin.....

all my spreaddys are called "Ronseal" (Serious!)

One thing you have to bear in mind if pulling in a lot of data this way is that there's a limit of 0.25m data packets (ie individual pieces of bbg data) that you can pull in per day, after that, your bbg terminal will be blocked out untill midnight (they allow you two resets per month if you ask nicely).

eg pulling in this data accross several brokers and a couple of thousand bonds will lock you out of bbg for the rest of the day by lunchtime

 

[GammaJammer]

wow that would suck. Although to be fair I'd get some peace and quiet if my bloomie went down......