Importing loans using TFScreen.Deals

godlewski

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

Alex Putkov.1

@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")