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.