1. Home
  2. Article Catalog
  3. Build queries easily using Refinitiv's Search API with Python and Jupyter

Article

Build queries easily using Refinitiv's Search API with Python and Jupyter

Nick Zincone
Developer Advocate Developer Advocate

As I outlined in my Building Search into your Application Workflow article, Refinitiv provides a wealth of financial information through its Search service covering content such as quotes, instruments, organizations, people, and many more assets that users can programmatically access and integrate within their business applications. While Search provides a significant amount of content, power, and flexibility, there are challenges when attempting to navigate through the hundreds of available financial properties when deciding how to extract data. In that article, I provide guidance and tips to determine the possible approaches, from simple discovery through experimentation to more advanced techniques such as generating debug output and extracting metadata details. While the suggestions are helpful and effective, the work involved can be challenging and intimidating.

The following article outlines a convenient tool that greatly simplifies the challenges of discovering financial properties when programmatically building Search.  While the techniques outlined in the Search article demonstrate some extremely useful tips, this tool will alleviate the repetitive steps of extracting, formatting, uploading, and interrogating unformatted data points necessary to discover relevant properties.  The goal of this tool is to elevate the ability to understand and construct the necessary queries to pull out desired content within the Refinitiv ecosystem.

Contents

The article will focus on a specific Use Case that demonstrates the key capabilities of the service as well as other useful features. 

Prerequisites

The source code demonstrating the SearchBrowser module is available within GitHub.  The source code example provides that SearchBrowserLib module and the SearchBrowser example Python Notebooks outlined within this article.  You must have access to either the Refinitiv Workspace or Eikon desktop applications and the ability to run within a Python Jupyter environment or using the Refinitiv desktop CodeBook app available within Refinitiv Workspace or Eikon.

Getting Started

The SearchBrowser tool is a simple python module, built on top of the Refinitiv Data Platform (RDP) Library for Python, that accepts criteria allowing users to easily discover properties and values. As we walk through some examples, you will better understand how and where this tool will help in your construction of queries and the extraction of values that will greatly accelerate the goal of retrieving desired content. To get the most out of this article and its utility, I would recommend you have a basic familiarity with Search and basic details outlined within the Search Article referenced above.

The SearchBrowser module is designed to run within the Refinitiv Workspace/Eikon CodeBook environment. To get started we will need to import the RDP Library for Python as well as the SearchBrowser module that is included within the download package.

    	
            

import refinitiv.dataplatform as rdp

import pandas as pd

%run ./SearchBrowserLib.ipynb

 

rdp.open_desktop_session('DEFAULT_CODE_BOOK_APP_KEY')

Create a Search Browser object

The SearchBrowser module is a simple class that is responsible for managing the manipulation of debug and metadata information. The goal of this module is to hold financial properties, metadata, and data values, based on search criteria, that users can observe and interrogate at any time. To get started, simply create an instance of the SearchBrowser object. A single instance can be used throughout your interrogation - this is a one-time action.

    	
            

# Create our Search browser used throughout this workbook

browser = SearchBrowser(rdp)

Use Case

The simplest way to demonstrate what this module will provide is to define a simple use case that outlines the journey of what is involved in determining and discovering the appropriate search criteria and properties to solve the problem.

For example, we'll define the following use case:

  • Retrieve a list of active bonds for a specific organization, eg: Santander Bank
  • For this organization, only request bonds issued within a specific country, eg: United States
  • For each bond, retrieve details such as:
    • Maturity date
    • Issue Date
    • Coupon Rate
    • Coupon Type
    • Amount Outstanding
    • Amount Issued
    • Issuer Country

Important Note: I'm not interested in Perpetual Bonds - these do not have a maturity date.

Suggested approach

The best way users typically approach dealing with the above use case is to refer to an existing example and build from there. This will jump-start the journey to give a sense of what is returned. However, once you realize you need additional information or the data returned isn't exactly what you were looking for, you will be forced to interrogate the service. That is, you will typically go through an iteration of actions of guessing at criteria in hopes you find the answer. As part of these actions, you will likely need to perform 2 distinct steps, as outlined within the Search article:

  1. Extract the metadata

    This provides a complete list of properties and additional attributes about each field.

  2. Retrieve debug output

    Debug output is a dump of values associated with a specific query. As the Search Article outlines, the output will contain hundreds of properties and their values for you to interrogate.

