Willow Tips

Tip #2002-5: Some Tips and Tricks

Creating Dynamic Range Names

We created a dynamic range name to store the list of corporate bonds. What is unique about a dynamic range name is that it automatically increases in size as the number of items in the range increases. In this case you can keep adding bonds to the list you want to analyze and as new bonds are added the list will expand automatically to display the newly added bonds. There is no need to resize the range if you need to add more bonds. Dynamic ranges are created using Excel COUNTA and OFFSET functions to define the named range. The COUNTA function counts the items on the list and is used as the row argument in the OFFSET function to define the range of cells for the name.

Excel’s offset function has 3 arguments. A starting cell or a name, a row argument and column argument. The dynamic range name which contains the list of corporate bonds is called corpnames. The range of cells which contain the list is defined by using two OFFSET functions rather than an absolute cell range. The ending cell of the range is determined by the row argument in the second OFFSET function. In this case it is Excel’s COUNTA function. The COUNT function is used to count numeric values, the COUNTA function is used to count strings or alphanumeric values. In this case it counts the all the text values in the column C. If there are ten values in column C then the range for corpnames will extend 10 rows from the starting cell. If you add 10 more bond names to the list, the corpnames range will then expand to 20 rows from the starting cell. Using dynamic range names is an extremely powerful time saving technique. There is no need to write macro code to keep ranges updated as they shrink or expand. We have found dynamic ranges extremely useful when creating models which use charts to display data which is constantly being added, such as collecting tick or economic data.

Once you have created a dynamic range name to contain the list of instruments for which you want to use in your model, you just need to refer to that name when you create your drop-down control. To set the range to reference for the drop-down control, hold down the Ctrl key and right click on the control. Select Format Control… and click on the control tab. For the input range type in the dynamic range name. The Cell Link text box contains a cell address or named cell. If you select the third item on the list Excel will place a 3 in that cell.

Using Excel’s INDEX Function to Get the Bloomberg Instrument Code

We use Excel’s INDEX function along with the number in the linked cell from the drop-down to get the corresponding Bloomberg instrument code for the corporate bond we selected from the drop-list. Although all you see is the issuer name when you select a bond for analysis, in order to get the real-time and historical information from Bloomberg you need to enter the Bloomberg instrument code which is composed of the Cusip number for the instrument and the Bloomberg market category identifier, which for corporate bonds is Corp. For each name of the corporate bond on our list, we put the corresponding Bloomberg symbol code in the adjoining cell. When you select a corporate bond from the list, Excel places the number of the item you selected in the linked cell.

The INDEX function returns a value from a range. It has 3 arguments, the range, which is used to find the value, the row and the column. In our case we created another dynamic range called blbdata. It contains the instrument codes and corporate names in column B and column C. The row argument is the value in the cell linked to the drop-down selection. Since the instrument codes are in the first column, the column argument is always 1. The formula, which finds the instrument code is in cell E2, which we named symbol, on the Storage worksheet:

The value returned by the INDEX function assigned to a variable in the GetBLBPriceData macro which runs when a new bond is selected from the drop-down. The macro does nothing more than update the real-time and historical DDE formulas.