Bloomberg Excel API - Question

Water Cooler
#1

Can anyone help a complete noob on bloomberg’s excel plug in.

For example:

I have the formula =BDP(“SPX INDEX”,“PX_LAST”) which returns last close price for SP500.

How can i manipulate/rewrite this formula so that I can link it to cells with specific dates, and the formula returns the closing price at that specific date?

#2

Wouldn’t you just import historical prices? In my excel i can go under the Bloomberg tab > import data > realtime/historical > historical end of day > SPX Index > Mkt Activity > Last Price > Enter Fixed Time Series.

#3

I dont want a time series though.

For example, i want cell B1 to show the price dependent on the inputs (Security/Date) in cells A1 and A2.

So i can change A1 and A2 and it automatically updates cell B1!

Thoughts?

#4

=BDH(“SPX Index”, “PX LAST”, 10/22/2013, 10/22/2013) will return one cell with the closing price on 10/22/2013. You just put the start and end dates as the same date in BDH.

#5

just for spx or you want to be able to type in security id and pull a price for anything?

edit: nvm Ohai got it

#6

^ You can also replace the hard coded dates with cell references to a date.

#7

you can also reference a bloomberg security id (e.g SPX Index or AAPL Equity) if you wan to pull prices on a date for multiple securities

#8

Thanks guys - got it all sorted out!!

#9

FYI, i ended up using:

=BDH(B8,A8,B2,B2,“Dir=V”,“Dts=H”,“Sort=A”,“Quote=C”,“QtTyp=Y”,“Days=A”,“Per=cd”,“DtFmt=D”,“Fill=P”,“UseDPDF=Y”)

Where:

B8 = Stock Identifier

A8 = PX_LAST

B2 = Date

This may be more complicated than necessary (See Ohai’s post - i couldnt get this version to work) but seems to work!