In both cases, you will likely need to export the data to an editor or application like Excel to perform your interrogation. While these steps are useful, they are very cumbersome, especially when you are performing a number of tests across different views. To alleviate the burden, the SearchBrowser module provides a path that will greatly simplify the discovery of properties and the values they contain. As you will discover below, this will rapidly accelerate the interrogation steps when building your criteria.

Based on the above use case, the following steps are recommended:

  1. Step 1 - Start with a basic query

    The SearchBrowser interface supports the specification of a basic query expression as a quick way to retrieve related content. While this is a simple and convenient way to get started, you may already have a jump-start with an existing example. Optionally, you can supply some more detailed expressions such as a filter and possibly a view. Either way, this step is used to define a quick way to get some relevant data from the service.

  2. Step 2 - Filter out unwanted data

    Once a data set has been generated, we begin to narrow down our result set by specifying filter expressions. This step is critical as it leads to step 3 below. In some cases, we may not discover the appropriate output properties unless we properly filter our request first. For example, in our use case, we must ensure our results do not include perpetual bonds. If they did, we would not be able to discover a maturity date property - perpetual bonds have no maturity.

  3. Step 3 - Define the properties we want to retrieve

    Once we have defined a relevant filter expression, we can begin the process of discovering the output properties we want to capture.

Step 1 - Start with a basic query

When using SearchBrowser, the goal is to feed the tool search criteria. Initially, you will likely begin with simple, google-like, query expressions. As you become more advanced, you will move to more complicated filters and even navigators.

    	
            

# The SearchBrowser interface provides criteria to request for data.  Let's start with a basic query...

browser.execute("Santander bonds")

(46837, 954)

The above execution will perform the steps to query the Search services, retrieve debug and metadata information and prepare the results within the SearchBrowser. The execute() method returns the # of hits resulting from the request and the number of properties found in the first hit, respectively. The browser will only contain the details related to the first hit.

    	
            

# Dump the result of the above query...

browser.df

This table represents the complete data set related to the above query. Already, we have a visual of all the properties related to the request and their associated values. Effectively, a combination of the metadata and debug steps mentioned above. At this stage, you can quickly scan the output above to verify relevancy. In addition, you can optionally use the above query within an actual Search to determine if the data set is appropriate. That is, does it appear like we have retrieved bond information for Santander Bank? If so, we can begin the journey of narrowing down and filtering out unwanted hits. Alternatively, we can change our query and play with any expression you feel may provide more relevant results.

Step 2 - Filter out unwanted data

In this step, the goal is to remove unwanted hits.  For example, we are only interested in active bonds that have been issued in the United States.  In addition, our requirement is to filter out perpetual bonds. As part of this exercise, we will continually update the search criteria and likely perform multiple executions until we believe our criteria match our requirements.  By doing this, we can properly narrow down the result set.

The following interrogation steps will look for properties that will help us create filter expressions to narrow down our result set.

As outlined within the Search Article, the Search ecosystem defines logical views representing the entire data set available within the service. It is extremely important you generally understand how views can alter the type of data you retrieve. For example, the above execution to retrieve 'Santander bonds' will search across the entire Search content set - this is represented by the logical view called: 'SearchAll'. Doing so, may not only return bond instruments but other data, such as bond pricing information. Given this, there is a logical view, GovCorpInstruments, that will narrow down the result set to provide only the bond, not the individual pricing that is included within the view GovCorpQuotes.

    	
            

# First, narrow down the data set by limiting the search within a specific view

browser.execute(

    view = rdp.SearchViews.GovCorpInstruments,

    query = 'Santander bonds'

)

(24685, 954)

The above criteria limits the result to provide instruments (bond) only. You can see how the result set has significantly dropped down to ~25,000 hits. Let's continue.

    	
            

# Filter out perpetual bonds.  To do this, let's see if a relevant property exists...

browser.properties("perpetual")

The properties() method provides a simple way to discover relevant properties available within the browser.  By specifying case-insensitive text, we can ask the browser to return all matching properties.  Doing so, we are presented with all properties matching our input expression: 'perpetual'.  We can clearly see our match is a suitable candidate that indicates whether the bond is perpetual or not.  We can use this value to filter out those bonds that are perpetual.

    	
            

