Reuters Workbook89626

Reuters PowerPlus Pro 5.
0 Workbook
Document Number 500504.2 30 July 2005
30 July 2005
Reuters Financial Software PowerPlus Pro Workbook Copyright 2005 Reuters. All Rights Reserved. Except as permitted by law, no part of this document may be reproduced or transmitted by any process or means without the prior consent of Reuters. Reuters, by publishing this document, does not guarantee that any information contained herein is and will remain accurate or that use of the information will ensure correct and faultless operation of the relevant service or equipment. Reuters, its agents, and employees shall not be held liable to or through any user for any loss or damage whatsoever resulting from reliance on the information contained herein.
Reuters and the Reuters sphere logo are registered trademarks and trademarks of the Reuters group of companies around the world. Lipper is a Reuters company.
ADFIN, KOBRA, and KONDOR are registered trademarks, and Reuters 3000 Xtra, Reuters Analytic Data System (ADS), Reuters Data Contribution Server (DCS), Reuters Data Transformation System (DTS), Reuters Dealing 20002, Reuters Dealing 3000, Reuters Enterprise Licensing System (ELS), Reuters Integrated Data Network (IDN), Reuters Intelligent Advisor (RIA), Reuters Kondor Global Limits, Reuters Kondor Trade Processing, Reuters Kondor Value at Risk, Reuters Knowledge, Reuters Market Data System (RMDS), Reuters Network Management System (NMS), Reuters News 2000, Reuters News Distribution System (NDS), Reuters Optimizing Contribution Server (OCS), Reuters Personalized Delivery System (PDS), Reuters PowerPlus Pro, Reuters Trader for Commodities (RTC), Reuters Triarch are trademarks of the Reuters group of companies around the world. Acknowledgement is made to all other brand or product names referred to in the text that are registered trademarks, trademarks, or trade names of their respective owners.
Document History
Revision by Lucinda Croft Sebastien Genet Lucinda Croft Comments Converted into MS Word for 5.0 updates by support team. Updates for 5.0 Published as a PDF. Date 15/04/2005 07/06/2005 30/07/2005
Disclaimer:
This memo is a working document, it is for informational purposes only and is subject to change as the product cycle evolves. It is to be used as internal support for Reuters staff only acting in capacity as upgrade planners, designers and engineers who need some guidelines on likely performance timelines. The tests were run on pre-RRG software releases, to provide an indication of potential upgrade performance. As with all performance related exercises results in different environments may differ substantially. It is not intended to be a performance tuning guide.
30 July 2005
Reuters Financial Software PowerPlus Pro Workbook
TABLE OF CONTENTS
TABLE OF CONTENTS .................................................................................................................... 3 1 INTRODUCTION ..................................................................................................................... 12
1.1 Using This Guide...........................................................................................................................................12 1.1.1 What This Guide Explains .......................................................................................................................12 1.1.2 Intended Audience ..................................................................................................................................12 1.1.3 Assumed Knowledge...............................................................................................................................12 1.1.4 How This Guide is Organized..................................................................................................................12 1.1.5 How to Use This Guide ...........................................................................................................................12 1.2 Conventions Used in This Guide .................................................................................................................12 1.2.1 Text .........................................................................................................................................................12 1.2.2 Terminology.............................................................................................................................................13
INTEGRATION OF REALTIME DATA: ADFIN REALTIME FUNCTIONS ........................................... 14
2.1 About Adfin Realtime Functions..................................................................................................................14 2.1.1 Features ..................................................................................................................................................14 2.1.2 Compatibility with DDE Servers...............................................................................................................14 2.1.3 Delayed RICs ..........................................................................................................................................14 2.2 RtGet() ............................................................................................................................................................14 2.2.1 Purpose ...................................................................................................................................................14 2.2.2 Arguments ...............................................................................................................................................14 2.2.3 Example Using RtGet() ...........................................................................................................................15 2.2.4 Example Using Absolute Addresses .......................................................................................................16 2.2.5 Example Using Equity RICs ....................................................................................................................16 2.2.6 Example Using Bonds .............................................................................................................................16 2.2.7 Improved RtGet() Update Mechanisms ...................................................................................................17 2.2.8 RTD.........................................................................................................................................................17 2.2.9 RTD-assisted RtGet()..............................................................................................................................17 2.2.10 Compatibility with Other Versions of Microsoft Excel and Reuters PowerPlus Pro .................................17 2.2.11 Copy/Paste..............................................................................................................................................17 2.3 RtUpdate() ......................................................................................................................................................17 2.3.1 Purpose ...................................................................................................................................................17 2.3.2 Arguments ...............................................................................................................................................18 2.3.3 Example Using RtUpdate()......................................................................................................................18 2.3.4 Frequency of Update...............................................................................................................................20 2.3.5 Doing a Snapshot Update .......................................................................................................................20 2.3.6 Retrieving the System Date Using RtUpdate...........................................................................................21 2.4 RtSeries() .......................................................................................................................................................21 2.4.1 Purpose ...................................................................................................................................................21 2.4.2 Arguments ...............................................................................................................................................21 2.4.3 Example Using RtSeries() .......................................................................................................................22 2.4.4 RtMode Keywords for RtSeries().............................................................................................................23 2.5 RtChain() ........................................................................................................................................................23 2.5.1 Purpose ...................................................................................................................................................23 2.5.2 Arguments ...............................................................................................................................................23 2.5.3 Example Using RtChain() ........................................................................................................................23
30 July 2005
Reuters Financial Software PowerPlus Pro Workbook 2.5.4 RtMode Keywords for RtChain()..............................................................................................................24
2.6 RtHistory()......................................................................................................................................................24 2.6.1 Purpose ...................................................................................................................................................24 2.6.2 Arguments ...............................................................................................................................................25 2.6.3 Example Using RtHistory() ......................................................................................................................25 2.6.4 Modifying the Input Arguments................................................................................................................26 2.7 RtHistoryInfo() ...............................................................................................................................................26 2.7.1 Purpose ...................................................................................................................................................26 2.7.2 Arguments ...............................................................................................................................................26 2.7.3 Example Using RtHistoryInfo() ................................................................................................................27 2.8 RtNow() ..........................................................................................................................................................27 2.8.1 Purpose ...................................................................................................................................................27 2.8.2 Arguments ...............................................................................................................................................28 2.9 RtToday() .......................................................................................................................................................28 2.9.1 Purpose ...................................................................................................................................................28 2.9.2 Arguments ...............................................................................................................................................28
3 REUTERS REAL TIME ASSISTANTS, DDE CONVERTER, DIAGNOSTIC VIEWER, AND REAL TIME SETTINGS ................................................................................................................................... 29
3.1 Real Time Data Assistants ...........................................................................................................................29 3.1.1 Purpose ...................................................................................................................................................29 3.2 Real-Time Quotes Assistant.........................................................................................................................29 3.2.1 Purpose ...................................................................................................................................................29 3.2.2 Description of Options .............................................................................................................................32 3.3 Using the Real Time Chain Assistant ..........................................................................................................33 3.3.1 Purpose ...................................................................................................................................................33 3.4 Using the Real Time Series Assistant .........................................................................................................37 3.4.1 Purpose ...................................................................................................................................................37 3.5 Using the Real Time History Assistant........................................................................................................41 3.5.1 Purpose ...................................................................................................................................................41 3.6 Reuters DDE Converter ................................................................................................................................42 3.6.1 About the DDE Converter........................................................................................................................43 3.6.2 Open Reuters DDE Converter.................................................................................................................43 3.6.3 Reuters DDE Converter Settings.............................................................................................................43 3.6.4 Modify the Configuration File...................................................................................................................44 3.6.5 Conversion Example ...............................................................................................................................44 3.6.6 Reuters DDE Field Mapping Settings......................................................................................................45 3.7 Diagnostic Viewer .........................................................................................................................................45 3.7.1 Purpose ...................................................................................................................................................45 3.7.2 Illustration ................................................................................................................................................45 3.7.3 Tabs ........................................................................................................................................................45 3.8 Adfin Real Time Settings Dialog Box ..........................................................................................................46 3.8.1 Open Realtime Settings ..........................................................................................................................46 3.8.2 Setup.......................................................................................................................................................46 3.8.3 Aliases.....................................................................................................................................................46
30 July 2005
Reuters Financial Software PowerPlus Pro Workbook 3.8.4 3.8.5 3.8.6 3.8.7 3.8.8 Settings ...................................................................................................................................................47 Description of Options .............................................................................................................................47 Microsoft Excel RTD................................................................................................................................48 Watch List................................................................................................................................................48 Use the Watch List ..................................................................................................................................49
REUTERS INTEGRATION OF REUTERS 3000 DATABASES........................................................ 50
4.1 Logging in to RDB.........................................................................................................................................50 4.1.1 Purpose ...................................................................................................................................................50 4.1.2 Automatic Login.......................................................................................................................................50 4.1.3 Manual Login...........................................................................................................................................50 4.2 Securities 3000 Database .............................................................................................................................50 4.2.1 Description ..............................................................................................................................................50 4.2.2 Company Analysis Data ..........................................................................................................................50 4.2.3 Index Analysis Data.................................................................................................................................51 4.2.4 Country Analysis Data.............................................................................................................................51 4.3 Treasury 3000 Database ...............................................................................................................................51 4.3.1 Description ..............................................................................................................................................51 4.3.2 Bond Data ...............................................................................................................................................51 4.4 Fundamental Data Engine Settings .............................................................................................................51 4.4.1 Purpose ...................................................................................................................................................51 4.4.2 Data Engine Settings...............................................................................................................................51 4.4.3 Source Type: All Sources ........................................................................................................................52 4.4.4 Reconnect ...............................................................................................................................................52 4.4.5 Connection Status ...................................................................................................................................52 4.4.6 Cache Information ...................................................................................................................................52 4.4.7 Global Refresh ........................................................................................................................................53 4.4.8 Refresh Data ...........................................................................................................................................53 4.5 Retrieving Single Values for an Instrument from the Reuters 3000 Database: DeUpdate() ...................53 4.5.1 Introduction..............................................................................................................................................53 4.5.2 DeUpdate()..............................................................................................................................................53 4.5.3 Arguments ...............................................................................................................................................53 4.5.4 Example Using DeUpdate() with the Securities Database ......................................................................54 4.5.5 Example Using DeUpdate() with the Treasury Database ........................................................................55 4.5.6 Complete List of Available Fieldnames for Data Mapping .......................................................................56 4.5.7 Using the Securities Instrument Assistant ...............................................................................................56 4.5.8 Using the Treasury Instrument Assistant.................................................................................................58 4.6 Retrieving Historical Data from the Reuters 3000 Database: DeHistory()................................................60 4.6.1 DeHistory() ..............................................................................................................................................60 4.6.2 Arguments ...............................................................................................................................................60 4.6.3 Retrieving Historical Data from Securities Database...............................................................................61 4.6.4 Retrieving Historical Prices and Yield from the Treasury 3000 Database ...............................................62 4.6.5 Using the Security History Assistant:.......................................................................................................63 4.6.6 Access Ten Years of Daily Historical Data ..............................................................................................66 4.6.7 Using Treasury History Assistant ............................................................................................................69 4.7 Retrieving Time Series Data from the Data Backup Unit Server: DeHistory()..........................................72 4.7.1 Retrieving Time and Sales from DBU:.....................................................................................................72 4.7.2 Using the Time Series Assistant:.............................................................................................................73 4.8 Retrieving a List of Similar Information for an Instrument: DeList().........................................................76 4.8.1 DeList()....................................................................................................................................................76 4.8.2 Arguments ...............................................................................................................................................76 4.8.3 Example Using DeList() with the Securities Database ............................................................................77 4.8.4 Example Using DeList() with the Treasury Database ..............................................................................78
30 July 2005
Reuters Financial Software PowerPlus Pro Workbook 4.8.5 Using the Security Lists Assistant ...........................................................................................................78
4.9 Sending SQL Requests to the 3000 Database: DeQuery().........................................................................81 4.9.1 DeQuery()................................................................................................................................................81 4.9.2 Arguments ...............................................................................................................................................81 4.9.3 Database Structure .................................................................................................................................81 4.9.4 Example with SQL Requests...................................................................................................................82
ADFIN BONDS FUNCTIONS .................................................................................................... 83
5.1 Adfin Bonds Exercises .................................................................................................................................83 5.1.1 Purpose ...................................................................................................................................................83 5.1.2 Pre-requisite Knowledge .........................................................................................................................83 5.1.3 Before You Begin ....................................................................................................................................83 5.2 Structure Concepts .......................................................................................................................................84 5.2.1 BondStructure Argument .........................................................................................................................84 5.2.2 How to View BondStructure Styles..........................................................................................................84 5.2.3 How to Create a New Bond Structure .....................................................................................................85 5.2.4 RateStructure Argument..........................................................................................................................86 5.2.5 How to View RateModel Styles ...............................................................................................................86 5.2.6 How to Create a New Structure...............................................................................................................87 5.3 Adfin Bonds Functions.................................................................................................................................88 5.3.1 BdSettle() ................................................................................................................................................88 5.3.2 Arguments ...............................................................................................................................................88 5.3.3 Example Using BdSettle() .......................................................................................................................88 5.3.4 AdBondYield() .........................................................................................................................................89 5.3.5 Arguments ...............................................................................................................................................89 5.3.6 Example Using AdBondYield() ................................................................................................................89 5.3.7 AdBondDeriv().........................................................................................................................................90 5.3.8 Arguments ...............................................................................................................................................90 5.3.9 Example Using AdBondDeriv()................................................................................................................91 5.3.10 Accrued().................................................................................................................................................91 5.3.11 Arguments ...............................................................................................................................................92 5.3.12 Example Using Accrued()........................................................................................................................92 5.3.13 AdBondReturn() ......................................................................................................................................92 5.3.14 Arguments ...............................................................................................................................................92 5.3.15 Example Using AdBondReturn() .............................................................................................................93 5.3.16 ABondProceeds() ....................................................................................................................................94 5.3.17 Arguments ...............................................................................................................................................94 5.3.18 Example Using AdBondProceeds() .........................................................................................................95 5.3.19 CfYld() .....................................................................................................................................................96 5.3.20 Arguments ...............................................................................................................................................96 5.3.21 Example Using CfYld() ............................................................................................................................97 5.4 Floating Rates Notes.....................................................................................................................................98 5.4.1 Objective .................................................................................................................................................98 5.4.2 AdFrnPrice() ............................................................................................................................................99 5.4.3 Arguments ...............................................................................................................................................99 5.4.4 Example Using AdFrnPrice() ...................................................................................................................99 5.4.5 AdFrnCashFlows() ................................................................................................................................100 5.4.6 Arguments .............................................................................................................................................101 5.4.7 Example Using AdFrnCashFlows() .......................................................................................................101 5.4.8 AdFrnMargin() .......................................................................................................................................102 5.4.9 Arguments .............................................................................................................................................102 5.4.10 Return Value .........................................................................................................................................103 5.4.11 Example Using AdFrnMargin() ..............................................................................................................103 5.4.12 AdFrnYield() ..........................................................................................................................................105 5.4.13 Arguments .............................................................................................................................................105 5.4.14 Example using AdFrnYield()..................................................................................................................106 5.4.15 AdfrnDeriv() ...........................................................................................................................................108 5.4.16 Arguments .............................................................................................................................................108
30 July 2005
Reuters Financial Software PowerPlus Pro Workbook 5.4.17 5.4.18 Example Using AdFrnDeriv().................................................................................................................108 Australians FRNs ..................................................................................................................................110
5.5 Convertible Bonds ......................................................................................................................................110 5.5.1 Retrieving Convertible Bond Data .........................................................................................................110 5.5.2 AdConvPrice() .......................................................................................................................................112 5.5.3 Arguments .............................................................................................................................................112 5.5.4 Example Using AdConvPrice() ..............................................................................................................113 5.5.5 Example: The Cheapest to Deliver........................................................................................................114 5.5.6 BdConvFactor() Arguments...................................................................................................................117
6
6.1 6.2
REUTERS ADFIN TERMSTRUCTURE FUNCTIONS ................................................................... 119

Overview of Adfin TermStructure Functions ............................................................................................119 Zero Coupon Yield Curve: Bootstrapping Method ...................................................................................119
6.3 Calculate a Forward Yield Curve................................................................................................................122 6.3.1 Calculate the Maturity Dates of the Corresponding Forward Periods....................................................123 6.3.2 Calculate the Forward Zero Coupon Curve from the Table Previously Defined ....................................123 6.3.3 Convert the Discount Factors to Zero Coupon Rates............................................................................125 6.4 Zero Coupon Yield Curve: Vasicek-Fond Method ....................................................................................126 6.4.1 Calculate the Start Date for Each Bond.................................................................................................127 6.4.2 Calculate the Vasicek-Fong coefficients................................................................................................128 6.4.3 Generate the Zero Coupon Yield Curve from Vasicek-Fong Coefficients .............................................129 6.4.4 Calculate the Discount Factors from the Vasicek Fong Coefficients .....................................................130 6.5 Zero Coupon Yield Curve: Basis Spline Method ......................................................................................131 6.5.1 Calculate the Basis Spline Parameters .................................................................................................131 6.5.2 Generate the Zero Coupon Yield Curve from Basis Spline Parameters................................................132 6.5.3 Calculate the Discount Factors from Basis Spline Parameters .............................................................133 6.5.4 Convexity Adjustments and Turn of Year ..............................................................................................134
7
7.1
ADFIN SWAPS FUNCTIONS .................................................................................................. 135

Overview of Adfin Swaps Functions .........................................................................................................135
7.2 Interest Rates Swaps ..................................................................................................................................135 7.2.1 IrsStructure Concept .............................................................................................................................135 7.2.2 Example of IrsStructure.........................................................................................................................136 7.2.3 How to View IRS Styles.........................................................................................................................136 7.2.4 How to Create a New Structure.............................................................................................................136 7.3 Interest Rates Swaps Examples.................................................................................................................137 7.3.1 SwIrsSolve() ..........................................................................................................................................137 7.3.2 Arguments .............................................................................................................................................137 7.3.3 Example Using SwIrsSolve() .................................................................................................................137 7.3.4 SwIrsPx()...............................................................................................................................................138 7.3.5 Arguments .............................................................................................................................................139 7.3.6 Example Using SwIrsPx()......................................................................................................................139 7.3.7 SwIrsCashFlows() .................................................................................................................................140 7.3.8 Arguments .............................................................................................................................................140 7.3.9 Example Using SwIrsCashFlows() ........................................................................................................140 7.3.10 AdIrsDeriv() ...........................................................................................................................................141 7.3.11 Arguments .............................................................................................................................................141 7.3.12 Example Using AdIrsDeriv() ..................................................................................................................142
30 July 2005
Reuters Financial Software PowerPlus Pro Workbook 7.4 Overnight Indexed Swaps (OIS) .................................................................................................................144
7.5 Asset Swaps ................................................................................................................................................145 7.5.1 Definition ...............................................................................................................................................146 7.5.2 AdAssetSwapBdCashFlows()................................................................................................................146 7.5.3 AdAssetSwapBdPrice() .........................................................................................................................146 7.5.4 Function Arguments ..............................................................................................................................146 7.5.5 AdAssetSwapBdSpread()......................................................................................................................146 7.5.6 Function Arguments ..............................................................................................................................146 7.5.7 Argument Descriptions ..........................................................................................................................146 7.5.8 AdAssetSwapBdCashFlows Return Value ............................................................................................147 7.5.9 AdAssetSwapBdPrice Return Value......................................................................................................147 7.5.10 AdAssetSwapBdSpread Return Value ..................................................................................................147 7.5.11 AdAssetSwapBdCashFlows Example ...................................................................................................147 7.5.12 Adapted AdAssetSwapBdPrice and Spread Example...........................................................................148 7.5.13 AdAssetSwapGenCashFlows() .............................................................................................................149 7.5.14 AdAssetSwapGenPrice().......................................................................................................................149 7.5.15 Function Arguments ..............................................................................................................................149 7.5.16 AdAssetSwapGen Spread() ..................................................................................................................149 7.5.17 Function Arguments ..............................................................................................................................149 7.5.18 AdAssetSwapGen Function Arguments ................................................................................................149 7.5.19 AdAssetSwapGenCashFlows Return Value..........................................................................................150 7.5.20 AdAssetSwapGenPrice Return Value ...................................................................................................150 7.5.21 AdAssetSwapGenSpread Floating Leg Return Value ...........................................................................150 7.5.22 AdAssetSwapGenSpread Fixed Leg Return Value ...............................................................................151 7.5.23 Example with AdAssetSwapGenCashFlows() Function ........................................................................151 7.5.24 Adapted AdAssetSwapGenPrice Example ............................................................................................152 7.5.25 Adapted AdAssetSwapGenSpread Example ........................................................................................153 7.6 Currency Swaps ..........................................................................................................................................153 7.6.1 Purpose of the Exercise ........................................................................................................................153 7.6.2 The CsStructure Concept ......................................................................................................................153 7.7 Currency Swap Examples ..........................................................................................................................154 7.7.1 Discount Factor Curve for Currencies Exchanged (EUR/GBP) .............................................................154 7.7.2 Swap Points Between Currencies Exchanged (EURGBP) ....................................................................155 7.7.3 SwCsPx() ..............................................................................................................................................156 7.7.4 Arguments .............................................................................................................................................156 7.7.5 Example ................................................................................................................................................157 7.7.6 SwCsSolve()..........................................................................................................................................158 7.7.7 Arguments .............................................................................................................................................158 7.7.8 Example Using SwCsSolve().................................................................................................................158 7.7.9 SwCsCashFlows().................................................................................................................................159 7.7.10 Arguments .............................................................................................................................................159 7.7.11 Example Using SwCsCashFlows()........................................................................................................160 7.7.12 AdCBSToSwp() .....................................................................................................................................161 7.7.13 Arguments .............................................................................................................................................161 7.7.14 Example Using AdCBSToSwp() ............................................................................................................161
8
8.1 8.2
ADFIN CREDIT FUNCTIONS .................................................................................................. 164

Overview of Adfin Credit Functions ..........................................................................................................164
Calibrate a Default Probability Curve from a Credit Default Swap Curve and a Discount Factor Curve 164 8.2.1 Objective ...............................................................................................................................................164 8.2.2 Retrieve the CDS Spread Curve from Realtime ....................................................................................164 8.2.3 Retrieve the EUR Yield Curve from Realtime........................................................................................165 8.2.4 AdCreditStructure() ...............................................................................................................................165 8.2.5 Arguments .............................................................................................................................................166 8.2.6 Example Using AdCreditStructure() ......................................................................................................166
30 July 2005
Reuters Financial Software PowerPlus Pro Workbook 8.3 Calculate the Net Present Value (NPV) of a Credit Default Swap with the Probability Curve Risk Model 167 8.3.1 AdCdsNpv()...........................................................................................................................................167 8.3.2 Arguments .............................................................................................................................................167 8.3.3 Example Using AdCdsNpv()..................................................................................................................168
8.4 Calibrate Cox Ingersoll Ross Coefficients from a Credit Default Swap Spread Curve .........................168 8.4.1 AdCreditStructure() ...............................................................................................................................168 8.4.2 Arguments .............................................................................................................................................169 8.4.3 Example Using AdCreditStructure() ......................................................................................................169 8.5 Calculate the Spread of a Credit Default Swap from the Cox Ingersoll Ross Intensity Coefficients ...170 8.5.1 AdCdsSpread()......................................................................................................................................170 8.5.2 Arguments .............................................................................................................................................170 8.5.3 Example Using AdCdsSpread().............................................................................................................170
9
9.1 9.2
ADFIN FOREX & MONEYMARKET, AND ADFIN COMMON FUNCTIONS ..................................... 172
Overview ......................................................................................................................................................172 Calendar, Currency & Cross Currency Styles ..........................................................................................172
9.3 Adfin Forex & Money Markets and Adfin Common Functions ................................................................173 9.3.1 AdStyleAttribute() ..................................................................................................................................173 9.3.2 Arguments .............................................................................................................................................173 9.3.3 Example Using AdStyleAttribute() .........................................................................................................173 9.3.4 DfAddWD() ............................................................................................................................................174 9.3.5 Arguments .............................................................................................................................................174 9.3.6 Example Using DfAddWD() ...................................................................................................................175 9.3.7 DfAddPeriod()........................................................................................................................................175 9.3.8 Arguments .............................................................................................................................................175 9.3.9 DfListHolidays() .....................................................................................................................................176 9.3.10 Arguments .............................................................................................................................................176 9.3.11 Example Using DfListHolidays() ............................................................................................................176 9.3.12 FxCross()...............................................................................................................................................176 9.3.13 Arguments .............................................................................................................................................177 9.3.14 Example Using FxCross() .....................................................................................................................177 9.3.15 AdFxSwpToSwp() .................................................................................................................................177 9.3.16 Arguments .............................................................................................................................................177 9.3.17 Return Value .........................................................................................................................................178 9.3.18 Example Using AdFxSwpToSwp() ........................................................................................................178 9.3.19 AdFxDepToSwp()..................................................................................................................................179 9.3.20 Arguments .............................................................................................................................................179 9.3.21 Return Value .........................................................................................................................................180 9.3.22 Example Using AdFxDepToSwp().........................................................................................................180 9.3.23 AdFxSwpToDep()..................................................................................................................................182 9.3.24 Arguments .............................................................................................................................................182 9.3.25 Return Value .........................................................................................................................................183 9.3.26 Example Using AdFxSwpToDep().........................................................................................................183 9.3.27 AdInterp() ..............................................................................................................................................185 9.3.28 Arguments .............................................................................................................................................185 9.3.29 Example of Linear Interpolation Using AdInterp()..................................................................................185 9.3.30 Example of Cubic Interpolation Using AdInterp()...................................................................................187 9.3.31 Example of Linear Extrapolation Using AdInterp().................................................................................187 9.3.32 AdInterpolation()....................................................................................................................................188 9.3.33 Arguments .............................................................................................................................................188 9.3.34 Linear Interpolation Example Using AdInterpolation() ...........................................................................188 9.3.35 Perform a Cubic Interpolation with AdInterpolation() .............................................................................190 9.3.36 Linear and Cubic Interpolation on a Surface with AdInterpolation .........................................................190
10
ADFIN OPTIONS FUNCTIONS ............................................................................................ 192
30 July 2005
Reuters Financial Software PowerPlus Pro Workbook 10.1 Overview and Concepts..............................................................................................................................192 10.1.1 Overview ...............................................................................................................................................192 10.1.2 Structure Concepts................................................................................................................................192 10.2 Adfin Options Functions ............................................................................................................................195 10.2.1 OpHistVol()............................................................................................................................................195 10.2.2 Implied Volatility Calculation: OpImpliedVol() ........................................................................................196 10.2.3 OpPremium().........................................................................................................................................197 10.2.4 Greek Indicators Calculation: OpCalcDeriv().........................................................................................199 10.2.5 Pricing a Warrant...................................................................................................................................200 10.3 Adfin Exotics Functions .............................................................................................................................203 10.3.1 OpAsianPremium()................................................................................................................................203 10.3.2 OpBinaryImpliedVol() ............................................................................................................................205 10.3.3 OpBarrierPremium() ..............................................................................................................................206 10.3.4 OpBarrierDeriv() ....................................................................................................................................208 10.3.5 Pricing a Bermudan Option Using OpPremium()...................................................................................209 10.4 Adfin Interest Rate Derivatives Functions ................................................................................................211 10.4.1 Pricing Caplet Premiums With or Without Amortization.........................................................................211 10.4.2 AdCapFloorDeriv() ................................................................................................................................214 10.4.3 AdCapFloorVolSurface() .......................................................................................................................216 10.4.4 AdCapFloorBSCaplets() ........................................................................................................................218 10.4.5 AdCapFloorBSVol()...............................................................................................................................220 10.4.6 AdCapFloorBSPremium()......................................................................................................................223 10.5 Pricing a Swaption ......................................................................................................................................224 10.5.1 AdSwaptionBSDeriv()............................................................................................................................224 10.5.2 AdSwaptionBSPremium()......................................................................................................................228 10.5.3 AdSwaptionBSImpliedVol() ...................................................................................................................229
11
11.1
ADFIN EQUITIES FUNCTIONS ............................................................................................ 231

Overview of Adfin Equities Functions.......................................................................................................231
11.2 Adfin Equities Functions ............................................................................................................................231 11.2.1 AdEqDividendDiscountModel()..............................................................................................................231 11.2.2 Arguments .............................................................................................................................................231 11.2.3 Example Using AdEqDividendDiscountModel().....................................................................................232 11.2.4 AdStatRegression() ...............................................................................................................................236 11.2.5 Arguments .............................................................................................................................................236 11.2.6 Example Using AdStatRegression() ......................................................................................................236
12
12.1
COM BASED PROGRAMMING........................................................................................... 239

COM and Reuters PowerPlus Pro Functions ............................................................................................239
12.2 AdfinX Analytics..........................................................................................................................................239 12.2.1 Description ............................................................................................................................................239 12.2.2 AdfinXAnalytics Example ......................................................................................................................240 12.2.3 AdxBondModule Example in VBA .........................................................................................................240 12.2.4 AdxBondModule Example in Internet Explorer ......................................................................................242 12.3 DataEngine ActiveX (DEX)..........................................................................................................................243 12.3.1 Description ............................................................................................................................................243 12.3.2 DataEngine ActiveX Examples..............................................................................................................243 12.3.3 DataEngine ActiveX Example in VB ......................................................................................................244
30 July 2005
10
Reuters Financial Software PowerPlus Pro Workbook 12.4 AdfinX Real Time.........................................................................................................................................245 12.4.1 Description ............................................................................................................................................245 12.4.2 AdfinXRealTime examples ....................................................................................................................246 12.4.3 AdfinXRealTime example in VBA ..........................................................................................................246
13
INSERTING CHARTS IN EXCEL WITH REUTERS POWERPLUS PRO ....................................... 248
13.1 How to Insert a Chart in Excel....................................................................................................................248 13.1.1 How to Insert a Chart in Excel ...............................................................................................................248
14
14.1
ERROR MESSAGES IN REUTERS POWERPLUS PRO .......................................................... 250

General Excel Error Messages...................................................................................................................250
14.2 Real Time Error Messages .........................................................................................................................251 14.2.1 Introduction............................................................................................................................................251 14.2.2 Error Messages .....................................................................................................................................251
30 July 2005
11
1 INTRODUCTION
1.1 Using This Guide
1.1.1 What This Guide Explains
The PowerPlus Pro 5.0 Workbook describes the principal functions and tools of Reuters PowerPlus Pro. It gives you examples of how to use Adfin functions and enables you to acquire the skills you need to enable you to build models performing the analyses you want.
1.1.2
Intended Audience
The PowerPlus Pro 5.0 Workbook is intended for all users of Reuters PowerPlus Pro.
1.1.3
Assumed Knowledge
The PowerPlus Pro 5.0 Workbook assumes that readers are familiar with: Windows operating systems financial information services
1.1.4
How This Guide is Organized
The PowerPlus Pro 5.0 Workbook is organized in chapters representing the different categories of Adfin functions.
1.1.5
How to Use This Guide
First read Conventions Used in This Guide to familiarize yourself with the manner in which information is presented and the terminology used in this guide. Then read the chapters in any order depending on which category of functions you are interested in.
1.2 Conventions Used in This Guide

1.2.1
Convention italics
Text
Explanation Menu names and items, command buttons, and titles of guides.
Text in quotation marks References to chapters or sections. Messages displayed. bold words or phrases Numbered text Text Emphasizes an explanation. A series of actions that you perform in the defined order. A one-step procedure to perform. User input, directories, file names, and contents.
courier font
<courier_italics> Site-specific variables or parameters.

-> Sequence of menu items to choose.
30 July 2005
12
Reuters Financial Software PowerPlus Pro Workbook Convention Explanation The user that you must be to enter a command.
root# any_other_user$
1.2.2
Term Activate Choose Click Double-click
Terminology
What You Do Place the cursor over the item and click. Make a choice from two or more available menu items. Quickly press and release the mouse button while the cursor is over the item. Quickly press and release the mouse button twice in succession while the cursor is over the item. Press and hold the mouse button while the cursor is over the item, then move the cursor to the required position, and then release the mouse button. Type in data. Place the cursor over a row in a table (or a cell in a matrix) and quickly press and release the mouse button while the pointer is over the item. Press a key on your keyboard.
Drag and drop Enter Highlight Press
30 July 2005
13
2 INTEGRATION OF REALTIME DATA: ADFIN REALTIME FUNCTIONS

2.1 About Adfin Realtime Functions
2.1.1 Features
Adfin Real Time is a function library that greatly enhances the performance and simplifies the development of realtime Excel applications. It provides some powerful features such as dynamic cell referencing, data caching, and contribution facilities.
2.1.2
Compatibility with DDE Servers
While it is compatible with most DDE servers, Adfin Real Time can also directly interface with realtime data sources, such as Reuters P2Ps or RTIC.
2.1.3
Delayed RICs
When some requested RICs are not permissioned, it is possible to configure Reuters PowerPlus Pro to replace them with delayed RICs: When the RIC ends with "=", the delayed RIC is the RIC name followed by X. For example, the delayed RIC for "EUR=" is "EUR=X". When the last character of the RIC is different from "=", the delayed RIC is the RIC name preceded by "/". For example, the delayed RIC for "RTR.L" is "/RTR.L". See the online help for information on configuring Reuters PowerPlus Pro to use delayed data.
2.2 RtGet()
2.2.1 Purpose
Adfin Real Time provides you with the RtGet() function to retrieve realtime data from a data source. For full details see the PowerPlus Pro 5.0 Adfin Realtime User Guide.
RtGet() is a volatile function, which means that all RtGet() functions in all open workbooks are recalculated
whenever an event occurs in Microsoft Excel. Recalculation is very fast and, under normal conditions, does not hinder the overall performance of the application. However, financial functions often depend on realtime data, so a realtime data update can actually trigger the complete recalculation of the spreadsheet. In large applications, this recalculation can take several seconds and could be considered as a problem. An alternative data retrieval function is RtUpdate().
2.2.2
Arguments
RtGet() function takes four arguments: =RtGet(SourceAlias, InstrumentCode, FieldName, RtMode)

Name Description
30 July 2005
14
Reuters Financial Software PowerPlus Pro Workbook Name Description Source alias (for example: IDN) Reuters instrument code (for example: EUR=). Field name or number (for example: BID). Extended argument defining the operation.
SourceAlias InstrumentCode FieldName RtMode
2.2.3
Step 1
Example Using RtGet()

