|
The Bloomberg Excel Add-in Version 3 (October 7, 2002 Build 23) allows you to access the RTD function
in Excel XP. You can use the Table Wizard
to enter the formula in the cells of a table. When the RTD function is entered this way it uses the text value in the instrument and
field cells. We found it easier to type the formula shown below directly in the cell. Then enter an absolute column relative row
reference for the cell containing the instrument code and to enter an absolute row reference relative column reference for the field
as shown below:
| =RTD("BLOOMBERG.RTD",,$B9,D$8) |
Once this was done we copy-filled the cells across and down.
The Bloomberg BLPSH Function
We have discussed the Bloomberg BLPH function which is used to retrieve historical data in previous Willow Tips. However if you
just need one entry from a time series Bloomberg advises using the BLPSH function. Like the RTD function it takes cell references
as arguments, which eliminates the need to run macro code if you change the instrument or the date. We used the History wizard to
retrieve on date and then edited the formula to reference the cell containing the instrument code and the cell which contained our
user-defined function which calculates the last business day for the previous quarter. ( A detailed explanation of this function is
provided in Willow Tips 2002-10. The BLPSH formula is shown below:
| =BLPSH($B10,$D$8,PrevQDate,,,BLP|H!'DJI Index,[LAST PRICE] ST=20021231 END=20021231 PD=D') |
Since you are retrieving only one date you will notice that the arguments for the start date (ST) and end date (END) are
the same.
Once the real-time last price and the last price for the last business day for the previous quarter were retrieved all you need to do is
perform some simple calculations to get the percent changer for the quarter. Bloomberg also has a field in their Technical Analysis category
which calculates the year-to-date percent change. We used the RTD function to retrieve this field so that we can get a year-to-date
performance comparison as well as a quarter-to-date performance comparison. During the first quarter, these numbers should be very close.
Since quarter-to-date number uses the changing real-time last price in its calculation it will keep updating as prices change. However in
the second quarter these prices will begin to differ.
|
