How to aggregate Amount Outstanding based on Issue Rating for all US Municipal Bonds using python?
Hello Team, client needs a python script that would aggregate the Amount Outstanding based on Issue Rating for all US Municipal Bonds using python.
We have this script but it is limited to 10,000 rows and the total result is 992,151.
import refinitiv.data as rd
rd.open_session()
rd.discovery.search(
view = rd.discovery.Views.MUNICIPAL_INSTRUMENTS,
top = 10000,
filter = "(IsActive eq true and SearchAllCategoryv2 eq 'US Municipal Bonds')",
select = "LongName,Ticker,CouponRate,MaturityDate,CUSIP,RIC,MuniStateDescription,CouponClassDescription,EJVAssetID,BusinessEntity,PI,SearchAllCategoryv3,SearchAllCategoryv2,SearchAllCategory,IssueDate,BankQualified,BondInsuranceDescription,IsCallable,IsPutable,IsSinkable,FaceOutstanding"
)
After getting all the results, she would like to aggregate the Amount Outstanding per Issue Rating. She is looking to achieve
1) US Muni bond with Moody's rating Aaa = X amount outstanding
US Muni bond with Moody's rating Aa = Y amount outstanding
US Muni bond with Moody's rating Baa = Z amount outstanding ........ and for all Moody's ratings
2) All US Muni Bonds = DDD amount outstanding (doesn't matter if it was rated by Moody's or is missing any rating )
Best Answer
-
You can use the Search metadata to list all available fields in the MUNICIPAL_INSTRUMENTS view.
response = search.metadata.Definition(
view = search.Views.MUNICIPAL_INSTRUMENTS # Required parameter
).get_data()
response.data.df0
Answers
-
Thank you for reaching out to us.
The search API limits the results to 10,000 entries so it is not effective to get all 992,151 results.
I am not sure if CFS on RDP can provide all US Municipal Bonds. Please check with the product support team.
0 -
Thank you for your response. I understand that there is a limit of 10,000 entries per pull. However, my question is specifically about adjusting the Python code to split 992,151 entries into 100 pulls, each containing 10,000 entries.
For example:
Is it possible to modify the function where "top = 10000" is set, so that it covers a range, such as 10,001 to 20,000 for one pull, 20,001 to 30,000 for the next, and so on?
Alternatively, is there a way to adjust the "filter" function to pull based on a specific range of row numbers?
Or, could a loop be implemented to achieve this result?
0 -
You can use Navigators that provide the ability to categorize and summarize the results. Then, you can use the values returned within the buckets to use in your filters.
For more information, please refer to the Building Search into your Application Workflow article.
0 -
Hi @dianne.palmario, I would advise using the LDL following this tutorial.
0 -
Thanks! I was able to filter by MaturityDate and iterate through the data in a loop. Now, I’m working on adding more variables to the query, including the available credit ratings. Where can I find the field names for credit ratings in the MUNICIPAL_INSTRUMENTS database? I attempted using CodeCrator, but the field names that work for individual U.S. municipal bonds don’t seem to work when I use them in my query >>
df = rd.discovery.search( view=rd.discovery.Views.MUNICIPAL_INSTRUMENTS, top=10000, filter=base_filter + date_filter, select="LongName,Ticker,CouponRate,MaturityDate,CUSIP,RIC,MuniStateDescription,CouponClassDescription,EJVAssetID,BusinessEntity,PI,SearchAllCategoryv3,SearchAllCategoryv2,SearchAllCategory,IssueDate,BankQualified,BondInsuranceDescription,IsCallable,IsPutable,IsSinkable,FaceOutstanding,RATING")0
Categories
- All Categories
- 3 Polls
- 6 AHS
- 36 Alpha
- 166 App Studio
- 6 Block Chain
- 4 Bot Platform
- 18 Connected Risk APIs
- 47 Data Fusion
- 34 Data Model Discovery
- 684 Datastream
- 1.4K DSS
- 615 Eikon COM
- 5.2K Eikon Data APIs
- 10 Electronic Trading
- Generic FIX
- 7 Local Bank Node API
- 3 Trading API
- 2.9K Elektron
- 1.4K EMA
- 249 ETA
- 554 WebSocket API
- 37 FX Venues
- 14 FX Market Data
- 1 FX Post Trade
- 1 FX Trading - Matching
- 12 FX Trading – RFQ Maker
- 5 Intelligent Tagging
- 2 Legal One
- 23 Messenger Bot
- 3 Messenger Side by Side
- 9 ONESOURCE
- 7 Indirect Tax
- 60 Open Calais
- 275 Open PermID
- 44 Entity Search
- 2 Org ID
- 1 PAM
- PAM - Logging
- 6 Product Insight
- Project Tracking
- ProView
- ProView Internal
- 22 RDMS
- 1.9K Refinitiv Data Platform
- 643 Refinitiv Data Platform Libraries
- 4 LSEG Due Diligence
- LSEG Due Diligence Portal API
- 4 Refinitiv Due Dilligence Centre
- Rose's Space
- 1.2K Screening
- 18 Qual-ID API
- 13 Screening Deployed
- 23 Screening Online
- 12 World-Check Customer Risk Screener
- 1K World-Check One
- 46 World-Check One Zero Footprint
- 45 Side by Side Integration API
- 2 Test Space
- 3 Thomson One Smart
- 10 TR Knowledge Graph
- 151 Transactions
- 143 REDI API
- 1.8K TREP APIs
- 4 CAT
- 26 DACS Station
- 121 Open DACS
- 1.1K RFA
- 104 UPA
- 192 TREP Infrastructure
- 228 TRKD
- 915 TRTH
- 5 Velocity Analytics
- 9 Wealth Management Web Services
- 90 Workspace SDK
- 11 Element Framework
- 5 Grid
- 18 World-Check Data File
- 1 Yield Book Analytics
- 46 中文论坛