Action Open Reuters PowerPlus Pro and a new Excel spreadsheet. In cell B3, enter IDN. In cell B4, enter the EUR= RIC code. In cell C3, enter the BID fieldname. Choose cell C4, and choose Insert -> Function. Choose Adfin Real Time in the Function category and RtGet() in Function name.
2 3
Click OK. The RtGet() function dialog box appears.
Enter the appropriate cell references for the arguments: Choose cell B3 in SourceAlias. Choose cell B4 in InstrumentCode. Choose cell C3 in FieldName. Note: The RtMode argument is optional.
30 July 2005
15
Reuters Financial Software PowerPlus Pro Workbook Step 6 Action Click OK. The BID price of the Euro (EUR=) against the US dollar appears in realtime in cell C4. Note: In cell B4 simply change EUR= to GBP= and the BID price of Sterling (GBP=) appears.
2.2.4
Example Using Absolute Addresses
You can use standard Excel features with Adfin functions, for example to copy and paste the formula into a column of cells and get the BID prices of multiple currencies. To do this you must use absolute addresses in the formula for the constant arguments: Step 1 Action Modify the formula in cell C4 to use absolute addresses for the fieldname and the source alias:
=RtGet(B$3;B4;C$3)
2 3 Select the cell C4 and copy and paste it down the column. Enter a list of currency RICs in cells B5, B6, B7 and so on. RtGet displays the appropriate bid prices in realtime.
2.2.5
Example Using Equity RICs
Repeat the same procedure to get this realtime data.
2.2.6
Example Using Bonds
Repeat the same procedure to get this realtime data.
30 July 2005
16
Reuters Financial Software PowerPlus Pro Workbook For example, in cell D6 the formula is =RtGet($B$5",$B6,D$5) Note: Adfin returns dates in numerical format, for example 39924. To display these dates in a recognisable format, use the cell format properties in Excel: choose Format -> Cells -> Number -> Date.
2.2.7
Improved RtGet() Update Mechanisms
Reuters PowerPlus Pro provides you with improved RtGet() update mechanisms, using Microsoft Excel RTD. You must install Microsoft Excel 2002 or later version, which is part of MS Office XP, to use RTD in Reuters PowerPlus Pro.
2.2.8
RTD
Microsoft Excel Real Time Data (RTD) is an update mechanism for the delivery of realtime data into Excel, Reuters PowerPlus Pro leverages this functionality to optimize its delivery of realtime data into Excel.
2.2.9
RTD-assisted RtGet()
This function is a non-volatile function. The realtime data engine does not update all RtGet() functions in a workbook when an event such as a cell modification or a recalculation occurs. The engine only updates RtGet() function(s) whose input data has changed, thus providing significant performance gains by reducing recalculation. This functionality enables you to improve greatly the performance of realtime data retrieval with RtGet().
2.2.10 Compatibility with Other Versions of Microsoft Excel and Reuters PowerPlus Pro
You can open workbooks built with another version of Microsoft Excel or Reuters PowerPlus Pro within Microsoft Excel 2002 and RtGet will seamlessly use RTD.
2.2.11 Copy/Paste
The RTD mechanism enables you to copy and paste data when an update occurs on your spreadsheet. This was not possible previously since a volatile recalculation occurred when any data was updated, causing Excel to clear its copy buffers. This is no longer the case with RTD. If you choose Tools -> Options -> Calculation and set Calculation to Manual,, the recalculation mechanism of Microsoft Excel temporarily suspends the RtGet updates performed by RTD. You then have to press F9 to finish the calculation. To take advantage of the realtime performance provided by RTD, set Calculation to Automatic.
2.3 RtUpdate()
2.3.1 Purpose
Like RtGet(), RtUpdate() retrieves realtime data from a data source.
RtUpdate() is a non-volatile (asynchronous) function that does not cause non-volatile functions to recalculate whenever there is a spreadsheet recalculation. Only the individual cells containing changed data is updated.
All RtUpdate() functions on a spreadsheet are independent from each other. In contrast to RtGet(), RtUpdate() copies the realtime data retrieved into a destination cell, which is not the one from which the function was called. When a data update occurs, all volatile RtUpdate() functions update only the required destination cells. Consequently, Microsoft Excel only recalculates the functions that depend on the updated cells, in other ones the ones with changed input. This function is highly recommended when you are using contribution functions and models.
30 July 2005
17
2.3.2
Arguments
RtUpdate() takes six arguments: =RtUpdate(SourceAlias, InstrumentArray, FieldNameArray, DestinationCell, MacroName, RtMode)
Name Description Source alias (for example: IDN) One-dimensional array of instrument codes ( for example .DJI or .FCHI) One-dimensional array of field names or numbers (LAST,
SourceAlias InstrumentArray FieldNameArray DestinationCell MacroName RtMode
HISTORIC CLOSE)
Reference to cell at the upper left corner of the destination range Name of the macro to run on update Extended argument defining the operation, see the online help for further information
2.3.3
Example Using RtUpdate()
Reproduce the following table exactly by respecting the addresses of cells and by using the RtUpdate() function to obtain realtime links. Step Action 1 2 Choose cell C2, and choose Insert -> Function. Choose Adfin Real Time in the Function category and RtUpdate() in Function name.
30 July 2005
18
Reuters Financial Software PowerPlus Pro Workbook Step 3 Action Click OK The RtUpdate() function dialog box appears.
a. b. c. d. e.
In SourceAlias enter B3 to define the Reuters data source. In InstrumentArray enter the array of cells B5:B14. In FieldNameArray enter the array of cells C4:F4. In DestinationCell enter C5. In RtMode enter D2, see Frequency of Update.
Note: The MacroName argument is optional. Here, leave it blank. 5 Click OK. All the requested realtime data appears in the table. Cell C2 displays the string Updated at <HH:MM:SS> which is refreshed after each successful update. Note: By default, the update frequency is 30 seconds.
30 July 2005
19
2.3.4
Frequency of Update
You can specify the maximum frequency of update using the keyword FRQ in RtMode (cell D2). Step Action In cell D2, enter the keyword and value FRQ:1s for the RtMode argument. Realtime data is updated every second.
2.3.5
Doing a Snapshot Update
Adfin Real Time provides you with the option of retrieving realtime data continuously, or at regular/predefined intervals in the form of a data snapshot. RtUpdate() requests the input instruments only once, retrieves the data, and then closes all your data subscriptions. To enable the snapshot mechanism, you must set the UPDATE:SNAP keyword in RtMode. Adfin Real Time can leave the subscriptions open between two snap updates if you wish by setting the LIVE:YES keyword in RtMode. Adfin Real Time allows you to schedule the snap updates. RtUpdate() can perform an automatic snap update at a given date and/or time if you specify the ONTIME keyword in RtMode. It can be repeated several times if you indicate one ONTIME keyword for each period. The snapping, schededuling and series function (RtUpdate() and RtSeries()) offer the option to snap data only on weekdays if you specify the DT keyword in RtMode. Step Action In cell D2, enter the keywords and values UPDATE:SNAP and ONTIME:17:56 for the RtMode argument. Adfin updates the realtime data only at the designated time, 17:56:00. Cell C2 displays the string Snapped on <MM/DD/YY> at <HH:MM:SS>, which is refreshed after each snapshot.
Note:
You can also set the snap update frequency using the FRQ keyword in RtMode, but only at a frequency greater than 60 seconds.
30 July 2005
20
Reuters Financial Software PowerPlus Pro Workbook You can manually trigger a global update of all RtUpdate() Snap functions. To do this use the RtRefreshSnap on the Reuters PowerPlus Pro toolbar. This means that you do not have to restart the Realtime Data button Engine each time. To format the parameters for this function easily, use the Real-time Quote assistant, see Real-Time Quotes Assistant.
2.3.6
Retrieving the System Date Using RtUpdate
A meta-instrument is available for RtUpdate() to return the system date in a non volatile way. This provides an easy way to optimize complex spreadsheets for users who are not yet using Office XP. Step Action 1 Set up the input arguments: a. In cell A3, enter the "IDN" Source Alias code. b. In cell A4, enter the "TODAY" InstrumentArray. c. In cell A5, enter the "LAST" FieldNameArray. Use the RtUpdate() function to obtain the system date of the computer.
2.4 RtSeries()
2.4.1 Purpose
Adfin Real Time provides you with the RtSeries() function to update realtime data in the form of a list of snapshots. This enables you to build lists of time-series data from realtime quotes, using only one function, for a single instrument.
2.4.2
Arguments
RtSeries() takes six arguments: = RtSeries(SourceAlias, InstrumentCode, FieldNameArray, DestinationCell, MacroName, RtMode)
Name Description Source alias (for example: IDN). Instrument code.
SourceAlias InstrumentCode
30 July 2005
21
Reuters Financial Software PowerPlus Pro Workbook Name Description One-dimensional array of field names or numbers (for example:
FieldNameArray DestinationCell MacroName RtMode
LAST, HISTORIC CLOSE).

Reference to cell at the upper left corner of the destination array. Name of the macro to run. Extended argument defining the operation.
2.4.3
Example Using RtSeries()
Reproduce the table exactly by respecting the addresses of cells and by using the RtSeries() function to obtain realtime data. To format the parameters of this function easily it is best to use the Realtime Series assistant, see Using the Real Time Series Assistant. Step Action 1 Set up the input arguments: a. In cell B3 enter IDN b. In cell B4 enter EUR= c. In cell B5 enter BID d. In cell C5 enter ASK Choose cell A5. Choose Insert -> Function. Choose Adfin Real Time in the Function category and RtSeries() in Function name, then click OK. The RtSeries() function dialog box appears. Enter the appropriate cell references for the arguments: a. In SourceAlias enter B3. b. In InstrumentCode enter B4. c. In FieldNameArray enter the cell array of cells B5:C5. d. In DestinationCell enter A6. Note: The MacroName argument is optional. Here leave it blank. 6 7 In RtMode enter the keywords and values START:31MAY05:18:00 and FRQ:1M . Click OK.
2 3 4
Realtime data snapshots are collected from May 31, 2005 at 18:00. Cell A5 displays the string Snapped on <MM/DD/YY> at <HH:MM:SS>, which is refreshed after each snapshot. The snap frequency of the BID and ASK fields for the Euro is once a minute. Note: The function returns the timestamp data in the first column of the array, since the default value of the TSPOS keyword in RtMode is TSPOS:LEFT. For details of RtMode in relation to RtSeries() see below. For full details see the PowerPlus Pro 5.0 Adfin Realtime User Guide.
30 July 2005
22
2.4.4
RtMode Keywords for RtSeries()
The table below shows which keyword to use in RtMode to set the update parameters for RtSeries(). Keyword Purpose
START ONTIME FRQ TSPOS
Enables you to set the date at which you want to begin the snap update. Performs automatic snap updates at given dates and/or times. It can be repeated several times if you indicate one ONTIME keyword per update. Sets the snap update frequency. Returns a timestamp field. Note: By default, the function returns the timestamp data in the first column of the array, since the default value of TSPOS keyword is TSPOS:LEFT.
DT
Specifies whether the snap is performed only on weekdays
2.5 RtChain()
2.5.1 Purpose
The RtChain() function fills an Excel range with the underlying records of a chain/tile.
2.5.2
Arguments
RtChain() takes five arguments: =RtChain(SourceAlias, InstrumentCode, DestinationCell, MacroName, RtMode)

Name Description Source alias (for example: IDN). Instrument code (for example: EUBMK=). Reference to cell at the upper left corner of the destination array. Name of the macro to run. Extended argument defining the operation.
SourceAlias InstrumentCode DestinationCell MacroName RtMode
2.5.3
Step 1
Example Using RtChain()

Action Set up the input arguments: a. In cell B2, enter the Reuters code EUBMK = for the Euro-denominated government benchmark chain. b. In cell B3, enter RET:A50 to clear 50 cells each time the function is recalculated. Choose cell B4. Choose Insert -> Function.
2 3
30 July 2005
23
Reuters Financial Software PowerPlus Pro Workbook Step 4 Action Choose Adfin Real Time in Function Category and RtChain() in Function name then click OK. The RtChain() function dialog box appears.
a. b. c. d.
Enter IDN in SourceAlias. Choose cell B2 in InstrumentCode. Choose cell B7 in DestinationCell. Choose cell B3 in RtMode.
Click OK. All RICs of the chain EUBMK= appear in column B, starting in cell B7. Cell B4 displays the string Updated at <HH:MM:SS> after each successful update. Note: You can change the chain dynamically, for example from EUBMK= to USBMK=.
It is possible to use RtChain() function on RICs such as EFX= or EURIRS.
2.5.4
Keyword
RtMode Keywords for RtChain()

Purpose Specifies whether chain/tile records stay open after retrieval with RtChain(): LIVE:NO to get snapshot data LIVE:YES to get live data Use the LIVE:YES option for chains/tiles with contents that change frequently, rather than underlying data, for example market statistics chains. Specifies whether RtChain() retrieves chain or tile records: TILE:NO to get chain data TILE:YES to get tile data
LIVE
TILE
2.6 RtHistory()
2.6.1 Purpose
RtHistory() retrieves a list of historical (TS1) data for an instrument. Before using the RtHistory() function, it is necessary to know the fields available in TS1 for an instrument. The RtHistoryInfo() function can retrieve this information. (See RtHistoryInfo()).
30 July 2005
24
2.6.2
Arguments
RtHistory() takes five arguments: =RtHistory(SourceName, InstrumentCode, FieldNames, HistoryStructure, HistoryMode)

Name Description Source alias (for example: IDN). Instrument code. String of field names separated by commas. Extended argument defining the range of dates between which to retrieve historical data. Extended argument customizing the format of the return array.
SourceName InstrumentCode FieldNames HistoryStructure HistoryMode
2.6.3
Step 1
Example Using RtHistory()

Action Set up the input arguments: a. In cell B3 enter the code EUR=. b. In cell B5 choose the fieldnames available from the RtHistoryInfo() function. c. In cell B7 enter NBEVENTS:200 to obtain the latest 200 historical records. d. In cell B9 enter NULL:SKIP to skip the dates with any null field and H:YES to return headers. Choose cell B11. Choose Insert -> Function. Choose Adfin Real Time in Function Category and RtHistory() in Function name.
2 3 4
Click OK. The RtHistory() function dialog box appears.
30 July 2005
25
Reuters Financial Software PowerPlus Pro Workbook Step 6 Action Enter appropriate values or cell references for the parameters: a. In SourceName enter IDN. b. In InstrumentCode enter B3. c. In FieldNames enter B5. d. In HistoryStructure enter B7. e. In HistoryMode enter B9. Click OK. The first date of the instrument appears in cell B11. Choose the result range, press F2, then Ctrl+Shift+Enter.
7 8
9 10
Format the date values in cells B11:B24 using Format ->Cells -> Number ->Date. In cell B3 replace the code EUR= by GBP=. The historical data updates automatically.
2.6.4
Modifying the Input Arguments
Modify the input data in the example to retrieve data for the Dow Jones: Cell B3 (InstrumentCode): .DJI Cell B5 (FieldName): DATE,CLOSE,OPEN,HIGH,LOW,VOLUME Note: When the result array you select is too big for the data you retrieve, Adfin displays #N/A in the empty cells.
2.7 RtHistoryInfo()
2.7.1 Purpose
The RtHistory() function retrieves the list of TS1 fields available for an instrument. You use this function before you use RtHistory().
2.7.2
Arguments
RtHistoryInfo() takes three arguments:
30 July 2005
26
=RtHistoryInfo(SourceName, InstrumentCode, HistoryMode)

Name Description Source alias (for example: IDN). Instrument code (for example: EUR=). Extended argument customizing the return array.
SourceName InstrumentCode HistoryMode
2.7.3
Step 1 2 3 4 5
Example Using RtHistoryInfo()

Action Enter the currency code EUR= in cell B3. Choose cell B5. Choose Insert -> Function. Choose Adfin Real Time in Function Category and RtHistoryInfo() in Function Name. Click OK. The RtHistoryInfo() function dialog box appears.
Enter appropriate values or cell references for the input parameters: In SourceAlias enter IDN In InstrumentCode enter B3 Click OK. Adfin returns a string listing all fields available in TS1 for the Euro currency, and displays this string in cell B5. You can use these field names as input data for RtHistory() to retrieve historical data, as in Example Using RtHistory().
2.8 RtNow()
2.8.1 Purpose
RtNow() function retrieves the current system date and time. RtNow() function is a non-volatile and asynchronous function that replaces the volatile Now() function of Microsoft Excel 2002. Adfin Real Time enables you to manage RtNow() updates separately from the global recalculation of your spreadsheet, thus improving its realtime performance. RtNow() can only run with Microsoft
Excel 2002 or later version. You can only use RtNow() in RTD mode.
30 July 2005
27
2.8.2
Arguments
This function takes no arguments.
2.9 RtToday()
2.9.1 Purpose
The RtToday() function retrieves the current system date. RtToday() is a non-volatile and asynchronous function that replaces the volatile Today() function of Microsoft Excel 2002. Adfin Real Time enables you to manage RtToday() updates separately from the global recalculation of your spreadsheet, thus improving its realtime performance. RtToday() only runs with Microsoft Excel 2002 or later version. You can only use RtToday() in RTD mode.
2.9.2
Arguments
This function takes no arguments.
30 July 2005
28
3 REUTERS REAL TIME ASSISTANTS, DDE CONVERTER, DIAGNOSTIC VIEWER, AND REAL TIME SETTINGS
3.1 Real Time Data Assistants
3.1.1 Purpose
Reuters PowerPlus Pro incorporates assistants to facilitate data retrieval. These assistants allow you use complex functions immediately. This is done by guiding you through a series of step-by-step dialog boxes that ask questions or provide options from which to choose. Your answers and choices enable the assistant to perform its task according to your specifications.
3.2 Real-Time Quotes Assistant

3.2.1 Purpose
The Real-Time Quotes assistant allows you to create realtime formulas for a table of instruments and fields. Step Action 1 2 Choose Reuters -> Assistants -> Real Time Quotes. An assistant appears. Enter RICs for a few futures (for example: FGBLc1,) in Code and click each time on Add to enter them in Selected Subjects.
Click Next. Fields are classified by category (Fields by Category), by favorite (Favourites), or listed by alphabetical order (All Fields).
30 July 2005
29
Reuters Financial Software PowerPlus Pro Workbook Step 4 Action Double-click the fieldnames retrieved to enter in the Selected Fields windows. In our example, choose: Display Name, Last, Net Change, Volume Accumulated, Open Interest, Historic Close.
Click Next. See the online help for an illustration and description of the options available in the dialog box. Note: By default the Real Time Quotes assistant selects Frequency (RtUpdate) as the Update Mode for using the RtUpdate() function (update every one second).
6 7
In the example, choose Snap, choose the Schedule check box, then On, and enter the date at which you want to snap the data. Click the Add button to build your time schedules.
30 July 2005
30
Reuters Financial Software PowerPlus Pro Workbook Step 8 Action Click Next. The table below shows you the options you can choose to format your result. The onscreen display reflects the format choices you make. Orientation allows you to display Field values in columns or Field values in rows. Show Field Description allows you to display the full field names at top of columns, instead of the database column names or real time field identifiers.
Click Finish. Adfin builds the table automatically using the snap mechanism provided by the RtUpdate() function.
30 July 2005
31
3.2.2
Description of Options
Item 1
Name Update Mode
Description Allows you to configure the update mechanism for retrieving realtime data. Choose: Frequency, to use RtUpdate(). Snap, to use the snapping mechanism provided with RtUpdate().
Comment
Macros
Print
If you choose Snap, the following functionalities are available: Update every check box. Enter the time interval in Hour(s) or Minute(s) at which the Real Time Engine updates data. Schedule check box. Choose On or Every day at and enter the date at which you want to snap the data. In this case, click Add to build your time schedules. Allows you to specify the name of The Macros section is enabled if the Frequency or Snap option is selected a VBA macro to run when the function updates. Enter the name in the Update Mode section. of a VBA macro you have written that is to be run by RtUpdate() after each realtime update. Allows you to choose a list of cell The Print section is enabled if the ranges to print after each Snap option is selected in the Update realtime update. Mode section. Allows you to save the workbook The Save section is enabled if the after each realtime update. If you Snap option is selected in the Update choose a range or list of ranges Mode section. to be printed in the Print section and/or the Active Document button in the Save section, a new field appears in the Macros section. The default option is No Save.
Save
30 July 2005
32
3.3 Using the Real Time Chain Assistant

3.3.1 Purpose
The Real Time Chain assistant populates an Excel range with the underlying records of a chain/tile using the RtChain() function. Step Action 1 2 Choose Reuters -> Assistants -> Real Time Chain. Reuters PowerPlus Pro opens a dialog box. Enter the RIC EUBMK= in Code and click Add to enter it in Selected Subjects.
Note:The Chain assistant accepts just one instrument.
30 July 2005
33
Reuters Financial Software PowerPlus Pro Workbook Step 3 Action Click Next. Fields are classified by category (Fields by Category), by favorite (Favourites), or listed by alphabetical order (All Fields).
4 5
Double-click the fieldnames retrieved to enter in Selected Fields. In the example, choose Maturity Date and Real Time Yield 1.
30 July 2005
34
Reuters Financial Software PowerPlus Pro Workbook Step 6 Action Click Next. The Update Mode section allows you to choose the refresh mechanism you want to retrieve chain and titles. Choose: Frequency, to use RtChain() and RtUpdate(). Snap, to use RtChain() and the snapping mechanism provided with RtUpdate(). Dynamic Chain allows you to get a single snapshot of the data (No) or to keep the subscriptions open and receive further updates (Yes). The default dynamic chain is No. By default the Real Time Chain assistant selects Frequency(RtUpdate) as the Update Mode to use the RtChain() and RtUpdate() functions (update every 1 second).
30 July 2005
35
Reuters Financial Software PowerPlus Pro Workbook Step 7 Action Click Next. The table below shows you the options you can choose to format your result. The onscreen display reflects the format choices you make. Orientation allows you to display Field values in columns or Field values in rows. Show Field Description allows you to display the full field names at top of columns, instead of the database column names or real time field identifiers. No. of Rows allows you to display the number of rows to display. Skip allows you to enter the number of first entries of the chain (usually RICs) to skip from the retrieved data.
8 9
Enter 25 in No. of Rows. Click Finish. In the following figure, the table is built automatically with the RtChain() and RtUpdate() functions with the update frequency set to 1 second.
30 July 2005
36
3.4 Using the Real Time Series Assistant

3.4.1 Purpose
The Real Time Series assistant enables you to retrieve data series from only one instrument using the RtSeries() function. Step Action 1 2 Choose Reuters -> Assistants- > Real Time Series. Reuters PowerPlus Pro opens a dialog box. Enter the RIC RTR.L= in Code and click Add to enter it in Selected Subjects.
Click Next. Fields are classified by Category (Fields by Category), by favorite (Favourites), or listed by alphabetical order (All Fields).
30 July 2005
37
Reuters Financial Software PowerPlus Pro Workbook Step 4 Action Double-click the fieldnames retrieved to move them into Selected Fields. In the example, choose Last, Percent Change and Trade Volume.
Click Next. The Update Mode section allows you to choose the refresh mechanism you want to retrieve data series. You can activate the following check boxes: Start Date, then On to specify the date and time at which the Real Time Engine begins updating data or At to specify the time at which the Real Time Engine begins updating data, every day. End Date, then On to specify the date and time at which the Real Time Engine stops updating data or At to specify the time at which the Real Time Engine stops updating data, every day. Update Every, to enter the time interval in Hour(s) or Minute(s) at which the Real Time Engine begins updating data. The default interval is 1 Minute. Schedule, then On or Every day at and enter the date at which you want to snap the data. In this case, click Add to build your time schedules. Weekdays only, to retrieve only data which is recorded for weekdays The Macros section allows you to use VBA programs to update data. Enter the name of a VBA macro you have written that is to be run by RtSeries() after each real time update. The Print section allows you to choose a list of cell ranges to print after each real time update. The Save section allows you to save the workbook after each real time update. Note: If you choose a range or list of ranges to be printed in the Print section and/or the Active Document option in the Save section, a new field appears in the Macros section. It specifies the default name PPPMacro1 of a macro, which enables you to save the current workbook, print the selected ranges and run the macro you selected in the list box.
30 July 2005
38
Reuters Financial Software PowerPlus Pro Workbook Step 6 Action In the example, activate Update Every, then use the default interval, which is one minute.
30 July 2005
39
Reuters Financial Software PowerPlus Pro Workbook Step 7 Action Click Next. The table below shows you the options you can choose to format your result. The onscreen display reflects the format choices you make. Orientation allows you to display Field values in columns or Field values in rows. Show Field Description allows you to display the full field names at top of columns, instead of the database column names or real time field identifiers. TimeStamp allows you to add a date/time column at the left of the result array (LEFT) or add no date/time column (NONE) or add a date/time column at the right of the result array (RIGHT). Sort Data allows you to list data in ascending or descending order.
Click Finish. In the following figure, the table is built automatically with the RtSeries() function with the update frequency set to 1 minute.
30 July 2005
40
3.5 Using the Real Time History Assistant

3.5.1 Purpose
The History assistant retrieves arrays of TS1 (Time Series) historical data for one instrument using the RtHistory() function. This Assistant is only available if DBU (Data Backup Unit Server) has NOT been installed. Note: When you install via RDMC, if you do not install Metastock, DBU is not installed either. If DBU is available, go to Retrieving Time Series Data from the Data Backup Unit Server: DeHistory(). Step 1 2 Action Choose Reuters -> Assistants -> Time Series Data. Reuters PowerPlus Pro opens a dialog box. Enter the futures RIC BRT- in Code and click Add to enter it in Selected Subjects.
The History assistant accepts just one instrument. Click Next. Fields are classified by category (Fields by Category), by favorites (Favourites) or listed in alphabetical order (All Fields). Double-click the fieldnames retrieved to enter them in Selected Fields. In our example, choose Date and Close.
30 July 2005
41
Reuters Financial Software PowerPlus Pro Workbook Step 5 Action Click Next.
In the Orientation section, choose Field values in columns to obtain the results in two columns. Click Finish. The table is automatically built with the RtHistory() function.
3.6 Reuters DDE Converter
30 July 2005
42
3.6.1
About the DDE Converter
The DDE Converter is a separate add-in, which can be used with any version of Reuters PowerPlus Pro or even plain Excel. It supports DDE link syntaxes such as: TIBDDE/TIBLink TS1 page-based feeds Arrays of links are converted seamlessly. You can generate a log file to analyze the conversion process. This gives you the benefit of better performance and greater flexibility using the real time data engine.
3.6.2
Step 1
Open Reuters DDE Converter

Action Open one workbook that contains DDE formulas to replace. Reply No to the question about starting REUTER.exe. Cancel the file search windows which may appear if there are other Add-Ins or links to workbooks which do not exist on the machine. Choose Reuters -> DDE Converter. Reuters PowerPlus Pro opens the following user form.
The DDE converter only tries to convert the area of cells you specify in this dialog box. Any other cells will be ignored. When a document is protected, only the cells that are not locked are converted. Four selections are available: All Opened Workbooks (also called the silent mode) converts all cells of All Opened Workbooks Active Workbook converts all cells of all sheets of the Active Workbook. Active Worksheet converts all cells of the Active Worksheet. Range converts all cells of the selected range You can create a log file during conversion by activating Create log file. This log file details all the operations done during the conversion of the area. The log file is generated in the same directory as the active workbook. Its name is prefixed by Log_ and followed by the workbook name and the suffix .log.
3.6.3
Reuters DDE Converter Settings
The Conversion Patterns... button opens Reuters DDE Converter - Conversion Pattern Settings.
30 July 2005
43
Item 1
Description Type of the source, it can be: REUTERS TIB FIST BRIDGE CSK TWINDDE RMM Name of the DDE server. Name of the DDE topic (source). Name of the subtype of the source. Prefix that appears, in the DDE link, before the name of the RIC (usually only used for TIB). Suffix that appears, in the DDE link, after the name of the RIC (usually only used for TIB). Name of the source that will be used in the corresponding Reuters PowerPlus Pro function generated by the DDE converter.
2 3 4 5 6 7
3.6.4
Step
Modify the Configuration File

Action To modify a line in the .dat configuration file, click Edit. To delete a line, click Remove. To add new lines, click New.
On New/Edit the following user form is displayed.
Server, Topic, Subtype and To are mandatory entries.
3.6.5
Conversion Example
Formula before conversion
=TIBLINK|TIB!RSF.REC.EUR=.NaE,BID
Formula after conversion
=RtGet(IDN, EUR=, BID)
30 July 2005
44
3.6.6
Reuters DDE Field Mapping Settings
The Field Mapping button opens Reuters DDE Converter - Field Mapping Settings.
Use this window to convert a contributor field name to a Reuters field name. Item Description 1 2 3 4 Subtype of the field. Name of the FID. Name of the data field. User of the field.
3.7 Diagnostic Viewer

3.7.1 Purpose
Diagnostic Viewer tracks Realtime messages in a file and lets the user choose to display them or not in a specific dialog box. It is an independent application launched when starting up Reuters PowerPlus Pro, and minimized as an icon in the taskbar.
3.7.2
Illustration
3.7.3
Tabs
The Diagnostic Viewer displays messages, sorted in four tabs: The Feed tab specifies the status of the different sources (DDS, IDN_SELECTFEED). The RIC tab specifies whether a RIC name is valid. The Stale tab specifies whether information about RICs is lost and why. The Contrib tab specifies whether the contribution process is working (Ack) or not (Nack). The Open Delayed RIC tab displays the:
30 July 2005
45
Reuters Financial Software PowerPlus Pro Workbook name of the application feed name delayed RIC
3.8 Adfin Real Time Settings Dialog Box

3.8.1
Step
Open Realtime Settings

Action Choose Reuters -> Settings -> Real Time Data.
3.8.2
Setup
The Setup tab of the Adfin Real Time Settings dialog box defines the realtime platform driver used. You can also restart Adfin Real Time from this display. You can resize the Setup tab.
3.8.3
Aliases
The Aliases tab defines aliases for data sources, instrument codes, and field names, as well as some standard settings for data requests. You can resize the Aliases tab.
30 July 2005
46
3.8.4
Settings
The Settings tab defines the default settings for: RtGet(), see RtGet() RtContribute() RtHistory(), see RtHistory() You can resize the Settings tab.
3.8.5
Item
Description of Options
Description
30 July 2005
47
Reuters Financial Software PowerPlus Pro Workbook Item 1 Description Update Mode defines the frequency of the real time data update. Choose: Always to get all realtime updates. Frequency to define a minimum time interval between two updates. Stop Updates to stop all realtime updates. Note: You may find that the recalculation performance of Microsoft Excel suffers if RtGet() results are fetched in real time. Set the Update Mode to Frequency instead of Always to avoid this. 2 Cache Holding Time stores the most recently requested instruments. You can specify the length of time information is held in the cache.
3.8.6
Microsoft Excel RTD
If you are using Microsoft Excel 2002 or a later version, Reuters PowerPlus Pro provides you with an improved RtGet() update mechanism, using Microsoft Excel RTD. In this case, the Settings tab of the Adfin Real Time Settings dialog box offers an additional text box. You can manage RtGet() updates in milliseconds, by setting Throttle Interval to 1000 ms or less when the Update Mode is set to Always.
3.8.7
Watch List
The Watch List tab monitors the status of a source and lists all open items for that source. It also displays the record template of the selected item. Either positive or Negative Field_IDs can appear in the template dialog box. You can resize the Watch List tab.
30 July 2005
48
3.8.8
Step
Use the Watch List

Action Choose the Source Name IDN_SELECTFEED, IDN or RSF (depends on your system). All the instruments used are listed in Instrument and for each instrument it shows you the fields available from the realtime data source.
30 July 2005
49
4 REUTERS INTEGRATION OF REUTERS 3000 DATABASES

4.1 Logging in to RDB
4.1.1 Purpose
The Data Engine functions allow you to retrieve fundamental and reference data from the RDB (Reuters Database) and from the Data Back Up (DBU).
4.1.2
Automatic Login
When you launch Reuters PowerPlus Pro login is automatic. The Username and the password are set during the installation process.
4.1.3
Manual Login
If Reuters PowerPlus Pro cannot log in automatically, then you have to log in manually. Step Action Enter your user code 3000 and your Password.
4.2 Securities 3000 Database

4.2.1 Description
The Reuters 3000 Equities database contains fundamental data allowing the detailed analysis of companies, stocks, indices, economic indicators or countries.
4.2.2
Company Analysis Data
Company analysis data comprises: Profit and Loss account and Balance Sheet and descriptive information (e.g. company activities, address) for nearly 35 000 companies from 47 countries world-wide (up to 10 years of history). Cash Flows (Net Cash Operating, Net Cash Investing) of the principal European and American companies. Local and world sector classifications (Sectors MSCI).
30 July 2005
50
Reuters Financial Software PowerPlus Pro Workbook Historical quotations daily over one period from 5 to more than 15 years for more than 108 000 active equity quotations emanating from more than 122 stock exchange in 70 countries. Earning forecast and dividend estimates for over 15 000 of the major companies. Full scope of company ownership Mergers and acquisitions histories
4.2.3
Index Analysis Data
Index analysis data comprises: History of index constituents, weights and index derived data for 800 equity indices from 42 countries. Calculated data: PER, average output... Historical quotations over one period from 5 to 15 years for more than 4000 national or international indexes.
4.2.4
Country Analysis Data
Country analysis data comprises: More than 3300 economic indicators of 80 countries: public finance, national accounts, production, employment, price and foreign trade. Up to 40 years of history.
4.3 Treasury 3000 Database

4.3.1 Description
The Reuters 3000 Fixed Income database contains more than 580 000 bonds (un-redeemed bonds, governments bonds, eurobonds, semi-government bonds, corporate) and covers 117 countries.
4.3.2
Bond Data
The Reuters 3000 Fixed Income database contains for a large variety of bond instruments the following data: Term and Conditions: Reimbursement, Market convention, and Quotation Market. Historical Rating. Clearing codes. Realtime price sources available for a given bond. Historical bond prices sourced from Reuters. Historical price and yield sources.
4.4 Fundamental Data Engine Settings

4.4.1 Purpose
You use the Fundamental Data Engine Setting to configure the connections to the various data sources.
4.4.2
Step
Data Engine Settings

Action
30 July 2005
51
Reuters Financial Software PowerPlus Pro Workbook Step Action From the menu bar, choose Reuters -> Settings -> Fundamental Data Engine.
Depending on which Data Engine you are using, the Source type will show any of the following: EQUITY 3000 TREASURY 3000 GSE Xtra Business Logic Server (XBLS) Generic ODBC Provider Database Backup Unit (DBU) eDex provider The other fields on this dialog box enable you to define the cache sizes, refresh time and purge time.
4.4.3
Source Type: All Sources
To set the default source, activate the check box just above the connection status text. (The value set here becomes the default value for the SOURCE keyword within DeMode. You can override this manually in any function call.) Cache, Purge and Global Refresh properties are not supported when data are retrieved from the DBU server. The corresponding parts in the Data Engine Settings dialog box are disabled.
4.4.4
Reconnect
Use the Reconnect button to re-establish an interrupted server connection. Use the Reconnect button when you receive either of the following error messages:
#N/A Connection to Data Source Failed.
The connection to the server or ODBC driver has been lost for some reason, and you need to reconnect in order to continue with data retrieval.
4.4.5
Connection Status
The Connection Status indicates whether connection to the 3000 database has been effected for the ODBC driver (Treasury and Equity sources).
4.4.6
Cache Information
Specify the maximum memory cache size in KB in the Memory cache size field. The size must be between 50 KB and 32,000 KB. The percentage of the cache used is displayed as "N% currently used."
30 July 2005
52
Reuters Financial Software PowerPlus Pro Workbook If you choose the Use cache option, all data stored in the cache is saved in the file specified in the Directory field when you exit the application. This data is restored when you restart the application. You can also specify the Purge Time in this section. This time delay is used by the purge mechanism. When data is stored in the cache for longer than the specified length of time without being requested, the cache is emptied to make room for fresh data. Changes made here modify the relevant user settings file depending on the source type chosen.
4.4.7
Global Refresh
To activate the automatic refreshing of data, choose Daily Refresh Activated. Selecting this option displays Time of Daily Refresh (hh:mm). The default time of day is 00:05, but you can change this to any other time of day. The Data Engine uses this value to assign a random refresh time, plus or minus one hour from the time of day specified, for each data request in a worksheet. This random mechanism ensures that not all requests are refreshed at the same time. The Last Global Refresh field displays the time of the latest global refresh. Changes here modify the relevant user settings file depending on the source type chosen. For a formula using a Data Engine function to be refreshed automatically, set REFRESH:YES in the DeMode parameter. If the REFRESH keyword is omitted, the default value is REFRESH:NO.
4.4.8
Refresh Data
Use the Refresh data button to flush the cache and refresh all data, even for queries which include the REFRESH:NO parameter. When you click this button, the cache is emptied, all data are updated on screen, and the cache is refilled with new data. If retrieved data are erroneous, you must use the Refresh data button to re-send the request to the data source. Simply re-executing the function in the worksheet retrieves the same erroneous data from the cache.
4.5 Retrieving Single Values for an Instrument from the Reuters 3000 Database: DeUpdate()
4.5.1 Introduction
The Reuters 3000 Fixed Income Databases have been migrated from the legacy Treasury 3000 infrastructure to the RDB (Reuters Database), which contains fundamental and reference data.
4.5.2
DeUpdate()
The DeUpdate() function retrieves a table of values from a named data source (Reuters Securities 3000 database, Reuters Treasury 3000 database for instance). You can use DeUpdate() to retrieve fields like ADF_BONDSTRUCTURE, COUPON and MATDATE which you can use as arguments in Adfin Bonds functions. The DeUpdate() function supersedes and replaces several previous functions. For full details see the Reuters 3000 Data Engine Guide or the online help. Spreadsheets which contain the superseded functions still work, but these functions are only supported for backward compatibility purposes. The superseded functions no longer appear in the list of functions.
4.5.3
Arguments
The DeUpdate() function is composed of six arguments: Name Description
CodeList FieldList
Identifies a list of instruments for which data are to be retrieved, using valid security identifiers. List of fields for which data are to be retrieved.
30 July 2005
53
Reuters Financial Software PowerPlus Pro Workbook Name Description Identifies the top-left position to hold the table returned by the function. The name of a macro to run on retrieval of data or refresh. String containing one or more conditional parameters separated by semicolons. Defines the format of the results, sets limits on the days for which data are to be retrieved, and specifies whether results are refreshed automatically.
DestinationCell MacroName Conditions DeMode
4.5.4
Example Using DeUpdate() with the Securities Database
In this exercise we demonstrate how to find the name, the sector name, the price change on 1 month, the price change on 3 months, the beta, the high price over the last 52 weeks and the low price over the last 52 weeks of corresponding equity RICs codes using the DeUpdate() function. Reproduce the following table:
Step 1
Action a. In cells C2:I2 enter the FieldList (to know the available FieldList open the online help of DeUpdate() and click on the FieldList link). In our example the FieldList is:
ORG_SHORT_NAME, GLOBAL_SECTOR_NAME, PRICE_PCT_CHG_1M, PRICE_PCT_CHG_3M, BETA, HIGH_52W, LOW_52W (in cells C2:I2 in our
example) b. In cells B3:B9 enter the CodeList (RIC of equities in our example). c. In cells B12 define the DeMode. Use the keyword SOURCE:EQUITY to access the Reuters 3000 Equity database. It is a mandatory keyword except if this source has been set to default in the Data Engine dialog box (to access from the Reuters toolbar, choose Settings, then Fundamental Data Engine, choose Equities 3000 as Source Type and check the Set as default source check box.). 2 3 4 Choose cell B2, and choose Insert -> Function. Choose Reuters 3000 Date Engine in Function Category and DeUpdate() in the Function Name then click OK. Enter the arguments to obtain the following formula
=DeUpdate(B3:B9,C2:I2,C3,,,B12)
30 July 2005
54
Reuters Financial Software PowerPlus Pro Workbook Step 5 Action Click OK. The name, the sector name, the price change on one month, the price change on three months, the beta, the high price over the last 52 weeks and the low price over the last 52 weeks of corresponding equity RICs codes are displayed.
4.5.5
Example Using DeUpdate() with the Treasury Database
This exercise demonstrates how to find the coupon, the maturity date, the Bond Structure, the Rate Structure, the issuer, the first coupon date and the frequency of corresponding bond RICs codes using the DeUpdate() function. Some retrieved fields like ADF_BONDSTRUCTURE, ADF_RATESTRUCTURE, COUPON and MATURITY can be used as arguments in Adfin Bonds analytic functions (see Error! Reference source not found.). Reproduce the following table: Step Action 1 a. In cells C2:I2 enter the FieldList (see Complete List of Available Fieldnames for DataMapping for a list of fields available in the function). In our example the field list is: COUPON, MATURITY_DATE,
ADF_BONDSTRUCTURE, ADF_RATESTRUCTURE, ISSUER, FIRSTCP, FREQ.

b. In cells B3:B9 enter CodeList (RIC of bonds). c. In cells B12 define the DeMode. Use the keyword SOURCE:TREASURY to access the Reuters 3000 Fixed Income database. It is a mandatory keyword unless the TREASURY source is defined as the default source. Choose cell B2, and choose Insert -> Function. Choose Reuters 3000 Date Engine in Function Category and DeUpdate() in the Function Name then click OK, (See the figure in the preceding exercise.) The dialog box of the DeUpdate() function appears. Enter the arguments to obtain the following formula: =DeUpdate(B3:B9,C2:I2,C3,,,B12). Click OK. The coupon, the maturity date, the Bond Structure, the Rate Structure, the issuer, the first coupon date and the frequency of corresponding bond RICs codes are displayed.
2 3
4 5
30 July 2005
55
4.5.6
Complete List of Available Fieldnames for Data Mapping
To obtain the complete list of available field names for Data Mapping refer to the online help. To do this, in the DeUpdate() function, click the help icon in the function window and then click the FieldList argument.
4.5.7
Using the Securities Instrument Assistant
Reuters PowerPlus Pro provides assistants to help you. Step Action 1 2 From the menu bar, choose Reuters -> Assistant -> Security Instruments. Reuters PowerPlus Pro opens an assistant window. Enter the RICs of the instruments in Code and click Add each time to add the codes to Selected Subjects.
30 July 2005
56
Reuters Financial Software PowerPlus Pro Workbook Step 3 Action If you do not know the RIC of a given company type the name of the company in Search Text and then click Search. RICs available for this company will be displayed in the Search Part or the Assistant.
Click Next to go to the fieldnames selection step in the Securities 3000 database. Fields are classified by topics.
a. In the Fields by Category option, open the Equity folder by clicking the preceding + sign. b. In Equity, open the Equity Summary option. Double-click the fieldnames to retrieve to add the codes to the accumulated list labeled Selected Fields.
30 July 2005
57
Reuters Financial Software PowerPlus Pro Workbook Step 7 Action In the following example, choose Market Cap (Dollars), Revenue, Report Currency Code, Price earnings Ratio, Global Sector Name, Earnings Per Share, Dividend per share. In the Orientation option, choose Field values in columns to obtain the instrument in rows. Click Finish.
A table appears with selected values and fieldnames defined in the Assistant steps.
The fields requested are not fixed. It is possible to change the codes in the first column to obtain data on other instruments. You can extend the table to retrieve more data by adding or inserting columns and entering field names. You should then edit the DeUpdate() function to ensure that the function arguments include the new field selection.
4.5.8
Using the Treasury Instrument Assistant
Reuters PowerPlus Pro incorporates assistants to facilitate data retrieval. They let you use sophisticated utilities immediately. This is done by guiding you through a series of step-by-step dialog boxes that ask questions or provide options from which to choose. Your answers and choices enable the wizard to perform its task according to your specifications. Step Action 1 From the menu bar, choose Reuters -> Assistant -> Treasury Instruments. An assistant window appears.
30 July 2005
58
Reuters Financial Software PowerPlus Pro Workbook Step 2 Action Enter a few RICs (<ZGXK>, <ZGTL>, <DE011672639=>, <FR0000583288=>, <IT013754446=>) in Code/TCM and click each time on Lookup to enter them in the Code/TCM window.
Click Next to go to the fieldnames selection step in the Treasury 3000 database.
In Fields by Category, open the Basic Terms & Conditions folder. Double click on fieldnames to pass in the right part of the dialog box called Selected Subjects. In the following example, choose: Issuer Name, Original Issue Date, Original Issue Amount, Original Issue Price, and Lead Manager.
30 July 2005
59
Reuters Financial Software PowerPlus Pro Workbook Step 5 Action Click Next to go to the step of parameter settings of result format.
Click Finish. A table appears with selected values and fields.
As explained for the Security assistant, the retrieved data is not fixed. See Using the Securities Instrument Assistant for more information.
4.6 Retrieving Historical Data from the Reuters 3000 Database: DeHistory()
4.6.1 DeHistory()
DeHistory() function retrieves historical data for an instrument either from a specified table of Securities 3000 or
Treasury 3000 databases. The fields to be retrieved are specified in the function arguments. The function returns a table of data for a set of defined instruments and fields.
4.6.2
Arguments
DeHistory() is composed of seven arguments: =DeHistory(Code, TableName, FieldList, DestinationCell, MacroName, Conditions, DeMode)
Name Description Identifies the instrument name for which data is to be retrieved.
Code
30 July 2005
60
Reuters Financial Software PowerPlus Pro Workbook Name Description Name of the table or view in the Treasury 3000 or Securities 3000 databases from which data is to be retrieved. String containing the fields to be retrieved. Identifies the top-left position to hold the table returned by the function Name of the macro used to run on retrieval of data or refreshing. String containing one or more conditional parameters separated by semicolons. Defines the format of the results, sets limits on the days for which data is to be retrieved, and specifies whether results are refreshed automatically.
TableName FieldList DestinationCell MacroName Conditions DeMode
4.6.3
Retrieving Historical Data from Securities Database
The objective is to retrieve Reuters historical dividend. Reproduce this table exactly:
Step 1 2
Action Choose cell B9, and choose Insert -> Function. Choose Reuters 3000 Data Engine in Function Category and DeHistory() in Function Name then click OK. Reuters PowerPlus Pro displays the DeHistory() function dialog box. a. In Code, choose cell C3:D3. b. In TableName, choose cell C4. c. In FieldList choose cell C5. DPS_NET_ADJ fieldname is the adjusted net dividend. DPS_EX_DATE fieldname is the dividend ex date. DPS_GROSS_ADJ fieldname is the adjusted dividend value. d. In DestinationCell, choose cell C9. e. In MacroName, leave blank. f. In Conditions, choose cell C6. g. In DeMode, choose cell C7.
30 July 2005
61
Reuters Financial Software PowerPlus Pro Workbook Step 3 Action Then click OK.
To retrieve all available arguments, refer to the online help for the DeHistory() function. The table is built dynamic. By typing another instrument code, for example FTE.PA, in cell C3, the historical data for that equity is obtained immediately.
4.6.4
Retrieving Historical Prices and Yield from the Treasury 3000 Database
The objective is to retrieve British Benchmark Bond historical prices and yield. Reproduce the following table:
Step 1 2
Action Choose cell B9, and choose Insert-> Function. Choose Reuters 3000 Data Engine in Function Category and DeHistory() in Function name then click OK. Reuters PowerPlus Pro opens the DeHistory() dialog box. a. In Code, choose cell C3. b. In TableName, choose cell C4. c. In FieldList choose cell C5. d. In DestinationCell, choose cell C9. e. In MacroName, leave blank. f. In Conditions, choose cell C6. g. In DeMode, choose cell C7. To retrieve all available arguments, refer to the online help for the DeHistory() function.
30 July 2005
62
Reuters Financial Software PowerPlus Pro Workbook Step 3 Action Then click OK.
The table is dynamic. By typing another instrument code, for example DE10YT=RR, in cell C3, the historical data for that equity is obtained immediately. The DeList() function retrieves all price sources for a bond. It is also possible to retrieve, for example, the last 200 values of a set of data by using EVENTS:200 keyword in the Conditions argument in place of START and END keywords.
4.6.5
Using the Security History Assistant:
Reuters PowerPlus Pro incorporates assistants to facilitate data retrieval. These assistants let you use complex utilities immediately. Step Action 1 Choose the Reuters menu, the Assistant option then Security History. The Security History Assistant appears.
30 July 2005
63
Reuters Financial Software PowerPlus Pro Workbook Step 2 Action Enter the company RIC selected in the Code text box and click Add to access in the right part of the dialog box called Selected Subjects.
Click Next to pass to the fieldnames selection step in the Securities 3000 database. Fields are classified there by topics. In the Fields by Category option, open the Equity folder by clicking the preceding + sign. In Equity, open the Company Reports option then the Report Information History option.
30 July 2005
64
Reuters Financial Software PowerPlus Pro Workbook Step 4 Action Double-click the fieldnames to be retrieved to pass to the right part of the dialog box called Selected Fields. a. In the Report Information History option, choose the Period End Date and Report Currency ID fields. b. In the P&L/Balance Sheet History option, choose the Revenue and Net Attributable fields.
Click Next to pass to the step of property result.
6 7
Choose the Consolidated option. See the preceding figure. For the Period End Date field, enter 10 to obtain the last 10 years.
30 July 2005
65
Reuters Financial Software PowerPlus Pro Workbook Step 8 9 Action Choose the Final option in the Period Type list box. Click Next to pass to the step of parameter settings of result format.
a. In the Orientation text box, choose Field values in columns to obtain the instruments on line. b. In the No. of Rows text box, enter 20. 10 To finish, click Finish. A table appears with selected values and fields.
The data retrieved is not fixed. It is possible for example to modify the RTR.L code to MSFT.O (in the cell B2) to obtain the same data for Microsoft SA.
4.6.6
Access Ten Years of Daily Historical Data
Reuters PowerPlus Pro enables you to retrieve more than ten years of daily historical data. Step Action 1 Choose the Reuters menu, the Assistant option then Security History. The Security History Assistant appears.
30 July 2005
66
Reuters Financial Software PowerPlus Pro Workbook Step 2 Action Enter a RIC (or other instrument code) in the Combo box labeled Code and click Add to add the code to the list labeled Selected Subjects.
3 4 5
Click Next to go to the fieldnames selection step in the Wizard. Fields are classified by topics. In the Fields by Category option, open the Equity folder by clicking the preceding + sign. In Equity, open the Equity Price History option. In the Equity Price History option, choose the Trade Date, Close and Volume fields.
30 July 2005
67
Reuters Financial Software PowerPlus Pro Workbook Step 6 Action Click Next to go to the property result step.
a. Choose Daily in the Frequency option. b. In the Trade Date option, enter 10 in the From zone. Click Next to go to the format step.
a. In the Orientation text box, choose Field values in columns to obtain the instruments in rows. b. In the No. of Rows text box, enter 2500.
30 July 2005
68
Reuters Financial Software PowerPlus Pro Workbook Step 8 Action Click Finish. A table appears with selected values and fields.
The retrieved data is not fixed. It is possible to modify the RTR.L code to MSFT.O, for example, (in cell B2) to obtain the same data for Microsoft (ten years of historical data).
4.6.7
Using Treasury History Assistant
The Treasury Data History Assistant is similar to the Treasury Data List Assistant.
30 July 2005
69
Reuters Financial Software PowerPlus Pro Workbook It is for example possible to retrieve an historical coupon on a floater (<WRT=190736>).
The field used is FRNCP (Floating Rate Coupon History).
Step
Action
30 July 2005
70
Reuters Financial Software PowerPlus Pro Workbook Step 1 Action In the No. of Rows text box, enter 20.
In the Orientation text box, choose Field values in columns to obtain the instruments in a line.
30 July 2005
71
Reuters Financial Software PowerPlus Pro Workbook Step 3 Action To finish, click Finish. A table appears with selected values and field.
4.7 Retrieving Time Series Data from the Data Backup Unit Server: DeHistory()
The Data Backup Unit Server (DBU) is a realtime historical time series data server that supplies data on request to graphics or Time Series based client applications. It is a source sink library (SSL) application (running through SSLRV for Rendezvous platforms) and can connect to one or more MarketFeed data sources from which it derives both realtime intra-day and inter-day historical time series. The DeHistory() function uses the Data Engine ActiveX (DEX) to retrieve this data from the DBU, which is the preferred source of time series data in Reuters PowerPlus Pro.
4.7.1
Retrieving Time and Sales from DBU:
The objective is to retrieve the last ten Reuters Time and Sales. Reproduce exactly the following table:
Step 1
Action Choose cell B9,and choose Insert -> Function.
30 July 2005
72
Reuters Financial Software PowerPlus Pro Workbook Step 2 Action Choose Reuters 3000 Data Engine in Function Category and DeHistory() in Function name then click OK. The dialog box of the DeHistory() function appears. a. In Code, choose cell C3. b. In TableName, choose cell C4. c. In FieldList choose cell C5. d. In DestinationCell, choose cell C9. e. In MacroName, leave blank. f. In Conditions, choose cell C6 (INTERVAL:TAS for Time and Sales, EVENTS:10 specifies the number of events to retrieve) g. In DeMode, choose cell C7. Then click OK.
To retrieve all available arguments, refer to the online help for the DeHistory() function. The table is built dynamic. By typing another instrument code, for example FTE.PA, in cell C3, the last ten Time and Sales data for that equity is obtained immediately.
4.7.2
Using the Time Series Assistant:
This Time Series Data Assistant is only usable if DBU (Data Backup Unit Server) has been installed. Step Action 1 Choose the Reuters menu, the Assistant option then Time Series Data. The Time Series Data Assistant appears.
30 July 2005
73
Reuters Financial Software PowerPlus Pro Workbook Step 2 Action Enter a few RICs (<RTR.L>, <VOD.L>, <MNU.L>), selected in the Code text box and click Add to pass to the right part of the dialog box called Selected Subjects.
Click Next to pass to the fieldnames selection step in the DBU server. Fields are classified there by topics. In the Field/Frequency option, open the LAST TRADE folder by clicking the preceding + sign. In LAST TRADE, open the TAS option. Double-click the fieldnames to be retrieved to pass to the right part of the dialog box called Selected Fields. Choose the TIMESTAMP and VALUE fields.
4 5
30 July 2005
74
Reuters Financial Software PowerPlus Pro Workbook Step 6 Action Click Next to pass to the step of property result.
7 8 9
Choose the Start Date option (see the preceding figure), then for the Period Start Date field, the date and the time requested Enter 25 in the Data Points field to obtain the last 25 data. Click Next to pass to the step of parameter settings of result format. In Orientation, choose Field values in columns to obtain the instruments online.
30 July 2005
75
Reuters Financial Software PowerPlus Pro Workbook Step 10 Action To finish, click Finish. A table appears with selected values and fields.
The data retrieved are not fixed. It is possible for example to modify the RTR.L code by MSFT.O (in the cell B2) to obtain the same data for Microsoft SA.
4.8 Retrieving a List of Similar Information for an Instrument: DeList()

4.8.1 DeList()
From the Reuters Securities 3000 database, the DeList() function retrieves the constituents of an index. The index is specified in the parameters. From the Reuters Treasury 3000 database this function makes it possible to retrieve lists of RICs, clearing codes, rating sources, and price sources.
4.8.2
Arguments
The DeList() function is composed of seven arguments: Name Description Identifies the instrument name for which data is to be retrieved. Name of the table in Securities 3000 or Treasury 3000 databases from which data are to be retrieved. Name of the table in Securities 3000 or Treasury 3000 databases from which data are to be retrieved. Identifies the top-left position to hold the table returned by the function Name of the macro used to run on retrieval of data or refreshing. String containing one or more conditional parameters separated by semicolons. Defines the format of the results, sets limits on the days for which data are to be retrieved, and specifies whether results are refreshed automatically.
Code TableName FieldList DestinationCell MacroName Conditions DeMode
30 July 2005
76
4.8.3
Example Using DeList() with the Securities Database
In this exercise we demonstrate how to find the index constituents of the Footsie using the DeList() function. Reproduce the following table. Step Action 1 a. In cell B3, enter the Code (a chain in our example: .FTSE for Footsie) b. In cell B4 enter the TableName. When data are retrieved from the Securities 3000 database as it is the case in this exercise, the only TableName argument available is INDEX_CONSTITUENTS. c. In cell B5 enter the DeMode. The keyword SOURCE:EQUITY is used to access the Reuters Equity 3000 database. When the keyword H or HEADER:YES or HE is present in the DeMode argument string, data results include column names. Choose cell D1, and choose Insert -> Function. Choose Reuters 3000 Date Engine in Function Category and DeList in the Function Name then click OK (see the figure in the DeUpdate() part). The dialog box of the DeList() function appears. Enter the arguments to obtain the following formula:
2 3
4 5
=DeList(B3,B4,,D4,,,B5)
Click OK. The constituents of the Footsie Index are displayed.
30 July 2005
77
4.8.4
Example Using DeList() with the Treasury Database
This exercise demonstrates how to find a bond RIC from an ISIN code using the DeList() function. Reproduce the following table.
Step 1
Action a. In cell B3, enter the Code (an ISIN code in our example. b. In cell B4 enter the TableName. Go to the online help to get some more information about table names available in the Reuters 3000 Treasury database. c. In cell B5 enter the DeMode. The keyword SOURCE:TREASURY is used to access the Reuters Treasury 3000 database. When the keyword H or HEADER:YES or HE is present in the DeMode argument string, data results include column names. Choose cell D1, and choose Insert -> Function. Choose Reuters 3000 Date Engine in Function Category and DeList() in the Function Name then click OK (see the figure in the DeUpdate() part). The dialog box of the DeList() function appears. Enter the arguments to obtain the following formula:
2 3
4 5
=DeList(B3,B4,,D4,,,B5)
Click OK. The RICs corresponding to the bond ISIN code are displayed in column D (refer to the preceding picture).
4.8.5
Step 1
Using the Security Lists Assistant

Action Choose the Reuters menu, the Assistant option then Security Lists. The Security Lists Assistant appears.
30 July 2005
78
Reuters Financial Software PowerPlus Pro Workbook Step 2 Action Enter a RIC (or other instrument code) in the Combo box labeled Code and click Add to add the code to the list labeled Selected Subjects.
3 4
Click Next to go to the fieldnames selection step in the Wizard. Fields are classified by topics. In the Fields by Category option, open the Equity folder by clicking the preceding + sign. In Equity, open the Ownership folder and then open the Insider\Stake Holders option. In the Insider\Stake Holders option, choose the Entity Name, Number of Shares and Percentage of shares.
30 July 2005
79
Reuters Financial Software PowerPlus Pro Workbook Step 6 Action Click Next to go to the format step.
In the Orientation text box, choose Field values in columns to obtain the instruments in rows. Click Finish. A table appears with selected values and fields.
The retrieved data is not fixed. It is possible to modify the RTR.L code to MSFT.O, for example, (in cell A1) to obtain the same data for Microsoft.
30 July 2005
80
4.9 Sending SQL Requests to the 3000 Database: DeQuery()

This chapter is recommended only for those users familiar with SQL syntax.
4.9.1
DeQuery()
DeQuery() function sends an SQL request to the Securities or Treasury 3000 database. The request must be a
SELECT request. The Data Engine checks the SQL syntax embedded in the query prior to passing it to the database.
4.9.2
Arguments
DeQuery() function is composed of five arguments. =DeQuery(RequestString, DestinationCell, Conditions, MacroName, DeMode)
Name Description String containing the SELECT request. Identifies the top-left position to hold the table returned by the function. Name of the macro used to run on retrieval of data or refreshing. String containing one or more conditional parameters separated by semicolons. Defines the format of results and whether data are refreshed automatically. This argument is optional.
RequestString DestinationCell MacroName Conditions DeMode
4.9.3
Database Structure
SQL queries enable you to send queries directly to the Securities 3000 database. A web-site, called the Reuters Data Encyclopaedia, is at your disposal on Reuters Web, which details the structure, tables and fields of the Securities 3000 database. To access the site do this. Step Action In Reuters PowerPlus Pro choose Reuters -> Help ->Reuters Data Encyclopaedia.
30 July 2005
81
4.9.4
Example with SQL Requests
SQL request to use to obtain the RIC name from GB0002369139 ISIN code:
SELECT RIC FROM CODE_SRCH WHERE Official_Code = 'GB0002369139' AND Official_Code_Scheme = 'ISN' and primary_quote_ind = 'Y'
Step 1 2 Action Choose cell A5, and choose Insert -> Function. Choose Reuters 3000 Data Engine in Function Category and DeQuery() in Function name then click OK. The dialog box of the DeQuery() function appears. a. b. In RequestString, choose cell B2 that contains the SQL request. In DeMode, choose cell B3.
3 4
Click OK. The RIC name for GB0002369139 ISIN code appears in cell B7.
30 July 2005
82
5 ADFIN BONDS FUNCTIONS

5.1 Adfin Bonds Exercises
5.1.1 Purpose
Adfin Bonds supports a large variety of bond instruments (government bonds, FRN, callable/puttable bonds, stepped coupon bonds, sinking funds, index linked-bonds, latam bonds). This version supports five different pricing models (constant yield, zero coupon curve, Vasicek Fong, Black-Derman-Toy, Hull & White). The Adfin Bonds module covers cash instruments and associated derivatives (such as repos and bond futures). It is also possible to price the following convertible bonds: vanilla, premium redemption, puttable, callable (hard and soft call), Step up and multiple currencies.
5.1.2
Pre-requisite Knowledge
Adfin bonds functions require knowledge of realtime functions, RtUpdate() in particular and the DeUpdate() function.
5.1.3
Before You Begin
Before using the Adfin Bonds calculation functions, you must: retrieve the realtime data for the bonds i.e. the issuer name, the coupon rate, the maturity date, the bid price, the ask price, and the price sources. use the fields DISPLAY NAME, COUPON RATE, MATURITY DATE, PRIM ACT 1, SECOND ACTIVY 1 and CONTRIBUTOR 1 respectively. reproduce the following table (note the cell addresses) by using the RtUpdate() function to obtain real-time links on the selected fields. For more details on the RtUpdate() function, see RtUpdate().
30 July 2005
83
5.2 Structure Concepts

5.2.1 BondStructure Argument
Most of the Adfin Bonds functions use a BondStructure. A BondStructure is a group of arguments that specify all the parameters of the instrument necessary for the accurate generation of cash flows. For example, a BondStructure defines the coupon frequency, the national or international calendar to use, and so on. Reuters PowerPlus Pro also provides some predefined bond styles for the principal types of bonds traded around the world.
5.2.2
Step 1
How to View BondStructure Styles

Action Choose Reuters -> Settings -> Style Management -> Bond Styles.
30 July 2005
84
Reuters Financial Software PowerPlus Pro Workbook Step 2 Action Double click EUR1 European Treasury Bonds (Annual) BondStructure. A dialog box opens indicating all the parameters of calculation taken into account by this BondStructure. You must use EUR1 structure in the BondStructure argument of the bond functions to price a European Treasury bond.
The structures contained in Style Management are generic structures intended to facilitate the use of Adfin Bonds functions.
5.2.3
How to Create a New Bond Structure
You can create your own structures on the basis of the existing structures. Step Action Choose the structure of your choice in the Style Management dialog box then click New. Alternatively, you can specify the set of characteristics of your bond directly in the Adfin Bonds functions (BondStructure argument). This exercise uses the DeUpdate() function to retrieve the BondStructure appropriate to the ADF_BONDSTRUCTURE fieldname.
Note:
Obtain the BondStructure for each bond with the DeUpdate() function in column I:
30 July 2005
85
5.2.4
RateStructure Argument
Most of the Adfin Bonds functions use the RateStructure of a bond. A RateStructure is a group of arguments, which specify the rate model for pricing instruments (yield calculation parameters). For example, a RateStructure defines the day count basis, the yield calculation method, the rate model (Yield Curve, BDT, Vasicek-Fong model), if rates or discount factors are used and so on. Reuters PowerPlus Pro also provides some RateModel styles for the principal types of yield calculation conventions available.
5.2.5
Step 1
How to View RateModel Styles

Action Choose Reuters -> Settings -> Style Management -> Yield To Maturity Styles.
30 July 2005
86
Reuters Financial Software PowerPlus Pro Workbook Step 2 Action Double click AA, Yield To Maturity model. A dialog box opens indicating all the parameters of calculation taken into account by this RateStructure. You must use AA style in the RateStructure argument of bond functions to price a bond instrument using ISMA Yield to Maturity.
The structures contained in Style Management are generic structures intended to facilitate the use of Adfin Bonds functions.
5.2.6
How to Create a New Structure
You can create your own structures on the basis of the existing structures. Step Action Choose the structure of your choice in the Style Management dialog box then click New. Alternatively, you can specify the set of characteristics of your bond directly in the Adfin Bonds functions (RateStructure argument).
DeUpdate() function retrieves RateStructure from the Reuters Treasury 3000 database. See Example
Using DeUpdate() with the Treasury Database. This exercise uses the DeUpdate() function to retrieve the RateStructure appropriate to the ADF_RATESTRUCTURE fieldname. Obtain the RateStructure for each bond with the DeUpdate() function in column J:
30 July 2005
87
5.3 Adfin Bonds Functions

5.3.1 BdSettle()
The BdSettle() function calculates the settlement date using a BondStructure.
5.3.2
Arguments
BdSettle() function is composed of two arguments: =BdSettle(CalcDate, BondStructure)

Name Description Calculation date (trade date, in this case). Extended argument defining the BondStructure.
CalcDate BondStructure
5.3.3
Step 1 2 3
Example Using BdSettle()

Action Enter the current date in cell K4. Use the Excel function =TODAY(). Choose cell K6, and choose Insert -> Function. Choose Adfin Bonds in Function Category and BdSettle() in Function name then click OK. The BdSettle() function dialog box appears. a. In CalcDate, choose cell K4 and specify the absolute address $K$4. b. In BondStructure, choose cell I6. Click OK. The settlement date for the first bond in the table then appears in cell K6.
30 July 2005
88
Reuters Financial Software PowerPlus Pro Workbook 5 Fill down from cell K6 to obtain the settlement dates for all the bonds.
5.3.4
AdBondYield()
AdBondYield() calculates the Yield To Maturity of non-optionable bonds. It calculates the yields at call/put dates of optionable bonds.
5.3.5
Arguments
The AdBondYield() function is composed of eight arguments:
=AdBondYield(SettlementDate, Price, Maturity, Coupon, BondStructure, RateStructure, AdMode)

Name Description Settlement date. Clean or gross price in % of the nominal. Maturity date of the bond. Nominal coupon rate of the bond. Extended argument defining the bond structure. Extended argument defining the rate structure. Extended argument customizing the return value.
SettlementDate Price Maturity Coupon BondStructure RateStructure AdMode
5.3.6
Step 1
Example Using AdBondYield()

Action Choose cell L6, and choose Insert -> Function.
30 July 2005
89
Reuters Financial Software PowerPlus Pro Workbook Step 2 Action Choose Adfin Bonds in Function Category and AdBondYield() in Function name then click OK. The dialog box of the AdBondYield() function appears. a. In SettlementDate, choose cell K6. b. In Price, choose cell F6. c. In Maturity, choose cell E6. d. In Coupon, choose cell D6. e. In BondStructure, choose cell I6. f. In RateStructure, choose cell J6. 1) Put % for the price of the bond and the coupon rate. The price can be expressed in cash, when the NOTIONAL keyword is used in BondStructure to define the face value of the bond. 3 4 Click OK. The yield to maturity for the first bond in the table appears in cell L6. Fill down from cell L6 to obtain the yield to maturity of all bonds.
5.3.7
AdBondDeriv()
The AdBondDeriv() function calculates the derivatives of a bond (Price, Option Free Price, Volatility, PVBP, Duration, Average Life, Convexity) using a bond structure.
5.3.8
Arguments
The AdBondDeriv() function is composed of nine arguments:
=AdBondDeriv(SettlementDate, RateArray, Maturity, Coupon, Spread, BondStructure, RateStructure, CalcStructure, AdMode)

Name Description Settlement date. Yield To Maturity. Maturity date of the bond. Nominal coupon rate of the bond. Spread value. Extended argument defining the bond structure.
SettlementDate RateArray Maturity Coupon Spread BondStructure
30 July 2005
90
Reuters Financial Software PowerPlus Pro Workbook Extended argument defining the rate structure. Extended argument defining the calculation method. Extended argument customizing the return value.
RateStructure CalcStructure AdMode
5.3.9
Step 1 2
Example Using AdBondDeriv()

Action Choose cell M6, and choose Insert -> Function. Choose Adfin Bonds in Function Category and AdBondDeriv() in Function name then click OK. The dialog box of the AdBondDeriv() function appears. a. In SettlementDate, choose cell K6. b. In RateArray, choose cell L6. c. In Maturity, choose cell E6. d. In Coupon, choose cell D6. e. In Spread, put 0. f. In BondStructure, choose cell I6. g. In RateStructure, choose cell J6. 2) Put % for the price of the bond and the coupon rate.
Click OK. The first value (Price) for the first bond in the table appears in cell M6.
AdBondDeriv() is a function that returns an 8-cell array (Price, Option Free Price, Volatility, PVBP, Duration, Average Life, Convexity, Yield To Worst / Yield To Best date) Step Action
1 2 3 4 Choose an array of cells (M6:T6) such that the original cell is at the upper-left corner of the array. Press F2 to edit the function in the cell. Press CTRL+SHIFT+ENTER to convert it into an array function. Fill down from cells M6:T6 to obtain the derivatives of all the bonds.
5.3.10 Accrued()
The Accrued() function calculates the accrued interest using a BondStructure.
30 July 2005
91
5.3.11 Arguments
The Accrued() function is composed of four arguments:
=Accrued(CalcDate, Maturity, Coupon, BondStructure)

