Bloomberg BLP formulas in excel/vba

[braincat]

I have a vba macro that tries to update a cell containing



='C:\Program Files\blp\API\dde\Blp.xla'!BLP($B$1,AF4)



After the macro runs, I get #N/A in that cell. However

if I update the cell manually (or run the macro again),

the cell would contain a proper information.

Are there any issues in excel, vba and bloomberg, related

for example to timing? Is there any way around this

problem?

Thanks,

Brain.

[chiral3]

I have been having the same problem more lately.  Check to make sure that the cell is automatically updating both in excel and in bbg (sometimes the auto in the bbg drop down on the upper left unchecks).  There was one Japanese index that I was getting that I get #N/A for now.  I got on the live chat with bbg and they said it was just issues they were having and was affecting more than a few data.  Where it will really te you is if it doesn't update - like if it updates everything but the 15y tenor on the swap curve.  It is a realistic number, but 12bps off.  I have had trading affected by this lately.

[Tradenator]

It's been a while, but I vaguely recall a VBA command like blpfire or blpreset that you could put in your code.  I'll see if I can find where/how they were used for you.

[Cheng]

I had a similar problem once in a while. After recalculating the sheet once (eg by pressing ) the formulas did not change. I had to recalculate twice to get everything in order, but this always worked for me.



My 2 tiny bps.

[braincat]

Same for me -- if I run the vba script (or press shift-f9) twice,

the formulas update. The problem is that I have to do

that by hand. I made a separate vba function that runs

the script twice automatically, and that doesn't update the cells either.

Basically I cannot make the scripts run unattended.

[Dimatrix]

I'm not sure, if its the same problem, but I had a similar one. I used to have a BLP formula in cell B1 (e.g. last price), which referred to another cell, A1(e.g. underlying). Initially I wrote a macro, which put a bloomberg code for an index in A1, e.g. SPX. B1 did not update automatically, I got #N/A in B1. Than I wrote a macro, which directly connects in VBA with Bloomberg and than writes the result in B1 directly, works fine.

[braincat]

This sounds exactly like the problem

I am experiencing. Would it be

possible for you to post that macro?

I have no idea how to do that in vba.

Thank you!

[Dimatrix]

no worries braincat, I'll post the Excel file tomorrow morning (European time) at work.

[Dimatrix]

Ok, here it is: [url=/User%20Files/1559/DIMATRIX_MANAGER.xls]Attached File: DIMATRIX_MANAGER.xls[/url]



Just a few words, so you don't have to work out everything from the beginning:


[list]



[*]I hope, there are no hidden commands, which are German Excel-specific. It works fine on my PC.

[*]If you push the "Update Recommendations" button, it updates all analyst recommendations for the tickers from column C in the "MAIN" sheet. The recommendations are written to the "RECOMMENDATIONS" sheet

[*]If I open the sheet the first time in the morning, it sometimes has the #NA error, if I try it the first time. Afterwards its fine. Think it is a problem with the bloomberg DDE Server connection, when I connect to it the first time. I would never try to ask the bloomberg helpdesk, why that problem occurs. Usually, they don't know more, than I do.

[*]The Bloomberg request is in the "subs" module in the "write_general_data" sub. Its a generic function which you could use for your purposes. The Bloomberg request is pretty simple and short.

[/list]

Still not sure, if its exactly the thing, you're looking for. Hope it helps.

Dimatrix

[braincat]

Hi Dimatrix,



thank you, much appreciated!

brain.