Importing loans using TFScreen.Deals
Hi
[disclaimer: I am a beginner]
I need to import large samples of loans, for instance all loans issued in Europe since 1999 with all the loan variables available in Eikon
The deal screener has a limitation of 3000 loans
I would like to spare me many clicks on deal screener and hope for an excel solution, eventually a template like solution
I am puzzled by the universe function
After using dealscreener and obeying the 3000 constraint i select 1 country, exclude loans to financials & governments, and limit to loans after 01.01.2014; the resulting TFScreen.Deals is below
TFScreen.Deals(DEALSEXPRFORMAT(Deals,Active,1, IssuerStatus=Loans),curn=USD,(IN(TF.LO.BorrowNation(),"United Kingdom")=1),(NOT_IN(TF.LO.BorrowTRBCEcoSec(),"Financials","Governmental Services")=1),(TF.LO.CloseDate()>01/01/2014))
Then every item i want to pull for example:
TF.LO.LODealNo() TF.LO.Headline() TF.LO.Desc() TF.LO.Type() TF.LO.AnnDate() TF.LO.CloseDate() TF.LO.MaturityDate()
Uses this TFScreen.Deals
My idea is to try to automatize the extractions using the TFScreen.Deals, where 3 items would be changing: the borrower nation, the close date, and eventually the borrower sector)
The problem i have is that when i want to reference cells into the formula it doesn't work; i try to map somehow eikon templates i found (for M&A deals and so on)
For instance using such a formula:
'TFScreen.Deals(DEALSEXPRFORMAT(Deals,Active,1, IssuerStatus=Loans),curn=USD,((IN(TF.LO.BorrowNation(),"""&J4&""")=1)),((NOT_IN(TF.LO.BorrowTRBCEcoSec(),"Financials","Governmental Services")=1)),((TF.LO.CloseDate()>"&TEXTE(K4;"dd/mm/yyyy")&"))
where cell J4 has United Kingdom and K4 has 01/01/2014 doesn't work and i can't figure out why and what i am doing wrong
Thank you very much in advance for any help
Best
CG
Best Answer
-
@godlewski
This forum is dedicated to software developers using Refinitiv APIs. It's not the right place to ask questions about Excel add-ins available in Eikon, lest about string concatenation in Excel, which is what your issue boils down to. I will answer your question this once, but in the future if you need help with using Eikon Excel add-ins please contact Refinitiv Helpdesk. And if you need help with Excel, please use appropriate resources on the Internet.
To answer your question, first of all an apostrophe in front of the text entered into an Excel cell means the text is not evaluated as a formula. It's interpreted as static text. Then if you need to include double quotes in a concatenated string, Excel requires that double quotes are represented as double-double quotes, i.e. as "". Assuming you'd like to construct your deals screener expression in cell L4 the formula in L4 should be:="TFScreen.Deals(DEALSEXPRFORMAT(Deals,Active,1, IssuerStatus=Loans),curn=USD,(IN(TF.LO.BorrowNation(),"""&J4&""")=1),(NOT_IN(TF.LO.BorrowTRBCEcoSec(),""Financials"",""Governmental Services"")=1),(TF.LO.CloseDate()>"&TEXT(K4,"DD/MM/YYYY")&"))"
And then you can retrieve the list of loan deal numbers using
=TF(L4, "TF.LO.LODealNo()", "cleanup=2")
0
Answers
-
thank you and sorry for posting in the wrong place
best
CG
0
Categories
- All Categories
- 6 AHS
- 39 Alpha
- 162 App Studio
- 4 Block Chain
- 5 Bot Platform
- 17 Connected Risk APIs
- 47 Data Fusion
- 30 Data Model Discovery
- 608 Datastream
- 1.3K DSS
- 577 Eikon COM
- 4.9K Eikon Data APIs
- 7 Electronic Trading
- Generic FIX
- 7 Local Bank Node API
- Trading API
- 2.7K Elektron
- 1.3K EMA
- 236 ETA
- 519 WebSocket API
- 33 FX Venues
- 10 FX Market Data
- 1 FX Post Trade
- 1 FX Trading - Matching
- 12 FX Trading – RFQ Maker
- 5 Intelligent Tagging
- 2 Legal One
- 20 Messenger Bot
- 2 Messenger Side by Side
- 9 ONESOURCE
- 7 Indirect Tax
- 59 Open Calais
- 264 Open PermID
- 39 Entity Search
- 2 Org ID
- PAM
- PAM - Logging
- 8.4K Private Comments
- 6 Product Insight
- Project Tracking
- ProView
- ProView Internal
- 20 RDMS
- 1.4K Refinitiv Data Platform
- 370 Refinitiv Data Platform Libraries
- 3 Refinitiv Due Diligence
- LSEG Due Diligence Portal API
- 3 Refinitiv Due Dilligence Centre
- Rose's Space
- 1.1K Screening
- 18 Qual-ID API
- 13 Screening Deployed
- 23 Screening Online
- 10 World-Check Customer Risk Screener
- 990 World-Check One
- 44 World-Check One Zero Footprint
- 45 Side by Side Integration API
- Test Space
- 3 Thomson One Smart
- 1.2K TR Internal
- Global Hackathon 2015
- 2 Specialists Who Code
- 10 TR Knowledge Graph
- 150 Transactions
- 142 REDI API
- 1.7K TREP APIs
- 4 CAT
- 21 DACS Station
- 117 Open DACS
- 1.1K RFA
- 103 UPA
- 172 TREP Infrastructure
- 224 TRKD
- 886 TRTH
- 5 Velocity Analytics
- 5 Wealth Management Web Services
- 60 Workspace SDK
- 9 Element Framework
- 5 Grid
- 13 World-Check Data File
- Yield Book Analytics
- 46 中文论坛