Name Description Calculation date (settlement date, in this case). Maturity date of the bond. Nominal coupon rate of the bond. Extended argument defining the bond structure.
CalcDate Maturity Coupon BondStructure
5.3.12 Example Using Accrued()

Step 1 2 Action Choose cell U6, and choose Insert -> Function. Choose Adfin Bonds in Function Category and Accrued() in Function name then click OK. The dialog box of the Accrued() function appears. a. In CalcDate, choose cell K6. b. In Maturity, choose cell E6. c. In Coupon, choose cell D6 (Put %). d. In BondStructure, choose cell I6. Click OK. The accrued interest for the first bond in the table appears in cell U6. Fill down from cell T6 to obtain the accrued interest values of all the bonds.
3 4
5.3.13 AdBondReturn()
The AdBondReturn() function calculates the return on a bond using a bond structure.
5.3.14 Arguments
The AdBondReturn() function is composed of 10 arguments.
=AdBondReturn(PricesArray, EvaluationDate, Maturity, Coupon, Nominal, RatesArray, BondStructure, RateStructure, CalcStructure, AdMode)
Name Description Table defining input buy and sell prices with their value dates (format 2*2). Evaluation date (must be later than or equal to the sell date). Maturity date of the bond. Nominal coupon rate of the bond (expressed in percentage). Nominal amount of the bond. Table defining reinvested and refinancing rates structures and values (format 2*2). Extended argument defining the bond structure.
PricesArray EvaluationDate Maturity Coupon Nominal RatesArray BondStructure
30 July 2005
92
Reuters Financial Software PowerPlus Pro Workbook Name Description Extended argument defining the rate model. Extended argument defining the evaluation method. Extended argument customizing the return value.
RateStructure CalcStructure AdMode
5.3.15 Example Using AdBondReturn()

Reproduce the following table.
Step 1 2
Action Choose cell A16, and choose Insert -> Function. Choose Adfin Bonds in Function Category and AdBondReturn() in Function name then click OK. The dialog box of the AdBondReturn() function appears. a. In PricesArray, choose cells B3:C4 the buy and sell dates and prices. b. In EvaluationDate, choose cell B5 the evaluation date c. In Maturity, choose cell B6 the maturity date of the bond. d. In Coupon, choose cell B7 the coupon rate of the bond. e. In Nominal, choose cell B8. f. In RatesArray, choose cells B9:C10 the refinancing and reinvesting rates structures and values. g. In BondStructure, choose cell B11. h. In RateStructure, choose cell B12. i. In CalcStructure, choose cell B13. j. In AdMode, choose cell B14.
30 July 2005
93
Reuters Financial Software PowerPlus Pro Workbook Step 3 Action Click OK. The header Total Return appears in cell A16.
AdBondReturn() is a function that returns a 15-cell array.

Step 1 2 3 Action Choose an array of cells (A16:B30) such that the original cell is at the upper-left corner of the array. Press F2 to edit the function in the cell. Press CTRL+SHIFT+ENTER to convert it into an array function.
5.3.16 ABondProceeds()
The AdBondProceeds() function calculates the proceeds of a bond for a given quote price and nominal amount the return on a bond using a bond structure.
5.3.17 Arguments
The AdBondReturn() function is composed of seven arguments:
30 July 2005
94
=AdBondReturn(SettlementDate, Price, Maturity, Coupon, Nominal, BondStructure, AdMode)

Name Description Settlement date. Quoted Price Maturity date of the bond. Nominal coupon rate of the bond (expressed in percentage). Nominal amount of the bond. Extended argument defining the bond structure. Extended argument customizing the return value.
SettlementDate Price Maturity Coupon Nominal BondStructure AdMode
5.3.18 Example Using AdBondProceeds()

Step 1 2
Action Choose cell A11, and choose Insert -> Function. Choose Adfin Bonds in Function Category and AdBondProceeds() in Function name then click OK. The dialog box of the AdBondProceeds() function appears. a. In SettlementDate, choose cell B3 the settlement date. b. In Price, choose cell B4 the price of the bond. c. In Maturity, choose cell B5 the maturity date of the bond. d. In Coupon, choose cell B6 the coupon rate of the bond. e. In Nominal, choose cell B7. f. In BondStructure, choose cell B8. g. In AdMode, choose cell B9.
30 July 2005
95
Reuters Financial Software PowerPlus Pro Workbook Step 3 Action Click OK. The header Total Proceeds appears in cell A11.
AdBondProceeds() is a function that returns a 4-cell array.

Step 1 2 3 Action Choose an array of cells (A11:A14) such that the original cell is at the upper-left corner of the array. Press F2 to edit the function in the cell. Press CTRL+SHIFT+ENTER to convert it into an array function.
5.3.19 CfYld()
The CfYld() function calculates the yield from price using cash flows.
5.3.20 Arguments
CfYld() function is composed of five arguments. =CfYld(CalcDate, GrossPrice, CfDates, Cf, CalcMethod)
Name Description Calculation date (settlement date, in this case). Clean price plus accrued interest expressed in the same unit as cash flows
CalcDate GrossPrice
30 July 2005
96
Reuters Financial Software PowerPlus Pro Workbook Array of cash flow dates corresponding to the bond reimbursement Array of cash flow values corresponding to the bond reimbursement Extended argument defining the calculation method
CfDates Cf CalcMethod
5.3.21 Example Using CfYld()

Step 1 2 3 4 5
Action Choose cell B10, and choose Insert -> Function. Choose the Adfin Bonds category then the CfYld() function and click OK. Choose cell B1 in CalcDate then B2 in GrossPrice. Choose the range A4:A8 in CfDates then B4:B8 in Cf. Enter CLDR:EMU CM:AA in CalcMethod to specify the calendar and the calculation method used. Click OK.
30 July 2005
97
Reuters Financial Software PowerPlus Pro Workbook Step Action
Follow the same procedure in cells B11 and B12 to calculate the duration and modified duration using the functions CfDur() and CfVol(), respectively.
5.4 Floating Rates Notes

5.4.1 Objective
The objective is to calculate the price and the cash flows from a Zero-coupon curve of a Euro-Floating Rates Notes. Reproduce the following table for the Floating Rates Notes calculation:
30 July 2005
98
5.4.2
AdFrnPrice()
The AdFrnPrice() function calculates the price from a yield curve of a floating rate instrument.
5.4.3
Arguments
AdFrnPrice() function is composed of 11 arguments: =AdFrnPrice(SettlementDate, RateArray, Maturity, Spread, QuotedMargin, CurrentIndex, ProjectedIndex, FrnStructure, RateStructure, IndexStructure, AdMode)
Name Description Settlement date. Term structure or data array related to the rate model used to price the instrument. Maturity date of the FRN. Discount margin input in basis points. Margin applied to the index. Index rate for the current coupon. Array of future index rates used for coupon calculation. Extended argument defining the FRN structure. Extended argument defining the rate model. Extended argument defining the rate model to be used to calculate the coupon value. Extended argument customizing the return value.
SettlementDate RateArray Maturity Spread QuotedMargin CurrentIndex ProjectedIndex FrnStructure RateStructure IndexStructure AdMode
5.4.4
Step 1
Example Using AdFrnPrice()

Action Choose cell B16, and choose Insert -> Function.
30 July 2005
99
Reuters Financial Software PowerPlus Pro Workbook Step 2 Action Choose Adfin Bond in Function Category and AdFrnPrice() in Function name then click OK. The dialog box of the AdFrnPrice() function appears. a. In SettlementDate: choose cell C5 the settlement date. b. In RateArray: choose cells E4:F18 the zero-coupon yield curve. c. In Maturity: choose cell C7 the maturity date. d. In Spread: choose cell C8 the discount margin. e. In QuotedMargin: choose cell C9 the margin applied to the index. f. In CurrentIndex: choose cell C10 the index rate for the current coupon. g. In ProjectedIndex: leave it blank. The value of the current index will be used. h. In FrnStructure: choose cell C12. i. In RateStructure: choose cell C11. j. In IndexStructure: leave it blank. The model of the current index will be used. k. In AdMode: choose cell C13. Click OK. The header Price appears in cell B16.
AdFrnPrice() is a function that returns a 2-column array (Price, Option Free Price).
Step 1 2 3 Action Choose an array of cells (B16:C17) such that the original cell is at the upper-left corner of the array. Press F2 to edit the function in the cell. Press CTRL+SHIFT+ENTER to convert it into an array function.
5.4.5
AdFrnCashFlows()
The AdFrnCashFlows() function generates an array with the remaining cash flows of a floating rate instrument.
30 July 2005
100
5.4.6
Arguments
The AdFrnCashFlows() function is composed of eight arguments:
=AdFrnCashFlows(SettlementDate, Maturity, QuotedMargin, CurrentIndex, ProjectedIndex, FrnStructure, IndexStructure, AdMode)

