Company Selection

The following procedures can be used to determine the company of interest based on its Organization ID, or Company Perm ID. Within the desktop, there are a few ways to acquire the ID. In each case, you can search for the name of the company and look for the ID as follows:

• From the search window within the LSEG Workspace Desktop Taskbar, start typing the name of the company. The search window provides auto-suggest and will present matches. When found, hit the "tab" key. Follow this up by typing "Codes" then hit tab and a screen will show the "Company PermID".
• Or, within the desktop, pull up the DIB (Data Item Browser) and locate the field [TR.OrganizationID]. For example, as above, select the company. In the field input window, type "Organization" and look for TR.OrganizationID.
• Or, you can programmatically determine the ID when providing the company Name (see below)

• Within the desktop, pull up the DIB (Data Item Browser) and locate the field [TR.OrganizationID].  For example, as above, select the company.  In the field input window, type "Organization" and look for TR.OrganizationID.

• Programmatically, you can determine the ID when providing the company Name.

From the above code sample, all ID's will reference the same company which will be the ID to choose. Whichever mechanism chosen to acquire the ID, assign the value below that will be named 'org_id' in our code going forward. In addition, we can also review the company name references included in the output.

Note: We could possibly use the company name as the search criteria in our main searching of bonds, as opposed to finding the organization id, but we want to ensure we're referring to the actual company as opposed to the possibility of returning results that closely match based on the company expression provided.

Present the company name.

Using the Search service, query for the specified organization to retrieve the full parent name. We'll use this as a simple title for our presentation chart.

Debt Structure

The following request utilizes the Search call to select the list of bonds associated with the defined company. When searching for bonds, we must define criteria where the state of the bond is active. For each bond, we pull out a set of properties, or fields, that are relevant for our collection. Most notably, we choose some critical values such as the Amount Oustanding, Coupon Rate, and Total Face Value Issued for the bond. In addition, we must add the currency of each bond, as this is ultimately the determining factor when we standardize a specified currency for the values selected.

The search() API call is a powerful utility offering detailed search analysis that developers can customize for their requirements. The output, i.e. columns or fields returned, is based on the select parameter - see below. The specific selection of these properties has been determined by utilizing Search exploration APIs. These APIs provide the ability to interrogate the values returned, allowing the user to determine the desired properties. For additional details around Search and how to get the most out of this service, refer to the Building Search into your Application Workflow article.

To begin, define the output fields/properties of interest. In the search exploration article mentioned above, you can follow the steps to narrowing down which specific properties/fields to utilize for output. In addition, the ability to narrow the domain of information within a specific content set, referred to as a View. In our case, we are looking at the domain of GovCorpInstruments.

Search for our bonds.

The value of the ID defined above is injected into our search expression below.

Currency Conversion

Looking at the above display, the set of bonds returned may be represented in multiple currencies. The goal of our exercise is to standardize our numeric values based on a selected, base Currency. For example, we may choose to have all values within the result set to be represented in the Euro (EUR).

In the above request, we have utilized the navigators parameter when performing our search. This parameter provides a convenient way to collect and bucket all currencies within our search output. By doing this, I can walk through this collection and perform a conversion to a selected base currency and capture the conversion factors within a conversion table. In a later step, the table can then be used to convert the numeric values for each bond we selected.

The code below performs the conversion in a 2-step process.

1. Retrieve the Currency Conversion factor

   LSEG has created a number of convenient Cross Rate instrument codes exclusively designed to retrieve a conversion value that can be used as the basis for conversion. For example, the following Cross Rate code converts from the British Pound to Euro:

   GBPEUR=R

   The algorithm below derives the code from the source currency, eg: 'GBP', and the base currency (what we want to convert to), eg: 'EUR'. The conversion value returned may require additional factoring based on a Scaling factor. When retrieving the conversion value, we also retrieve a scaling factor that we must apply an additional calculation. For example, a cross rate code may return a scaling factor of 1, where another may return a scaling factor of 100. In either case, I must apply a final calculation as:

   Conversion Factor = Conversion value / Scaling factor

2. Optionally, derive Currency Conversion factor

   In some cases, there may be no Cross Rate instrument code defined, depending on the source currency and/or the base currency. As such, the algorithm will manually create the conversion factor by deducing both the source currency rate, eg: 'GBP=', and the base currency rate: 'EUR=' and perform a simple calculation to derive the Cross Rate factor.

The result of the following functionality is to produce a conversion table, keyed on the source currency code, that allows simple access to the conversion factor for a selected base currency.

Derive conversion factors

Based on the bucket of currencies retrieved from the Search (Navigator), build the cross-rate table. As input, the 2nd parameter represents the base currency.

Bond Conversion

Now that we have in place the currency conversion table, I can walk through the bond collection and apply the conversion factor against all numeric values defined within the table. The resulting table will be represented in a base currency.

Display our results

Now we are ready to pull up the final dataframe which we've named 'bonds' that will have all of our issued bonds converted from their initial currency into Euros (in this case), with the proper decimals.

Visualization and analysis of results

It's great to see how straightforward it has been to pull up the bonds that met the requirements, make sure the data is properly populated, format the floating results to a standard 2 decimals, and display them in their newly standardized currency.

With this done, let's look at totals, and a simple chart to get a broad idea of which subsidiary owes what, what the Rank is of each of the bonds, and how they contribute to the big picture of the organizations' debt structure.

Conclusions

In this simple yet constructive article, we have looked at how to use the APIs to search for active bonds for a company, bring back specific properties for the same, and then convert them all into a single currency so we are able to look at the whole picture. The currency conversion done in this article is transferrable to any asset class and any data set. Furthermore, the visualization in a table and chart is useful to get the 'quick big picture' in a large range of use cases. Look out for an article from the same authors on cash flow analysis for a portfolio of bonds where we'll be looking at expected cash flows, pay dates, and much more - all with the use of the basic tips you've learned here.