Article

Combining A FINCAD User Data Workbook With a Bloomberg Finance LP*-enabled Swap Curve Workbook

This useful tip describes how to combine a FINCAD (User Data) workbook with a Bloomberg Finance LP-enabled swap curve workbook. This allows users to price an instrument based on an interest rate curve that is built from real-time market data pulled from a Bloomberg Finance LP terminal. In version 11, FINCAD has restructured its workbook solutions, making this process easier.

» Click to evaluate the lastest trial version of FINCAD Analytics.

This article shows examples of a Bloomberg Finance LP-enabled workbook with a single swap curve, and with two swap curves.

Bloomberg Finance LP-Enabled Workbooks with a Single Swap Curve.

  1. Open a FINCAD workbook that has a swap curve (for example, FINCAD XL → Workbooks (User data) → Credit Derivatives (CDS & Options) → CDS (Single Asset)).
  2. Press Alt + F11 to activate the "Visual Basic Editor" and unhide the FC_Switches_Curve worksheet as shown in Figure 1.

Figure 1

  1. Select the FC_Switches_Curve worksheet in the "Project Explorer".
    Note: If the "Project Explorer" is not visible, go to View → Project Explorer or press Ctrl + R
  2. Navigate to the "Properties Window" and select the area next to "Visible".
  3. Select -1 - xlSheetVisible from the dropdown list.
  4. Press Alt + F11 to go back to Excel and the FC_Switches_Curve worksheet is visible.
    Note: If the "Properties Window" is not visible, go to View → Properties Window or press F4.

For Excel 2007 users, the Visual Basic button is located in the Developer ribbon. If the ribbon is not accessible, go to Office icon → Excel Options → Popular, select Show Developer Tab in the Ribbon, and click OK.

3. Remove the following worksheets from the current workbook: Curve, Holidays, Graph, and FC_Switches_Curve as shown In Figure 2 below.

Figure 2

a) To select multiple worksheets, click a worksheet tab while holding down the Ctrl key.
b) Right click a worksheet tab and select Delete. This will cause some existing named ranges in the workbook to become invalid.

4. Insert a subroutine into the module of the current workbook to remove the invalid named ranges as in Figure 3 below.

Figure 3

a) Press Alt + F11 to activate the "Visual Basic Editor". Right click the current workbook in the "Project Explorer". Go to Insert → Module.
b) Add the following subroutine in the module by copying and pasting the code below.

Sub Remove_Names()

Dim n As Name

For Each n In ActiveWorkbook.Names

If InStr(n.RefersTo, "#REF") <> 0 Then

n.Delete

End If

Next

End Sub

Note: This subroutine removes all invalid named ranges after the curve worksheets are deleted.
c) As shown in Figure 4 below, press Alt + F11 to go back to Excel and run the macro. Go to Tools → Macro → Macros or press Alt + F8. Select Remove_Names in the list of macros. Click Run.

Figure 4

Note: This has cleaned up the invalid named ranges caused by the deletion of worksheets.

5. Combine the current workbook with the Bloomberg Finance LP-enabled swap curve workbook.

a) Go to FINCAD XL → Workbooks (Bloomberg Finance LP Data) → Analysis Tools → Swap Curve (BLP®).
b) Press Alt + F11 to activate the "Visual Basic Editor" and unhide the FC_Switches_Curve worksheet.
c) Select all worksheets in the swap curve workbook and move them to the destination workbook. As shown in Figure 5 below, right click a worksheet tab. Choose Select All Sheets.

Figure 5


d) As in Figure 6 below, right click a worksheet tab. Select Move or Copy. Choose the destination workbook in the To book dropdown list. Select (move to end) in the Before sheet area. Leave the Create a copy box blank. Click OK.

Figure 6

e) Click Yes when Excel prompts about named ranges that already exist in the destination workbook as shown in Figure 7 below. This will use the name as defined in the destination workbook. Click Yes for all similar warnings.

Figure 7

Note: All worksheets in the curve workbook move to the destination workbook and the file will close automatically.

For users with Excel 2007, run the macro Update_Links. This will ensure that all macro buttons in the curve worksheets have the correct links.

6. Set the value date of the curve to be the same as the workbook. As shown in Figure 8 below, go to the Curve worksheet and enter the formula =value_date into cell C3.

Figure 8


7. Hide the FC_Switches_Curve worksheet through the "Visual Basic Editor".
The workbook is ready to use. Users can save the workbook.

Bloomberg Finance LP-Enabled Workbooks with Two Swap Curves

1. To Bloomberg Finance LP-enable a workbook with two swap curves, follow steps 1 to 5 from the previous section with the following exceptions:

a) In step 3, delete the two worksheets containing the swap curves, the Holidays worksheet, and the FC_Switches_Curve worksheet.
b) In step 5, the corresponding Bloomberg Finance LP-enabled swap curve workbook is called Swap Curve (two curves) (BLP®).

2. Set the value date of the domestic curve to be the same as the workbook. Go to the Curve - Domestic worksheet and enter the formula =value_date into cell C3.

Note: The domestic curve uses the named range df_curve.

3. Set the value date of the foreign curve to be the same as the workbook. Go to the Curve - Foreign worksheet and enter the formula =value_date into cell C3.
4. Define the named range df_curve2 for the foreign curve.

a) Go to Insert → Name → Define in Excel 2003 or go to Formulas→ Define Name → Define Name in Excel 2007.
b) In the Define Names box, enter df_curve2 in the Names in workbook text box as shown in Figure 9.
c) Enter =df_curve_f and click OK.

Figure 9

Note: The foreign curve uses a named range df_curve_f while the current workbook uses df_curve2. Since the original df_curve2 was removed when we removed the invalid named ranges (caused by the deletion of the worksheets), we would like to re-add the df_curve2 named range back to the workbook; and point it to df_curve_f.

Optional If the original workbook uses the named ranges holidays_2 and holidays2_2, we would like to re-add these two named ranges with formulas =holidays_f and =holidays2_f respectively. (Similar to the previous step)

5. Hide the FC_Switches_Curve worksheet through the "Visual Basic Editor".
The workbook is ready to use. Users can save the workbook.

Custom workbook solutions, co-development of analytics and advisory services are now offered through FINCAD Professional Services.

Disclaimer

Your use of the information in this article is at your own risk. The information in this article is provided on an "as is" basis and without any representation, obligation, or warranty from FINCAD of any kind, whether express or implied. We hope that such information will assist you, but it should not be used or relied upon as a substitute for your own independent research.

For more information or a customized demonstration of the software, contact a FINCAD Representative.

 

* Bloomberg is a trademark of Bloomberg Finance LP. FINCAD is not associated in any way with Bloomberg Finance LP.