# Update the execution to filter out perpetual bonds...

browser.execute(

    view = rdp.SearchViews.GovCorpInstruments,    

    query = "santander bonds",

    filter = "IsPerpetualSecurity ne true"

)

(24557, 804)

Using this same kind of technique as we did above, we can further filter those bonds that have not already matured and are active. Let's interrogate the browser to find these properties.

Note As outlined within the Common Properties section of the Search article, two useful properties, IsActive and AssetState can be used to help ensure the bonds we request for are active and valid.

    	
            

# Let's ensure we have an 'active' property...

browser.properties("active")

    	
            

# As well as a 'state' property...

browser.properties("state")

As the Search Article outlines, in some cases, the state property, AssetState, may not be available. However, we have both. At this stage, it is up to you which one may be more relevant. Typically, the AssetState does provide more granularity about the state, and that you can use this one as opposed to relying on the boolean state of IsActive. That being said, using both may be redundant but certainly doesn't hurt. The filter expression utilizing these 2 properties looks like this:

"IsActive eq True and not(AssetStatus in ('MAT' 'DC'))"

The above expression filters bonds that are active with a status that is neither matured nor de-activated.

    	
            

# Next, how do we figure out bonds that are issued within a specific country?  

# Let's first see if there is a property that will help.

browser.properties("issuercountry")

    	
            

# The RCSIssuerCountryLeaf, or RCSIssuerCountry, properties are navigable.  

# Let's perform an execution and show what we can do...

browser.execute(

    view = rdp.SearchViews.GovCorpInstruments,

    query = "Santander bonds",

    filter = "IsPerpetualSecurity ne true and IsActive eq true and not(AssetStatus in ('MAT' 'DC'))",

    navigator = "RCSIssuerCountryLeaf"

)

    	
            

# Display the result of the above execution showing the results of the navigation...

browser.navigator

Not only do we get a list of all countries within our results, but a distribution of hits for each. For our use case, we can update the filter expression to ensure we are listing bonds that are issued within the United States. At this point, it is worth noting the exact attribute value associated with the property RCSIssuerCountryLeaf. A value of True means we can search for countries that exactly match our expression.

At this point, we should have a fairly accurate request that will pull down the list of relevant bonds.

    	
            

# Add our country filter using an exact match expression...

browser.execute(

    view = rdp.SearchViews.GovCorpInstruments,

    query = "Santander bonds",

    filter = "IsPerpetualSecurity ne true and IsActive eq true and \

              not(AssetStatus in ('MAT' 'DC')) and RCSIssuerCountryLeaf xeq 'United States'",

)

(38, 966)

Step 3 - Define the properties we want to retrieve

Now that we have the set of bonds we want to capture, let's go through the action of determining the collection of properties, based on our use case. The steps involved here to interrogate the service are the same as we outlined in Step 2 above.

Just as a reminder, we want to capture the following:

  • Maturity date
  • Issue Date
  • Coupon Rate
  • Coupon Type
  • Amount Outstanding
  • Amount Issued
    	
            

# Locate Maturity Date...

browser.properties('maturitydate')

    	
            

# Issue date...

browser.properties('issuedate')

    	
            

# Coupon rate...

browser.properties('couponrate')

    	
            

# Coupon type...

browser.properties('coupontype')

    	
            

# Amount outstanding...

browser.properties('outstanding')

    	
            

# Amount issued...

browser.properties("issued")

Step 4 - Putting it all together

While going through the above exercise, I was able to successfully locate the relevant required properties. However, this exercise will require some experimentation. The biggest challenge with Search is how to figure out the names of the properties. Given there are hundreds available, having the ability to quickly search for them will be invaluable. Because of this, you will likely play with the expressions you type. The nice thing is that you can at least see the values associated with the properties displayed in order for you to better understand the meaning behind the property.

Now that we've retrieved our output fields, let's create our final search and show the results, ordered by the date each matures.

    	
            

