|
The workbook has two worksheets, Spread Analysis and Storage. The Storage worksheet contains the
list of Bloomberg instrument codes along with the name of the issuer’s bond. This list is referenced by a
drop-down control on the Spread Analysis worksheet. A VBA macro is attached to the control and retrieves
the real-time and historical data every time a new selection is made. By using a control the user does
not need to type the Bloomberg instrument code, which is composed of the CUSIP number for the fixed income
instrument. This feature lets the user select the instrument for analysis by the name of the issuer’s bond
rather than the error prone method of typing alphanumeric characters into a cell.
The Storage Worksheet
Fifteen months of weekly historical data is retrieved and stored in a table on the Storage worksheet in
reverse chronological order. It includes the last price, the bid yield and the ask yield. We use Excel’s
AVERAGE function to calculate the mid yield by getting the average of the bid and ask yield. We calculate the
spread from the current yield by subtracting the mid yield for each week in the time series from today’s
mid yield. We assigned the name TodayMidYield to the cell with the most current mid yield. We multiply
the results by 100 to convert the spread to basis points. The formula to calculate the spread:
The Spread Analysis Worksheet
Once the spread was calculated we used Excel’s chart wizard to chart the spread. We put the chart on the
Spread Analysis worksheet along with the real-time data for the selected fixed income instrument. A macro
is attached to the drop down box and when a bond is selected for analysis, the macro enters the Bloomberg
real-time and historical formulas into the correct ranges to update the data. Excel updates and scales the
chart automatically when the data is updated.
|
