Bloomberg Excel API documentation question

I’m trying to rewrite a spreadsheet so that it will load up quotes off of a bloomberg machine. In my own work, I use something else, but now a client wants me to rewrite it so that he can use it with his system.

However, I don’t have a machine or subscription myself… too expensive. I’m pretty sure that the syntax to rewrite it going to be easy, because I don’t really need anything particularly complex or esoteric… basically, all I need are closing prices on particular days. For eample

Col A | Col B

Date | Close SPY

4 jan 10 | 104.02

11 jan 10 | 105.36

18 jan 10 | 102.71

And I want to make it so that if I change a date (say I cange 11 jan 10 to 9 jan 10), it will update the appropriate row (it’s ok if you have to hit recalculate now/F9).

Everything I’ve seen says that the bloomberg wizard in excel will fill the columns for you, but I don’t want that. I want each cell in column B to look at the date in column A and take its cue from that (because I’ll be changing those dates at times, mostly just moving them forward or back a week).

So my specific question. I think this might work in cell B2. Would that do it?

=BLPH(“SPY Equity”, “LAST_PX”, A2, A2)

More general questions:

Why is it so difficult to find a PDF or something that actually tells you the Excel API. There are a few things I’ve found that give you a smattering of bits of code, and that’s how I’ve pieced together the above line so far.

In particular, I’d like to know what other options other than “LAST_PX” there are that I can send with the BLPH code. Isn’t there a list somewhere of even the most commonly used alternatives. I might want to use a return figure instead of a price figure, but I can’t tell what is available.

One thing I like about downloading Yahoo financial data is that they have back-adjusted split and dividend adjusted prices, so I can compute total return figures. I assume that Bloomberg has a similar and better capability, but I don’t see any references to it.

im not sure i followed the table part, but if you want to just be able to change the date in a cell and get an updated bloomberg quote this would be a syntax, with C1 referring to the date: =BDH(“SPY US Equity”,“Px_Last”, C1,C1,“CDR=#A”) i put an option in the end to adjust for a trade date calendar (no #N/A N/A for weekends)

Thanks. That helps a lot. I’d love to find a reference source that describes the difference between BDH, BLP, BLPH, etc,. and what the available mnemonic codes are so I don’t have to come here for every little permutation I might need. Is there something free or inexpensive available that doesn’t require that I have a BBG subscription?

bchadwick, there is such a reference available to BB subscribers. I can send this to you this evening if you provide an email address. It’s not a universal guide, since BB assumes that you can use functions like FLDS to find information that is specifically relevant to what you’re doing. However, it does have a few tricks that you might find helpful. Some of the formulas are just old/new versions of the same thing, btw. For instance, BDP() is the new version of BLP(), but both should work. Anyway, let me know if you want this. The file is about 7 mb.

Thanks, ohai, that would be awesome. My email is brucebiz_wi at the yeah who place. Thanks, also, cookthebooks. Your post should do the trick for me, and ohai’s should help me with any additional things that come up on this issue. The AF club rocks again!

hey ohai,

I would appreciate it if you can also send me this guide, if you don’t mind.

My email: aly dot somji at gmail

thanks in advance

i can haz reference document? angel

heart PLEASE heart

rayankh AT gmail

see if this helps.

or this

developer’s guide