rdp.search(

    view = rdp.SearchViews.GovCorpInstruments,

    query = "Santander bonds",

    top = 100,

    filter = "IsPerpetualSecurity ne true and IsActive eq true and \

              not(AssetStatus in ('MAT' 'DC')) and RCSIssuerCountryLeaf xeq 'United States'",

    select = "MaturityDate, IssueDate, CouponRate, FaceOutstanding, FaceIssuedTotal, RCSCouponTypeLeaf",

    order_by = "MaturityDate"

)

Browser Features

The SearchBrowser was designed to facilitate many useful interrogation features enabling the user to rapidly discover properties. Given the power and flexibility of the Search service, you may need multiple ways to arrive at your discovery. For example, you may generally know, or guess, at the name of a property, or you may know a value to look for. If you are in a position where the name or value does not provide the answers, you may need to narrow down your results based on the property type or property that provides navigation. The bottom line is that the more tools you have available for use, the better the success rate at building your expressions.

Note: As you go through the exercise of locating properties, you may notice there are no relevant candidates for you to choose from, despite all the tools available within the SearchBrowser. If you are unable to figure out why certain properties are unavailable for the kind of data you are retrieving, this may be related to the search criteria provided. Because the SearchBrowser only captures data for the first hit encountered, updating your search criteria may present a different data set and as a result, different properties. Otherwise, you may need to reach out to the Refinitiv Helpdesk and they can involve a Content Specialist who can investigate further.

Below is a general outline of the core features. For each, I have provided some context when you may need to use them to give you a better understanding.

Properties

    	
            help(SearchBrowser.properties)
        
        
    

properties(self, text)

Browse the properties that match the text expression.
Eg: browser.properties('ISIN')

The properties() provide a list of properties that match the expression text provided. As you observed throughout the above Use Case, we have taken full advantage of this capability to not only provide a list of candidates we can use to narrow down our result set, but allowing us to choose the fields we need to capture.

DataFrame

Object Properties:

df - Lists the entire result table containing all properties, metadata, and their values, based on the 1st hit

When you simply want to dump the entire data frame captured within the SearchBrowser, this can be achieved by specifying this property on the browser object.

Values

    	
            help(SearchBrowser.values)
        
        
    

values(self, text)

Browse the values that match the text expression.
Eg: browser.values('united kingdom') - returns all values containing the expression 'united kingdom'

While the properties() method is extremely useful, at times you may be using the desktop, or some other tool, to build out your results and have the need to convert your request using the Search API. For example, in the above Use Case, we used the query expression "Santander bonds" to pull bonds for a specific company. In most cases, using a company name or ticker will generate an accurate list of bonds. However, there may be instances where Search will associate a specific query belonging to multiple organizations, thus generating an invalid list of bonds. This is entirely dependent on your query expression and that Search performs its queries that closely match your expression, as opposed to finding only hits related to the company expression you provided.

Alternatively, you may prefer to use the Company ID (Organization ID/Perm ID). In this scenario, if you have used the desktop to retrieve the Company Perm ID, eg: 8589934205, you can use this value to build out a filter that returns hits only related to this specific company ID.

    	
            

# List all properties that contain the following value...

browser.values("8589934205")

Using the above results, I can update my request by adding the following criteria within our filter:

"ParentOAPermID xeq '8589934205'"

    	
            

# Remove query and replace with an updated filter expression

browser.execute(

    view = rdp.SearchViews.GovCorpInstruments,

    filter = "ParentOAPermID xeq '8589934205' and IsPerpetualSecurity ne true and IsActive eq true and \

              not(AssetStatus in ('MAT' 'DC')) and RCSIssuerCountryLeaf xeq 'United States'",

)

(39, 607)

Applying the new filter, you may notice the number of hits may be different.

Note: Listed bonds change daily and the results may vary.

To justify why this may occur, will require a deeper analysis of the data. This is where the SearchBrowser shines. Using the available tools will allow users to easily interrogate fields and values to understand what conditions may have caused the disparity. To be successful, users will require some domain knowledge of the data they are retrieving. While the disparity above is beyond the scope of this article and may require a content specialist to investigate, users with some domain knowledge can interrogate the results, looking for conditions or settings that provide an explanation.

Types

    	
            help(SearchBrowser.type)
        
        
    

type(self, property_type)