Name Description Settlement date. Maturity date of the FRN. Margin applied to the index. Index rate for the current coupon. Array of future index rates used for coupon calculation. Extended argument defining the FRN structure. Extended argument defining the rate model to be used to calculate the coupon value. Extended argument customizing the return value.
SettlementDate Maturity QuotedMargin CurrentIndex ProjectedIndex FrnStructure IndexStructure AdMode
5.4.7
Step 1 2
Example Using AdFrnCashFlows()

Action Choose cell B21, and choose Insert -> Function. Choose Adfin Bond in Function Category and AdFrnCashFlows() in Function name then click OK. The dialog box of the AdFrnCashFlows() function appears. a. In SettlementDate: choose cell C5 the settlement date. b. In Maturity: choose cells C7 the maturity date of the FRN. c. In QuotedMargin: choose cell C9 the margin applied to the index. d. In CurrentIndex: choose cell C10 the index rate for the current coupon. e. In ProjectedIndex: choose cells E4:F18 the zero coupon yield curve. f. In FrnStructure: choose cell C12. g. In IndexStructure: choose cell C11. h. In AdMode: write IAC RET:A50. IAC keyword is used to return a 4-column table. RET:Ai is used to return the first i values of the default array. Click OK. The first Payment Date of the floating rate note appears in cell B20.
AdFrnCashFlows() is a function that returns a 4-column array (Payment Date, Cash flows values, Capital Value
and Coupon Rate). Step Action 1 2 Choose an array of cells (B21:E26) such that the original cell is at the upper-left corner of the array. Press F2 to edit the function in the cell.
30 July 2005
101
Reuters Financial Software PowerPlus Pro Workbook Step 3 Action Press CTRL+SHIFT+ENTER to convert it into an array function.
5.4.8
AdFrnMargin()
The AdFrnMargin() function calculates the price, the margin, or the yield of a floating rate note.
5.4.9
Arguments
The AdFrnMargin() function is composed of 11 arguments:
=AdFrnPrice(SettlementDate, Maturity, QuotedMargin, CurrentIndex, ProjectedIndex, InputMargin, RepoRate, FrnStructure, RateStructure, IndexStructure, AdMode)
Argument Description
SettlementDate Settlement date. Maturity QuotedMargin CurrentIndex InputMargin RepoRate FrnStructure RateStructure
Maturity date of the FRN (expressed as a date or a code such as 1Y). Margin applied to the index. Index rate for the current coupon.
ProjectedIndex Array of future index rates used for coupon calculation.

Margin of input (gross, clean, simple margin etc.). Depends on the FROM keyword in AdMode. Reinvestment rate. Extended argument defining the FRN structure. Extended argument defining the rate model. coupon value.
IndexStructure Extended argument defining the rate model to be used to calculate the AdMode
Extended argument customizing the return value.
30 July 2005
102
5.4.10 Return Value

The return value depends on the keyword used in the AdMode argument: FROM (input) and RES (output) use the same keywords. The return value is one of the following: Clean price (RES:PXC ) Gross price (RES:PXG ) Adjusted price (RES:PXA ) Simple margin (RES:SM ) Adjusted simple margin (RES:ASM ) Adjusted total margin (RES:ATM ) Discounted margin (RES:DM ) Yield to maturity (RES:YTM ) Yield to maturity spread (RES:YIELD) YTWYTB Date (RES:YTWYTB) Strike (RES:STRIKE) Event type (RES:EVENT)
5.4.11 Example Using AdFrnMargin()

Step 1 Action From cell C2 to cell C11, enter the FRN features: SettlementDate (CalcDate), StartDate, MaturityDate, Px (margin or yield), CurrentIndex, ProjectedIndex, QuotedMargin, RepoRate, FrnStructure, RateStructure. From cell D16 to cell D27, enter the FrnMode keyword for each expected return value: FROM:XXX RES:XXX
30 July 2005
103
Reuters Financial Software PowerPlus Pro Workbook Step 3 Action a. b. Select cell C1 and insert the AdFrnMargin() function. Enter all arguments requested to obtain the following formula:
=AdFrnMargin($C$2,$C$4,$C$8,$C$6,$C$7,$C$5,$C$9,$C$10,$C$1 1,,D16)
Click OK to obtain the Clean Price. Use the AdFrnMargin() function to display other types of output rate for AdFrnMargin.
To display coupon features, you can use AdFrnCalcCpn()array function. Step Action
30 July 2005
104
Reuters Financial Software PowerPlus Pro Workbook 1 Select cell G16 and insert the AdFrnCalcCpn() function Enter all arguments requested as follows:
=AdFrnCalcCpn(C2,C4,C8,C6,C7,C10,C11,"LAY:V RET:A5")
2 3 4 5
Click OK. The previous coupon date appears in cell G16. Choose an array of cells (G16:G20) such as the original cell is at the upper-left corner of the array. Press F2 to edit the function in the cell. Press CTRL+SHIFT+ENTER to convert it into an array function.
5.4.12 AdFrnYield()
The AdFrnYield() function calculates the yield to maturity of non-optionable FRNs and also the yields at call/put dates of optionable FRNs.
5.4.13 Arguments
The AdFrnYield() function is composed of ten arguments: Argument Description
30 July 2005
105
SettlementDate Settlement date. Price Maturity QuotedMargin CurrentIndex

Clean or gross price of the FRN expressed as a percentage of the nominal. Maturity date of the FRN. Margin applied to the index. Index rate of the current coupon.
ProjectedIndex Array of future index rates used for coupon calculation. FrnStructure RateStructure
Extended argument defining the FRN structure. Extended argument defining the rate model. coupon value. Extended argument customizing the return value.
5.4.14 Example using AdFrnYield()

Reproduce the following table:
Step 1 2
Action Choose cell C12, and choose Insert -> Function. Choose Adfin Bond in Function Category and AdFrnYield() in function name then click OK. The dialog box of the AdFrnYield() function appears. a. In SettlementDate: choose cell C2 the settlement date. b. In Price: choose cell C5 the price of the FRN. c. In Maturity: choose cell C4 the maturity date of the FRN. d. In QuotedMargin: choose cell C8 the margin applied to the index. e. In CurrentIndex: choose cell C6 the index rate of the current coupon. f. In ProjectedIndex: choose cell C7. g. In FrnStructure: choose cell C9. h. In RateStructure: choose cell C10. i. In IndexStructure: leave it blank. The model of the current index will be used. j. In AdMode: leave it blank.
30 July 2005
106
Reuters Financial Software PowerPlus Pro Workbook 3 Click OK. The yield to maturity of the non-optionable FRN appears in cell C12.
The AdFrnYield()function can also calculates yields at call/put dates of optionable FRNs. Step Action 1 Define in cells C15:C16 a new FRN structure and a new rate structure: FRN Structure:FRQ:1 CALL:12DEC05:0.95 PUT 15MAY07:1.1 Rate Structure:RM:YTB EY:4 RATEFRQ:ZERO DCB:A0 RATETYPE:MM
CLDRADJ:CLDR
2 3 Choose cell B18, and choose Insert -> Function. Choose Adfin Bond in Function Category and AdFrnYield() in function name then click OK. Enter all the requested arguments as follow:
=AdFrnYield(C2,C5,C4,C8,C6,I3:J49,C14,C15)
4 5 6
Click OK. The first FRN Yield appears in cell B18. Choose an array of cells (B18:E21) such that the original cell is at the upper-left corner of the array. Press F2 to edit the function in the cell.
30 July 2005
107
Reuters Financial Software PowerPlus Pro Workbook 7 Press CTRL+SHIFT+ENTER to convert it into an array function.
5.4.15 AdfrnDeriv()
The AdFrnDeriv() function calculates the price of an FRN and its market sensitivities.
5.4.16 Arguments
The AdFrnDeriv() function is composed of 11 arguments: Name Description
SettlementDate Settlement date. RateArray Maturity

Term structure or data array related to the rate model used to price the instrument. Maturity date of the FRN.
DiscountMargin Discount margin input in basis points. QuotedMargin CurrentIndex

Margin applied to the index. Index rate for the current coupon.
ProjectedIndex Array of future index rates used for coupon calculation. FrnStructure RateStructure
Extended argument defining the FRN structure. Extended argument defining the rate model. coupon value.
Extended argument customizing the return value.
5.4.17 Example Using AdFrnDeriv()

30 July 2005
108
Step 1 2
Action Choose cell B15, and choose Insert -> Function. Choose Adfin Bond in Function Category and AdFrnDeriv() in Function name then click OK. The dialog box of the AdFrnDeriv() function appears. a. In SettlementDate: choose cell C5 the settlement date. b. In RateArray: choose cells E4:F18 the zero-coupon yield curve. c. In Maturity: choose cell C6 the maturity date. d. In DiscountMargin: choose cell C7 the discount margin. e. In QuotedMargin: choose cell C8 the margin applied to the index. f. In CurrentIndex: choose cell C9 the index rate for the current coupon. g. In ProjectedIndex: leave it blank. The value of the current index will be used. h. In FrnStructure: choose cell C11. i. In RateStructure: choose cell C10. j. In IndexStructure: leave it blank. The model of the current index will be used. k. In AdMode: choose cell C12. Click OK. The header Price appears in cell B15.
30 July 2005
109
AdFrnDeriv() is a function that returns a 9-cell array (Price, Option free price, Spread duration, Index duration,
PVBP, Duration, Average life, Convexity, YTW/YTB date). Step Action 1 2 3 Choose an array of cells (B15:C23) such as the original cell is at the upper-left corner of the array. Press F2 to edit the function in the cell. Press CTRL+SHIFT+ENTER to convert it into an array function.
5.4.18 Australians FRNs

Adfin pricing of FRNs now extends to Australian FRNs. To apply the discount margin days per year of 365 for Australian FRN calculations add the keyword DMDY:365 to the FrnStructure.
5.5 Convertible Bonds

Reuters PowerPlus Pro V.5.0 supports the following convertible instruments that can be priced: vanilla, premium redemption, puttable, callable (hard and soft call), step up and multiple Currency. Adfin Bonds provides two groups of function: Convertible-oriented (price, premium, implied vol, ratios and Greeks). Bond-oriented (cashflows, bond derivatives, yield). According to the following models: One-factor model where the diffusion process concerns the stock price (in a tree for the binomial tree, in a mesh for finite differences). A two-factor model (two-factor trinomial tree, with Hull & White process for the rate factor).
5.5.1
Retrieving Convertible Bond Data
To calculate a convertible bond price and its derivatives, you must retrieve the following data via data mapping (see DeUpdate()): Field Names Results returned by DeUpdate() The settlement rule The coupon rate of the convertible bond The maturity date of the convertible bond
ADF_SETTLE ADF_COUPON ADF_MATDATE
30 July 2005
110
Reuters Financial Software PowerPlus Pro Workbook Field Names Results returned by DeUpdate() The structure of the convertible bond The ratestructure of the convertible bond
ADF_STRUCTURE ADF_RATESTRUCTURE
Reproduce the following table: Step Action 1 2
Choose cell B4, and choose Insert -> Function. Selection Reuters 3000 Data Engine in Function category and DeUpdate() in Function name then click OK. The dialog box of the DeUpdate() function appears. DeUpdate() is composed of six arguments (see DeUpdate() for more information). a. b. In CodeList: choose the RIC of the convertible bond (FR009458468=, cell C4). In FieldList: choose the field names (ADF_SETTLE, ADF_COUPON,
ADF_MATDATE, ADF_STRUCTURE, ADF_RATESTRUCTURE, ADF_BONDSTRUCTURE; cells B5:B10).

c. d. 4 In DestinationCell: choose cell C5. In DeMode: type SOURCE:TREASURY LAY:V.
Click OK. The settlement rule, coupon rate, maturity date, convstructure and ratestructure appear in array of cells C5:C10.
An equity historical volatility, needed for the convertible bond calculations, can be retrieved from the Reuters database. Step Action 1 2 Choose cell B13, and choose Insert- > Function. Selection Reuters 3000 Data Engine in Function category and DeUpdate() in Function name then click OK. The dialog box of the DeUpdate() function appears. DeUpdate() is composed of 6 arguments (see DeUpdate() for more information). a. b. c. d. In CodeList: choose the RIC of the underlying stock (AXAF.PA; cell C13). In FieldList: choose the field name (VOLATILITY_90D; cell B14). In DestinationCell: choose cell C14. In DeMode: type SOURCE:EQUITY.
30 July 2005
111
Reuters Financial Software PowerPlus Pro Workbook Step 4 Action Click OK. The historical 90 days volatility for the underlying equity appears in cell C14.
Retrieve the realtime data for the underlying equity. Step Action 1 2 Choose cell B16, and choose Insert -> Function. Selection Adfin Real Time in Function category and RtUpdate() in Function name then click OK. The dialog box of the RtUpdate() function appears. RtUpdate() is composed of six arguments ( see RtUpdate() for more information). a. In SourceAlias: type IDN. b. In InstrumentArray: choose the RIC of the underlying stock (AXAF.PA; cell C17). c. In FieldNameArray: choose the field names (LAST, YIELD; cells B18:B19). d. In DestinationCell: choose cell C18. e. In RtMode: type LAY:V. Click OK. The last quoted price and the annual continuous dividend rate appear in cells C18:C19.
5.5.2
AdConvPrice()
AdConvPrice() calculates the main values of a convertible bond using a conversion structure (Theoretical price, Equity premium, Option premium, Total premium, Straight value, Break-even period).
5.5.3
Arguments
The AdConvPrice() function is composed of nine arguments. Name Description
SettlementDate
Settlement date.
30 July 2005
112
Reuters Financial Software PowerPlus Pro Workbook Name Description Maturity date of the convertible bond. Coupon rate, expressed as a percentage. Argument defining the interest rate model in the bond currency: Single-factor models: single interest rate or zerocoupon array Two-factor model: a [dates/rates/rate volatilities/mean reversions] array Spot price of the underlying instrument. Volatility of the underlying instrument (expressed as a percentage). Annual dividend rate or array of dividend dates and amounts. Spot rate for the cross currency. Credit spread of the issuer expressed in basis points. Extended argument defining the convertible structure. Extended argument defining the structure of the interest rate model. Extended argument defining the calculation method. Extended argument customizing the return value.
Maturity Coupon RateArray
EquityPrice EquityVolatility EquityDivArray SpotFX Spread ConvStructure RateStructure CalcStructure AdMode
5.5.4
Step 1 2
Example Using AdConvPrice()

Action Choose cell B28, and choose Insert -> Function. Choose Adfin Bond in Function Category and AdConvPrice() in Function name then click OK. The dialog box of the AdConvPrice() function appears. a. b. c. d. e. f. g. h. i. j. k. l. In SettlementDate: choose cell C22 the settlement date (calculated with the
BdSettle () function using the ADF_BONDSTRUCTURE).

In Maturity: choose cell C7 the maturity date. In Coupon: choose cell C6 the coupon rate (in percentage). In RateArray: choose cell C23 the risk free rate (in percentage). In EquityPrice: choose cell C18 the spot price of the underlying. In EquityVolatility: choose cell C14 the volatility of the underlying (in percentage). In EquityDivArray: choose cell C19 the dividend rate (in percentage). In SpotFX: leave blank. In Spread: choose cell C24 the credit spread in basis point. In ConvStructure: choose cells C8:D8 the structure of the convertible bond. In RateStructure: choose cell C9. In CalcStructure: choose cell C25. CMT:TREE keyword specifies that the calculation model type is tree model (by default a tree is a one-factor tree); TITER:30 specifies that the number of discrete time steps is 30. m. In AdMode: choose cell C26.
30 July 2005
113
Reuters Financial Software PowerPlus Pro Workbook Step 4 Action Click OK. The header price appears in cell B28.
AdConvPrice() is a function that returns a 6-cell array (Theoretical price, Equity premium, Option premium, Total
premium, Straight value, Break-even period). Step Action 1 2 3 Choose an array of cells (B28:C33) such as the original cell is at the upper-left corner of the array. Press F2 to edit the function in the cell. Press CTRL+SHIFT+ENTER to convert it into an array function.
5.5.5
Example: The Cheapest to Deliver
This exercise demonstrates how to calculate in realtime the cheapest to deliver bond from the Bund futures contract. Follow these steps to calculate the cheapest to deliver bond: Retrieve the RICs corresponding to the bond futures contract; Calculate the conversion factor. This can be done using the BdConvFactor() function; Calculate the futures price (=Price of the bond futures contract * Conversion factor);
30 July 2005
114
Reuters Financial Software PowerPlus Pro Workbook Step 1 Calculate the implied repo rate. The highest positive implied repo rate gives the CTD bond. Action Enter the following: a. In cell A1 the Bund contract code FGBL . b. In cell B1 the maturity code (for example M5 for June 2005 / cf. Page <RULES2> via RTW or Kobra workstations). c. In cell A8, enter the RtChain() function. d. IDN in Source Alias. Reproduce the following RICs: <FGBLU0=DLV> a. Enter A1&B1&"=DLV" in InstrumentCode. b. Choose cell B8 in DestinationCell. c. Enter LIVE:YES in RtMode to ensure all changes to the basket of deliverable bonds are considered. Click OK. All RICs appear in cells B8:B11.
Use the RtUpdate() function in cell B7 to retrieve realtime data for the following fields: DISPLAY NAME, COUPON RATE, MATURITY DATE, PRIM ACT 1.
30 July 2005
115
Reuters Financial Software PowerPlus Pro Workbook Step 5 Action Do the following: a. In column G, retrieve the BondStructures for all bonds with the DeUpdate() function b. In column H, calculate the settlement date with the BdSettle() function c. In column I, the yield to maturity with the AdBondYield() function
d.
In column J, calculate the conversion factor of the bonds with the Apply the percentage format (%) to the Coupon argument.
BdConvFactor() function
e.
In column K, calculate the future price for each bond. Future price = price of the bond futures contract (in cell B4) * conversion (in column J). Enter the AdStyleAttribute() function in cell D3 to find the nominal rate of the bond futures contract. a. In StyleTable, enter BDFUTURE. b. In StyleCode, choose cell A1. c. In StyleAttribute, enter RATE. The nominal rate of the bond futures contract appears in cell D3.
30 July 2005
116
Reuters Financial Software PowerPlus Pro Workbook Step 8 Action In D4, enter the BdConvFactor() function. a. In BondFutStructure, choose cell A1. b. In MaturityCode, choose cell B1. c. In Maturity, choose cell E10. d. In Coupon, choose cell D3. e. In BdMode, enter RET:3.
The end date of the delivery period appears in cell D4. 9 Set up the function call: a. In column L, enter the BdRepo() function. b. In CalcDate, choose the calculation date (column H). c. In HorizonDate, choose the end date of delivery period (cell D4). d. In NPV, choose the price of the bond in percentage format (column F). e. In FV, choose the future value in percentage format (column K). f. In RepoRate, enter nothing. g. In Maturity, choose the maturity date of the bond (column E). h. In Coupon, choose the coupon rate of the bond in percentage format (column D). i. In BondStructure, choose the BondStructure of the bond (column G). j. In RepoMode, enter RES:IMPRATE NPV:C FV:C The implied repo rate appears in the range L8:L11. The highest positive implied repo rate gives the cheapest-to-deliver bond.
5.5.6
BdConvFactor() Arguments
This function is composed of six arguments. Name Description
BondFutStructure MaturityCode
Extended argument defining the bond futures structure. String defining the 3-month futures contract maturity (example: "Z6").
30 July 2005
117
Reuters Financial Software PowerPlus Pro Workbook Name Description Maturity date of the bond. Nominal coupon rate of the bond. Extended argument defining deliverable bond oddities. Extended argument customizing the return value.
Maturity Coupon ConvMode BdMode
30 July 2005
118
6 REUTERS ADFIN TERMSTRUCTURE FUNCTIONS

6.1 Overview of Adfin TermStructure Functions
Adfin Term Structure enables you to build zero coupon rates or discount factor curves by market of quotation. The curves of zero coupon rates can be created using deposits, futures, bonds or swaps. A selection made up of swap rates or bond rates can be used to extend the zero coupon curve to long maturities. The following Yield Curve models can be generated for pricing and spread calculations: Zero Coupon curve, Vasicek Fong curve, Basis Splines. It is also possible to calibrate dynamic term structure models using market quoted volatilities. Furthermore Adfin calculates adjustment values for convexity bias on short-term interest rate future using the Hull and White model.
6.2 Zero Coupon Yield Curve: Bootstrapping Method

The objective is to build a European Zero Coupon yield curve based on deposits and Government Bonds. Reproduce the following table for Zero Coupon Curve calculation (C6:H25): Instrument Type (D for deposits, B for Bonds) Maturity date and Coupon rate for each Bond (see Integration of Reuters 3000 Databases for more information) Bid price for Deposit and Bond instruments (can be retrieved from Realtime, see Integration of Realtime Data: Adfin Real Time Functions ). Instrument structure for Deposit (EUR) and Bond (EUR1) instruments
Calculate the Start and Maturity dates of the short period curve. Step Action 1 Choose cell D6, and choose Insert -> Function.
30 July 2005
119
Reuters Financial Software PowerPlus Pro Workbook Step 2 Action Choose Adfin Forex & MM in Function Category and FxCalcPeriod() in Function name then click OK. The dialog box of the FxCalcPeriod() function appears.
The FxCalcPeriod() function is composed of four arguments: Name Description
CalcDate Cur1Cur2 Period FxMode

Step 1 Action a. b. c.
Calculation date (trading date or spot date). Cross currency code (example: "EURGBP"). Period code (example: "1M"). Extended argument customizing the return value.
In CalcDate: choose the trading date (E3). Put the absolute address ($E$3). In Cur1Cur2: enter the cross currency code (EUR, cell H6). In Period: enter the period code (ON located in the column B).
Click OK. The start date for the first period code (ON) appears in cell D6.
FxCalcPeriod() is a function that returns a 4-cell array (Start date and End date, Adjustment explanation, nonadjusted end date). Step Action 1 2 3 Choose an array of cells (D6:E6) so that the original cell is at the left hand side of the array. Press F2 to edit the function in the cell. Press CTRL+SHIFT+ENTER to make it an array function. Fill down from cells D6:E6 to obtain the Start and End dates of all the deposits.
Calculate the Start date of the long part of the curve (maturity greater than 2 years): Step Action 1 2 Choose cell D15, and choose Insert -> Function. Choose Adfin Bonds in Function Category and BdSettle() in Function name then click OK. The dialog box of the BdSettle() function appears.
30 July 2005
120
Reuters Financial Software PowerPlus Pro Workbook The BdSettle() function is composed of two arguments: Name Description
CalcDate BondStructure
Step 1 2 Action a. b.
Calculation date. Extended argument defining the bond structure
In CalcDate: choose the trading date (E3). Put the absolute address ($E$3). In BondStructure: choose cell H15.
Click OK. The start date for the first bond in the table appears in cell E15. Fill down from cell E15 to obtain the start date of all the bonds.
Calculate the Zero Coupon Curve using Deposit and Bond data: Step Action 1 2 Choose cell C30, and choose Insert -> Function. Choose Adfin TermStructure in Function Category and AdTermStructure() in Function name then click OK. The dialog box of the AdTermStructure() function appears.
The AdTermStructure() function is composed of three arguments: Name Description
InstrumentArray RateStructure AdMode

Step 1 Action a. b.
Array of instruments as described later. Extended argument defining the structure of the interest rate model. Extended argument customizing the return value.
In InstrumentArray: choose cells-array C6:H25. In RateStructure: choose cell D27 with the following keywords:
RM:YC DCB:AA RATETYPE:CMP IM:LIN ZCTYPE:RATE.

This structure means that the Rate Model used is a Yield Curve, the Day Count Basis is Actual/Actual, the RATETYPE is compounded, the Interpolation Method is Linear and the Yield Curve is composed of rates.
30 July 2005
121
Reuters Financial Software PowerPlus Pro Workbook Step 2 Action Click OK. The First Zero Coupon date in the table appears in cell C30.
AdTermStructure() calculates a term structure from an instrument array.

Step 1 2 3 Action Choose an array of cells (C30:D49) such as the original cell is at the upper-left hand corner of the array. Press F2 to edit the function in the cell. Press CTRL+SHIFT+ENTER to make it an array function.
6.3 Calculate a Forward Yield Curve

The objective is to build a forward European Zero Coupon yield curve from a spot yield curve. Reproduce the following table:
30 July 2005
122
Reuters Financial Software PowerPlus Pro Workbook a Zero Coupon yield curve starting the 9th May 2004. a forward curve start date (24th May 2005) Forward Period codes (6M, 1Y).
6.3.1
Step 1 2
Calculate the Maturity Dates of the Corresponding Forward Periods

Action Choose cell E5, and choose Insert -> Function. Choose Adfin Forex & MM in Function Category and FxCalcPeriod() in Function name then click OK. The dialog box of the FxCalcPeriod() function appears.
As explained previously the FxCalcPeriod() is composed of four arguments. Step Action 1 a. In CalcDate: choose the forward curve start date (E2). Put the absolute address ($E$2). b. In Cur1Cur2: enter the cross currency code EUR. c. In Period: enter the period code (6M: cell D5). d. To retrieve the End Date: insert RET:2 in FxMode. Click OK. Fill down from cell E5 to cell E15 to obtain the End dates of all forward periods.
6.3.2
Calculate the Forward Zero Coupon Curve from the Table Previously Defined
Action Choose cell F5, and choose Insert -> Function. Choose Adfin TermStructure in Function Category and AdRate() in Function name then click OK. The dialog box of the AdRate() function appears.
Step 1 2
30 July 2005
123
Reuters Financial Software PowerPlus Pro Workbook The AdRate() function is composed of five arguments: Name Description
CalcDate DateArray RateArray
Calculation date of the yield curve. Input array of dates. Term Structure array. Depending on the model, this array has several forms: a single value if the Yield To Maturity YTM is used, a 1 dimensional array containing Vasicek-Fong coefficient if VF is specified, a 2-dimensional array containing the dates (row or column depending on the array orientation specified with the LAY keyword) and the values for the rates/discount factors if a ZCCurve is used, a 2dimensional array containing the dates (row or column depending on the array orientation specified with the LAY keyword), the values for the rates/discount factors and the volatilities if BDT is used, a 2-dimensional array containing the dates, the values for the rates/discount factors, the volatilities and the mean-reversion if HW is used. The model used is specified through the keyword RM (Rate Model) in the RateStructure argument. Extended argument defining the interest rate model. Extended argument customizing the return value.
RateStructure AdMode
Step 1 Action
a. In CalcDate: choose the forward start date of the curve: cell E2. b. In DateArray: choose the array of forward dates E5:E15. c. In RateArray: choose in this array argument the spot yield curve (Dates and Zero Coupon rates): A3:B23. d. In RateStructure: choose cell D17 with the following keywords: RM:YC DCB:AA
RATETYPE:CMP IM:LIN ZCTYPE:RATE

This structure means that the Rate Model used is a Yield Curve, the Day Count Basis is Actual/Actual, the RATETYPE is compounded, the Interpolation Method is Linear and the Yield Curve is composed of rates. 2 Click OK. The First Forward Discount Factor in the table appears in cell F5.
AdRate() is an array function containing forward discount factors.

Step 1 2 Action Choose an array of cells (F5:F15) such as the original cell is at the upper-left hand corner of the array. Press F2 to edit the function in the cell.
30 July 2005
124
Reuters Financial Software PowerPlus Pro Workbook Step 3 Action Press CTRL+SHIFT+ENTER to make it an array function.
6.3.3
Step 1 2
Convert the Discount Factors to Zero Coupon Rates

Action Choose cell G5, and choose Insert -> Function. Choose Adfin TermStructure in Function Category and AdRateConv() in Function name then click OK. The dialog box of the AdRateConv() function appears.
The AdRateConv() function is composed of four arguments: Name Description
StartDate EndDate RateMode Rate

