After opening the file please make sure that all references are loaded correctly, and if required relink the libraries by going to Tools > References... in the Visual Basic Editor toolbar.

Tools and API

For this task we are going to use several Eikon COM APIs, located in your Thomson Reuters folder %PROGRAMFILES(X86)%\Thomson Reuters\Eikon(X, Y or Z folder)\Bin.

* Located in ...\Bin\Apps\TR.OFFICE.CORE\0.0.0.0\Bin\

For creating objects we are going to use this function:

I am not going to dive into the details of the usage of specific APIs as this is out of the scope of this article and I trust you can have a look at the documentation and samples on the Developer Community portal.

However, since we will be working with data, I will need to add an introduction to two entities that we are going to use: real-time records and chains.

A real-time record is a set of standard fields that contains the data related to a financial instrument. The key identifier of a record that is used by Refinitiv is a RIC, or the Refinitiv Instrument Code.

For instance, the bid quote for GBP/USD can be found in field BID of the GBP= RIC, or the offer yield for a 10-year US Treasury is in the SEC_YLD_1 field of the US10YT=RR RIC.

The RIC structure is mainly defined by the asset class of the financial instrument, but can also contain the currency, tenor and the instrument type information.

Since RICs are used as a universal identifier for all sorts of databases that we are going to use, all of the services will accept RICs as arguments.

Records can be organized into series, which are called chains. For example, GBP= can be found on the EFX= chain, that contains all European currencies, and US10YT=RR is contained in the current on-the-run US Treasury chain 0#USBMK=.

Project structure

Our app will take a list of ISINs and output a z-spread when the underlying curve updates. We will create separate services for search, reference data, real-time price updates and curves in order to separate the concerns. However, a real calculator used in production will have a different structure, so use with caution!

We need to create two classes that will hold data:

• Bond will hold all reference data required for z-spread calculation and its results, as well as a calculation ready event;
• Curve will be a snapshot of the current zero coupon yield curve for the bond currency;

The following services will serve the data:

• Search service - to get the initial list of bond identifiers based on some search criteria;
• Reference data service - to get the reference data required for calculations;
• Curve service - to get the latest term structure in the selected currency;
• Price services - to get the latest price for a bond.

All services will generate events that the main app module will be subscribed to and do necessary manipulations.

So, here is how it is going to roughly look like:

Search service

We need to generate a list of codes based on some criteria, so for this we are going to create a wrapper around RSearchLib.RSearchMgr and RSearchLib.RSearchQuery (rsearch.dll) workflow with a Query() method and OnUpdate() event, delivering the results.

For more information on RSearch please go check out this tutorial on the Developer Community portal.

For our example, we are going to request all active Eurobonds with a fixed coupon rate issued by governments and maturing after today.

This roughly translates into the following search query.

ThisWorkBook.bas:

NOTE: There is no definitive guide on RSearch parameters, so a good practice will be to build your query using the Search tool in Refinitiv Eikon for Excel.

We will need to sort the results by the issuer name, so the request parameters will look like this.

ThisWorkBook.bas:

After the query is executed, SearchService will raise an OnUpdate() event, containing a 1-dimension array with bond codes. Now that we have them, we will request the reference data.

ThisWorkbook.bas:

At the moment of writing, the query returned 831 instrument.

Reference data service

The ReferenceDataService is a wrapper around Dex2Lib.Dex2Mgr and Dex2Lib.Rdata (dex2.dll) with a Query() method and OnUpdate() event.

For more information on Dex2 please check out this tutorial on the Developer Community portal.

The Query() method takes a list of codes and fields. For our example we are going to request the following fields:

• TR.PreferredRic;
• TR.FiDescription;
• TR.FiCouponRate;
• TR.FiMaturityDate;
• TR.FiCurrency;
• TR.ADF_BONDSTRUCTURE.

So, going back to mSearchService_OnUpdate() event handler:

ThisWorkbook.bas:

After the query is executed and the data is collected, the service will return an OnUpdate() event with a 2-dimensional array of results. At this point we have enough data to generate our collections of bonds and zero curves.

For the bonds collection, mBonds, I am going to use the Scripting.Dictionary class from Microsoft Scripting Runtime library (scrrun.dll). This dictionary will contain objects of Bond class.

I am also adding two checks: ric is not NULL, coupon is not NULL - so only valid records are added to the collection.

ThisWorkbook.bas:

Now we need to request a yield curve for each currency and subscribe to updates for all bonds in the mBonds collection.

Let us start with curves.

Generating events (a digression)

There are two ways of working with events in VBA: built-in Event type (with WithEvents and RaiseEvent) and a custom method that mimics how event propagation and handling works in more complex environments.

While the first one is easy to implement, it has one major drawback: it is not possible to pass events from a collection of objects. So, unless you declare an object explicitly as WithEvents, working with its events is impossible.