Browse the types that match the specified property type.
Eg: browser.type(SearchBrowser.PropertyType.Double) - returns all properties that have a double type

Where the properties() and values() methods fail to provide a clue for the information you are seeking, you can use the type() method to possibly help narrow down your discovery. For example, there may be a boolean condition that determines the applicability of the bond that may be important for your analysis. This mechanism does provide a way to narrow down your discovery. The following execution presents all properties that provide a conditional flag. Depending on your requirements, some of these conditions may be relevant to the details you require - you can include these within your filter expressions.

    	
            

# Locate all properties that provide a boolean flag...

browser.type(SearchBrowser.PropertyType.Boolean)

Navigators

    	
            help(SearchBrowser.navigable)
        
        
    

navigable(self, prop=None, value=None)

Browse the metadata that matches all properties that are navigable.
Apply additional criteria that matches properties or values.
Eg: browser.navigable() - returns all navigable properties
browser.navigable('Description') - returns all navigable properties containing 'Description'
browser.navigable(value='euro') - returns all navigable properties with a value containing 'euro'
browser.navigable('RCS', 'euro') - returns all RCS-based navigable properties with a value containing 'euro'

When you begin your journey to hunt down properties, you will notice a Navigable attribute as part of the output listed for each property. A value of true indicates that the property provides a well-defined bucket of values. For example, an industry sector, asset category, or country code is a typical navigator that defines a collection of values associated with that property. Navigators not only bucket data for users to understand the domain of how data is collected but include distribution of how many hits are specific to your search criteria.

Using navigators, I can discover an appropriate property.

For example, within our specific use case, you will notice many of the bonds returned do not contain values for "FaceOutstanding" or "FaceIssuedTotal". Doing some more investigated work with a content specialist, I discovered that this data is not collected/available for bonds that belong to the "certificates of deposits" category. If I prefer to filter out bonds that belong to this category, I can approach this in a few ways. One simple way is to utilize the properties() feature to search for anything related to a 'category'. However, it may be more worthwhile to determine if there are other categories I may want to filter out.

As a first step, let's determine if there is a navigable category property:

    	
            

# Give me all navigators that may be related to a 'category'...

browser.navigable('category')

At this moment, I can see many candidates to choose from. The one that seems most interesting/applicable is the 'AssetCategoryLeaf'. Let's list out all the asset categories for my latest query.

    	
            

# Select the 'RCSAssetCategoryLeaf' to navigate.

browser.execute(

    view = rdp.SearchViews.GovCorpInstruments,

    filter = "ParentOAPermID xeq '8589934205' and IsPerpetualSecurity ne true and IsActive eq true and \

              not(AssetStatus in ('MAT' 'DC')) and RCSIssuerCountryLeaf xeq 'United States'",

    navigator = "RCSAssetCategoryLeaf"

)

    	
            

# The following distribution and domain of names apply to the above request

browser.navigator

    	
            

# Updating our search to filter out 'Certificate of Deposit'...

# It's worth noting that I could simply ignore all categories except 'Bond' as an alternative.

# This depends on your requirements.

rdp.search(

    view = rdp.SearchViews.GovCorpInstruments,

    top = 100,

    filter = "ParentOAPermID xeq '8589934205' and IsPerpetualSecurity ne true and IsActive eq true and \

              not(AssetStatus in ('MAT' 'DC')) and RCSIssuerCountryLeaf xeq 'United States' and \

              RCSAssetCategoryLeaf ne 'Certificate of Deposit'",

    select = "MaturityDate, IssueDate, CouponRate, FaceOutstanding, FaceIssuedTotal, RCSCouponTypeLeaf",

    order_by = "MaturityDate"

)

Next Steps

The SearchBrowser interface is a simple add-on tool you can load to greatly accelerate your journey when building out your search criteria. I would recommend creating a simple test workbook and loading the SearchBrowserLib module to quickly perform many and multiple what-if scenarios. Because Search is so flexible and sensitive to many conditions, you may not realize the collection of hits does truly represent the data set you intended to retrieve. Not only does the SearchBrowser simplify the listing of the entire set of properties and conditions that may affect your search criteria, but the ability to hunt down whether these conditions play a role in the data set returned. The more you master Search and what this tool can provide, the greater the rate of success and the reduced amount of time spent.