Adding Currencies to your Bloomberg Finance LP*-Enabled Workbooks in FINCAD® XL

For FINCAD XL workbook solutions that can pull market data from your Bloomberg Finance LP terminal to quickly build zero curves with LIBOR, swap and futures rates as well as swaption rates used for calibration, click to evaluate the lastest trial version of FINCAD Analytics.

Introduction

FINCAD XL v9.1 offers over 150 professionally designed and ready-to-use workbook solutions. Amongst these workbook solutions, 10 templates are Bloomberg Finance LP-enabled and provide users with the capability to pull market data to quickly build an appropriate interest rate curve. Also in the case of our Bloomberg Finance LP-enabled calibration workbook, the user can pull swaption volatilities data to automate the calibration process. These workbooks fall either into the Analysis Tools or Portfolios solutions categories. See below for their descriptions:

Bloomberg Finance LP-enabled FINCAD Workbook solutions:

Workbook Names
Solutions
Analysis Tools
Implied Forward Rates (BLP(R))Calculate a single or a table of forward rates by inputting data into a zero curve worksheet. Uses Bloomberg Finance LP data feed for building zero curve with LIBOR, swap and futures rates.
Par Swap Rate Analyzer(BLP(R))Calculate a table of par swap rates by inputting data into a zero curve worksheet. Uses Bloomberg Finance LP data feed for building zero curve with LIBOR, swap and futures rates.
Swap Curve (BLP(R))Calculate an integrated discount factor and spot rate curve using cash rates, future or FRA rates, and par swap rates. Template allows for extending, smoothing or adding a spread. Uses Bloomberg Finance LP data feed for building zero curve with LIBOR, swap and futures rates.
Calibration (BK & HW - using Swaptions) (BLP(R))Calculate the rate volatility and mean reversion constant for the Hull-White or Black-Karasinki model given swaption data. Uses Bloomberg Finance LP data feed for building zero curve with LIBOR & swap rates as well as feed for swaption rates used for calibration.
Portfolios
Vanilla Interest Rate Swap Portfolio (BLP(R))Calculate the fair value of an individual swap or a swap portfolio. Allows addition of a swap being valued, to a created portfolio. Analyze each individual swap in the portfolio on a swap analysis sheet. Uses Bloomberg Finance LP data feed for building zero curve with LIBOR, swap and futures rates.
Swaption Portfolio (BLP(R))Calculate the fair value and delta of invidual swaptions and the complete portfolio. Uses Bloomberg Finance LP data feed for building zero curve with LIBOR, swap and futures rates.
CDS (Single Asset) Portfolio (BLP(R))Calculate the fair value and various risk statistics for a portfolio of single asset credit default swaps. A dashflow table calculating the expected premium cashflows in included. Uses Bloomberg Finance LP data feed for building zero curve with LIBOR, swap and futures rates.
Cap or Floor Portfolio (BLP(R))Calculate the fair value of an individual cap/floor or a portfolio. Other individual outputs include delta, gamma, theta, vega and intrinsic value.Uses Bloomberg Finance LP data feed for building zero curve with LIBOR, swap and futures rates.
Bond Portfolio (BLP(R))Calculate the clean price, accrued interest, duration, spread, yield of individual Bonds and the complete portfolio. Uses Bloomberg Finance LP data feed for building zero curve with LIBOR, swap and futures rates.

Customizing your FINCAD XL Bloomberg Finance LP-Enabled Workbooks

Although these workbooks can be customized in many different ways, the focus of this article is to customize FINCAD's Bloomberg Finance LP-enabled workbooks to accommodate for different currencies. Currently our Bloomberg Finance LP market data coverage for curve building includes LIBOR, swap and futures rates for the following currencies: AUD, CAD, EUR, GBP, JPY, USD, CHF and NZD, while the coverage for pulling swaption volatilities include the following currencies: AUD, EUR, GBP and USD. The following two sections will describe the steps for adding additional currencies and their respective instruments data feed for a) zero curve building and b) calibration using swaption volatilities.

A. Adding currencies to pull additional data for building a zero curve:

Here are the 8 steps required to modify the Bloomberg Finance LP templates in order to add currencies to the curve tab:

1. Find the new currency's Bloomberg Finance LP codes (different tenors) for the instruments you want to use to build your curve (i.e. LIBOR rates, par swap rate and futures: BBAM, IRSB and EDSF).

2. Extend the instruments rate code table (BLP Link tab; M4:T19 for LIBOR, M26:T44 for Swap rates, and M52:T92 for futures). Enter the codes found in step#1 into this expanded table. Also, remember to extend the frequency, accrual, and date adjustment drop down menus at the bottom of each currency column.