In our example I do not know how many curves I need to request before executing the search query, while I plan to be able to subscribe to curve events in order to finalize my calculation.

Here is how we can work around this limitation:

• We will create an interface that will hold all of the events as methods, for example IEventHandler;
• The collection object will implement IEventHandler;
• All elements of the collection will hold a reference to the collection as IEventHandler.

This may look like an overkill for smaller projects, but for calculators that handle multiple types of events from multiple entities it is quite handy.

Curve service

The previous step yielded 27 currencies, but in real life not all currencies will have enough instruments for a yield curve.

Curve class and ICurveEventHandler

A curve from real-time data perspective is a chain with terms as RICs.

So, in order to build a term structure in the required format: a 2-dimensional array with dates and rates - we will need to:

1. Get all RICs from the chain;
2. Subscribe to fields GV2_DATE and PRIMACT_1 fields on each RIC;
3. Build a term structure on each update;
4. Raise the `OnUpdate' event.

The Curve class is essentially a wrapper around the functionality of AdfinXRtLib.AdxRtChain for #1 and AdfinXRtLib.AdxRtList for #2 from the real-time data API (rtx.dll).

For more information on AdfinXrt please go check out this tutorial on the Developer Community portal.

Let us start with requesting the chain.

Curve.cls:

We can see if the chain RIC exists by looking at DataStatus. If all is ok, we can subscribe to all individual RICs (terms) from mRtxChain.Data.

Curve.cls:

Once we receive the snapshot of the current values, we can build the term structure array.

Curve.cls:

We need to generate two types of events and pass them to the parent class CurveService, that can propagate them further to the main module of the app:

• OnUpdate() - fired every time there is an update on the curve;
• OnError() - should the curve not exist for a selected currency.

So, let us create the ICurveEventHandler interface that will help us achieve the goal.

ICurveEventHandler.cls:

Now we need to add:

• a placeholder for the event handler and create two methods that will call it;
• AddHandler to add the reference;
• RemoveHandler to remove it during object finalization.

Curve.cls:

That's it! Let us see how the implementation of ICurveEventHandler will look like in the CurveService class.

CurveService class

First, let us add the following line right after Option Explicit.

CurveService.cls:

This will generate two methods which will collect the events from the dictionary of Curve objects and pass them on to the parent object.

CurveService.cls:

After the blueprint of our event system is in place, we add the constructor routine. Since one can not pass arguments into the default class constructor, we will add a CreateObject() routine, that will initialize the object.

The common practice for the swap-derived zero curve codes is to add 0# before the currency code and add Z=R to the ending, for example, 0#SGDZ=R for Singaporean dollar. However, I decided to override default curves for several currencies, hence the mCurveRicByCcy dictionary.

The most important thing here is adding the reference to the event handler c.AddHandler(Me), so we can start receiving events.

CurveService.cls:

So, now when CurveService raised the OnUpdate() event, we can loop through the bond collection to find which bonds are eligible for recalculation.

ThisWorkBook.bas:

Price data service

The PriceDataService wraps around a single instance of AdfinXRtLib.AdxRtList, taking in a list of RICs and returning an event for each price update through the OnUpdate() event.

PriceDataService.cls:

Now each time there is an update on any of the instruments, the parent class will receive the event.

ThisWorkBook.bas:

Putting it all together

So, now when all events are flowing in nicely, we are going to use two functions from the Bond module of the Adfin Analytics library (adfxo.dll) to calculate the bond settlement date and the z-spread:

• BondModule.BdSettle(Trade date, Bond structure, Maturity date) - to calculate a settlement date;

• BondModule.AdBondSpread(Settlement date, Zero curve, Price, Maturity date, Coupon, Bond structure, Curve structure) - to calculate the z-spread.

For this we will need to revisit the mCurveService_OnUpdate() routine.

ThisWorkBook.bas:

So, now if you call ThisWorkBook.Start(), you should see the following output in the immediate window listing the bond, the price and the z-spread:

It took only 18 seconds on my machine (Windows VM on a 2015 MacBook Pro) which is quite impressive.

If you keep it running, curve service will trigger updates every minute and our universe will be recalculated.

Conclusion

As you can see, with the help of just a few tricks we were able to calculate a z-spread for over 600 instruments in less than 20 seconds - all inside Excel. And I hope as well that I have demonstrated that using custom event handlers in VBA is a powerful tool that helps you save time and computational resources.

Additional resources

1. Working with Excel Templates (requires a Refinitiv Eikon login);

2. COM API tutorials;

3. COM API Developer's guide;

4. Running search (requires a Refinitiv Eikon login

5. AdBondSpread (requires a Refinitiv Eikon login).

Downloads

Article.EikonCOMApi.VBA.EventDrivenFinancialCalculations