Can we pull up the consolidated RIC for Warrants using their CUSIP in API Codebook?

Hello team,

I'm using Python API. I use the below query to retrieve Consolidated RIC for Equities by their CUSIP. I need to retrieve consolidated RIC for other security types (Warrants, Bonds, Options). When I tried to use the same query for Warrants it did not work. Looks like Warrants do not have "ExchangeType" field available. Same issue with Options. Please advise how I can pull Consolidated RIC for Warrants and Options. Here is my query for equities:

1. Equity query:

df1 = rd.discovery.search(

view=rd.discovery.Views.SEARCH_ALL,

top=10,

filter=f"(AssetState ne 'DC' and ExchangeType eq 'Consolidated' and (RCSExchangeCountryLeaf eq 'Canada') and RCSCurrencyLeaf eq 'Canadian Dollar' and (CUSIP in ('929082105') or CinCUSIP in ('929082105')))",

select = "DTSubjectName,AssetState,ExchangeName,ExchangeType,RIC,IssueISIN,Gics,AssetState,BusinessEntity,PI,SearchAllCategoryv3,SearchAllCategoryv2,SearchAllCategory,RCSTRBC2012Leaf,RCSAssetCategoryLeaf,RCSCurrencyLeaf,ExDividendDate,CUSIP,CinCUSIP,SEDOL,RCSExchangeCountryLeaf"

)

df1

Returns:

2. Warrant query with ExchangeType eq 'Consolidated' filter:

df2 = rd.discovery.search(

view=rd.discovery.Views.SEARCH_ALL,

top=10,

filter=f"(AssetState ne 'DC' and ExchangeType eq 'Consolidated' and (RCSExchangeCountryLeaf eq 'Canada') and RCSCurrencyLeaf eq 'Canadian Dollar' and (CUSIP in ('09238B118') or CinCUSIP in ('09238B118')))",

)

df2

Returns: Nothing

3. Warrant query without ExchangeType eq 'Consolidated' filter:

df3 = rd.discovery.search(

view=rd.discovery.Views.SEARCH_ALL,

top=10,

filter=f"(AssetState ne 'DC' and (RCSExchangeCountryLeaf eq 'Canada') and RCSCurrencyLeaf eq 'Canadian Dollar' and (CUSIP in ('09238B118') or CinCUSIP in ('09238B118')))",

)

df3

Returns multiple rows. The Exchange Type column is missing. No way to identify which one is Consolidated RIC.

When I insert Warrant Cusip into "my_cusip" variable in the above query it doesn't return anything. I need to remove ExchangeType eq 'Consolidated' from the filter to retrieve Warrant data. However, it returns data for multiple exchanges and I need only "Consolidated" RIC. Please advise.

Thank you!

Find more posts tagged with

api

workspace

codebook

#content

Accepted answers

Jirapongse

@kevinlancelot.balais

Thank you for reaching out to us.

You can try the IsConsolidated field.

df2 = rd.discovery.search(
    view=rd.discovery.Views.SEARCH_ALL,
    top=10,
    filter=f"(AssetState ne 'DC' and IsConsolidated eq true and (RCSExchangeCountryLeaf eq 'Canada') and RCSCurrencyLeaf eq 'Canadian Dollar' and (CUSIP in ('09238B118') or CinCUSIP in ('09238B118')))",
    select = "DTSubjectName,AssetState,ExchangeName,ExchangeType,RIC,IssueISIN,Gics,AssetState,BusinessEntity,PI,SearchAllCategoryv3,SearchAllCategoryv2,SearchAllCategory,RCSTRBC2012Leaf,RCSAssetCategoryLeaf,RCSCurrencyLeaf,ExDividendDate,CUSIP,CinCUSIP,SEDOL,RCSExchangeCountryLeaf"
)
df2

The output is:

All comments

Jirapongse

@kevinlancelot.balais

Thank you for reaching out to us.

You can try the IsConsolidated field.

df2 = rd.discovery.search(
    view=rd.discovery.Views.SEARCH_ALL,
    top=10,
    filter=f"(AssetState ne 'DC' and IsConsolidated eq true and (RCSExchangeCountryLeaf eq 'Canada') and RCSCurrencyLeaf eq 'Canadian Dollar' and (CUSIP in ('09238B118') or CinCUSIP in ('09238B118')))",
    select = "DTSubjectName,AssetState,ExchangeName,ExchangeType,RIC,IssueISIN,Gics,AssetState,BusinessEntity,PI,SearchAllCategoryv3,SearchAllCategoryv2,SearchAllCategory,RCSTRBC2012Leaf,RCSAssetCategoryLeaf,RCSCurrencyLeaf,ExDividendDate,CUSIP,CinCUSIP,SEDOL,RCSExchangeCountryLeaf"
)
df2

The output is:

kevinlancelot.balais

Hi @Jirapongse, thank you for replying back to me and the confirmation - this is indeed very helpful.

Can you please confirm if we have a documentation on all possible filter options in the below query under filter field:

f2 = rd.discovery.search(
view=rd.discovery.Views.SEARCH_ALL,
top=10,
filter=f"(AssetState ne 'DC' and IsConsolidated eq true and (RCSExchangeCountryLeaf eq 'Canada') and RCSCurrencyLeaf eq 'Canadian Dollar' and (CUSIP in ('09238B118') or CinCUSIP in ('09238B118')))",
select = "DTSubjectName,AssetState,ExchangeName,ExchangeType,RIC,IssueISIN,Gics,AssetState,BusinessEntity,PI,SearchAllCategoryv3,SearchAllCategoryv2,SearchAllCategory,RCSTRBC2012Leaf,RCSAssetCategoryLeaf,RCSCurrencyLeaf,ExDividendDate,CUSIP,CinCUSIP,SEDOL,RCSExchangeCountryLeaf"
)
df2

Thank you so much!

yoy.delarama

Hi @Jirapongse, good day. May we follow up on the above query?

Do we have any documentation on all possible filter options in the below query under "filter" field (in bold):

f2 = rd.discovery.search(
view=rd.discovery.Views.SEARCH_ALL,
top=10,
filter=f"(AssetState ne 'DC' and IsConsolidated eq true and (RCSExchangeCountryLeaf eq 'Canada') and RCSCurrencyLeaf eq 'Canadian Dollar' and (CUSIP in ('09238B118') or CinCUSIP in ('09238B118')))",
select = "DTSubjectName,AssetState,ExchangeName,ExchangeType,RIC,IssueISIN,Gics,AssetState,BusinessEntity,PI,SearchAllCategoryv3,SearchAllCategoryv2,SearchAllCategory,RCSTRBC2012Leaf,RCSAssetCategoryLeaf,RCSCurrencyLeaf,ExDividendDate,CUSIP,CinCUSIP,SEDOL,RCSExchangeCountryLeaf"
)
df2

Jirapongse

@kevinlancelot.balais

To get the completed lists of all possible valuse of fields, you need to contact the helpdesk support team via MyRefinitiv.

However, you can use the navigators which provide the ability to summarize the distribution of your results. They are particularly useful when you are interested in gathering the domain of values for a specific property.

For example:

response = search.Definition(
    view = search.Views.SEARCH_ALL,
    top=0,
    navigators="RCSExchangeCountryLeaf"
).get_data()


response.data.raw["Navigators"]["RCSExchangeCountryLeaf"]["Buckets"]

The output is:

For more information, please rerfer to the Building Search into your Application Workflow article.

The examples are also on GitHub.

yuliya01

Thanks all! This is very helpful.

EXPLORE OUR SITES