3. Press Alt+F11 and make the "FC switches" sheet visible (this sheet is 'very hidden' and requires un-hiding through Excel VBA).

4. On the FC switches tab, add the currencies abbreviation (i.e. USD) and the corresponding first two letters of the security ID that Bloomberg Finance LP uses (see grey area).

5. Go to Insert → Name→ define and add/modify the following name ranges:

  1. If you plan on using futures: add Comdty_fut_(currency abbreviation) and refer to the futures code that you added to the futures table on the BLP links tab ( Step#2).
  2. Add range_libor_ (currency abbreviation) and refer to the Libor codes you added to the deposit table on the BLP links tab ( Step#2).
  3. Add range_swap_(currency abbreviation) and refer to the swap codes you added to the futures table on the BLP links tab ( Step#2).
  4. Extend the reference to "sw_currency_list" to include the currency abbreviation that you entered in step #4
  5. Extend the reference to "table_currency" " to include the currency abbreviation and its Bloomberg Finance LP code that you entered in step #4
  6. Extend the reference to "table_futures_settings" to include the new currencies table created in step #2
  7. Extend the reference to "table_LIBOR_settings" to include the new currencies table created in step #2
  8. Extend the reference to "table_swaps_settings" to include the new currencies table created in step #2

6. Adjust the "accrual", "date adjustment" and "rate basis fields" at the bottom of the Libor rate codes according to the Bloomberg Finance LP description for the instrument in the additional currency.

7. Adjust the "frequency" ,"accrual", "date adjustment", "rate basis" and "settles in N market days" fields at the bottom of the par Swap rate codes according to the Bloomberg Finance LP description for the instrument in the additional currency.

8. Adjust the "frequency" ,"accrual" and "date adjustment" fields at the bottom of the Futures rate codes according to the Bloomberg Finance LP description for the instrument in the additional currency.

B. Adding currencies to pull additional swaption volatility data for Calibration ( BK and HW):

Here are the 8 steps required to modify the Calibration (BK HW - using Swaptions) (BLP®) workbook in order to add new currencies from which to pull swaptions volatilities for calibration.

1. Find the swaption codes for desired currency. In Bloomberg Finance LP you can find these code by entering WCV <GO> , select the currency and then further select Swaption Volatilities. For example, Canadian Swaption Volatility Rates codes can be found by entering WCV <GO>, selecting 39) Canadian Dollar, followed by 14)Swaption Volatility Rates ( see screenshot below). For example, with the swaption code "CDSV0A1 Curncy," the 2 first letters represent the country code ( CD = Canada) and the remaining "SV0A1 Curncy" represents the code for swaption volatility and the time tenor. ( Hint: once you have identified the currency code you can use MS Excel concatenate function to generate all the swaption volatility points for the desired currency)

2. Once you have identified the currency code and the swaption volatility rate codes for every periods described in column H on the BLP Swpn Links tab, set up an new column in the swaption code table where you can specified the swaption volatility codes.

3. Add the new currency to the available menu by opening the FC Switches tab (Press Alt+F11 and make the "FC switches" sheet visible -this sheet is 'very hidden' and requires un-hiding through Excel VBA-). On the FC switches tab, add the currencies abbreviation (i.e. CAD) and the corresponding first two letters of the security ID that Bloomberg Finance LP uses (CD) in the grey area (L2:M6) below sw_swpn_currency.

4. Expand the Swaps Data Table in cells X5:AB8 on the BLP Swpn Links tab, with currency anchor #, number, Accrual Method, Frequency, Bus day convention. When adding currencies to this table, make sure you copy the exact name for the various entries (accrual method, frequency, bus day convention). The best approach is to copy it directly from the switch definition on the 'FC Switches' page. The reason why it is important to get the exact name is that other formulas are directly matching the name to the position in the position. IF the name is spelled wrong a solution will not be found.

5. Go to Insert → Name→ define and add/modify the following name ranges:

  1. Extend the reference to "sw_swpn_currency" to include the new currency and code set up in step #3 above.
  2. Extend the reference to "curr_tables" to include the added fields created in step #4 above.
  3. Add the following name range : "rates_ (currency id )" (i.e. rates_CAD) and reference the table created in step #2 (i.e. 'BLP Swpn Links'!$F$5:$F$200)

6. Modify the formulas in column I and J to add an extra if statement following the previous If statements such as: IF(currency_anchor=3,E5,F5)

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.

Request a Free Trial