bloomberg ALLQ function to excel?

[Martinghoul]

I don't think there are enough countries in EUR for me to run into these sorts of limitations...

 

[A Dashing Blade]

All Euro govvies pulled in with four auto-ex brokers who refresh every 15 seconds is soon gonna hit the limit . . .

 

[Martinghoul]

All Euro govvies pulled in with four auto-ex brokers who refresh every 15 seconds is soon gonna hit the limit . . .

Crap, right you are! This here multiplication thing sure is powerful! I guess I won't be doing it then and will have to remain content with simple things like TradeWeb and BondVision.

At any rate, I guess that's how BBG are able to justify their hefty data pipe fees (they were hefty when we were looking at them a couple of years ago, if memory serves)...

 

[GammaJammer]

Can't one just turn off the realtime update and pull prices in when needed? Or throttle it back severely? Usually you don't need em every 15 secs right?

 

[A Dashing Blade]

Working on the throttle parameter as we speak, the BDP documentation in Excel's help suggests it's possible but can't get it to work atm.

You can use excel's inbuilt =RTD() to pull it in (advantage is that it can be input as a formula array, disadvantage is you can't override) which deffo is throttleable via vba at least

syntax for RTD is . . .
=RTD("BLOOMBERG.RTD","",Ticker,Field Name) Ticker = "Isin@XYZ Corp"

as a formula array . . . .
={RTD("BLOOMBERG.RTD","",D928,E$8:L$8)}

(apols, obv "9:" = smiley in xml speak!)

Note that at the end of the day, just as BLP() (& PPPro/Adfin/Gissing etc) were wrappers around DDE, so BDP() is a wrapper around RTD() functionality

 

[A Dashing Blade]

hmmm . . .
Doesn't look like you can throttle via a formula. But this bit of code does work ( ThrottleInterval = time in milliseconds) . . .

Sub SetRTDFrequency()

Dim BBG As RTD

Set BBG = Application.RTD
BBG.ThrottleInterval = 100000

End Sub

 

[A Dashing Blade]

Take 2 . . . resetting the RTD.ThrottleInterval persists into a new instance of excel (must write to the registry I guess). Also, setting the interval to 60000ms ie 1 minute means that the first time updates arn't gonna appear for a minute ie you'll have loads of #N/A's.

So set to very quick indeed (say 1 sec) in an Autpen macro which calls a second sub to fire off in a minute or so to the desired throttle interval ie . . .

Option Explicit

Private Sub Autpen()

Dim BBG As RTD

Set BBG = Application.RTD
BBG.ThrottleInterval = 1000 '1 sec interval for the first minute

Call Application.OnTime(Now + TimeValue("00:01:00"), "ResetRTDFrequency")

End Sub
'
'==================================
'
Public Sub ResetRTDFrequency()

Dim BBG As RTD

Set BBG = Application.RTD
BBG.ThrottleInterval = 60000 '1 minute updates

End Sub
'
'==================================
'

 

[GammaJammer]

Or you could fire it up in the morning already set to 1 min, and just grab a coffee while it updates...... (I'm kinda old skool - prefer the unsophisticated approach - more robust).

 

[signhere]

wow i'm glad the discussion is still going on

i'm more than happy now, have incrop into my vba there is a small point thou as there are hundreds of brokers out there is there a way to list out all brokers who've supplied quotes without needing to know who they are and their mnemonic?

 

[A Dashing Blade]

Apart from the ALLQ screen, not to my knowledge (tho' have to say never looked into it).

Typically however, you'll only be interested in a subset of the available brokers that appear on the ALLQ screen as these are gonna be the active ones and you should find the same set of brokers are gonna be the active ones in any particular sector.

 

[GammaJammer]

Bloomberg may be able to mail you a file.....

 

[Kevvo]

Formula to re-price bonds

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

Hello All,

Hoping you can advise on my automation project.

Part of my work is to re-price bonds using the average of the bid/ask yields.

Currently my spreadsheet is setup only to extract bid/ask yields for a particular bond using a formula similar to above - =BDP(V23,$W$22) - where V23 is ISIN@XXXX govt and W22 is YLD_CNV_BID and YLD_CNV_ASK

Rest of the steps are all manual. To summarize, this is what i need to perform manually to re-price a bond.
Use the mid yield from above and go to BBG / ID ISIN / paste new yield / GO / copy dirty price from screen and paste in my spreadsheet.
My spreadsheet then has formulas setup in other fields to reprice using this price and calculate exposure.

Can i somehow automate this step in Excel?

Any input is much appreciated.

Thanks
Cav