Step 1 Action
Start date of the period. End date of the period. Extended argument defining the type of conversion. Rate to be converted.
a. In StartDate: choose the forward start date of the curve: cell E2 (Put the absolute address ($E$2). b. In EndDate: choose the end date of the forward period (E5 for 6M). c. In RateMode: define your rate mode. In the example, FROM:DF TO:AA, means from Discount Factors to Actual/Actual rates. d. In Rate: choose cell F5 (the discount factor).
30 July 2005
125
Reuters Financial Software PowerPlus Pro Workbook Step 2 Action Click OK. Fill down from cell G5 to cell E15 to obtain the rates corresponding to all forward discount factors.
6.4 Zero Coupon Yield Curve: Vasicek-Fond Method

The objective is to build from scratch a European Zero Coupon yield curve based on European Government Bonds only using the Vasicek-Fong method. Reproduce the following table for Zero Coupon Curve calculation (C6:H55): Instrument Type (B for Bonds). Maturity date and Coupon rate for each Bond (can be retrieved from the database, see Integration of Reuters 3000 Databases). Bid market price for Bond instruments (can be retrieved from Real Time). Bond Structure (use a BondStyle such as EUR1 or retrieve the BondStructure as explained in the part Integration of Reuters 3000 database).
30 July 2005
126
6.4.1
Step 1 2
Calculate the Start Date for Each Bond

Action In the exercise, choose cell D8, and choose Insert -> Function. Choose Adfin Bonds in Function Category and BdSettle() in Function name then click OK. The dialog box of the BdSettle() function appears.
As explained previously, the BdSettle() function is composed of two arguments. Step Action 1 a. b. In CalcDate: choose the trading date (E4). Put the absolute address ($E$4). In BondStructure: choose cell H8.
30 July 2005
127
Reuters Financial Software PowerPlus Pro Workbook Step 2 Action Click OK. The start date for the first bond in the table appears in cell E8. Fill down from cell D8 to cell D55 to obtain the start date of all the bonds.
6.4.2
Step 1 2
Calculate the Vasicek-Fong coefficients

Action In the exercise, choose cell J9, and choose Insert -> Function. Choose Adfin TermStructure in Function Category and AdTermStructure() in Function name then click OK. The dialog box of the AdTermStructure() function appears.
As explained previously, the AdTermStructure() function is composed of three arguments. Step Action 1 a. In InstrumentArray: choose cells-array C8:H55. b. In RateStructure: choose cell K6 with the following keywords: RM:VF RM:VF means that the Rate Model used is Vasicek-Fong. Click OK. The first Vasicek-Fong coefficient appears in cell J9.
AdTermStructure() calculates a term structure from an instrument array (in the example the function returns
Vasicek-Fong coefficients). Step Action 1 2 Choose an array of cells (J9:J14) such that the original cell is at the upper-left hand corner of the array. Press F2 to edit the function in the cell.
30 July 2005
128
6.4.3
Generate the Zero Coupon Yield Curve from Vasicek-Fong Coefficients
Define periods for your yield curves dates, in the example: 1M, 3M, 6M, 1Y, 1Y6M, 2Y, 3Y, 4Y, 10Y, 30Y. Calculate the Maturity Dates of the corresponding periods. Step Action 1 2 In the exercise, choose cell K18, and choose Insert -> Function. Choose Adfin Forex & MM in Function Category and FxCalcPeriod() in Function name then click OK. The dialog box of the FxCalcPeriod() function appears.
As explained previously, the FxCalcPeriod() function is composed of four arguments. Step Action 1 a. In CalcDate: choose the zero coupon curve start date (E4). Put the absolute address ($E$4). b. In Cur1Cur2: enter the cross currency code EUR. c. In Period: enter the period code (6M: cell $J18). d. To retrieve the End Date: insert RET:2 in the FxMode.
30 July 2005
129
Reuters Financial Software PowerPlus Pro Workbook Step 2 Action Click OK. Fill down from cell K18 to cell K27 to obtain the End dates of all forward periods.
6.4.4
Step 1 2
Calculate the Discount Factors from the Vasicek Fong Coefficients

Action In the exercise, choose cell L18, and choose Insert -> Function. Choose Adfin TermStructure in Function Category and AdRate() in Function name then click OK. The dialog box of the AdRate() function appears.
As explained previously, the AdRate() function is composed of five arguments. Step Action 1 a. b. c. d. In CalcDate: choose the start date of the curve: cell J9. In DateArray: choose the array of dates K18:K27. In RateArray: choose in this array argument the Vasicek-Fong coefficients: J9:J14. In RateStructure: choose cell K6 with the following keywords
RM:VF Note: RM:VF means that the rate model used is Vasicek-Fong.
2 Click OK. The first Discount Factor in the table appears in cell L18.
AdRate() is an array function containing Discount factors.

Step 1 2 Action Choose an array of cells (L18:L27) such that the original cell is at the upper-left hand corner of the array. Press F2 to edit the function in the cell.
30 July 2005
130
Reuters Financial Software PowerPlus Pro Workbook Step Action Press CTRL+SHIFT+ENTER to make it an array function.
Then convert your discount factors to rates using AdRateConv() as explained previously.
6.5 Zero Coupon Yield Curve: Basis Spline Method

The objective is to build from scratch a European Zero Coupon yield curve based on European Government Bonds only using the basis spline method. The same InstrumentArray as in the previous example will be used, but the Yield Curve will be generated at a different Calculation Date: change cell E4 to 24May2004.
6.5.1
Step 1 2
Calculate the Basis Spline Parameters

Action In the exercise, choose cell J9, and choose Insert -> Function. Choose Adfin TermStructure in Function Category and AdTermStructure() in Function name then click OK. The dialog box of the AdTermStructure() function appears.
As explained previously, the AdTermStructure() function is composed of three arguments. Step Action 1 2 In InstrumentArray: choose cells-array C8:H55. Re-use the same InstrumentArray as in the previous example. In RateStructure: choose cell K4 with the following keywords:
RM:BSPLINE SMOOTH:CONT MDWA:YES Note: RM:BSPLINE means that the Rate Model used is basis spline; SMOOTH:CONT meant that the smoothing spine method is continuous and MDWA:YES indicates that
the volatility of the bond is taken into account. 3 Click OK. The first basis spline parameters appear in cell J9.
30 July 2005
131
AdTermStructure() calculates a term structure from an instrument array (in the example the function returns
basis spline parameters). Step Action 1 2 3 Choose an array of cells (J9:K28) such as the original cell is at the upper-left hand corner of the array. Press F2 to edit the function in the cell. Press CTRL+SHIFT+ENTER to make it an array function.
6.5.2
Generate the Zero Coupon Yield Curve from Basis Spline Parameters
Define periods for your yield curves dates, in the example: 1M, 3M, 6M, 1Y, 1Y6M, 2Y, 3Y, 4Y, 10Y, 30Y. Calculate the Maturity Dates of the corresponding periods. Step Action 1 2 In the exercise, choose cell N9, and choose Insert -> Function. Choose Adfin Forex & MM in Function Category and FxCalcPeriod() in Function name then click OK. The dialog box of the FxCalcPeriod() function appears.
As explained previously, the FxCalcPeriod() function is composed of four arguments. Step Action 1 a. In CalcDate: choose the zero coupon curve start date (E4). Put the absolute address ($E$4). b. In Cur1Cur2: enter the cross currency code EUR. c. In Period: enter the period code (6M: cell $J18). d. To retrieve the End Date: insert RET:2 in the FxMode. e. In CalcDate: choose the zero coupon curve start date (E4). Put the absolute address ($E$4).
30 July 2005
132
Reuters Financial Software PowerPlus Pro Workbook Step 2 Action Click OK. Fill down from cell N9 to cell N18 to obtain the End dates of all forward periods.
6.5.3
Step 1 2
Calculate the Discount Factors from Basis Spline Parameters

Action In the exercise, choose cell O9, and choose Insert -> Function. Choose Adfin TermStructure in Function Category and AdRate() in Function name then click OK. The dialog box of the AdRate() function appears.
As explained previously, the AdRate() function is composed of five arguments. Step Action 1 a. b. c. d. In CalcDate: choose the start date of the curve: cell J9. In DateArray: choose the array of dates N9:N18. In RateArray: choose in this array argument the basis spline parameters: J9:K28. In RateStructure: choose cell K4 with the following keywords
RM:BSPLINE SMOOTH:CONT MDWA:YES

2 Click OK. The first Discount Factor in the table appears in cell O9.
AdRate() is an array function containing discount factors.

Step 1 2 Action Choose an array of cells (09:018) such that the original cell is at the upper-left hand corner of the array. Press F2 to edit the function in the cell.
30 July 2005
133
Then convert your discount factors to rates using AdRateConv() as explained previously.
6.5.4
Convexity Adjustments and Turn of Year
Using Adfin TermStructure functions, you can build a zero-coupon yield curve with a convexity adjustments method adjusting the term structure with defined volatility and mean reversion using the Hull and White model. It can be done using either the AdCalibrate() function or using the AdTermStructure() function. You can also impact the turn of year phenomenon on the zero-coupon yield curve recalculating forward rates for the turn of year period.
30 July 2005
134
7 ADFIN SWAPS FUNCTIONS

7.1 Overview of Adfin Swaps Functions
Adfin Swaps covers interest rate swaps (IRS) based on the common floating rate indices (LIBOR 3 months, EONIA, etc) as well as the currency swap market. Adfin Swaps supports non-standard swap structures, such as asset swaps and amortizing swaps. It is also possible to calculate swap derivatives such as PVBP (Price Value of Basis Point) and Duration.
7.2 Interest Rates Swaps

This exercise demonstrates how to calculate the fixed rate swap starting from a zero coupon rate curve. The exercise also presents the Net Present Value calculation of a swap starting from a zero coupon curve as well as the cash-flow calculation. Before using the Adfin Swaps functions, it is necessary to retrieve a zero coupon rate curve. Our exercise uses the European Zero Coupon real-time curve 0#EURZ=R from Reuters. You can also retrieve other zero coupon curve code in Kobra on page ZERO/1. Reproduce the following table using the same cell addresses and then use the RtUpdate() function to obtain realtime data. Step Action 1 2 3 Retrieve the fields MATURITY DATE and PRIM ACT 1 for the swaps code. Enter cell B6 in SourceAllias Choose cells B8:B19 in InstrumentArray, then cells C6:D6 in FieldNameArray, choose C8 in DestinationCell.
Note: Apply the percentage format (%) in the PRIM ACT 1 column.
7.2.1
IrsStructure Concept
Most of the Interest Rate Swap functions rely on the IrsStructure concept. This is manifest in the IrsStructure argument, a group of keywords that specify all calculation parameters necessary to calculate an Interest Rate Swap correctly. For example, an IrsStructure integrates calculation conventions (Act/Act, 30/360...) of the floating and fixed swap leg as well as the frequency of cash flows, the national or international calendar to use, etc... Some keywords have been especially created to specify the characteristics of both swap legs (LBOTH), characteristics of the paid leg (LPAID) or characteristics of the received leg only (LRECEIVED).
30 July 2005
135
7.2.2
Example of IrsStructure
LBOTH CLDR:EMU LPAID LTYPE:FIXED FRQ:1 LRECEIVED LTYPE:FLOAT FRQ:2
This example means: the two swap legs use the European calendar (specified by the keywords LBOTH CLDR:EMU), the floating leg has a semi-annual coupon frequency (LTYPE:FLOAT FRQ:2) and an annual frequency for the fixed leg (LTYPE:FIXED FRQ:1). Reuters PowerPlus Pro also provides some predefined Irs styles for the major types of Interest Rate Swaps traded internationally.
7.2.3
Step 1
How to View IRS Styles

Action Choose Reuters -> Settings ->Style Management -> IRS Styles.
Double click EUR_AB6E Euro Annual Bond Basis vs 6-Month Euribor IrsStructure. A dialog box opens indicating all the calculation parameters taken into account by this Irs structure.
You indicate the style name EUR_AB6E in the IrsStructure argument of the Interest Rate Swap functions to price a standard Euribor swap. If you want to know the keywords of the EUR_AB6E Irs style, click Open to view the style definition and the click the Structure button.
The structures contained in Style Management are generic structures intended to facilitate the use of the Adfin Swaps functions.
7.2.4
How to Create a New Structure
You can create your own structures on the basis of the existing structures.
30 July 2005
136
Reuters Financial Software PowerPlus Pro Workbook Step Action Choose the structure of your choice in the Style Management dialog box then click New. Alternatively, you can specify the set of characteristics of your swap using the structure keywords directly in the Adfin Swaps functions (IrsStructure).
7.3 Interest Rates Swaps Examples

The exercise analyzes a six month Euribor swap. To do this it uses the EUR_AB6E style for the IrsStructure argument.
7.3.1
SwIrsSolve()
In this exercise, we determine the fixed rate to apply to a swap Annual Bond Basis Vs a six month Euribor initialized on the 24/05/05, with a forward start date of 26/05/05. SwIrsSolve()calculates the fixed rate or the floating rate spread equivalent to a predefined net present value.
7.3.2
Name
Arguments
Description Calculation date. Array of zero-coupon dates. Array of zero-coupon rates or discount factors. Start date of the swap (effective date). Maturity date of the swap (expressed as a date or a code such as "1Y"). Rate of the swap fixed leg. Rate of the swap floating leg for the current calculation period. Present value of the swap or one of its legs. Extended argument defining the interest rate swap structure. Extended argument customizing the return value.
SwIrsSolve() is composed of ten arguments. CalcDate ZcDates ZcRates StartDate Maturity FixedRate CurFloatingRate NetPresentValue IrsStructure SwMode
7.3.3
Example Using SwIrsSolve()
Reproduce the following table: Step Action 1 a. In cell G7, insert the date of calculation. b. In cell G8, insert the date of the beginning of the swap. c. In cell G9, insert the maturity of the swap in date or period format (i.e. 4Y for 4 years). d. In cell G11, insert the floating rate or use the real-time rate for the 6 months Euribor from the RtGet() formula: RtGet("IDN", " EURIBOR6MD=", "LAST")%. e. In cell G12, insert 0 for the current value of the swap. The objective of the exercise is to determine the fixed rate for a swap whose Net Present Value is null. f. In cell G13, insert the IRS Structure EUR_AB6E. g. In cell G14, insert the argument RES:FIXED to obtain the result for the swap fixed rate.
30 July 2005
137
Reuters Financial Software PowerPlus Pro Workbook Step 2 3 Action Choose cell G16, and choose Insert -> Function. Choose Adfin Swaps in Function Category and SwIrsSolve() in Function name then click OK.
The dialog box of the SwIrsSolve() function appears. a. In CalcDate: Choose cell G7 b. In ZcDates: Choose cells C8:C19, c. In ZcRates: Choose cells D8:D19, d. In StartDate: Choose cell G8, e. In Maturity: Choose cell G9, f. In FixedRate: Enter 0. Fixed Rate is the rate to determine, g. In CurFloatingRate: Choose cell G11, h. In NetPresentValue: Choose cell G12, i. In IrsStructure: Choose cell G13, j. In SwMode: Choose cell G14.
Click OK. The fixed rate of the swap appears in cell G16.
7.3.4
SwIrsPx()
This exercise shows how to calculate the current value (NPV) of a swap using a zero coupon curve. The SwIrsPx() function calculates the current value of an Interest Rate Swap.
30 July 2005
138
7.3.5
Name
Arguments
Description Calculation date. Array of zero-coupon dates. Array of zero-coupon rates or discount factors. Start date of the swap (effective date). Maturity date of the swap (expressed as a date or a code such as "1Y"). Rate of the swap fixed leg. Rate of the swap floating leg for the current calculation period. Extended argument defining the interest rate swap structure. Extended argument customizing the return value. If this argument is not indicated, the result obtained is the NPV of the swap.
SwIrsPx() is composed of nine arguments. CalcDate ZcDates ZcRates StartDate Maturity FixedRate CurFloatingRate IrsStructure SwMode
7.3.6
Example Using SwIrsPx()
For the continuation of this exercise, we will use the file created for the SwIrSolve() function exercise. Two additional arguments are however necessary (see the following figure). Step Action 1 2 3 a. b. In cell G5, insert the amount of the swap to be priced, In cell G10, insert the swap fixed rate.
Choose cell G18, and choose Insert -> Function. Choose Adfin Swaps in Function Category and SwIrsPx() in Function name then click OK. The dialog box of the SwIrsPx() function appears. a. In CalcDate: choose cell G7. b. In ZcDates: choose cells C8:C19. c. In ZcRates: choose cells D8:D19. d. In StartDate: choose cell G8. e. In Maturity: choose cell G9. f. In FixedRate: choose cell G10. g. In CurFloatingRate: choose cell G11. h. In IrsStructure: choose cell G13. i. In SwMode: leave it blank.
Click OK. The NPV of the swap then appears in cell G18.
30 July 2005
139
Reuters Financial Software PowerPlus Pro Workbook Step 5 Action Multiply this NPV by the Amount to get a weighted NPV. Note:If the result from SwIrsSolve() function (cell G16) is entered into cell G10 (FixedRate), an NPV of zero is returned by the function.
7.3.7
SwIrsCashFlows()
This exercise shows how to calculate a swap using a zero coupon curve. The SwIrsCashFlows() function generates an array of the remaining cash flows of an IRS.
7.3.8
Name
Arguments
Description Calculation date. Array of zero-coupon dates. Array of zero-coupon rates or discount factors. Start date of the swap (effective date). Maturity date of the swap (expressed as a date or a code such as "1Y"). Rate of the swap fixed leg. Rate of the swap floating leg for the current calculation period. Extended argument defining the interest rate swap structure. Extended argument customizing the return value. If this argument is not indicated, the function returns an array of 5 columns (Date, Payment of the floating part, Payment of the fixed part, Zero Coupon Rates, Discount factor).
SwIrsCashFlows() is composed of nine arguments. CalcDate ZcDates ZcRates StartDate Maturity FixedRate CurFloatingRate IrsStructure SwMode
7.3.9
Example Using SwIrsCashFlows()
To complete the exercise, this example generates the remaining cash flows from the preceding swap. Step Action 1 Choose cell B24, and choose Insert -> Function.
30 July 2005
140
Reuters Financial Software PowerPlus Pro Workbook Step 2 Action Choose Adfin Swaps in Function Category and SwIrsCashFlows() in Function name then click OK. The dialog box of the SwIrsCashFlows () function appears. a. In CalcDate: choose cell G7. b. In ZcDates: choose cells C8:C19. c. In ZcRates: choose cells D8:D19. d. In StartDate: choose cell G8. e. In Maturity: choose cell G9. f. In FixedRate: choose cell G10. g. In CurFloatingRate: choose cell G11. h. In IrsStructure: choose cell G13. i. In SwMode: leave blank.
3 4
Click OK. The first payment date of coupon appears in cell B24. Choose the range B24:F31, press F2, then Ctrl+Shift+Enter.
Note:To improve the presentation, the column headings in row 23 were added manually.
7.3.10 AdIrsDeriv()
The AdIrsDeriv() function calculates the price variation per basis point (PVBP) of an Interest Rate Swap and can also return its duration.
7.3.11 Arguments
The AdIrsDeriv() function is composed of ten arguments: Name Description
CalcDate RateArray StartDate Maturity FixedRate CurFloatingRate IrsStructure RateStructure CalcStructure
Calculation date. Term structure or data array containing the rates used to price the instrument. It depends on the specified rate model in the rate structure. Start date of the interest rate swap. Maturity date of the swap (expressed as a date or a code such as 1Y). Rate of the swap fixed leg. Rate of the swap floating leg for the current calculation period. Extended argument defining the interest rate swap structure. Extended argument defining the interest rate model. Extended argument defining the structure of the calculation method.
30 July 2005
141
Reuters Financial Software PowerPlus Pro Workbook Name Description Extended argument customizing the return value.
AdMode
7.3.12 Example Using AdIrsDeriv()

Reproduce the following table. Step Action 1 a. In cell B2, insert the calculation date. b. In cell B3, insert the start date the swap. c. In cell B4, insert the maturity of the swap in date or period format (i.e. 2Y for 2 years). d. In cell B5, insert the fixed rate of the swap. e. In cell B6, insert the currency floating rate of the swap. f. In cell B7, insert the IRS Structure. g. In our example: h. LPAID LTYPE:FIXED CCM:MMA0 FRQ:1 to define the paid leg of the IRS, its coupon calculation method and its frequency. i. LRECEIVED LTYPE:FLOAT CCM:MMA0 FRQ:1 to define the received leg of the IRS, its coupon calculation method and its frequency. j. In cell B8, insert the Rate Structure. k. In our example: l. RM:YC to define a yield curve rate model. m. ZCTYPE:RATE to define the type of yield curve. n. RATEFRQ:Y to define a yearly compounding frequency parameter. o. IM:CUBR to define a cubic rate interpolation p. In cell B9, insert the Calculation Structure. In our example IRSPVBP:CRV to calculate partial sensitivities. q. In cell B10, insert the AdMode. In our example LAY:H to return results horizontally. r. Define the zero coupon yield curve using RtUpdate() in the H3:I49 cells array.
2 3
Choose cell A13, and choose Insert -> Function. Choose Adfin Swaps in Function Category and AdIrsDeriv() in Function name then click OK. The dialog box of the AdIrsDeriv() function appears. a. In CalcDate: choose cell B2. b. In RateArray: choose cells H3:I49. c. In StartDate: choose cell B3. d. In Maturity: choose cell B4. e. In FixedRate: choose cell B5. f. In CurFloatingRate: choose cell B6. g. In IrsStructure: choose cell B7. h. In RateStructure: choose cell B8. i. In CalcStructure: choose cell B9. j. In AdMode: choose cell B10. Click OK. The first date appears in cell A13.
30 July 2005
142
Reuters Financial Software PowerPlus Pro Workbook Step 5 Action Choose the array A13:D27, press F2, then CTRL+SHIFT+ENTER.
Use the same method with the same inputs (except for CalcStructure choose cell B30: IRSPVBP:SUM and for AdMode, write RET:B4) for the AdIrsDeriv() function in cells B32:C35 to obtain the sum of all partial sensitivities.
To obtain global difference in NPVs for a parallel shift in the yield curve by one basis point, insert in cell B40 the AdIrsDeriv() function using the CalcStructure in cell B38 (IRSPVBP:PARALLEL)and extend it to cell C43.
30 July 2005
143
7.4 Overnight Indexed Swaps (OIS)

An Overnight Indexed Swap (OIS), also called a Money Swap, is a Fixed/Floating Interest Rate Swap with the Floating Leg tied to a daily overnight (or Tom/Next in some markets) rate reference. The term generally ranges from one week to one year. To price an OIS in Reuters PowerPlus Pro, you must use the IRS Swap functions (SwIrsPx(), SwIrsSolve(), SwIrsCashFlows()). Several styles have been created in the Style Database (go to Reuters menu, Settings option, then Style Management as explained at the beginning of this swap part) to hold all of the calculations parameters for the Overnight Indexed Swap: OIS_EONIA
30 July 2005
144
Reuters Financial Software PowerPlus Pro Workbook To make OIS related calculations historical data is generally required. The data may be stored in the local database. To retrieve this data from the database, use the keyword "IDX:" in IrsStructure to specify which data must be retrieved for the calculation. This historical database is in the Reuters -> Settings -> Style Management and the Index History Styles. To get the EURO OVERNIGHT INDEX AVERAGE, select oEONIA:
However, the EONIA index changes daily and it is important to update the database each day. Since 5.0 the update is done automatically when opening PowerPlus Pro. However if the update has not completed for some reason, you can still use the manual method explained below: You first have to retrieve the EONIA index history in Excel using the RtHistory() function. Step Action 1 2 Choose Adfin Real Time in Function Category and RtHistory() in Function name then click OK. In cell D1 the RtHistory function is: =RtHistory(A1,A2,A3,A4,A5)
The retrieved history can be used to update the "oEONIA" style in Reuters PowerPlus Pro (refer to the preceding figure) using to the AdHistoryUpdate() function. Step Action 1 2 Choose Adfin Common in Function Category and AdHistoryUpdate() in Function name then click OK. Format all closing rates as percentages.
Once the index database has been updated, the OIS_EONIA style can be used in the IrsStructure argument to calculate the Overnight Indexed Swap. If you want to change or add some keywords to the OIS IrsStructure, you can use the keyword LPAID to indicate the PAID leg or LRECEIVED for the received leg followed by the appropriate keywords. For more details refer to the online help.
7.5 Asset Swaps
30 July 2005
145
7.5.1
Definition
An Asset Swap is an Interest Rate Swap agreement combined with an asset (for example a Bond or a Floating Rate Note). It is used to convert the cash flows from an underlying security (a Bond or a Floating Rate Note), from Fixed coupon to Floating coupon, Floating coupon to Fixed coupon, or from one currency to another. To price an asset swap in Reuters PowerPlus Pro, different functions can be used in order to evaluate cash flows, the net present value, the spread and so on. These functions are: AdAssetSwapBdCashFlows() AdAssetSwapBdPrice() AdAssetSwapBdSpread()
7.5.2
AdAssetSwapBdCashFlows()
The AdAssetSwapBdCashFlows() function generates the remaining cash flows for the swap and the bond, and displays the combined swap and bond cashflows.
7.5.3
AdAssetSwapBdPrice()
The AdAssetSwapBdPrice() function calculates the bond price from the spread and the different net present values.
7.5.4
Function Arguments
The AdAssetSwapBdCashFlows() and AdAssetSwapBdPrice() functions are composed of ten arguments:
=<FunctionName> (SettlementDate, MaturityDate, RateArray, Spread, CouponRate, CurFloatingRate, BondStructure, FloatLegStructure, RateStructure, AdMode)
7.5.5
AdAssetSwapBdSpread()
The AdAssetSwapBdSpread() function calculates the spread from the bond price, the upfront spread and the swap spread.
7.5.6
Function Arguments
The AdAssetSwapBdSpread() function is composed of ten arguments:
AdAssetSwapBdSpread (SettlementDate, MaturityDate, RateArray, Price, CouponRate, CurFloatingRate, BondStructure, FloatLegStructure, RateStructure, AdMode )
7.5.7
Argument
Argument Descriptions
Description Settlement date. Maturity date of the bond. Argument defining the interest rate model. Argument used in AdAssetSwapBdSpread to designate the bond price. Argument used in AdAssetSwapBdCashFlows and AdAssetSwapBdPrice to designate the spread in basis points.
SettlementDate MaturityDate RateArray Price Spread
30 July 2005
146
Reuters Financial Software PowerPlus Pro Workbook Argument Description Coupon rate of the bond. Floating rate of the received leg for the currency calculation (%). Extended argument defining the structure of the bond. Extended argument defining the structure of the floating leg. Extended argument defining the structure of the interest rate model. Extended argument customizing the return value.
CouponRate CurFloatingRate BondStructure FloatLegStructure RateStructure AdMode
7.5.8
AdAssetSwapBdCashFlows Return Value
The return value of the AdAssetSwapBdCashFlows() function depends on the value of AdMode. The default return value is a 7-column array: Flow Dates Flows for the received leg (Interest + Capital) Flows for the paid leg (Interest + Capital) Flows of the asset (Interest + Capital) Combined Cash Flow (the sum of the preceding columns) Discount Factor for the received leg Discount Factor for the paid leg
7.5.9
AdAssetSwapBdPrice Return Value
The return value of AdAssetSwapBdPrice() function depends on the value of AdMode. The default return value is a 4-row array. Asset Price Swap NPV Paid Leg NPV Received Leg NPV
7.5.10 AdAssetSwapBdSpread Return Value

The return value of AdAssetSwapBdSpread()function depends on the value of AdMode. The return value is an array of two columns and three rows: Column 1 Column 2 Row 1 Row 2 Row 3 Total spread from the asset price in basis point Upfront spread in basis points Swap spread in basis points Spread from the asset price in cash Upfront spread in cash Swap spread in cash
7.5.11 AdAssetSwapBdCashFlows Example

This example uses the AdAssetSwapBdCashFlows function. You can adapt it to use the AdAssetSwapBdPrice and AdAssetSwapBdSpread functions. Reproduce the following table: Step Action 1 From cell C2 to cell C10, enter the Bond features: Settlement Date, Trade Date, Maturity, Issue Date, Coupon Rate, First LIBOR, Clean Price, Spread, BondStructure.
30 July 2005
147
Reuters Financial Software PowerPlus Pro Workbook Step 2 Action From cell C11 to cell C13, enter these arguments: a. In cell C11, enter the FloatLegStructure. b. In cell C12 enter the RateStructure. c. In cell C13 enter the AdMode. In cell (O3:P22), the RateArray argument is a Zero Coupon Yield Curve with a column for dates and a column for Discount Factors. Select cell E3 and insert the AdAssetSwapBdCashFlows() function.
3 4
a. b.
Enter all arguments requested to obtain the following formula: Click OK.
=AdAssetSwapBdCashFlows(C2,C4,O3:P22,C9,C6,C7,C10,C11,C12)
6 This is an array function. Extend the result to cell K14, press the F2 key to edit, and press Ctrl + Shift +Enter.
7.5.12 Adapted AdAssetSwapBdPrice and Spread Example

Use the same method and the same inputs (except for AdMode cell C13) for the AdAssetSwapBdPrice() function in cells (E17:F20). In cell (E23) enter the function AdAssetSwapBdSpread() and extend it to cell (G25).
30 July 2005
148
7.5.13 AdAssetSwapGenCashFlows()
AdAssetSwapGenCashFlows() generates the remaining cashflows for the swap and the asset, and displays the combined swap and asset cashflows.
7.5.14 AdAssetSwapGenPrice()
AdAssetSwapGenPrice() calculates the asset price from the spread and the different net present values for a
complex asset swap.
7.5.15 Function Arguments

The AdAssetSwapGenCashFlows() and AdAssetSwapGenPrice() functions are composed of 14 arguments.
=<function_name>(SettlementDate, MaturityDate, AssetRateArray, RecRateArray, FxArray, AssetRate, CurAssetRate, ReceivedRate, CurReceivedRate, AssetType, AssetStructure, RecLegStructure, RateStructure, AdMode )
7.5.16 AdAssetSwapGen Spread()

AdAssetSwapGenSpread() calculates the spread from the asset price, the upfront spread and the swap spread
for a complex asset swap.
7.5.17 Function Arguments

The AdAssetSwapGenSpread() function is composed of 14 arguments.
=AdAssetSwapGenSpread(SettlementDate, MaturityDate, AssetRateArray, RecRateArray, FxArray, Price, AssetRate, CurAssetRate, CurReceivedRate, AssetType, AssetStructure, RecLegStructure, RateStructure, AdMode )
7.5.18 AdAssetSwapGen Function Arguments

Argument Description
30 July 2005
149
Reuters Financial Software PowerPlus Pro Workbook Argument Description Settlement date. Maturity date of the asset. Argument defining the interest rate model for the asset currency. Argument defining the interest rate model for the received currency. Array of (N+1) lines and two columns (date, rates) where N is the number of swap point periods. Asset price. Coupon rate if the asset is a bond (%) or quoted margin if the asset is a floating rate note (basis points). Floating rate of the asset for the current calculation (%), applicable when the asset is a floating rate note. Floating rate of the received leg for the current calculation (%), applicable when the received leg is floating. Floating rate of the received leg for the current calculation (%), applicable when the received leg is floating. Type of the asset (BOND or FRN). Extended argument defining the structure of the asset. Extended argument defining the structure of the received leg. Extended argument defining the structure of the interest rate model. Extended argument customizing the return value.
SettlementDate MaturityDate AssetRateArray RecRateArray FxArray Price AssetRate CurAssetRate ReceivedRate CurReceivedRate AssetType AssetStructure RecLegStructure RateStructure AdMode
7.5.19 AdAssetSwapGenCashFlows Return Value

The return value of AdAssetSwapGenCashFlows() function depends on the value of AdMode. The return value is a 7-column array: Flow Dates Flows for the received leg (Interest + Capital) Flows for the paid leg (Interest + Capital) Flows of the asset (Interest + Capital) Combined Cash Flow (the sum of the preceding columns) Discount Factor for the received leg Discount Factor for the paid leg
7.5.20 AdAssetSwapGenPrice Return Value

The return value of AdAssetSwapGenPrice() function depends on the value of AdMode. The return value is a 4-row array: Asset Price Swap NPV Paid Leg NPV Received Leg NPV
7.5.21 AdAssetSwapGenSpread Floating Leg Return Value

The return value of the AdAssetSwapGenSpread() function for the floating leg is a two column by three row array. Column 1 Column 2
30 July 2005
150
Reuters Financial Software PowerPlus Pro Workbook Column 1 Row 1 Row 2 Row 3 Total spread from the asset price in basis point Upfront spread in basis points Swap spread in basis points Column 2 Spread from the asset price in cash Upfront spread in cash Swap spread in cash
7.5.22 AdAssetSwapGenSpread Fixed Leg Return Value

The return value of the AdAssetSwapGenSpread() function for the fixed leg is a two column by three row array. Column 1 Column 2 Row 1 Row 2 Row 3 Total fixed rate from the asset price as a percentage. Upfront fixed rate in percent. Swap fixed rate in percent. Total fixed rate from the asset price in cash. Upfront fixed rate in cash. Swap fixed rate in cash.
7.5.23 Example with AdAssetSwapGenCashFlows() Function

Step 1 Action From cell C2 to cell C12, enter the Frn features: Settlement Date, Trade Date, Maturity, Issue Date, Margin, Floating Rate Array, Coupon Rate, Asset Type, Clean Price, FrnStructure. a. b. c. In cell C13, enter the RecLegStructure. In cell C14 enter the RateStructure. In cell C15 enter the AdMode.
3 4
Select cell E3 and insert the AdAssetSwapGenCashFlows() function. Enter all arguments requested to obtain the following formula:
=AdAssetSwapGenCashFlows(C2, C4, Q1:R20, Q1:R20, Q22:R22, C6, C7, C8, 0, C9, C11:C12, C13, C14,"IAC") Note: In cell (Q1:R20), the AssetRateArray and RecRateArray arguments is a
Zero Coupon Yield Curve with a column for dates and a column for Discount Factors.
30 July 2005
151
Reuters Financial Software PowerPlus Pro Workbook Step 5 Action a. The column in the array displaying the cash flows of the asset can be separated between interest and capital flows, according to the IAC keyword in AdMode. b. Click OK. c. This is an array function. Extend the result to cell L14, press the F2 key to edit, and then press Ctrl + Shift + Enter.
7.5.24 Adapted AdAssetSwapGenPrice Example

Use the same method and the same inputs (except for AdMode cell C15) for AdAssetSwapGenPrice() (E18:F21).
30 July 2005
152
7.5.25 Adapted AdAssetSwapGenSpread Example

In cell (E24) enter array function AdAssetSwapGenSpread() function and extend it to cell (G26).
7.6 Currency Swaps

Adfin Swaps functions also provide calculation for Currency Swaps.
7.6.1
Purpose of the Exercise
The exercise illustrates how to calculate the Net Present Value, as well as cash-flow calculations. The swap used in the example is a fix-flo currency swap based on the exchange of a fixed debt denominated in Euro (Euro is paid) against a floating debt denominated in Sterling (Sterling is received).
7.6.2
The CsStructure Concept
The Currency Swap function relies on the CsStructure concept. The CsStructure argument is a group of keywords that specify all the calculation parameters necessary to calculate a currency swap correctly as IrsStructure was for Interest Rate Swaps. For example, CsStructure integrates calculation conventions (Act/Act, 30/360...) of the floating and fixed swap leg as well as the frequency of cash flows, the national or international calendars to use, etc. As explained in the IrsStructure part, some keywords were especially created to specify the characteristics of both swap legs (LBOTH), characteristics of the received leg (LRECEIVED) or characteristics of the paid leg only (LPAID). For example, the keywords LBOTH CLDR:EMU LRECEIVED CUR:USD LPAID CUR:EUR specify that the two legs of the swap use the European calendar that the paid leg is in US Dollars and the received leg is in Euro. To access the complete list of available arguments, look at the online help for the SwCsPx() function for example, and then at CsStructure. The CsStructure help lists all the available arguments.
30 July 2005
153
Reuters PowerPlus Pro does not have a style management database for currency swaps. So it is necessary to follow Adfin models and examples to create your own CsStructure.
7.7 Currency Swap Examples

This exercise illustrates how to calculate the fixed rate of a currency swap based on the exchange of a Euro debt against a Sterling debt. It also presents the Net Present Value calculation of a currency swap using a zero coupon curve, as well as cash-flow calculations. Before using the Adfin Currency Swaps functions, it is necessary to retrieve the discount factor curve and swap points for the currencies exchanged.
7.7.1
Discount Factor Curve for Currencies Exchanged (EUR/GBP)
Our exercise uses the Euro (0#EURZ=R) and Sterling (0#GBPZ=R) realtime discount factor curves from Reuters. Reproduce the following table with the same cell addresses and use the RtUpdate() function to obtain realtime data. Retrieve the following fields MATURITY DATE and SECOND ACTIVITY 1 for the RICs, expiry today (EURTNZ=R), and at the 10 year maturity (EUR10YZ=R). To do this, choose cell A4, insert the RtUpdate() function and reference the following cells. Repeat the same procedure for the Sterling realtime discount factor curve (0#GBPZ=R).
30 July 2005
154
7.7.2
Swap Points Between Currencies Exchanged (EURGBP)
The Swap Points & Outrights model provides the facility to calculate and display cross swap points and outrights in realtime for any currency or cross currency pair (choose from the Reuters menu the Data Store item, go to the FX&MM folder, then open the Analyse folder and choose Swap Points and Outrights). When the model is launched, click on Long (in the bottom left hand corner), then type EURGBP in the instrument box. In the part of the model called Long Dates (in the bottom left hand corner) add the maturity accordingly to the maturity of the discount factors of the yield curve: 3Y, 4Y, 5Y, 6Y, 7Y, 8Y, 9Y and 10 Y in our example.
Copy the Swap point EUR / GBP from ON (overnight) to 10Y in your spreadsheet (see the following figure).
30 July 2005
155
Reuters Financial Software PowerPlus Pro Workbook In the range J4:J21, we calculate the mid of the Bid and Ask array of the Swap Points EUR/GBP pasted from the model: cell J4: 0.517=(0.471+0.563)/2.
These swap points will be used in the Currency Swap functions in the FxArray argument.
7.7.3
SwCsPx()
The SwCsPx() function calculates the net present value of a currency swap or one of its legs.
7.7.4
Arguments
The SwCsPx() function is composed of 12 arguments. Name Description
CalcDate StartDate Maturity PaidRate CurPaidRate ReceivedRate CurReceivedRate ZcPaidArray ZcReceivedArray FxArray CsStructure
Calculation date. Start date of the swap. Maturity date of the swap. Fixed rate if the paid leg is fixed, floating rate spread if the paid leg is floating. Floating rate of the paid leg for the current calculation period if applicable. Fixed rate if the received leg is fixed, floating rate spread if the received leg is floating. Floating rate of the received leg for the current calculation period if applicable. Array of zero-coupon dates and rates or discount factors for the paid currency. Array of zero-coupon dates and rates or discount factors for the received currency. Array of (N+1) cells x 2 cells (dates, rates) where N is the number of swap point periods. Extended argument defining the currency swap structure.
30 July 2005
156
Reuters Financial Software PowerPlus Pro Workbook Name Description Extended argument customizing the return value.
SwMode
7.7.5
Example
Reproduce the following table. Step Action 1 a. In cell C24, enter the calculation date. b. In cell C28, enter EURGBP. c. In cell C30, enter the start date of the swap. d. In cell C31, enter the maturity date of the swap. e. In cell C33, enter the fixed rate of the paid leg (GBP). f. In cell C34, enter the floating rate of the paid leg for the current calculation period (enter the figure 0). g. In cell E33, enter the fixed rate of the received leg (EUR). h. In cell E34, enter the floating rate of the received leg for the current calculation period (enter the figure 0). i. In cell C36, enter the following swap CsStructure:
LRECEIVED LTYPE:FLOAT CUR:GBP CLDR:UKG LPAID LTYPE:FIXED CUR:EUR CLDR:EMU CROSS:EURGBP

The received leg is the floating leg which currency is GBP based on the British calendar. The paid leg is the fixed leg which currency is EUR based on the European calendar. 2 In cell C37, enter the SwMode argument. The keyword ZCTYPE:DF defines a discount factor curve. The keyword DC:GBP defines the currency used for discounting both legs Choose cell C40, and choose Insert -> Function. Choose Adfin Swaps in Function Category and SwCsPx() in Function name then click OK. The dialog box of the SwCsPx() function appears. a. In CalcDate: choose cell C24. b. In StartDate: choose cell C30. c. In Maturity: choose cell C31. d. In PaidRate: choose cell C33. e. In CurPaidRate: choose cell C34. f. In ReceivedRate: choose cell E33. g. In CurReceivedRate: choose cell E34. h. In ZcPaidArray: choose cells B5:C21. i. In ZcReceivedArray: choose cells F5:G21. j. In FxArray: choose cells I4:J21. k. In CsStructure: choose cell C36. l. In SwMode: choose cell C37. Click OK. The Net Present Value of the swap appears in cell C40. To obtain the Net Present Value of the floating leg and the Net Present Value of the fixed leg, extend the result range (see the previous figure), choose the range C40:E40, press the F2 key to edit, then press Ctrl+Shift+Enter.
3 4
5 6
30 July 2005
157
7.7.6
SwCsSolve()
Fixed rate calculation of a currency swap using zero coupon curves: SwCsSolve() The SwCsSolve() function calculates the fixed rate or the floating rate spread for any leg equivalent to a predefined net present value.
7.7.7
Arguments
The SwCsSolve() function is composed of 13 arguments. Name Description
CalcDate StartDate Maturity PaidRate CurPaidRate ReceivedRate CurReceivedRate ZcPaidArray ZcReceivedArray FxArray NetPresentValue CsStructure SwMode*
*
Calculation date. Start date of the swap. Maturity date of the swap. Fixed rate if the paid leg is fixed, floating rate spread if the paid leg is floating. Floating rate of the paid leg for the current calculation period if applicable. Fixed rate if the received leg is fixed, floating rate spread if the received leg is floating. Floating rate of the received leg for the current calculation period if applicable. Array of zero-coupon dates and rates or discount factors for the paid currency. Array of zero-coupon dates and rates or discount factors for the received currency. Array of (N+1) cells x 2 cells (dates, rates) where N is the number of swap point periods. Present value of the swap or one of its legs in discount currency. Extended argument defining the currency swap structure. Extended argument customizing the return value
The SwCsSolve() function requires the keyword RES in the SwMode argument. In this exercise, we use the keyword RES:PAID to specify that we want to calculate the fixed rate of the paid leg.
7.7.8
Step 1 2
Example Using SwCsSolve()

Action Choose cell D42, and choose Insert -> Function. Choose Adfin Swaps in Function Category and SwCsSolve() in Function name then click OK. The dialog box of the SwCsSolve() function appears. a. b. c. d. e. f. g. h. i. In CalcDate: choose cell C24 In StartDate: choose cell C30 In Maturity: choose cell C31 In PaidRate: choose cell C33 In CurPaidRate: choose cell C34 In ReceivedRate: choose cell E33 In CurReceivedRate: choose cell E34 In ZcPaidArray: choose cells B5:C21, In ZcReceivedArray: choose cells F5:G21,
30 July 2005
158
Reuters Financial Software PowerPlus Pro Workbook Step Action j. In FxArray: choose cells I4:J21, k. In NetPresentValue: Insert 0 l. In CsStructure: choose cell C36 m. In SwMode: choose cell C37 Click OK.
7.7.9
SwCsCashFlows()
The SwCsCashFlows() function generates an array with the remaining cash flows of the currency swap.
7.7.10 Arguments
The SwCsCashFlows() function is composed of 12 arguments. Name Description Calculation date. Start date of the swap. Maturity date of the swap. Fixed rate if the paid leg is fixed, floating rate spread if the paid leg is floating. Floating rate of the paid leg for the current calculation period if applicable. Fixed rate if the received leg is fixed, floating rate spread if the received leg is floating. Floating rate of the received leg for the current calculation period if applicable. Array of zero-coupon dates and rates or discount factors for the paid currency. Array of zero-coupon dates and rates or discount factors for the received currency. Array of (N+1) cells x 2 cells (dates, rates) where N is the number of swap point periods. Extended argument defining the currency swap structure.
CalcDate StartDate Maturity PaidRate CurPaidRate ReceivedRate CurReceivedRate ZcPaidArray ZcReceivedArray FxArray CsStructure
30 July 2005
159
Reuters Financial Software PowerPlus Pro Workbook Name Description The return value depends on the value of SwMode. The default value is a vertical 20-column array containing for all remaining cash flows the following data: Column #1: Date. Column #2: Principal payment of the paid leg in paid currency. Column #3: Interest payment of the paid leg in paid currency. Column #4: Principal payment of the received leg in received currency. Column #5: Interest payment of the received leg in received currency. Column #6: Zero-coupon rate of the paid currency. Column #7: Discount factor of the paid currency. Column #8: Zero-coupon rate of the received currency. Column #9: Discount factor of the received currency. Column #10: Outright rate. Column #11: Conversion rate to the discount currency for the paid leg. Column #12: Conversion rate to the discount currency for the received leg. Column #13: Discount factor in discount currency for the paid leg. Column #14: Discount factor in discount currency for the received leg. Column #15: Principal payment of the paid leg in discount currency. Column #16: Interest payment of the paid leg in discount currency. Column #17: Principal payment of the received leg in discount currency. Column #18: Interest payment of the received leg in discount currency. Column #19: Rates associated with the PAID leg. Column #20: Rates associated with the RECEIVED leg.
SwMode
7.7.11 Example Using SwCsCashFlows()

Step 1 2 Action Choose cell A45, and choose Insert -> Function. Choose Adfin Swaps in Function Category and SwCsCashFlows() in Function name then click OK. The dialog box of the SwCsCashFlows() function appears. a. In CalcDate: choose cell C24. b. In StartDate: choose cell C30. c. In Maturity: choose cell C31. d. In PaidRate: choose cell C33. e. In CurPaidRate: choose cell C34. f. In ReceivedRate: choose cell E33. g. In CurReceivedRate: choose cell E34. h. In ZcPaidArray: choose cells B5:C21. i. In ZcReceivedArray: choose cells F5: G21. j. In FxArray: choose cells I4:J21. k. In CsStructure: choose cell C36. l. In SwMode: choose cell C37. Click OK. The first payment date of the coupon appears in cell A45. Extend the result range, choose the range A45:E68, and press the F2 key to edit,
3 4
30 July 2005
160
Reuters Financial Software PowerPlus Pro Workbook Step Action then press Ctrl+Shift+Enter.
7.7.12 AdCBSToSwp()
The AdCBSToSwp() function calculates the long term swap point from currency-basis swap spreads.
7.7.13 Arguments
The AdCBSToSwp() function is composed of seven arguments: Name Description
CalcDate PaidRateArray ReceivedRateArray FxArray CBSArray
Calculation date. Array of zero-coupon dates and rates or discount factors for the paid currency. Array of zero-coupon dates and rates or discount factors for the received currency. Array of (N+1) cells x 2 cells (dates, rates) where N is the number of swap point periods. 6-columns array of CBS spreads containing: - Start date. - Maturity. - First rate fixing for paid leg. - First rate fixing for received leg. - Spread in basis point. - CbsStructure. Extended argument defining the interest rate model. Extended argument customizing the return value.
7.7.14 Example Using AdCBSToSwp()

Step 1 Action Reproduce the following table: To obtain the first row of the Fx Array, use the Rtget() function for the EURGBP spot cross rate (in our example we calculate the MID). To obtain the following rows of the Fx Array, use the Swap Points and Outrights Model in the Data Store, copy End dates and EURGBP Swap points (you need at least two rows).
30 July 2005
161
Note: The paid leg is in EUR and the received leg in GBP. Reproduce the CBS spread array. To obtain the maturities and their respective spreads, use the RtGet() function. The instrument used is the paid EURO received GBP CBS spread (for example, the RIC for the first spread is: GB3LEU3E1Y=ICAP). To see the CBS spread list, see the ICAB1 to ICAB4 pages on Reuters 3000Xtra. The CBS structure used for the example is:
LPAID LTYPE:FLOAT FRQ:2 CLDR:EMU_FI CUR:EUR LRECEIVED LTYPE:FLOAT CUR:GBP CLDR:UKG_FI LBOTH PEX:BOTH
3 4
Choose cell C39, and choose Insert -> Function. Choose Adfin Swaps in Function Category and AdCBSToSwp() in Function name then click OK. The dialog box of the AdCBSToSwp() function appears: a. In CalcDate: choose cell B2. b. In PaidRateArray: choose cells A8:B24. c. In ReceivedRateArray: choose cells D8:E24. d. In FxArray: choose cells G7:H16. e. In CBSArray: choose cells A28:F36. f. In RateStructure: choose cell B3. g. In AdMode: leave it blank. Click OK. The first date appears in cell C39. Extend the result range, choose the range C39:D55, and press the F2 key to edit, then press Ctrl+Shift+Enter.
5 6
30 July 2005
162
Note:
In the IRSStructure, use the keyword DMC:W which takes into account the IMM periods for the coupons generation. The IMM periods conventions are the third Wednesday of March, June, September and December of the current year according to the maturity class of the floating rate used (3 or 6 months). This new keyword value is applied to all Swaps function and can also be used for FRN, Bonds and other derivatives functions.
30 July 2005
163
8 ADFIN CREDIT FUNCTIONS

8.1 Overview of Adfin Credit Functions
Adfin Credit covers the pricing and valuation of any European or American credit default swaps, or cross-currency credit default swaps, with your own recovery rate assumption. Credit risk is handled using the deterministic CoxIngersoll-Ross default intensity model, or bootstrapping of the default probability term structure. A simple function allows calibrating those models from market inputs: quoted CDS spreads, a risky discount factor or rate curve for the issuer. In addition, a probability curve for any credit event can be calculated using the Jarrow-Lando-Turnbull approach, from a rating transition matrix.
8.2 Calibrate a Default Probability Curve from a Credit Default Swap Curve and a Discount Factor Curve
8.2.1 Objective
The objective is to calibrate a default probability curve from a Credit Default Swap curve and a Discount Factor curve for an American Credit Default Swap (CDS) in order to price a Credit Default Swap.
8.2.2
Retrieve the CDS Spread Curve from Realtime
Reproduce the following table to get your CDS Spread Curve:
Step
Action
30 July 2005
164
Reuters Financial Software PowerPlus Pro Workbook Step 7 Action a. In column A choose CDS RICs (cells A4:A7). b. In column B choose a StartDate (cells B4:B7). c. In column C get the maturity date from the RIC (using the Excel function Mid() for instance, cells C4:C7). d. In column D retrieve the data for the chosen RIC using the RtUpdate() function (see Example Using RtUpdate() for more information)( cells D4:D7). e. In column E define your CdsStructure (cells E4:E7), in the example:
CDSTYPE:AMERCDS DMC:M CLDR:EMU_FI LFLOAT AOD:YES LFIXED FRQ:2 CCM:MMA0

The CDS is American, the Date Modified Convention is Modified, the Calendar used in the pricing is the European calendar, the floating leg pays accrued interests in case of default, the fixed leg is semi-annual and its Coupon Calculation Method is Money market Actual/360.
8.2.3
Retrieve the EUR Yield Curve from Realtime
Reproduce the following table to get the EUR Discount Factor Yield Curve:
To get the RIC: for instance for the first period (1W, cell A13), from the period code generate the RIC for the Discount Factor by concatenating cells. Step Action 8 a. Choose cell B13 and type: =EUR&A13&Z=R. Fill down from cell B13 to cell B27 to obtain the RIC for all the periods. a. In cell C12 enter the calculation date (todays date in the example) and in cell D12, 100% as first discount factor. b. In cell B12 use RtUpdate() to retrieve the maturity date and the discount factor corresponding to each RIC.
8.2.4
Step 9
AdCreditStructure()
Action Choose cell A33, and choose Insert -> Function.
30 July 2005
165
Reuters Financial Software PowerPlus Pro Workbook Step a. Action Choose Adfin Credit in Function Category and AdCreditStructure() in Function name then click OK. The dialog box of the AdCreditStructure() function appears. This function builds a risk model array.
8.2.5
Arguments
The AdCreditStructure() function is composed of five arguments. Name Description
RateArray InstrumentArray CreditStructure RateStructure AdMode
Argument defining the interest rate model. Array of instruments used for the calibration. Extended argument defining the structure of the credit model. Extended argument defining the structure of the interest rate model. Extended argument customizing the return value
8.2.6
Step 10
Example Using AdCreditStructure()

Action a. In RateArray: choose the zero coupon curve (dates and df): C12:D27. b. In InstrumentArray: choose the CDS spread curve (start date, maturity date, CDS spread, CdsStructure): B4:E7. c. In CreditStructure: choose B30, the CreditStructure is:
RISKMODEL:CURVE RECOVERY:0.3 NBDAYS:5 INSTTYPE:CDS

d. The risk model is a Probability Curve, the Recovery rate in case of default of the CDS is 30%, the time frame to price this American CDS is 5 days, the Instrument Type is a CDS. e. In RateStructure choose B29, the RateStructure is: RM:YC ZCTYPE:DF
IM:CUBD
The RateModel is a Yield Curve, the curve is based on Discount Factors and the Interpolation Method is cubic on discount factors. a. a. a. Click OK. The first date of the Default Probability curve appears in cell A33. Choose an array of cells (A33:B37) such that the original cell is at the upper-left hand corner of the array. Press F2 to edit the function in the cell.
30 July 2005
166
Reuters Financial Software PowerPlus Pro Workbook Step a. Action Press CTRL+SHIFT+ENTER to make it an array function.
8.3 Calculate the Net Present Value (NPV) of a Credit Default Swap with the Probability Curve Risk Model
8.3.1
Step 11 a.
AdCdsNpv()
Action Choose cell E35, and choose Insert -> Function. Choose Adfin Credit in Function Category and AdCdsNpv() in Function name then click OK. The dialog box of the AdCdsNpv() function appears. This function calculates the NPV of a Credit Default Swap.
8.3.2
Arguments
The AdCdsNpv() function is composed of nine arguments. Name Description
SettlementDate StartDate Maturity Spread RateArray CreditArray CdsStructure CreditStructure RateStructure
Calculation date. Start date. Maturity date or code. Credit Default Swap spread in b.p. Argument defining the interest rate model. Argument defining the credit model Extended argument defining the structure of the cds Extended argument defining the structure of the credit model. Extended argument defining the structure of the interest rate model.
30 July 2005
167
8.3.3
Step 12
Example Using AdCdsNpv()

Action a. In SettlementDate: choose the calculation date of the CDS(B4). b. In StartDate: choose the start date (Today() for instance: cell C12). c. In Maturity: in the cell E33, input the maturity code (4Y in the example) or the maturity date of the CDS. d. In Spread: in the cell E34, input the spread from which the NPV of the CDS will be calculated (200 b.p. in the example). e. In RateArray: choose the zero coupon curve (dates and DFs): C12:D27. f. In CreditArray: choose the default probability curve previously calculated with AdCreditStructure() : A33:B37. g. As explained previously in CdsStructure: choose E4, the CdsStructure is:

h. i. a. As explained previously, in CreditStructure: choose B30, the CreditStructure is: As explained previously in RateStructure choose B29, the RateStructure is:
RISKMODEL:CURVE RECOVERY:0.3 NBDAYS:5 INSTTYPE:CDS RM:YC ZCTYPE:DF IM:CUBD

Click OK. The total NPV of the CDS appears in cell E35.
8.4 Calibrate Cox Ingersoll Ross Coefficients from a Credit Default Swap Spread Curve
In the example, the Realtime CDS Spread curve and Discount factors curve used in the previous example are reused.
8.4.1
Step 13 a.
AdCreditStructure()
Action Choose cell B33, and choose Insert -> Function. Choose Adfin Credit in Function Category and AdCreditStructure() in Function name then click OK. The dialog box of the AdCreditStructure() function appears. This function builds a risk model array.
30 July 2005
168
8.4.2
Arguments
As explained previously, the AdCreditStructure() function is composed of five arguments.
8.4.3
Step 14
Example Using AdCreditStructure()

Action a. In RateArray: choose the zero coupon curve (dates and DF): C12:D27. b. In InstrumentArray: choose the CDS spread curve (start date, maturity date, CDS spread, CdsStructure): B4:E7. c. In CreditStructure: choose B30, the CreditStructure is:
RISKMODEL:CIR RECOVERY:0.3 NBDAYS:5 INSTTYPE:CDS

The risk model is the Cox Ingersoll Ross, the Recovery rate in case of default of the CDS is 30%, the time frame to price this American CDS is 5 days, the Instrument Type is a CDS, the CIR calibration is done with an approximate formula. d. In RateStructure choose B29, the RateStructure is:
RM:YC ZCTYPE:DF IM:CUBD

The RateModel is a Yield Curve, the curve is based on Discount Factors and the Interpolation Method is cubic on discount factors.
a. a.
Click OK. The first value of the CIR intensity coefficient appears in cell B33. Choose an array of cells (B33:B35) such that the original cell is at the upper-left hand corner of the array. a. Press F2 to edit the function in the cell. Press CTRL+SHIFT+ENTER to make it an array function.
30 July 2005
169
8.5 Calculate the Spread of a Credit Default Swap from the Cox Ingersoll Ross Intensity Coefficients
8.5.1
Step 15 a.
AdCdsSpread()
Action Choose cell E35, and choose Insert -> Function. Choose Adfin Credit in Function Category and AdCdsSpread() in Function name then click OK. The dialog box of the AdCdsSpread() function appears. This function calculates the NPV of a Credit Default Swap.
8.5.2
Arguments
The AdCdsSpread() function is composed of nine arguments: Name Description
SettlementDate StartDate Maturity Npv RateArray CreditArray CdsStructure CreditStructure RateStructure
Calculation date. Start date. Maturity date or code. Net present value of the credit default swap. Argument defining the interest rate model. Argument defining the credit model. Extended argument defining the structure of the cds. Extended argument defining the structure of the credit model. Extended argument defining the structure of the interest rate model.
8.5.3
Step 16
Example Using AdCdsSpread()

Action a. In SettlementDate: choose the settlement date of the CDS(B4). b. In StartDate: choose the start date (Today for instance: cell C12). c. In Maturity: in the cell E33, input the maturity code (4Y in the example) or the maturity date of the CDS. d. In Npv: in the cell E34, input the Npv from which the spread of the CDS will be calculated (1% in the example). e. In RateArray: choose the zero coupon curve (dates and DF): C12:D27. f. In CreditArray: choose the default probability curve previously calculated with AdCreditStructure(): B33:B35. g. As explained previously in CdsStructure: choose E4, the CdsStructure is:

h. i. As explained previously, in CreditStructure: choose B30, the CreditStructure is: As explained previously in RateStructure choose B29, the RateStructure is:
RISKMODEL:CIR RECOVERY:0.3 NBDAYS:5 INSTTYPE:CDS RM:YC ZCTYPE:DF IM:CUBD
30 July 2005
170
Reuters Financial Software PowerPlus Pro Workbook Step a. Action Click OK. The total Spread of the CDS appears in cell E35.
30 July 2005
171
9 ADFIN FOREX & MONEYMARKET, AND ADFIN COMMON FUNCTIONS

9.1 Overview
Adfin Forex & Money Markets covers all calculations required for the Forex and short-term interest rate markets (swap points, cross currency, forward rates, money calendar). Predefined databases have been created to take account of market conventions and public holidays. Adfin Common contains a library of calculation functions that are useful in conjunction with the other modules. These functions include calculations on dates (calendar management, holiday calculations), and data interpolation.
9.2 Calendar, Currency & Cross Currency Styles

Most Adfin Forex & Money Market functions make use of the Styles concept. The Styles are Reuters PowerPlus Pro databases which take account of all the characteristics necessary to correctly price financial instruments. For example, Currency Style is a group of arguments, which specifies all the calculation parameters necessary for the currency calculations (day-count bases 360 or 365, direct or indirect quotation....). Calendar Style integrates the calendars of public holidays for all the markets. The Cross-Currency Style integrates calculation conventions for the cross. It is possible to see styles by selecting in the Reuters menu the Settings option then Style Management. In the Style Management dialog box, choose the Currency Style section.
30 July 2005
172
Step
Action Double click for example on the EUR - Euro Currency Style. A dialog box opens indicating all the parameters of calculations taken into account by this Currency Style. Thus by using the EUR code in an Adfin Forex & Money Market function, the characteristics of this currency will be automatically taken into account in calculations.
9.3 Adfin Forex & Money Markets and Adfin Common Functions
9.3.1 AdStyleAttribute()
The AdStyleAttribute() function returns the value of a style attribute. This function is located in the Adfin Common category.
9.3.2
Arguments
This function is composed of three arguments:
=AdStyleAttribute(StyleTable, StyleCode, StyleAttribute)

Name Description String argument defining the style table, e.g. CUR for currency. Style code, EUR in the table. Keyword corresponding to the style attribute, e.g. QM for Quotation Mode in the EUR style.
StyleTable StyleCode StyleAttribute
9.3.3
Step 1 2
Example Using AdStyleAttribute()

Action a. b. In cell A3, enter the ISO code of a currency, for example EUR. In cell B1, enter the QM argument.
Choose cell B3 then insert the AdStyleAttribute() function in the Adfin Style category.
30 July 2005
173
Reuters Financial Software PowerPlus Pro Workbook Step 3 Action a. b. c. In StyleTable, enter CUR. In StyleCode, choose cell A3 and put relative address: $A$3. In StyleAttribute, choose cell B1 and put relative address: B$1. Description Quotation Mode (Direct or Indirect), Money market Year Basis (360 or 365), Calendar code, FX market spot offset (0, 1 or 2 days), Money market spot offset (0, 1 or 2 days).
Name
QM YB CLDR DTS DTM
Step
Action Click OK. The Quotation Mode for the currency EUR appears. It is possible to obtain all the other characteristics.
9.3.4
DfAddWD()
The DfAddWD() function adds a number of working days to a date. This function is located in the Adfin Common category.
9.3.5
Arguments
This function is composed of four arguments:
=DfAddWD(Calendars, CalcDate, NbDays, DfMode )

Name Description String of calendar codes (example: FRA,UKG). Calculation date. Number of working days (from -366 to 366). Extended argument customizing the return value.
Calendars CalcDate NbDays DfMode
30 July 2005
174
9.3.6
Step 1
Example Using DfAddWD()

Action a. In cell A1, enter the calculation date. b. In cell C1, enter the figure 2 to indicate working days from the calculation date value. Choose cell C3 and insert the DfAddWD() function from the Adfin Common category. a. In Calendars, choose cell B3. b. In CalcDate, choose cell A1. c. In NbDays, choose cell C1. Click OK. Convert the result obtained to a date format.
9.3.7
DfAddPeriod()
The DfAddPeriod() function adds a period (number of calendar days, working days, weeks, months or years) to a date. This function is located in the Adfin Common category.
9.3.8
Arguments
The DfAddPeriod() function is composed of four arguments:
=DfAddPeriod(Calendars, CalcDate, Period, DfMode)

Name Description String of calendar codes (example: "FRA,UKG") Calculation date. Period code {iD, iM, iW, iWD, iY, with i as integer} iD i calendar days iM i months iW i weeks iWD i working days (from -366 to 366) iY i years Extended argument customizing the return value.
Calendars CalcDate Period
DfMode
In the following figure, 3 months are added to the 26-May-05 date. Example of period code: 5D for 5 days, 2M for 2 months, 1Y for 1 year.
30 July 2005
175
9.3.9
DfListHolidays()
The DfListHolidays() function lists one or several calendars holidays between two dates. This function is located in the Adfin Common category.
9.3.10 Arguments
This function is composed of four arguments:
=DfListHolidays(Calendars, StartDate, EndDate, DfMode)

Name Description String of calendar codes (example: "FRA,UKG"). Period start date. Period end date. Extended argument customizing the return value.
Calendar StartDate EndDate DfMode
9.3.11 Example Using DfListHolidays()

Step 1 Action a. In cell B1, type the currency code. b. Use the AdStyleAttribute() function to obtain the relevant calendar and type it in the cell D1. c. In cell B2, enter the start date. d. In cell D2, enter the end date. a. Choose cell D4 and insert the DfListHolidays() function in the Adfin Common category. b. In Calendars, choose cell D1. c. In StartDate, choose cell B2. d. In EndDate, choose cell D2. e. The DfMode argument customises the return value. The keyword RET:B6 creates an array with two columns and six rows. Click OK. The first value appears in cell D4. Choose the range D4:E12, press the F2 key to edit, and then press Ctrl+Shift+Enter.
3 4
9.3.12 FxCross()
The FxCross() function calculates the spot cross rate assuming that the cross value date and the spot dates are equal.
30 July 2005
176
9.3.13 Arguments
This function is composed of four arguments. It is located in the Adfin Forex & Money Market category:
=FxCross(Cur1Cur2, Spot1BA, Spot2BA, FxMode)

Name Description Cross currency code (example: "GBPEUR"). Cur1 spot rates (array Bid/Ask). Cur2 spot rates (array Bid/Ask). Extended argument customizing the return value.
Cur1Cur2 Spot1BA Spot2BA FxMode
9.3.14 Example Using FxCross()

Use the RtGet() function to obtain the fieldname (BID/ASK) spot rates for realtime currencies. Step Action 1 2 3 In cell B5, enter the formula =B2&B3 to add the character strings. In cell C5, insert the FxCross() function in Adfin Forex & Money Market category. a. b. c. In Cur1Cur2, choose cell B5. In Spot1BA, choose cells C2:D2. In Spot2BA, choose cells C3:D3.
4 5
Click OK. The first value appears in cell C5. Choose the range C5:D5, press the F2 key to edit, then press Ctrl+Shift+Enter.
9.3.15 AdFxSwpToSwp()
The AdFxSwpToSwp() function calculates the swap points of FX cross-currencies, given the FX spot and the FX swap points of the two currencies.
9.3.16 Arguments
The AdFxSwpToSwp() function is composed of nine arguments:
=AdFxSwpToSwp( CalcDate, PeriodArray, Cur1Cur2, Spot1BA, Spot2BA, Swp1Array, Swp2Array, CalcStructure, AdMode)
Name Description Calculation Date (Contract or Trading date). Array of periods or dates (one column for single periods, two columns for forward periods). Cross-currency, followed by a CrossStructure.
CalcDate PeriodArray Cur1Cur2
30 July 2005
177
Reuters Financial Software PowerPlus Pro Workbook Name Description Cur1 spot rates (Bid/Ask). Cur2 spot rates (Bid/Ask). Cur1 Array of periods (or dates) and swap points (Bid/Ask). Cur2 Array of periods (or dates) and swap points (Bid/Ask).
Spot1BA Spot2BA Swp1Array Swp2Array
CalcStucture Extended argument defining the calculation method. AdMode

Extended argument customizing the return value
9.3.17 Return Value

The return value is an array with one row for each period or date from the PeriodArray argument. Each row consists of these cells. Return Value Cell Description
SwpBid12 SwpAsk12 StartDate12 EndDate12 OutRightBid12 OutRightAsk12 Cur1 Swap Point Bid Cur1 Swap Point Ask Cur2 Swap Point Bid Cur2 Swap Point Ask
Bid side of the swap points for the cross-currency Cur1Cur2. Ask side of the swap points for the cross-currency Cur1Cut2 Start date of the period for cross-currency. End date of the period for cross-currency. Bid side of the outright for the cross-currency Cur1Cur2. Ask side of the outright for the cross-currency Cur1Cur2. Bid side swap points for currency 1. Ask side swap points for currency 1. Bid side swap points for currency 2. Ask side swap points for currency 2.
9.3.18 Example Using AdFxSwpToSwp()

Reproduce exactly the following table.
Step
Action
30 July 2005
178
Reuters Financial Software PowerPlus Pro Workbook Step 1 Action a. b. c. d. a. b. In cell D2, enter the GBP code. In cell D3, enter the EUR code. In cell D4, enter the formula =D2&D3. In cell D5, enter 1M for 1 month. In cell F3, enter =D2&"=" to obtain the RIC from the currency code. In cell F4, enter =D3&"=" to obtain the RIC from the currency code.
2 3
a. In cell F7, enter =$D$2&G7&"=", and fill down from cell F7 to cell F13 to obtain the swap point 1M RIC from the GBP currency. b. In cell F15, enter =$D$3&G15&"=", and fill down from cell F15 to cell F21 to obtain the swap point 1M RIC from the EUR currency. c. Use the RtGet() function to obtain the fieldname (BID/ASK) spot rates for realtime currencies and swap point. d. In cell B9, enter =TODAY(). a. Use the FxCalcPeriod() function to calculate the start date and the end date of a period. b. In cell C9, enter all arguments requested to obtain the following formula: =FxCalcPeriod(B9;D4;D5).
1.
5 a. b. c.
This is an array function. Extend the result to cell D9, press the F2 key to edit, and then press Ctrl + Shift + Enter. Select cell C24 and insert the AdFxSwpToSwp() function. Enter all arguments requested to obtain the following formula: Click OK.
=AdFxSwpToSwp(C9;G7:G13;D4;G3:H3;G4:H4;G7:I13;G15:I21)
2.
This is an array function. Extend the result to cell L30, press the F2 key to edit, and then press Ctrl + Shift + Enter.
9.3.19 AdFxDepToSwp()
The AdFxDepToSwp() function calculates the swap points of the FX cross-currencies, given the FX spot and the deposit rate of the two currencies.
9.3.20 Arguments
The AdFxDepToSwp() function is composed of ten arguments:
=AdFxDepToSwp(CalcDate, PeriodArray, Cur1Cur2, Spot1BA, Spot2BA, Dep1Array, Dep2Array, DepPivotArray, CalcStructure, AdMode)
30 July 2005
179
Reuters Financial Software PowerPlus Pro Workbook Name Description Calculation Date (Contract or Trading date). Array of periods or dates. Cross-currency, followed by a CrossStructure. Cur1 spot rates (Bid/Ask). Cur2 spot rates (Bid/Ask). Cur1 Array of periods (or dates) and deposit rates (Bid/Ask). Cur2 Array of periods (or dates) and deposit rates (Bid/Ask). Pivot currency array of periods (or of dates) and deposit rates (Bid/Ask). Extended argument defining the calculation method. Extended argument defining the return value
CalcDate PeriodArray Cur1Cur2 Spot1BA Spot2BA Dep1Array Dep2Array DepPivArray CalcStucture AdMode
9.3.21 Return Value

The return value is an array with one row for each period or date from the PeriodArray argument. Each row consists of ten cells. Return Value Cell Description
SwpBid12 SwpAsk12 StartDate12 EndDate12 OutRightBid12 OutRightAsk12 Dep1Bid Dep1Ask Dep2Bid Dep2Ask
Bid side of the swap points for the cross-currency Cur1Cur2. Ask side of the swap points for the cross-currency Cur1Cut2 Start date of the period for cross-currency. End date of the period for cross-currency. Bid side of the outright for the cross-currency Cur1Cur2. Ask side of the outright for the cross-currency Cur1Cur2. Bid deposit of the first currency in the period. Ask deposit of the first currency in the period. Bid deposit of the second currency in the period. Ask deposit of the second currency in the period.
9.3.22 Example Using AdFxDepToSwp()

30 July 2005
180
Reuters Financial Software PowerPlus Pro Workbook Step 1 Action a. b. c. d. a. b. a. b. In cell E3, enter the GBP code. In cell E4, enter the EUR code. In cell E5, enter the formula =E3&E4. In cell E6, enter 1M for 1 month. From cell H4 to cell H8, enter 1M, 2M, 3M, 1Y, 2Y. From cell H10 to cell H14, enter 1M, 2M, 3M, 1Y, 2Y. In cell C9, enter =E3&"=" to obtain the RIC from the currency code. In cell C10, enter =E4&"=" to obtain the RIC from the currency code.
2 3 4
a. In cell G4, enter =$E$3&H4&"D=", and fill down from cell G4 to cell G8 to obtain the deposit RIC from the GBP currency. b. In cell G10, enter =$E$4&H10&"D=", and fill down from cell G10 to cell G14 to obtain the 1M deposit RIC from the EUR currency. a. Use the RtGet() function to obtain the fieldnames (BID/ASK) spot rates for realtime currencies and deposit rates. b. Apply percentage format for the deposit rate from the GBP currency. c. In cells D13 and E13, calculate the spot cross rate GBPEUR with the FxCross() function. d. In cell C16, enter =TODAY(). Use the FxCalcPeriod() function to calculate the start date and the end date of a period. In cell D16, enter all arguments requested to obtain the following formula: =FxCalcPeriod(C16;E5;E6).
3.
This is an array function. Extend the result to cell E16, press the F2 key to edit, and then press Ctrl + Shift + Enter.
30 July 2005
181
Reuters Financial Software PowerPlus Pro Workbook Step 7 Action a. b. c. Select cell C19 and insert the AdFxDepToSwp() function. Enter all arguments requested to obtain the following formula: Click OK.
=AdFxDepToSwp(D16;H4:H8;E5;D9:E9;D10:E10;H4:J8;H10:J14)
4.
This is an array function. Extend the result to cell L23, press the F2 key to edit, and then press Ctrl + Shift + Enter.
9.3.23 AdFxSwpToDep()
The AdFxSwpToDep() function calculates the deposit rate of one currency, given the FX spot and the FX swap points of the two currencies, and the deposit rate of the other currency.
9.3.24 Arguments
The AdFxSwpToDep() function is composed of ten arguments:
=AdFxSwpToDep(CalcDate, PeriodArray, Cur1Cur2, Spot1BA, Spot2BA, Swp1Array, Swp2Array, DepArray, CalcStructure, AdMode)
Name Description Calculation Date (Contract or Trading date). Array of periods or of dates. Cross-currency, followed by a CrossStructure. Cur1 spot rates (Bid/Ask). Cur2 spot rates (Bid/Ask).
CalcDate PeriodArray Cur1Cur2 Spot1BA Spot2BA
30 July 2005
182
Reuters Financial Software PowerPlus Pro Workbook Name Description Cur1 Array of periods (or dates) and swap points (Bid/Ask). Cur2 Array of periods (or dates) and swap points (Bid/Ask). Array of periods (or dates) and deposit rates for one currency. Extended argument defining the calculation method. Extended argument customizing the return value.
Swp1Array Swp2Array DepArray CalcStucture AdMode
9.3.25 Return Value

The return value is an array with one row for each period or date from the PeriodArray argument. Each row consists of six cells. Return Value Cell Description
DepBid DepAsk StartDate EndDate DepInputBid DepInputAsk
Bid side of the deposit. Ask side of the deposit. Start date of the period. End date of the period. Bid deposit of the input currency in the period. Ask deposit of the input currency in the period.
9.3.26 Example Using AdFxSwpToDep()

Step 1
Action a. b. c. d. In cell D2, enter the GBP code. In cell D3, enter the EUR code. In cell D4, enter the formula =D2&D3. In cell D5, enter 1M for 1 month.
30 July 2005
183
Reuters Financial Software PowerPlus Pro Workbook Step 2 Action a. b. c. a. b. From cell G3 to cell G7, enter 1M, 2M, 3M, 1Y, 2Y. From cell G10 to cell G14, enter 1M, 2M, 3M, 1Y, 2Y. From cell G16 to cell G20, enter 1M, 2M, 3M, 1Y, 2Y. In cell B8, enter =D2&"=" to obtain the RIC from the currency code. In cell B9, enter =D3&"=" to obtain the RIC from the currency code.
3 4
a. In cell F3, enter =$D$2&G3&"D=", and fill down from cell F3 to cell F7 to obtain the deposit RIC from the GBP currency. b. In cell F10, enter =$D$2&G3&"=", and fill down from cell F10 to cell F14 to obtain the swap point RIC from the GBP currency. c. In cell F16, enter =$D$3&G3&"=", and fill down from cell F16 to cell F20 to obtain the swap point RIC from the GBP currency. a. Use the RtGet() function to obtain the fieldnames (BID/ASK) spot rates for realtime currencies and deposit rates. b. Apply percentage format to cells (H3:I7) for the deposit RIC from the GBP currency. c. In cell B12, enter =TODAY(). Use the FxCalcPeriod() function to calculate the start date and the end date of a period. In cell C12, enter all arguments requested to obtain the following formula: =FxCalcPeriod(B12;D4;D5).
5. This is an array function. Extend the result to cell D12, press the F2 key to edit,
and then press Ctrl + Shift + Enter. 7 a. b. Select cell B23 and insert the AdFxSwpToDep() function. Enter all arguments requested to obtain the following formula:
=AdFxSwpToDep(C12;G3:G7;D4;C8:D8;C9:D9;G10:I14;G16:I20;G3: I7;;"RES:DEP2")
c. Click OK.
6. This is an array function. Extend the result to cell G27, press the F2 key to edit,
and then press Ctrl + Shift + Enter.
30 July 2005
184
Reuters Financial Software PowerPlus Pro Workbook Step 8 Action Use keyword RES:DEP2 in AdMode argument to calculate the second currency (EUR) deposit rate. Apply percentage format for Bid and Ask side of the deposit cells (B23:C27) and Bid and Ask deposit of the input currency in the period (F23:G27).
9.3.27 AdInterp()
The AdInterp() interpolates a point from a curve according to a linear, loglinear, volatility or cubic spline method.
9.3.28 Arguments
The AdInterp() function is composed of four arguments:
=AdInterp(X, DateArray, ValueArray, InterpMode)

Name Description X value to be used for the interpolation. Array of X dates. Array of Y values. Extended argument defining the interpolation.
X DateArray ValueArray InterpMode
9.3.29 Example of Linear Interpolation Using AdInterp()

In the example, for each bond in the table below (European benchmark curve, EUBMK= curve), calculate the interpolated yield at the date of 26th Sept 2005.
30 July 2005
185
Step 1 2
Action Choose cell H9, and choose Insert -> Function. Choose Adfin Common in Function Category and AdInterp() in Function name then click OK. The dialog box of the AdInterp() function appears. a. In X, choose cell G9 (date at which we want to calculate the interpolated value, 26 Sept 05). b. In DateArray, choose cells C4:C19. c. In ValueArray, choose cells D4:D19. d. In InterpMode type: IM:LIN to perform a linear interpolation or leave the argument blank as the default value for IM is IM:LIN. Click OK. The interpolated rate at the X date appears in cell H9.
30 July 2005
186
9.3.30 Example of Cubic Interpolation Using AdInterp()

To perform a CUBIC interpolation, use the same function with the same arguments but change the InterpMode: Step 1 2 Action Choose cell H10, and choose Insert -> Function. Choose Adfin Common in Function Category and AdInterp() in Function name then click OK. The dialog box of the AdInterp() function appears. a. In X, choose cell G9 (date at which we want to calculate the interpolated value, 26 Sept 05). b. In DateArray, choose cells C4:C19. c. In ValueArray, choose cells D4:D19. d. In InterpMode type: IM:CUBR to perform a cubic interpolation on rates. Click OK. The interpolated rate at the X date appears in cell H10.
9.3.31 Example of Linear Extrapolation Using AdInterp()

Calculate the extrapolated yield at the date of 28th May 2040 (an extrapolated value will be returned even if the date is out of the array boundaries). Step Action 1 2 Choose cell H12, and choose Insert -> Function. Choose Adfin Common in Function Category and AdInterp() in Function name then click OK. The dialog box of the AdInterp() function appears. a. In X, choose cell G12 (date at which we want to calculate the extrapolated value, 28th May 40). b. In DateArray, choose cells C4:C19. c. In ValueArray, choose cells D4:D19. d. In InterpMode type: IM:LIX to perform a linear extrapolation.
30 July 2005
187
Reuters Financial Software PowerPlus Pro Workbook Step 4 Action Click OK. The extrapolated rate at the X date appears in cell H12.
Note: If you want to prevent Adfin from extrapolating add OBC:YES (Out of Boundary interpolation Check) to the InterpMode argument. If you want to perform a cubic extrapolation, type IM:CUBX in InterpMode instead of IM:LIX.
9.3.32 AdInterpolation()
The AdInterpolation() function interpolates a point or an array from a curve or a surface according to a linear, loglinear, volatility or cubic spline method.
9.3.33 Arguments
The AdInterpolation() function is composed of three arguments:
AdInterpolation(ToInterpolate, InputArray, InterpMode)

Name Description Array of values to interpolate (instead of only one value in AdInterp() function). X and Y array in AdInterp() function. Extended argument defining the interpolation.
ToInterpolate InputArray InterpMode
9.3.34 Linear Interpolation Example Using AdInterpolation()

In the example, calculate the interpolated yield at the date of the maturity date of each bond for the ZC array in the table below.
30 July 2005
188
Step 1 2
Action Choose cell D6, and choose Insert -> Function. Choose Adfin Common in Function Category and AdInterpolation() in Function name then click OK. The AdInterpolation() function dialog box appears. a. In ToInterpolate, select cells B6:B15 (date at which we want to calculate the interpolated value, 19 Feb 05) b. In InputArray, select cells E6:F15. c. In InterpMode: select cell D2. ND:ERR is used to generate error messages for a null date in the date array. LAY:V orientates the return array vertically. Alternatively, you can leave the InterpMode argument blank as the default value for IM is IM:LIN. To perform a linear interpolation on volatility curves, use the same function with the same arguments but change the InterpMode argument to IM:VOL. Click OK. Select cells D6:D15, press F2, then Ctrl+Shift+Enter. The interpolated rate at the ToInterpolate date appears in cells D6:D15.
4 5
30 July 2005
189
9.3.35 Perform a Cubic Interpolation with AdInterpolation()

To perform a cubic interpolation, use the same function and arguments as described in Linear Interpolation Example Using AdInterpolation() but change the InterpMode argument to: IM:CUBD for cubic spline discount factor interpolation IM:CUBR for cubic spline rate interpolation IM:CUBS for natural cubic spline interpolation IM:CUBX for natural cubic spline extrapolation
9.3.36 Linear and Cubic Interpolation on a Surface with AdInterpolation

In the example, we apply linear and cubic interpolation to a surface using the AdInterpolation function.
Step 1
Action In the exercise, choose cell A24, and choose Insert -> Function.
30 July 2005
190
Reuters Financial Software PowerPlus Pro Workbook 2 Choose Adfin Common in Function Category and AdInterpolation() in Function name then click OK. The AdInterpolation() function dialog box appears.
4 5
a. In ToInterpolate: select cells A3:B5 (date at which we want to calculate the interpolated value, 19 Feb 05) b. In InputArray: select cells B8:O19. c. In InterpMode type: select A22. d. "IM:CUBS LAY:V OBC:NO ND:ERR ND:DIS" natural cubic spline interpolation without extrapolation, ND:DIS keyword to discard null dates from the input array. Click OK. Select cells A24:A26, press F2, then Ctrl + Shift + Enter.
30 July 2005
191
10 ADFIN OPTIONS FUNCTIONS

10.1 Overview and Concepts
10.1.1 Overview
Adfin Options covers the vast majority of plain vanilla options traded globally, including options on stocks and instruments with discrete future cash flows, as well as warrants. This module also contains functions for exotic options (Asian, barrier, rainbow, cliquet, basket...), swaptions and options on bonds using the Black-Derman-Toy model. Adfin Option functions support calculations of premium, implied volatility and Greeks derivatives. The available models are Black and Scholes, Whaley, Garman-Kohlagen, Constant Elasticity of Variance (CEV), Cox, Ross and Rubinstein, Black, Derman and Toy and Hull and White. Reuters PowerPlus Pro also includes caps, floor and collar coverage: amortizing cap, reset in arrears (with the option to adjust for convexity bias) digital cap, barrier cap.
10.1.2
Structure Concepts
Most of the Adfin Options, Exotics, and Interest Rate Derivatives functions use the structures described below.
10.1.2.1 OptionStructure Argument

The OptionStructure argument is used to define the option characteristics such as the option type (call or put), the calendar linked to the option, the day count basis, the dividend payment date and amount, the exercise mode of the option (American, European or Bermudan) and the underlying asset type (option on commodities, on currencies, on futures or on securities). To access the complete list of the available arguments, refer to the online help for the OpPremium() function for instance.
30 July 2005
192
10.1.2.2 ExoticStructure Argument

The Adfin Exotics functions are based on the ExoticStructure concept. The ExoticStructure argument is a group of keywords, which specifies the parameters necessary to define an Exotic Option. For example, ExoticStructure integrates calculation methods (Asian option, Binary option, Barrier option, Rainbow option...) of the exotics option, the option type (CALL or PUT), the exercise mode (EXM:AMERICAN or EXM:EURO), the knock-out barrier flag (KO), knock-in down-barrier and knock-out up-barrier flag (KIKO), rebate amount for barrier and double barrier options (REBATE). To access the complete list of the available arguments, refer to the online help for ExoticStructure.
10.1.2.3 CapFloorStructure
The Adfin Cap, Floor, Collar functions are based on the CapFloorStructure concept. The CapFloorStructure argument is a group of keywords, which specifies the parameters necessary to define Cap, Floor and Collar. For example, CapFloorStructure integrates the type of the interest rate derivative (CAP, FLOOR, COLLAR), the knock-in barrier flag (KI), knock-out barrier flag (KO), rebate adjustment to the frequency of the cap (RADJ). To access the complete list of the available arguments, click for example on the online help of the AdCapFloorPremium() function, then click CapFloorStructure.
30 July 2005
193
10.1.2.4 RateStructure Argument

Most of the Adfin Options functions use the RateStructure argument. The RateStructure argument is a group of keyword/value pairs which specify the rate model for pricing instruments. One can choose the rate type (continuous, money market), the rate model (Yield Curve, constant rate, BDT, HullWhite), the zero-coupon yield curve type (rate or discount factor) To access the complete list of the available arguments, refer to the online help for the OpPremium() function for instance (see the preceding figure and choose RateStructure instead of OptionStructure). Reuters PowerPlus Pro provides some predefined Rate Model styles for the principal types of Rate Model available (select Reuters -> Settings -> Style Management and look at the Yield To Maturity page).
10.1.2.5 CalcStructure Argument

The CalcStructure argument defines the possible calculation methods. It is a very important argument for option pricing. It is in CalcStructure that you choose calculation model type: (tree, formula, or finite differences), the formula type (Black & Scholes Model, Whaley Model). To access the complete list of the available arguments, refer to the online help for the OpPremium() function for instance (see the preceding figure and click on CalcStructure instead of OptionStructure).
30 July 2005
194
10.2 Adfin Options Functions

10.2.1 OpHistVol()
Step 1 2 Action Choose Insert -> Function. Choose Adfin Options and choose OpHistVol(). The OpHistVol() function calculates the historical volatility of an option from a set of underlying prices.
10.2.1.1 Arguments
The OpHistVol() function is composed of two arguments:
=OpHistVol(PriceArray, OpMode)
Name Description 1-dimensional (for HVM:CLOSE) or 2-dimensional (for HVM:HL) array containing the prices (the orientation of this array should be specified with the LAY keyword in OpMode if it is different from the default orientation). Extended argument customizing the return value.
PriceArray
OpMode
10.2.1.2 Example Using OpHistVol()

Reproduce exactly the following table. Step Action 1 a. In cell B3, use RtGet() function to obtain the realtime quotation of the desired equity (in our example FTE.PA). b. In cell A5, use RtHistoryInfo() function to obtain equity historical fields. c. In cell A7, use the RtHistory() function to obtain equity historical data. d. Specify as arguments NBEVENTS:100 in HistoryStructure (Number of historical data retrieved) and NULL:SKIP in HistoryMode (to skip the dates with any null field). For more details on the RtHistoryInfo() and RtHistory() functions use, see Integration of Realtime Data: Adfin Real Time Functions. Choose cell D8 and insert OpHistVol() function, which is in the Adfin Options function category. In the dialog box of the function, for the PriceArray argument, choose cells B8:B103.
30 July 2005
195
Reuters Financial Software PowerPlus Pro Workbook Step 3 Action Click OK.
10.2.2 Implied Volatility Calculation: OpImpliedVol()

Step 1 2 Action Choose Insert -> Function. Choose Adfin Options and choose OpImpliedVol().
10.2.2.1 Arguments
The OpImpliedVol() function is composed of ten arguments.
=OpImpliedVol(CalcDate, ExpiryDate, SpotPrice, StrikePrice, Premium, RiskFreeRateArray, ReturnArray, OptionStructure, RateStructure, CalcStructure)
Name Description Calculation date. Expiry date of the option. Market or spot price of the underlying instrument. Exercise or strike price of the option. Market or spot price of the option. Term Structure array. Depending on the model, this array has several shapes: a Constant Yield (Value), a Zero Coupon Curve (Date, Value), Dividend array (Date, Dividend) Commodities: carrying costs Currencies: risk free rate for the foreign currency Futures: 0 Stocks: annual security yield Extended argument defining the option structure Extended argument defining the interest rate model
CalcDate ExpiryDate SpotPrice StrikePrice Premium RiskFreeRateArray
ReturnArray
OptionStructure RateStructure
30 July 2005
196
Reuters Financial Software PowerPlus Pro Workbook Name Description Extended argument defining the calculation method.
CalcStructure
10.2.2.2 Example Using OpImpliedVol()

Reproduce the following table. Step Action 1 a. b. c. d. In cell B1, insert the calculation date. Use as example =today() function. In cell A5, insert the RIC for the equity. In cells B4 and B5, insert LAST and YIELD fieldnames. Use the RtGet() function to obtain realtime data for the equity.
a. In cell A9, insert the option RIC. b. In cells B8, C8 and D8, enter LAST, STRIKE PRICE and EXPIRE DATE fieldnames. Use the RtUpdate() function to obtain realtime data for the option. For more details on RtUpdate() function, see RtUpdate(). In cell B11, enter the risk-free rate. Apply the percentage format (%) to this rate. In cells B14, B15 and B16, define the OptionStructure argument. CALL
3 4
EXM:EUR UI:SEC
a. In B14 enter the CALL argument. b. In B15 enter the Exercise Mode of the option: EXM:EUR (European Option) c. In B16 set the underlying asset to UI:SEC (UI:SEC has to be chosen for options on indexes, stocks, and bonds). 5 6 In cell E14 define the RateStructure: RATETYPE:CONT This specifies that continuous rates are used. In cells H14 and H15, define the CalcStructure. To choose the Black and Scholes model to price this option add the two following keywords in the CalcStructure: CMT:FORM FT:BS CMT:FORM (to specify that the Calculation Model Type is a formula) FT:BS (to set the Formula Type to Black and Scholes). Choose cell B18, and choose Insert -> Function. Choose Adfin Options and choose OpImpliedVol(), then click OK. Reuters PowerPlus Pro opens the OpImpliedVol() function dialog box, Enter all arguments requested to obtain the following formula:
7 8 9 10
=OpImpliedVol(B1,D9,B5,C9,B9,B11,C5,B14:B16,E14,H14:H15)
Click OK.
10.2.3 OpPremium()
The OpPremium() function calculates the premium of an option.
10.2.3.1 Arguments OpPremium() is composed of ten arguments.
30 July 2005
197
=OpPremium (CalcDate, ExpiryDate, SpotPrice, StrikePrice, Volatility, RiskFreeRate, ReturnArray, OptionStructure, RateStructure, CalcStructure)
Name Description Calculation date. Expiry date of the option. Market or spot price of the underlying instrument. Exercise or strike price of the option. Volatility of the underlying instrument. Term Structure array. Depending on the model, this array has several shapes: a Constant Yield (Value), a Zero Coupon Curve (Date, Value), a (Date, Rate, Volatility) array for the Black Derman Toy model a (Date, Rate, Volatility, Mean-reversion) array for the Hull & White model. Dividend array (Date, Dividend) Commodities: carrying costs Currencies : risk free rate for the foreign currency Futures : 0 Stocks: annual security yield. Extended argument defining the option structure. Extended argument defining the interest rate model. Extended argument defining the calculation method.
CalcDate ExpiryDate SpotPrice StrikePrice Volatility RiskFreeRateArray
ReturnArray
OptionStructure RateStructure CalcStructure
10.2.3.2 Example Using OpPremium()

Reproduce the following table. Step Action 1 2 In cell B1, enter the calculation date. Use for example =today() function. a. b. c. In cell A5, enter the RIC for the equity. In cells B4 and B5, enter LAST and YIELD fieldnames. Use RtUpdate() function to obtain realtime data for the equity.
4 5
a. In cell A9, insert the option RIC. b. In cells B8, C8 and D8, enter LAST, STRIKE PRICE and EXPIRE DATE fieldnames. Use RtUpdate() function to obtain realtime data for the equity. a. In cell B11, enter the risk free rate (apply the percentage format). b. In cell B18, enter the volatility (apply the percentage format) a. In cells B14, B15 and B16, define the OptionStructure argument as explained in the preceding example. b. In cell E14 define the RateStructure as explained in the preceding example. c. In cells H14 and H15, define the CalcStructure as explained in the preceding example. Choose cell B20, and choose Insert -> Function. Choose Adfin Options in Function Category and OpPremium() in Function name, then click OK. The dialog box of the OpPremium() function appears. Enter all arguments requested to obtain the following formula:
6 7
=OpPremium(B1,D9,B5,C9,B18,B11,C5,B14:B16,E14,H14:H15)
30 July 2005
198
10.2.4 Greek Indicators Calculation: OpCalcDeriv()

The OpCalcDeriv() function returns in an array all derivatives (delta, gamma, theta, vega, rho, gearing and break even time) of an option. In the case of a currency option four more derivatives are returned by the function: delta foreign, gamma foreign, theta foreign, vega foreign and rho foreign).
10.2.4.1 Arguments OpCalcDeriv() is composed of 11 arguments. =OpCalcDeriv(CalcDate, ExpiryDate, SpotPrice, StrikePrice, Volatility, RiskFreeRateArray, ReturnArray, OptionStructure, RateStructure, CalcStructure, AdMode)
Name Description Calculation date. Expiry date of the option. Market or spot price of the underlying instrument. Exercise or strike price of the option. Volatility of the underlying instrument. Term Structure array. Depending on the model, this array has several shapes: a Constant Yield (Value), a Zero Coupon Curve (Date, Value), a (Date, Rate, Volatility) array for the Black Derman Toy model an (Date, Rate, Volatility, Mean-reversion) array for the Hull & White model. Dividend array (Date, Dividend) Commodities: carrying costs Currencies: risk free rate for the foreign currency Futures: 0 Stocks: annual security yield Extended argument defining the option structure. Extended argument defining the interest rate model Extended argument defining the calculation method. Extended argument customizing the return value.
CalcDate ExpiryDate SpotPrice StrikePrice Volatility RiskFreeRateArray
ReturnArray
OptionStructure RateStructure CalcStructure AdMode
30 July 2005
199
10.2.4.2 Example Using OpCalcDeriv()

Reproduce the following table. Step Action 1 2 In cell B1, enter the calculation date. Use for example =today() function. a. b. In cell A5, enter the RIC for the equity. In cells B4 and B5, enter LAST and YIELD fieldnames. Use the RtUpdate() function to obtain realtime data for the equity.
5 6
a. In cell A9, insert the option RIC. b. In cells B8, C8 and D8, enter LAST, STRIKE PRICE and EXPIRE DATE fieldnames. Use the RtUpdate() function to obtain realtime data for the equity. a. In cell B11, enter the risk free rate (apply the percentage format). b. In cell B18, enter the volatility (apply the percentage format) c. In cells B14, B15 and B16, define the OptionStructure argument as explained in the preceding example. d. In cell E14 define the RateStructure as explained in the preceding example. e. In cells H14 and H15, define the CalcStructure as explained in the preceding example. Choose cell B22, and choose Insert -> Function. Choose Adfin Options in Function Category and OpCalcDeriv() in Function name then click OK. The dialog box of the OpCalcDeriv() function appears. Enter all arguments requested to obtain the following formula:
7 8
=OpCalcDeriv(B1,D9,B5,C9,B18,B11,C5,B14:B16,E14,H14:H15)
Click OK. The delta of the option is displayed. a. Press F2 to edit the function in the cell. b. Press Ctrl+Shift+Enter to make it an array function.
Note:
The OpPremium() and OpCalcDeriv() functions support non-constant volatility (volatility surface) within tree-based pricing method. So, to use this functionality, you must define the CalcStructure as follows (as a minimum): CMT:TREE NBBRANCH:2 NBFACTOR:1.
10.2.5 Pricing a Warrant

To price a warrant you need to get information about it using DeUpdate() and then calculate the price using OpPremium().
10.2.5.1 Getting Information about a Warrant Using DeUpdate()

The Reuters database provides views for warrants (see Treasury 3000 Database): from a warrant RIC, the DeUpdate() function returns the following results according to the field names:
30 July 2005
200
Reuters Financial Software PowerPlus Pro Workbook Field Names Results returned by DeUpdate() C for Call, P for Put The OptionStructure of the warrant The expiration date of the warrant
CALL_PUT_TYPE ADF_STRUCTURE EXPIRATION_DATE
We need this data in order to use the OpPremium() function. Reproduce the following table: Step Action 1 2 Choose cell A2, and choose Insert -> Function. Choose Reuters 3000 Data Engine in Function category and DeUpdate() in Function name then click OK. The dialog box of the DeUpdate() function appears. DeUpdate() is composed of six arguments (see DeUpdate() for more information). In CodeList: choose the RIC of the warrant (FR0010189365.PA, cell A3) In FieldList: choose the field names (ADF_STRUCTURE, EXPIRATION_DATE, CALL_PUT_TYPE) c. In DestinationCell, choose B3 In DeMode: type SOURCE:EQUITY a. b.
Retrieve the Volatility of the underlying stock (which RIC is OREP.PA) from the database. Step Action 1 2 Choose cell A6, and choose Insert -> Function. Choose Reuters 3000 DataEngine in Function category and DeUpdate() in Function name then click OK. The dialog box of the DeUpdate() function appears. DeUpdate() is composed of six arguments (see DeUpdate() for more information).
30 July 2005
201
Reuters Financial Software PowerPlus Pro Workbook Step 3 Action a. b. c. d. In CodeList: choose the RIC of the underlying stock (OREP.PA, cell A7). In FieldList: choose the field names (VOLATILITY_200D, cell B6). In DestinationCell, choose B7. In DeMode: type SOURCE:EQUITY.
10.2.5.2 Pricing the Warrant Using OpPremium()

As explained previously, OpPremium() is composed of ten arguments:
=OpPremium(CalcDate, ExpiryDate, SpotPrice, StrikePrice, Volatility, RiskFreeRateArray, ReturnArray, OptionStructure, RateStructure, CalcStructure)
Reproduce the following table: Step Action 1 a. b. c. d. In cell B12, enter the calculation date. In cell B13 enter the spot price (market price, can be retrieved from Real Time). In cell B17 enter the RiskFreeRate (this rate can be retrieved from Real Time). In cell B15 enter the Dividend if required (can also be retrieved from Real Time).
In cells B16, define the CalcStructure: We want to price the option with a Binomial Tree on 60 steps (binomial and trinomial models are recommended to price American warrants):
CMT:TREE TITER:60
It means that the Option will be price using a Tree on 60 calculation steps. If you do not specify the number of branches, the default value is NBBRANCH:2, the option will be priced with a Binomial Tree. Use the data returned from the database for the following arguments: OptionStructure: NBWARRANT:10000000 PUT NBSTOCK:658762176 EXM:E CONVRATIO:0.1 (This warrant is a PUT, and it is a European warrant. By default the dividend type is continuous, you do not need to specify DIVTYPE:CONT.) Volatility (14.59%), Expiry date (30 Sep 05). Choose cell B19, and choose Insert -> Function. Choose Adfin Options in Function Category and OpPremium() in Function name then click OK. The dialog box of the OpPremium() function appears.
3 4
30 July 2005
202
Reuters Financial Software PowerPlus Pro Workbook Step 5 6 Action Enter all arguments requested to obtain the following formula:
=OpPremium(B12,B3,B13,B14,B7%,B17,B15,D3,,B16)
Click OK.
10.3 Adfin Exotics Functions

In these examples, we determine the premium of an average rate Asian option, the implied volatility of a binary option, the premium of a barrier option, and derived values (Greeks) of a knock-out barrier option. Pay particular attention to the ExoticStructures of these three kinds of Exotic Options, as they differ from one another.
10.3.1 OpAsianPremium()
The OpAsianPremium() function calculates the premium of an Asian option As explained in the online help, Asian options are options where the final payoff is based on the average level of the underlying asset price over a period of time. The sampling frequency, the averaging period and the mathematical type of the average allow multiple variations. Asian options comprise average rate options and average strike options. Average rate options pay the difference between the average of the underlying prices and the strike price, whereas average-strike options pay the difference between the underlying price at maturity and the average of the underlying prices. The strike price for the latter is therefore set when the option expires. Our example is based on premium calculation of an average rate Asian option.
10.3.1.1 Arguments OpAsianPremium() is composed of 13 arguments: =OpAsianPremium(CalcDate, FirstFixingDate, ExpiryDate, SpotPrice, StrikePrice, AveragePrice, NbFixing, Volatility, RiskFreeRateArray, ReturnArray, ExoticStructure, RateStructure, CalcStructure)
Name Description Calculation date. Date of the first fixing for the average calculation. Expiry date of the option. Spot price of the underlying stock. Strike price of the option (ignored for average strike options).
CalcDate FirstFixingDate ExpiryDate SpotPrice StrikePrice
30 July 2005
203
Reuters Financial Software PowerPlus Pro Workbook Name Description Average of the underlying prices from the first fixing date to the calculation date. Number of fixings used to calculate the average. Anticipated volatility of the underlying. Array of data depending on the rate model chosen. Array of annual continuous yield rate for each asset. Extended argument defining the option structure. Extended argument defining the interest rate model. Extended argument defining the calculation method.
AveragePrice NbFixing Volatility RiskFreeRateArray ReturnArray ExoticStructure RateStructure CalcStructure
10.3.1.2 Example Using OpAsianPremium()

Reproduce the following table: Step Action 1 a. In cell C2, enter the Calculation Date. Use for example =today() function. b. In cell C3, enter the First fixing date of the option. c. In cell C4, enter the ExpiryDate. d. In cell C5, enter the Spot price of the underlying stock. e. In cell C6, enter the Strike Price. f. In cell C7, enter the Average Price of the Asian option. g. In cell C8, enter the Number of Fixings. h. In cell C9, enter the Volatility (do not forget to apply the percentage format). i. In cell C10, enter the Risk Free Rate (do not forget to apply the percentage format). j. In cell C11, enter the Return Array (do not forget to apply the percentage format). In cell C12, enter the ExoticStructure. the keyword UI:SEC defines the underlying asset type (Securities) the keyword AVE:ARI specifies an arithmetic average the keyword CALL specifies a CALL option the keyword ASIAN:RATE specifies an average rate Asian option the keyword EXM:E specifies a European Asian option In cell C13, enter the RateStructure. the keyword RATETYPE:CONT defines the type of the yearly rates. In cell C14, enter the CalcStructure. the keyword CMT:FORM specifies that the calculation model type is a formula. the keyword FT:BS specifies that the formula type used to price this option is Black and Scholes. Choose cell C16, and choose Insert -> Function. Choose Adfin Option in Function Category and OpAsianPremium() in Function name then click OK. The dialog box of the OpAsianPremium() function appears. Fill in the function with the argument described before. Enter all arguments requested to get the following formula:
3 4
5 6
=OpAsianPremium(C2, C3, C4, C5, C6, C7, C8, C9, C10, C11, C12, C13, C14)
30 July 2005
204
Reuters Financial Software PowerPlus Pro Workbook Step 8 Action Click OK. The premium appears in cell C15.
10.3.2 OpBinaryImpliedVol()
The OpBinaryImpliedVol() function calculates the implied volatility of a barrier option. As explained in the online help, All-or-nothing binary (or digital) options pay a predetermined fixed amount of cash if the underlying price at expiry is in the money (in other words above the option strike price for a call or below for a put). These binary options are referred to as cash-or-nothing. Asset-or-nothing binary options are similar except that the holder receives the asset instead of the cash amount. The example is based on an All or Nothing Asset Option.
10.3.2.1 Arguments OpBinaryImpliedVol() is composed of 12 arguments: =OpBinaryImpliedVol(CalcDate, ExpiryDate, SpotPrice, BarrierPrice, StrikePrice, CashAmount, Premium, RiskFreeRateArray, ReturnArray, ExoticStucture, RateStructure, CalcStructure)
Name Description Calculation date. Expiry date of the option. Spot price of the underlying stock. Price of the barrier of the option . Strike price of the option. Fixed amount of cash paid if applicable. Market or spot price of the option. Array of data depending on the rate model chosen. Array of annual continuous yield rate for each asset. Extended argument defining the option structure. Extended argument defining the interest rate model. Extended argument defining the calculation method.
CalcDate ExpiryDate SpotPrice BarrierPrice StrikePrice CashAmount Premium RiskFreeRateArray ReturnArray ExoticStructure RateStructure CalcStructure
30 July 2005
205
10.3.2.2 Example Using OpBinaryImpliedVol()

Reproduce the following table: Step Action 1 a. b. c. d. e. f. g. h. i. In cell C2, entry the Calculation Date. Use for example =today() function. In cell C3, enter the ExpiryDate. In cell C4, enter the Spot price of the underlying stock. In cell C5, enter the Barrier Price of the option. In cell C6, enter the Strike Price. In cell C7, enter the Cash Amount. In cell C8, enter the Premium Amount. In cell C9, enter the Risk Free Rate (do not forget to apply the percentage format) In cell C10, enter the Return Array (do not forget to apply the percentage format).
In cell C11, enter the ExoticStructure. the keyword UI:SEC defines the underlying asset type (Securities) the keyword EXM:E specifies a European Binary option the keyword BINARY:ASSET specifies an asset or nothing option the keyword CALL specifies a CALL option In cell C12, enter the RateStructure. the keyword RATETYPE:CONT defines the type of the yearly rates (continuous rates are used in the Black and Scholes model) In cell C13, enter the CalcStructure. the keyword CMT:FORM specifies that the calculation model type is a formula the keyword FT:BS specifies that the formula type used to price this option is Black and Scholes Choose cell C15, and choose Insert -> Function. Choose Adfin Option in Function Category and OpBinaryImpliedVol() in Function name then click OK. The dialog box of the OpBinaryImpliedVol() function appears. Enter all arguments requested to obtain the following formula:
5 6
=OpBinaryImpliedVol(C2, C3, C4, C5, C6, C7, C8, C9, C10, C11, C12, C13)
8 Click OK. The implied volatility of the barrier option appears in cell C15.
10.3.3 OpBarrierPremium()
The OpBarrierPremium() function calculates the premium of a barrier option.
30 July 2005
206
10.3.3.1 Arguments
OpBarrierPremium() is composed of 11 arguments:
=OpBinaryPremium(CalcDate, ExpiryDate, SpotPrice, StrikePrice, BarrierPrice, Volatility, RiskFreeRateArray, ReturnArray, ExoticStucture, RateStructure, CalcStructure)
Name Description Calculation date. Expiry date of the option. Spot price of the underlying stock Strike price of the option Price of the barrier of the option Anticipated volatility of the underlying Array of data depending on the rate model chosen Array of annual continuous yield rate for each asset Extended argument defining the option structure Extended argument defining the interest rate model Extended argument defining the calculation method
CalcDate ExpiryDate SpotPrice StrikePrice BarrierPrice Volatility RiskFreeRateArray ReturnArray ExoticStructure RateStructure CalcStructure
10.3.3.2 Example Using OpBarrierPremium()

The aim of this example is to calculate the premium of a barrier option with American exercise. Reproduce the following example: Step Action 1 a. In cell C2, entry the Calculation Date. Use for example =today() function. b. In cell C3, enter the ExpiryDate. c. In cell C4, enter the Spot price of the underlying stock. d. In cell C5, enter the Strike Price. e. In cell C6, enter the Barrier Price of the option. f. In cell C7, enter the Volatility of the underlying stock. g. In cell C8, enter the Risk Free Rate (do not forget to apply the percentage format). h. In cell C9, enter the Return Array (do not forget to apply the percentage format). In cell C10, enter the ExoticStructure. the keyword KO defines a knock out barrier option. the keyword EXM:A specifies an American barrier option. the keyword DIVTYPE:CONT specifies a continuous dividend type. the keyword CALL specifies a CALL option. In cell C11, enter the RateStructure. the keyword RM:YTM defines yield to maturity rate model. In cell C12, enter the CalcStructure. the keyword CMT:TREE specifies that the calculation model type is a tree. the keyword NBBRANCH:3 specifies the number of branches in the tree. Choose cell C14, and choose Insert -> Function. Choose Adfin Option in Function Category and OpBarrierPremium() in Function name then click OK. The dialog box of the OpBarrierPremium() function appears. Enter all arguments requested to obtain the following formula:
3 4
5 6
=OpBarrierPremium(C2, C3, C4, C5, C6, C7, C8, C9, C10, C11, C12)
30 July 2005
207
Reuters Financial Software PowerPlus Pro Workbook Step 8 Action Click OK. The premium of the barrier option with American exercise appears in cell C14.
10.3.4 OpBarrierDeriv()
The OpBarrierDeriv() function returns an array of all the derivatives (delta, gamma, theta, vega, rho) of a barrier option. As explained in the online help, Barrier options are options where the right to exercise depends on whether the underlying asset price reaches a predefined barrier level during the lifetime of the option. The exercise is based on a single barrier option. It is also possible to evaluate double barrier options.
10.3.4.1 Arguments OpBarrierDeriv() is composed of 12 arguments: =OpBarrierDeriv(CalcDate, ExpiryDate, SpotPrice, StrikePrice, BarrierPrice, Volatility, RiskFreeRateArray, ReturnArray, ExoticStucture, RateStructure, CalcStructure, AdMode)
Name Description Calculation date. Expiry date of the option. Spot price of the underlying stock. Strike price of the option. Price of the barrier of the option. Anticipated volatility of the underlying. Array of data depending on the rate model chosen. Array of annual continuous yield rate for each asset. Extended argument defining the option structure. Extended argument defining the interest rate model. Extended argument defining the calculation method. Extended argument customizing the return value.
CalcDate ExpiryDate SpotPrice StrikePrice BarrierPrice Volatility RiskFreeRateArray ReturnArray ExoticStructure RateStructure CalcStructure AdMode
30 July 2005
208
10.3.4.2 Example Using OpBarrierDeriv()

Reproduce the following table: Step Action 1 a. b. c. d. e. f. g. h. In cell C2, entry the Calculation Date. Use for example =today() function. In cell C3, enter the Expiry Date of the option. In cell C4, enter the Spot Price of the option. In cell C5, enter the Strike Price of the option. In cell C6, enter the Barrier Price. In cell C7, enter the Volatility (do not forget to apply the percentage format). In cell C8, enter the risk free rate (do not forget to apply the percentage format). In cell C9, enter the return array (do not forget to apply the percentage format).
In cell C10, enter the ExoticStructure. The keyword CALL specifies a CALL option. The keyword UI:SEC defines the underlying asset type (Securities). The keyword EXM:E specifies a European Barrier option. The keyword KO to specifies a knock-out barrier option. In cell C11, enter the RateStructure. the keyword RATETYPE:CONT defines the type of the yearly rates (continuous rates are used in the Black and Scholes model). In cell C12, enter the CalcStructure. The keyword CMT:FORM specifies that the calculation model type is a formula. The keyword FT:BS specifies that the formula type used to price this option is Black and Scholes. Choose cell C15, and choose Insert -> Function. Choose Adfin Option in Function Category and OpBarrierDeriv() in Function name then click OK. The dialog box of the OpBarrierDeriv() function appears. Enter all arguments requested to obtain the following formula:
5 6
=OpBarrierDeriv(C2, C3, C4, C5, C6, C7, C8, C9, C10, C11, C12)
8 9 Click OK. The Delta ratio appears in cell C15. Choose the range C15:G15, press the F2 key, and then press Ctrl+Shift+Enter. The Greeks appear in the range C15:G15:
10.3.5 Pricing a Bermudan Option Using OpPremium()

The OpPremium() function can also calculate the premium of a Bermudan option.
30 July 2005
209
10.3.5.1 Arguments
As explained previously OpPremium() is composed of ten arguments:
=OpPremium (CalcDate, ExpiryDate, SpotPrice, StrikePrice, Volatility, RiskFreeRate, ReturnArray, OptionStructure, RateStructure, CalcStructure) 10.3.5.2 Example Using OpPremium
To price a Bermudan Option the OptionStructure has to be changed. Reproduce the following table. Step Action 1 a. b. c. d. e. f. g. In cell B1, enter the calculation date. In cell B2 enter the expiry date. In cell B3 enter the spot. In cell B4 enter the strike. In cell B5 enter the Volatility. In cell B6 enter the RiskFreeRate. In cell B7 enter the Dividend if required.
In cells B9 define the OptionStructure for the Bermudan described below: Our option is a PUT; it can be exercised at the following dates: between the 27Jun05 and the 03Jul05 or between the 27Aug05 and the 03Sep05 or the 15Nov05 or at maturity For Bermudan options use EXM:DDMMMYY:DDMMMYY to specify a Bermudan option (can be priced with a Binomial or Trinomial tree). To specify Bermudan Options with variable strike use EXM:DDMMMYY:DDMMMYY:Strike (only available with a Trinomial pricing explained below). The OptionStructure will be:
PUT EXM:27JUN05:03JUL05 EXM:27AUG05:03SEP05 EXM:15NOV05:15NOV05

3 In cells B10, define the CalcStructure:
NBBRANCH:3 CMT:TREE TITER:30 This CalcStructure means that the Bermudan option will be priced with a
Trinomial Tree and the calculation will be performed on 30 steps. 4 5 Choose cell B13, and choose Insert -> Function. Choose Adfin Options in Function Category and OpPremium() in Function name then click OK. The dialog box of the OpPremium() function appears. Enter all arguments requested to obtain the following formula:
=OpPremium(B1,B2,B3,B4,B5,B6,B7,B9,,B10)
30 July 2005
210
Note:
OpImpliedVol(), OpPremium() and OpCalcDeriv() can also support intraday pricing in

vanilla option pricing. To use it, just input a Calculation date and/or an Expiry date as a date with hours (such as 27/05/2005 11:43:22). It is also possible to use intraday Spot date and Delivery date defining SPOT and DELIV keywords in OptionStructure with same format as CalcDate/ExpiryDate.
10.4 Adfin Interest Rate Derivatives Functions

10.4.1 Pricing Caplet Premiums With or Without Amortization
10.4.1.1 AdCapFloorCaplets() AdCapFloorCaplets() generates an array with the caplet/floret premiums of a cap, floor, and collar. 10.4.1.2 Arguments
The AdCapFloorCaplets() function is composed of 11 arguments. Name Description
SettlementDate RateArray
Calculation date. Term Structure array. Depending on the model, this array has several forms: an array (Dates, Rates, Constant volatility) if BS is specified. an array (Dates, Rates, Volatilities) if BDT is specified. an array (Dates, Rates, Constant volatility, constant meanreversion) if HW is specified. The model used is specified through the keyword RM (Rate Model) in the RateStructure argument. Start date of the cap, floor, or collar. Maturity date of the cap, floor, or collar. (can be expressed as a date or a code, such 1Y). Exercise or strike price of the cap.
StartDate Maturity CapStrikePrice
30 July 2005
211
Reuters Financial Software PowerPlus Pro Workbook Name Description Exercise or strike price of the floor. Rate of the cap, floor, or collar for the current calculation period. Extended argument defining the cap/floor structure. Extended argument defining the structure of the interest rate model. Extended argument defining the calculation method. Extended argument customizing the return value.
FloorStrikePrice FirstRate CapFloorStructure RateStructure CalcStructure AdMode
10.4.1.3 Pricing Caplets Without Amortization Using AdCapFloorCaplets()

Step 1 2
Action Choose cell G14, and choose Insert -> Function. Choose Adfin Options in Function Category and AdCapFloorCaplets() in Function name then click OK. The dialog box of the AdCapFloorCaplets() function appears. a. In CalcDate: choose cell H3 the calculation date. b. In RateArray, choose cells B4:D50 the (Dates, Rates, Constant volatility) array. c. In StartDate, choose cell H4 the start date of the cap. d. In ExpiryDate, choose cell H5 the expiry date of the cap, floor, or collar. e. In CapStrikePrice, choose cell H6 the Exercise or strike price of the cap. f. In FloorStrikePrice, choose cell H7 the Exercise or strike price of the floor. g. In FirstRate, leave blank. h. In CapFloorStructure, choose cell H9. (The keywords CAP FRQ:4 CCM:MMA0 specify a quarterly cap option with money market actual/360 rate.) i. In RateStructure, choose cell H10. RM:BS keyword specifies that the rate model is Black & Scholes. ZCTYPE:RATE keyword specifies zero-coupon rates IM:LIN keyword specifies linear interpolation. j. In CalcStructure, choose cell H11.
30 July 2005
212
CMT:FORM keyword specifies that the calculation model type is a formula. FT:BS keyword specifies that the formula type is Black & Scholes.
Click OK. The caplet date appears in cell G14.
AdCapFloorCaplets() returns a vertical 5-column array that contains for all caplets or floorlets, the date, the
strike price, the volatility, the forward rate, the premium and the notional. Step Action 1 2 3 Choose an array of cells (G14:L23) such that the original cell is at the upper-left corner of the array. Press F2 to edit the function in the cell. Press CTRL+SHIFT+ENTER to convert it into an array function.
10.4.1.4 Pricing Caplets With Amortization Using AdCapFloorCaplets

Using the same Adfin Options function, AdCapFloorCaplets(), the keyword AMORT:DDMMYY:i must be added into the CapFloorStructure argument to specify an amortization schedule. In our exercise, the cap option contains the following amortization schedule:
30 July 2005
213
Reuters Financial Software PowerPlus Pro Workbook Dates 12JUN05 12MAR06 12MAY07 12MAY08 Then the CapFloorStructure is: Amount 25% 25% 25% 25%
CAP FRQ:4 CCM:MMA0 AMORT:12JUN05:0.25 AMORT:12MAR06:0.25 AMORT:12MAR07:0.25 AMORT:12MAR08:0.25

Where AMORT:12JUN05:0.25 indicates that 0.25 is redeemed at the date 12JUN05.
10.4.2 AdCapFloorDeriv()
AdCapFloorDeriv() generates an array with the caplet/floorlet derivatives of a cap, floor, and collar. 10.4.2.1 Arguments
The AdCapFloorDeriv() function is composed of 11 arguments: Name Description
Calculation date. Term Structure array. Depending on the model, this array has several forms: an array (Dates, Rates, Constant volatility) if BS is specified. an array (Dates, Rates, Volatilities) if BDT is specified. an array (Dates, Rates, Constant volatility, constant meanreversion) if HW is specified. The model used is specified through the keyword RM (Rate Model) in the RateStructure argument. Start date of the cap, floor, or collar. Maturity date of the cap, floor, or collar.
StartDate Maturity
30 July 2005
214
Reuters Financial Software PowerPlus Pro Workbook Name Description Exercise or strike price of the cap. Exercise or strike price of the floor. Rate of the cap, floor, or collar for the current calculation period. Extended argument defining the cap/floor structure. Extended argument defining the structure of the interest rate model. Extended argument defining the calculation method. Extended argument customizing the return value.
CapStrikePrice FloorStrikePrice FirstRate CapFloorStructure RateStructure CalcStructure AdMode
10.4.2.2 Example Using AdCapFloorDeriv()

Step 1 2 Action Choose cell G29, and choose Insert -> Function. Choose Adfin Options in Function Category and AdCapFloorDeriv() in Function name then click OK. The dialog box of the AdCapFloorDeriv() function appears. a. b. c. d. e. f. g. h. i. j. k. In CalcDate: choose cell H3 the calculation date. In RateArray: choose cells B4:D50 the (Dates, Rates, Constant volatility) array. In StartDate: choose cell H4 the start date of the cap. In ExpiryDate: choose cell H5 the expiry date of the cap, floor, or collar. In CapStrikePrice: choose cell H6 the Exercise or strike price of the cap. In FloorStrikePrice: choose cell H7 the Exercise or strike price of the floor. In FirstRate: leave blank. In CapFloorStructure: choose cell H9. In RateStructure: choose cell H10. In CalcStructure: choose cell H11. In AdMode: leave it blank.
Click OK. The global delta of the cap appears in cell G29.
AdCapFloorDeriv() returns a vertical 6-column array containing for the global value, the global forward and all caplets or floorlets, the delta, the gamma, the vega, the theta, the BPV and the convexity.
30 July 2005
215
Reuters Financial Software PowerPlus Pro Workbook Step 1 2 3 Action Choose an array of cells (G29:L42) such that the original cell is at the upper-left corner of the array. Press F2 to edit the function in the cell. Press CTRL+SHIFT+ENTER to convert it into an array function.
Refer to the online help for any interpretation of the function.
10.4.3 AdCapFloorVolSurface()
The AdCapFloorSurface() function generates a well-defined surface from an input smile. This function handles ATM curves, smiles and flat values.
10.4.3.1 Arguments
The AdCapFloorSurface() function is composed of nine arguments: Name Description
Calculation date. Term Structure or date array related to the rate model used to price the instrument. RM:YC requires a two-column array containing the dates and the values for the zero-coupon rates or discount factors. Start date of the cap, floor, or collar. The interest rate for the first period. Three types of input are possible: A volatility smile An ATM curve A flat model Extended argument defining the cap/floor structure. Extended argument defining the interest rate model. Use RM:YC for a yield curve. Extended argument defining the input volatility curve used to create the volatility surface. Extended argument customizing the return value.
StartDate FirstRate VolSurface
CapFloorStructure RateStructure VolStructure AdMode
10.4.3.2 Example Using AdCapFloorVolSurface()

Step Action
30 July 2005
216
Reuters Financial Software PowerPlus Pro Workbook Step 1 Action From cell B1 to cell B10, enter the CapFloor features: SettlementDate, StartDate, Maturity, CapStrikePrice, FloorStrikePrice, FirstRate, CapFloorStructure, RateStructure, CalcStructure. From cell A12 to cell Q21, enter the VolSurface. To reproduce it, use the Rtget() function. You can also use Reuters 3000Xtra going to ICAPREJ page.
4 5
In cell B23, insert VM:SMILE ATMSTRIKE:YES ATMVOL:YES SMILE:VOL STRIKE:RATE for the VolStructure. VM:SMILE denotes that the volatility surface used is a smile. ATMSTRIKE:YES to indicate the volatility surface contains the ATM Strike column. ATMVOL:YES to indicate the volatility surface contains the ATM Vol column. STRIKE:RATE to indicate that the X-axis is expressed as a rate. Choose cell A23, and choose Insert -> Function. Choose Adfin Options in Function Category and AdCapFloorDeriv() in Function name then click OK. The dialog box of the AdCapFloorDeriv() function appears. Enter all required arguments to obtain the following formula:
=AdCapFloorVolSurface(B1,AE9:AF55,B2,B6,B12:Q21,B7:B8,B9,A 23) Note: The second argument RateArray in cells (AE9:AF55) defines the interest rate
7 model. In the example, it is a zero-coupon Yield Curve with Discount Factor. Click OK. The first return value appears in cell A23.
8 9
Choose an array of cells (A24:V33) such that the original cell is at the upper-left corner of the array. Press F2 to edit the function in the cell.
30 July 2005
217
10.4.4 AdCapFloorBSCaplets()
AdCapFloorBSCaplets() generates the array with the caplet or floorlet values of a Vanilla cap, floor, or collar, using a volatility surface, curve or flat value. 10.4.4.1 Arguments AdCapFloorBSCaplets() function is composed of 12 arguments: =AdCapFloorBSVol(SettlementDate, RateArray, StartDate, Maturity, CapStrikePrice, FloorStrikePrice, FirstRate, VolSurface, CapFloorStructure, RateStructure, CalcStructure, AdMode)
Argument Description Settlement date Term structure array Start date of the cap, floor, or collar Maturity date of the cap, floor, or collar Exercise or strike price of the cap Exercise or strike price of the floor Interest rate for the first period Volatility surface
SettlementDate RateArray StartDate Maturity CapStrikePrice FloorStrikePrice FirstRate VolSurface
CapFloorStructure Extended argument defining the CapFloor structure RateStructure CalcStructure AdMode
Extended argument defining the interest rate model Extended argument defining the calculation method Extended argument customizing the return value
10.4.4.2 Return Value

The return value is a vertical six-column array, containing the following values for all caplets or floorlets: Start date Strike price Volatility Forward rate Premium
30 July 2005
218
Reuters Financial Software PowerPlus Pro Workbook Remaining notional If you price a collar, the function returns both caplet and floorlet strike prices in two separate columns.
10.4.4.3 Example using AdCapFloorBSCaplets()

Step 1 Action From cell B1 to cell B10, enter the CapFloor features: SettlementDate, StartDate, Maturity, CapStrikePrice, FloorStrikePrice, FirstRate, CapFloorStructure, RateStructure, CalcStructure. From cell A12 to cell V21, enter the VolSurface. To reproduce it, use the AdCapFloorVolSurface() function.
3 4
Select cell B24 and insert the AdCapFloorBSCaplets() function. Enter all arguments requested to obtain the following formula:
=AdCapFloorBSCaplets(B1, AE9:AF55, B2, B3, B4, B5, B6, A12:V21, B7:B8, B9, C22, "RET:A60")
1. The second argument RateArray in cells (AE9:AF55) defines the interest rate
model. In the example, it is a Zero Coupon Yield Curve with Discount Factor. 5 Click OK.
30 July 2005
219
Reuters Financial Software PowerPlus Pro Workbook 6 This is an array function. Extend the result to cell G35, press the F2 key to edit, and then press Ctrl + Shift + Enter.
10.4.5 AdCapFloorBSVol()
AdCapFloorBSVol() calculates the implied volatility of a Vanilla cap, floor, or collar. 10.4.5.1 Arguments AdCapFloorBSVol() function is composed of 11 arguments: =AdCapFloorBSVol(SettlementDate, RateArray, StartDate, Maturity, CapStrikePrice, FloorStrikePrice, VolSurface, CapFloorStructure, RateStructure, CalcStructure, AdMode)
Argument Description Settlement date Term structure array Start date of the cap, floor, or collar Maturity date of the cap, floor, or collar Exercise or strike price of the cap Exercise or strike price of the floor Volatility surface Extended argument defining the CapFloor structure Extended argument defining the interest rate model Extended argument defining the calculation method Extended argument customizing the return array.
SettlementDate RateArray StartDate Maturity CapStrikePrice FloorStrikePrice VolSurface CapFloorStructure RateStructure CalcStructure AdMode
10.4.5.2 Example Using AdCapFloorBSVol()

Step 1 Action From cell B1 to cell B9, enter the CapFloor features: SettlementDate, StartDate, Maturity, CapStrikePrice, FloorStrikePrice, FirstRate, CapFloorStructure, RateStructure, CalcStructure. From cell A12 to cell V21, enter the VolSurface. To reproduce it, use the AdCapFloorVolSurface() function. Select cell D24 and insert the AdCapFloorBSVol() function.
2 3
30 July 2005
220
Reuters Financial Software PowerPlus Pro Workbook Step 4 Action Enter all required arguments to obtain the following formula:
=AdCapFloorBSVol(B1,AE9:AF55,B2,B3,B4,B5,A12:V21,B7:B8,B9, D50,"RES:CAPLET") In D50, the keyword BSVOL:CAPLET is used to value the cap using the forward
volatility for each caplet using the bootstrapping method.
2.
5 6 7 8
Second argument RateArray defines the interest rate model. In the example, it is a Zero Coupon Yield Curve with Discount Factor in cells (AE9:AF55). Keyword RES:CAPLET in argument AdMode is used to obtain CAPLET volatilities.
Click OK. This is an array function. Extend the result to cell F47, press the F2 key to edit, and then press Ctrl + Shift + Enter. Select cell K24 and insert the AdCapFloorBSVol() function. Enter all required arguments to obtain the following formula:
=AdCapFloorBSVol(B1,AE9:AF55,B2,B3,B4,B5,A12:V21,B7:B8,B9, D50,"RES:ALLIN")
3.
9
The Keyword RES:ALLIN in argument AdMode is used to obtain all-in volatilities.
This is an array function. Extend the result to cell M47, press the F2 key to edit, and then press Ctrl + Shift + Enter.
30 July 2005
221
10.4.5.3 Example Using Short Term Volatilities

The AdCapFloorBSVol() function supports also forward volatilities for the period less than one year using the STVOL keyword in CapFloorStructure. Step Action 1 From cell B1 to cell B9, enter the CapFloor features as follows:
4.
2 3 4
The keyword STVOL:9M means we use short term volatilities for the first nine months on the volatility surface. Those short term volatilities are forward volatilities calculated from caplets. From cell A12 to cell P24, enter the VolSurface. Select cell A27 and insert the AdCapFloorBSVol() function. Enter all required arguments to obtain the following formula:
=AdCapFloorBSVol(B1,AB4:AC49,B2,B3,B4,B5,A12:P24,B6,B7,B8, B9)
The second argument RateArray defines the interest rate model. In the example, it is a Zero Coupon Yield Curve with Discount Factor in cells (AB4:AC49). The RES:CAPLET keyword in the AdMode argument is used to obtain CAPLET volatilities. Click OK.
30 July 2005
222
Reuters Financial Software PowerPlus Pro Workbook 6 This is an array function. Extend the result to cell C50, press the F2 key to edit, and then press Ctrl + Shift + Enter.
10.4.6 AdCapFloorBSPremium()
AdCapFloorBSPremium() calculates the premium of a vanilla cap, floor or collar. 10.4.6.1 Arguments AdCapFloorBSPremium() function is composed of 11 arguments: AdCapFloorBSPremium( SettlementDate, RateArray, StartDate, Maturity, CapStrikePrice, FloorStrikePrice, FirstRate, CapFloorStructure, VolSurface, RateStructure, CalcStructure)
Argument Description Settlement date Term structure array Start date of the cap, floor, or collar Maturity date of the cap, floor, or collar Exercise or strike price of the cap Exercise or strike price of the floor Rate of the cap, floor, or collar for the current calculation period Volatility surface Extended argument defining the CapFloor structure Extended argument defining the interest rate model Extended argument defining the calculation method
SettlementDate RateArray StartDate Maturity CapStrikePrice FloorStrikePrice FirstRate VolSurface CapFloorStructure RateStructure CalcStructure
10.4.6.2 Return Value

The return value is the premium of a cap, floor, or collar.
30 July 2005
223
10.4.6.3 Example Using AdCapFloorBSPremium()

Step 1 2 3 Action Use the same inputs as for the AdCapFloorBSVol() function example. Select cell F50 and insert the AdCapFloorBSPremium() function. Enter all arguments requested to obtain the following formula:
=AdCapFloorBSPremium(B1, AE9:AF55, B2, B3, B4, B5, B6, D24:F47, B7:B8, B9, D50)
The second argument RateArray defines the interest rate model. In the example, it is a zero-coupon Yield Curve with Discount Factor. In the VolSurface argument, you can also use a volatility surface instead of the forward volatility curve calculated previously. Click OK.
10.5 Pricing a Swaption

Important: Use the AdSwaptionBSPremium(), AdSwaptionBSDeriv and AdSwaptionBSImpliedVol() functions to calculate the premium, all derivatives and the implied volatility of a swaption with European exercise mode from a volatility surface or a flat value using the Black and Scholes model. To price a swaption with American exercise mode use the other functions with the Black Derman and Toy model or the Hull and White model.
10.5.1 AdSwaptionBSDeriv()
The AdSwaptionBSDeriv() function calculates the Greeks of a swaption.
10.5.1.1 Arguments
The AdSwaptionBSDeriv() function is composed of 13 arguments:
=AdSwaptionBSDeriv(CalcDate, RateArray, SwapStartDate, SwapMaturity, ExpiryDate, SpotPrice, StrikePrice, Volatility, SwapStructure, OptionStucture, RateStructure, CalcStructure, AdMode)
30 July 2005
224
Reuters Financial Software PowerPlus Pro Workbook Name Description Settlement Date of the swaption. Term structure or data array related to the rate model used to price the instrument. Start date of the swap. Maturity date of the swap, expressed as a date or a maturity code. Expiry date of the option, expressed as a date or a maturity code. Market rate of the underlying forward starting swap. Exercise or strike rate of the swap. Volatility array of the underlying swap rate. Extended argument defining the swap. Extended argument defining the option. Extended argument defining the interest rate model. Extended argument defining the calculation method. Extended argument customizing the return value.
CalcDate RateArray SwapStartDate SwapMaturity ExpiryDate SpotPrice StrikePrice Volatility SwapStructure OptionStructure RateStructure CalcStructure AdMode
10.5.1.2 Example Using AdSwaptionsBSDeriv()

Step Action
30 July 2005
225
Reuters Financial Software PowerPlus Pro Workbook Step 1 Action Reproduce the following table: a. Get a zero coupon curve using first the RtChain() function with the EURZ=R RIC and then the RtUpdate() function to get the dates and rates of the zero coupon yield curve (A4:B50). b. In cell E3, determine the settlement date. c. In cell E4, determine the swap start date. d. In cell E5, determine the swap maturity. e. In cell E6, determine the expiry date of the option. f. In cell E7, determine the spot price. You can leave it blank. g. In cell E8, define the strike price. h. In cell E9, define the structure of the interest rate swap. In our example, we are using the EUR_AB6E structure. i. In cell E10, define the option structure. At least, you must define the option type. In our example, we are using a call with a European style so keywords are CALL EXM:E. j. In cell E11, specify as arguments IM:CUBR RM:YC ZCTYPE:RATE DCB:AA RATEFRQ:1 in RateStructure IM:CUBR (to specify that the interpolation method used is the cubic rate one) RM:YC (to specify that the rate model is a yield curve) ZCTYPE:RATE (to specify we are using rates and no discount factors in the yield curve) DCB:AA ( to define the period-based calculation) RATEFRQ:1 (to specify that the compounded frequency parameter is yearly). k. In cell E12, specify AdMode as RET:B4 LAY:V to return values with the headers and present it in a vertical way. l. From cell D15 to R32, draw a surface volatility using the RtGet() function. In our example, we are using the surface volatility of the Euro swaptions (EURSTN=ICAP and so on in Reuters Xtra 3000).
30 July 2005
226
Reuters Financial Software PowerPlus Pro Workbook Step 2 Action In cell G34, insert the AdSwaptionBSPremium() function in the Adfin Option function category. a. In CalcDate: choose cell E3. b. In RateArray: select the yield curve array (A4:B50). c. In SwapStartDate: choose the cell E4. d. In SwapMaturity: choose cell E5. e. In ExpiryDate: choose cell E6. f. In SpotPrice: choose cell E7 (or leave it blank). g. In StrikePrice: choose cell E8. h. In Volatility: select the surface volatility (D15:R32). i. In SwapStructure: choose cell E9. j. In OptionStructure: choose cell E10. k. In RateStructure: choose cell E11. l. In CalcStructure: choose cell E12. m. In AdMode: choose cell E13. Click OK. The first header appears in cell G34.
AdSwaptionBSDeriv()returns a vertical 4-cell array containing the Delta, Gamma, Theta and Vega ratios.
Step 1 2 Action Choose an array of cells (D34:E37) such that the original cell is at the upper-left corner of the array. Press F2 to edit the function in the cell.
30 July 2005
227
10.5.2 AdSwaptionBSPremium()
The AdSwaptionBSPremium() function calculates the premium of a swaption with European exercise mode from a volatility surface or a flat value.
10.5.2.1 Arguments
The AdSwaptionBSPremium() function is composed of 12 arguments:
=AdSwaptionBSPremium(CalcDate, RateArray, SwapStartDate, SwapMaturity, ExpiryDate, SpotPrice, StrikePrice, Volatility, SwapStructure, OptionStucture, RateStructure, CalcStructure)
Name Description Settlement Date of the swaption. Term structure or data array related to the rate model used to price the instrument. Start date of the swap. Maturity date of the swap, expressed as a date or a maturity code. Expiry date of the option, expressed as a date or a maturity code. Market rate of the underlying forward starting swap. Exercise or strike rate of the swap. Volatility array of the underlying swap rate. Extended argument defining the swap. Extended argument defining the option. Extended argument defining the interest rate model. Extended argument defining the calculation method.
CalcDate RateArray SwapStartDate SwapMaturity ExpiryDate SpotPrice StrikePrice Volatility SwapStructure OptionStructure RateStructure CalcStructure
10.5.2.2 Example Using AdSwaptionBSPremium()

Step Action
30 July 2005
228
Reuters Financial Software PowerPlus Pro Workbook Step 1 Action In cell H34, insert the AdSwaptionBSPremium() function in the Adfin Option function category and enter all the arguments as follows:
=AdSwaptionBSPremium(E3, A4:B50, E4, E5, E6, E7, E8, D15:R32, E9, E10, E11)
2 Click OK.
10.5.3 AdSwaptionBSImpliedVol()
The AdSwaptionBSImpliedVol() function calculates the implied volatility of a swaption.
10.5.3.1 Arguments
The AdSwaptionBSImpliedVol() is composed of 12 arguments:
=AdSwaptionBSPremium(CalcDate, RateArray, SwapStartDate, SwapMaturity, ExpiryDate, SpotPrice, StrikePrice, Premium, SwapStructure, OptionStucture, RateStructure, CalcStructure)
Name Description Settlement Date of the swaption. Term structure or data array related to the rate model used to price the instrument. Start date of the swap Maturity date of the swap, expressed as a date or a maturity code Expiry date of the option, expressed as a date or a maturity code Market rate of the underlying forward starting swap Exercise or strike rate of the swap Premium of the option Extended argument defining the swap Extended argument defining the option Extended argument defining the interest rate model Extended argument defining the calculation method
CalcDate RateArray SwapStartDate SwapMaturity ExpiryDate SpotPrice StrikePrice Premium SwapStructure OptionStructure RateStructure CalcStructure
30 July 2005
229
10.5.3.2 Example Using AdSwaptionBSImpliedVol()

Step 1 Action In cell H36, insert the AdSwaptionBSImpliedVol() function in the Adfin Option function category and enter all arguments as follow:
=AdSwaptionBSImpliedVol(E3,A4:B50,E4,E5,E6,E7,E8,H34,E9,E1 0,E11)
2 Click OK.
30 July 2005
230
11 ADFIN EQUITIES FUNCTIONS

11.1 Overview of Adfin Equities Functions
Reuters Power Plus Pro provides a set of Adfin Equities functions that price stock using different developments of the Gordon-Shapiro model: a zero-growth model a constant growth model a three-stage growth model These functions also support regression analysis in order to find the best coefficient(s) such that one dependant variable fits a series of explanatory variables.
11.2 Adfin Equities Functions

11.2.1 AdEqDividendDiscountModel()
The AdEqDividendDiscountModel() function calculates the theoretical stock price, or internal rate of return, or risk premium from the dividend discount model.
11.2.2 Arguments
The AdEqDividendDiscountModel() is composed of seven arguments:
=AdEqDividendDiscountModel(DividendArray, GrowthArray, RiskFreeRateArray, RP or Th Price, DividendStructure, RateStructure, AdMode)

Name Description Array of dates and dividends or annual dividend rate. Array of dates and growth: If the dividend Scenario is Zero-Growth model:
DividendArray GrowthArray
GrowthArray=0
If the dividend Scenario is Constant-Growth model:
GrowthArray={beginning date of the Growth; Growth}

If the dividend Scenario is Three-Stage model:
GrowthArray=
Beginning date of the 1st stage Beginning date of the 2nd stage Beginning date of the 3rd stage Value of the 1st growth Value of the 2nd growth Value of the 3rd growth
RiskFreeRateArray RP or Th Price DividendStructure
Array of dates and risk free rate (all rate models are allowed). Risk Premium or Theoretical Price (it depends on the returned value). Extended argument defining the structure of the dividend model. GROWTH{EST,HIST}, EST for an estimated dividend (default value), HIST for a historical dividend. UD{YES,NO}, YES for a user-defined model, NO for a not user-defined (default value).
30 July 2005
231
Reuters Financial Software PowerPlus Pro Workbook Name Description Extended argument defining the structure of the interest rate model. Extended argument customizing the return value.
11.2.3 Example Using AdEqDividendDiscountModel()

11.2.3.1 The Zero Growth Model
Step 1 Action Reproduce the following table: a. In cell B3, use the RtGet() function to obtain the real-time quotation of the desired equity (in our example, RTR.L). b. In cell B4, use the DeUpdate() function to obtain the Equity Annualized Dividend (ANNUALISED_DPS). c. For more details on DeUpdate() function, see Retrieving Single Values for an Instrument from the Reuters 3000 Database: DeUpdate(). d. In cell B5, determine a Risk Premium for the market (in our example we estimate it at 2 %). e. In cell B6, use the DeUpdate() function to obtain the Equity Beta (BETA). f. In cell B7, use the AdInterp() function to calculate the Risk Free Rate. g. In cell B8, calculate the cost of equity using the CAPM model: E (r ) = RFR + (=B5*B6+B7). In cell B9, specify as arguments GROWTH:HIST UD:YES in Dividend Structure. GROWTH:HIST specifies that the dividend used is historical. UD:YES specifies that the model is User Defined. i. In cell B10, define the rate structure you use (in our example, RM:YTM). h. j. In cell B11, define the returned value in AdMode. Firstly, we calculate the IRR. So specify RES:IRR. Choose cell B13 and insert the AdEqDividendDiscountModel() function, which is in the Adfin Equities function category. Enter all arguments requested to obtain the following formula:
=AdEqDividendDiscountModel(B4,0,B5,B3,B9,B10,B11)
4 Click OK.
5 6
In cell C11, insert RES:THP which means we are now calculating the theoretical price of the stock. In cell B15, calculate the risk premium of the company subtracting the Risk Free Rate to the Cost of equity (=B8-B7).
30 July 2005
232
Reuters Financial Software PowerPlus Pro Workbook Step 7 Action In cell B17, insert the AdEqDividendDiscountModel() function in the Adfin Equities function category and enter all arguments as follow:
=AdEqDividendDiscountModel(B4,0,B7,B15,B9,B10,C11)
8 Click OK.
11.2.3.2 The Constant Growth Model

Step 1 Action Reproduce the following table:
2 3
Choose cell B14 and insert AdEqDividendDiscountModel() function, which is in the Adfin Equities function category. Enter all arguments requested to obtain the following formula:
=AdEqDividendDiscountModel(B4,B6:C6,B8,B3,B10,B11,B12)
30 July 2005
233
5 6
In cell C12, insert RES:THP which means we are now calculating the theoretical price of the stock. In cell B16, calculate the risk premium of the company subtracting the Risk Free Rate to the Cost of equity (=B9-B8). In cell B17, insert the AdEqDividendDiscountModel() function in the Adfin Equities function category and enter all arguments as follow:
=AdEqDividendDiscountModel(B4,B6:C6,B8,B16,B10,B11,C12)
8 Click OK.
11.2.3.3 The Three-Stage Growth Model

Step Action
30 July 2005
234
Reuters Financial Software PowerPlus Pro Workbook Step 1 Action Reproduce the following table:
2 3
Note: The first period growth is a growth stage (high growth), the second is a transition stage (slower growth) and the third is a maturity stage (low growth). Choose cell B16 and insert the AdEqDividendDiscountModel() function, which is in the Adfin Equities function category. Enter all arguments requested to obtain the following formula:
=AdEqDividendDiscountModel(B4,B6:C8,B10,B3,B12,B13,B14)
4 Click OK.
5 6 7
In cell C14, insert RES:THP which means we are calculating the theoretical price of the stock. In cell B18, calculate the risk premium of the company subtracting the Risk Free Rate to the Cost of equity (=B11-B10). In cell B20, insert the AdEqDividendDiscountModel() function in the Adfin Equities function category and enter all arguments as follow:
=AdEqDividendDiscountModel(B4,B6:C8,B10,B18,B12,B13,C14)
30 July 2005
235
11.2.4 AdStatRegression()
The AdStatRegression() function returns statistics that describe a trend using the Least Square regression method.
11.2.5 Arguments
The AdStatRegression() function is composed of four arguments:
=AdEqDividendDiscountModel(Dependent_Y, Explanatory_Y, StatStructure, AdMode)

Name Description Dependent variable It is an array (with more than 3 data) whose dimension is (1*n) Explanatory variable It is an array whose dimension is (m*n) with m1 and n=n (normally) Extended argument defining the structure of the Equity Extended argument customizing the return value
Dependent_Y Explanatory_Y StatStructure AdMode
11.2.6 Example Using AdStatRegression()

Step Action
30 July 2005
236
Reuters Financial Software PowerPlus Pro Workbook Step 1 Action Reproduce the following table:
2 3
a. Use RtChain() with LAY:H in Admode to get all the components of explanatory variable (in our example, components of the CAC 40 Index, .FCHI). Paste it from cell F1 to cell AS1. b. Choose cell C1 and insert the RIC you want to compare to the chosen explanatory variable (in our example, CNLP.PA) c. Choose cell C2 and insert DeHistory() to get the Trade Dates (TRADE_DATE) and Closing Prices (CLOSE) of the chosen dependent variable. d. For more details on DeHistory() function, see Retrieving Historical Data from the Reuters 3000 Database: DeHistory() . e. Choose cell F2 and insert DeHistory() function to get the closing prices (CLOSE) of the explanatory variable and extend it until the end of the array. In our example, we choose to return 60 lines (RET:A60). Note: Delete rows using NULL:SKIP in AdMode to avoid #N/A ND returned values or the function will not work. Choose cell A63, and choose Insert -> Function. Choose Adfin Equities in Function Category and AdStatRegression () in function name then click OK. The dialog box of the AdStatRegression() function appears. a. In Dependent_Y: Choose array C4:C61. b. In Explanatory_X: Choose array F4:AS61. c. In StatStructure, leave blank. If you want to consider that the Market risk can not be inferior to the risk free rate, type INTERCEPT:NO seeing that the default value is YES. If it is done, add ISTAT:YES to be able to calculate R2, Adjusted-R2, Fstat, Fproba and DW statistics. d. In AdMode, type the keywords RES:REG to get all the results and RET:B52 to show all the results with headers.
30 July 2005
237
Reuters Financial Software PowerPlus Pro Workbook Step 4 Action Click OK The first header appears in cell A63.
AdStatRegression() is a vertical 5-column array containing for all returned values, the Variable, the
Coefficient, the Std Error, the t-Stat and the t-Proba. Step Action 1 Choose an array of cells (A63:E115) such that the original cell is at the upper-left corner of the array. Note: The returned array depends on how many explanatory variables you choose (in our example, 40). Press F2 to edit the function in the cell. Press CTRL+SHIFT+ENTER to convert it into an array function.
2 3
30 July 2005
238
12 COM BASED PROGRAMMING

12.1 COM and Reuters PowerPlus Pro Functions
Developed in C++ for optimal performance, the Reuters PowerPlus Pro functions are fully integrated into the familiar Microsoft Excel spreadsheet environment. However, for greater openness and flexibility, you can access most of Reuters PowerPlus Pro functionality outside Excel in a Microsoft Windows (2000 and XP) development environment thanks to a series of COM components included within Reuters PowerPlus Pro product. Reuters PowerPlus Pro 5.0 provides AdfinXRealTime which permits to use the same applications within Excel or whatever COM objects container (IE, VB, VBA or Visual C++).
12.2 AdfinX Analytics

12.2.1 Description
To make it as convenient as possible and to satisfy all developer profiles, AdfinX Analytics offers two different but complementary interfaces: an improved function oriented API upgraded for functional parity with Excel and an object oriented API based on the analytic library. Interface Function Oriented API Description Also known as: AdfinXAnalyticsFunctions API. This is aimed at standard users and performs the same analytical calculations as the Reuters PowerPlus Pro Excel functions. This is based on the analytic library
Object-Orientated API
The API provides the following interfaces: AdxBondModule AdxConvBondModule AdxDateModule AdxExoticModule AdxForexModule AdxSwapModule Development support is only provided as part of a Premium Support agreement. See below how AdfinXAnalytics exposes Analytics methods in VB/VBA:
30 July 2005
239
12.2.2 AdfinXAnalytics Example

Reuters PowerPlus Pro provides you with some examples to help you to use the AdfinXAnalytics methods. To access the set of AdfinXAnalytics examples available, choose:
C:\Program Files\Reuters\Samples\AdfinX\AdfinXAnalytics Module

Samples are grouped by development platform: C++, VBA, Internet Explorer and VB.
12.2.3 AdxBondModule Example in VBA

Step Action
30 July 2005
240
Reuters Financial Software PowerPlus Pro Workbook Step 4 Action Choose in the Excel folder the Bonds.xls file. This example is intended to show how to use the AdxBondModule interface to calculate some basic parameters of a bond.
You can change all yellow cells and insert your own inputs. Click on Calculate to run the AdxBondModule sample. To access to all methods in this sample, you are able to view the VBA code used. Choose Tools -> Macros-> Visual Basic Editor or press Alt+F11. The VBA code of Bond.xls file appears in your Visual Basic Editor.
30 July 2005
241
12.2.4 AdxBondModule Example in Internet Explorer

Step 5 Action Choose in the IE folder, the Bonds.htm file. The following web page appears.
You can change all yellow cells and insert your own inputs. Click on the Calculate button to run the AdxBondModule sample. To access to all methods in this sample, you are able to view the VBScript code used. Choose in the View menu, the Source option.
30 July 2005
242
12.3 DataEngine ActiveX (DEX)

12.3.1 Description
The COM server architecture of the 3000 Data Engine provides value added data retrieval within Excel. It provides functional parity with the Excel Data Engine functions. It has been enhanced to support all features in the 3000 Data Engine, such as retrieval from DBU. These functions are gathered in a library named DEX (DataEngine ActiveX), an ActiveX/COM interface for developers wishing to retrieve Reuters 3000 data flexibly outside of the Excel container. The DataEngine ActiveX library (DEXLib) provides the following interfaces:
MrvInstrument MrvInstrumentBag MrvLookup MrvQuery
You can see below how DEX exposes the DataEngine interfaces and its members (methods, properties and events) in VB/VBA:
12.3.2 DataEngine ActiveX Examples

Reuters PowerPlus Pro provides you with some samples to help you to use the DataEngine ActiveX methods. To view the set of DataEngine ActiveX examples available, go to:
C:\Program Files\Reuters\Samples\Dex
The aim of this example is to help you to develop applications using DEX.
30 July 2005
243
12.3.3 DataEngine ActiveX Example in VB

Step 6 Action Choose in the VB folder, the Dex_ex1.exe file. The following Form appears.
30 July 2005
244
Reuters Financial Software PowerPlus Pro Workbook Step Action To access to all methods in this sample, you are able to view the VB code used. Click on the Dex_ex1.vbp file in the same directory (VB folder). The VB code of Dex_ex1.exe file appears.
You can either retrieve data from Reuters Treasury database or Equity databases.
12.4 AdfinX Real Time

12.4.1 Description
To complete the ActiveX availability of its development tools, Reuters PowerPlus Pro 5.0 includes AdfinXRealTime. This API offers the functional parity with Adfin RealTime module available in Excel. The RealTime API provides the following interfaces:
AdxRtChain AdxRtContribute AdxRtHistory AdxRtList
As is the case with AdfinXAnalytics and DEX, the support on AdfinXRealTime is based on a Premium Support Agreement. You can see below how AdfinXRealTime exposes the RealTime interfaces and its members in VB/VBA:
30 July 2005
245
12.4.2 AdfinXRealTime examples

Reuters PowerPlus Pro provides you with some samples to help you to use the Real Time ActiveX methods. To view the set of DataEngine ActiveX examples available, go to:
C:\Program Files\Reuters\Samples\AdfinX\AdfinX Real Time.
12.4.3 AdfinXRealTime example in VBA

The aim of this example is to help you to develop applications using ActiveX controls. Step Action
30 July 2005
246
Reuters Financial Software PowerPlus Pro Workbook Step 7 Action Choose in the Excel folder the Chain.xls file.
You can change the instrument name (.FCHI) and insert your own input. Click on the Chain button to run the AdxRtChainModule sample.
30 July 2005
247
13 INSERTING CHARTS IN EXCEL WITH REUTERS POWERPLUS PRO

13.1 How to Insert a Chart in Excel
13.1.1 How to Insert a Chart in Excel
Step 1 2 3 Action Enter a RIC in the cell B2, (the Reuters code, RTR.L in our example). Press Enter and click the cell again to make sure that it is highlighted. In order to insert the Chart object, choose the cell B2 and choose the Insert Chart option in the Reuters menu of Reuters PowerPlus Pro . Click and drag across the cells where you want to position the Chart display. This automatically inputs the required cell range into the Insert Chart dialog box. You may need to move the dialog box before you click and drag. In the Insert Chart dialog box, choose the zone B5:J16 and click the Next button.
The dialog box shows the reference for the cell that contains the Reuters code (you can change this reference if it is not correct).
30 July 2005
248
Reuters Financial Software PowerPlus Pro Workbook Step 4 Action Click Finish. This dismisses the dialog box and displays Chart in the specified position on the sheet.
The Chart obtained in Excel with Reuters PowerPlus Pro is identical to the Metastock Chart object in Kobra. To modify the Chart object properties, for example the colors, rightclick the Chart object and choose the Properties option:
30 July 2005
249
14 ERROR MESSAGES IN REUTERS POWERPLUS PRO

14.1 General Excel Error Messages
Error Description Occurs when Microsoft Excel does not recognise text in a formula. If this message appears in a cell where an Adfin function is pasted, this can be due to different types of errors. Possible Reasons Fix
#NAME?
#REF!
Occurs when a cell reference in one of the function parameters is not valid.
#####
#VALUE!
Occurs when a wrong type of argument or operand is used.
Use the button fx to check that Adfin is not loaded and a the required function has been sheet using Adfin functions entered correctly. is opened. As Adfin functions are not Check that Reuters PowerPlus recognised by Excel (they Pro has been loaded (Reuters are not loaded), no menu must appear in the Excel calculation will be done menu tool). and #NAME? will appear. The Adfin function is misspelled. Thus, Excel can not recognise the function to use. To get rid of such errors use the function wizard so that the proper function is pasted and all the required arguments are entered. Cells referenced by other formulas Choose the cell, enter edit mode by have been deleted, pasted over or pressing the F2 key or Shitf+F3 to edit moved. the function with the function wizard and then check that the function parameters are correct and not invalid. Drag the boundary between the column The formula in the cell headings to increase the width of the produces a result that is column, or reduce the amount of data too long to fit in the cell. The formula in the cell has displayed by changing the number returned a negative value format and reducing the number of decimal places. and the cell has been formatted with a date format. The argument may be misspelled or Choose the cell, press F2 to may use a wrong format. For edit the function and check that instance, the bond price or coupon the function parameters are in must be entered in percentage the right cell and that they format. contain valid values. Choose Format Cells then from the Format Cells dialog choose the Number tab and check the applied format (see Excel conventions).
30 July 2005
250
14.2 Real Time Error Messages

14.2.1 Introduction
Adfin realtime error messages describe probems that occur when accessing realtime data from the datafeed.
14.2.2 Error Messages

Error Description Possible Reasons The field name is not valid for the instrument code. The field name is valid, but is not listed in the Appendix_A file when working in direct API mode. Thus Reuters PowerPlus Pro cannot process the request. Fix The file Appendix_A is located in the product directory. A new field name can be added to the file.
#N/A Invalid field name
#N/A Invalid item name #N/A ND
Occurs when the instrument name is not recognised by the real-time source. No data available for request if history request expand date range. this message is displayed while the real-time is invoked, before the requested value appears. The first thing to try is to click on the Restart button (in the Real Time dialog box) to restart Adfin Real Time.
#N/A N/A Most of the time,
30 July 2005
251

Reuters Workbook89626

Uploaded by

Copyright:

Available Formats

Reuters Workbook89626

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Reuters Workbook89626

Uploaded by

Copyright:

Available Formats

What is the purpose of this document?

What is the purpose of this document?

What are some of the error messages explained in the document?

What are some of the error messages explained in the document?

Reuters PowerPlus Pro 5.

Reuters Financial Software PowerPlus Pro Workbook

INTEGRATION OF REALTIME DATA: ADFIN REALTIME FUNCTIONS ........................................... 14

REUTERS INTEGRATION OF REUTERS 3000 DATABASES........................................................ 50

ADFIN BONDS FUNCTIONS .................................................................................................... 83

REUTERS ADFIN TERMSTRUCTURE FUNCTIONS ................................................................... 119

ADFIN SWAPS FUNCTIONS .................................................................................................. 135

ADFIN CREDIT FUNCTIONS .................................................................................................. 164

ADFIN OPTIONS FUNCTIONS ............................................................................................ 192

ADFIN EQUITIES FUNCTIONS ............................................................................................ 231

COM BASED PROGRAMMING........................................................................................... 239

INSERTING CHARTS IN EXCEL WITH REUTERS POWERPLUS PRO ....................................... 248

ERROR MESSAGES IN REUTERS POWERPLUS PRO .......................................................... 250

Reuters Financial Software PowerPlus Pro Workbook

How This Guide is Organized

How to Use This Guide

1.2 Conventions Used in This Guide

<courier_italics> Site-specific variables or parameters.

Drag and drop Enter Highlight Press

Reuters Financial Software PowerPlus Pro Workbook

2 INTEGRATION OF REALTIME DATA: ADFIN REALTIME FUNCTIONS

Compatibility with DDE Servers

RtGet() function takes four arguments: =RtGet(SourceAlias, InstrumentCode, FieldName, RtMode)

SourceAlias InstrumentCode FieldName RtMode

Example Using RtGet()

Click OK. The RtGet() function dialog box appears.

Example Using Absolute Addresses

Example Using Equity RICs

Repeat the same procedure to get this realtime data.

Example Using Bonds

Repeat the same procedure to get this realtime data.

Improved RtGet() Update Mechanisms

Like RtGet(), RtUpdate() retrieves realtime data from a data source.

Reuters Financial Software PowerPlus Pro Workbook

SourceAlias InstrumentArray FieldNameArray DestinationCell MacroName RtMode

Example Using RtUpdate()

Reuters Financial Software PowerPlus Pro Workbook

Doing a Snapshot Update

Retrieving the System Date Using RtUpdate

FieldNameArray DestinationCell MacroName RtMode

LAST, HISTORIC CLOSE).

Example Using RtSeries()

Reuters Financial Software PowerPlus Pro Workbook

RtMode Keywords for RtSeries()

START ONTIME FRQ TSPOS

Specifies whether the snap is performed only on weekdays

RtChain() takes five arguments: =RtChain(SourceAlias, InstrumentCode, DestinationCell, MacroName, RtMode)

SourceAlias InstrumentCode DestinationCell MacroName RtMode

Example Using RtChain()

It is possible to use RtChain() function on RICs such as EFX= or EURIRS.

RtMode Keywords for RtChain()

Reuters Financial Software PowerPlus Pro Workbook

RtHistory() takes five arguments: =RtHistory(SourceName, InstrumentCode, FieldNames, HistoryStructure, HistoryMode)

SourceName InstrumentCode FieldNames HistoryStructure HistoryMode

Example Using RtHistory()