Screener syntax in Python

I have the following Screener syntax, could you help me to convert it into Python syntax, many thanks!

TR("SCREEN(U(IN(DEALS)/*UNV:DEALSMNA*/), IN(TR.MnANationHQ,""AL"",""BY"",""CZ"",""LV"",""LT"",""MK"",""ME"",""PL"",""RS"",""SI"",""AD"",""BE"",""DK"",""FI"",""FR"",""DE"",""GR"",""GL"",""GG"",""IS"",""IE"",""IM"",""IT"",""LI"",""LU"",""M"&"T"",""SM"",""SJ"",""CH"",""AT"",""CY"",""FO"",""GI"",""JE"",""MC"",""NL"",""NO"",""PT"",""ES"",""SE"",""GB"",""VA""), IN(TR.MnAStatus,""U"",""C""), BETWEEN(TR.MnADateUnconditional,20190101,20220430)/*dt:Date*/, TR.MnAAcqIsBlankCheckC"&"o==true, CURN=USD)"

Find more posts tagged with

python

screener

Accepted answers

zoya faberov

Hello @laura.rossi2710,

Both work for you, right?

In that case, on the Eikon forumla, try adding the last part of the filter:

Then use "export as Formula" button to transfer the screener formula into Eikon Excel:

You should (with comments removed) have something like:

=@TR(&quot;SCREEN(U(IN(DEALS)/*UNV:DEALSMNA*/), IN(TR.MnANationHQ,""AL"",""BA"",""CZ""), IN(TR.MnAStatus,""U"",""C""), BETWEEN(TR.MnADateUnconditional,20190101,20220430)/*dt:Date*/, TR.MnAAcqIsBlankCheckCo==true, CURN=USD)","TR.MnASDCDealNumber,TR.MnAAnnDate,TR.MnARankDate,TR.MnARankValueIncNetDebt(Curn=USD,Scale=6),TR.MnAT"&"arget,TR.MnATargetPermId,TR.MnATargetMacroInd,TR.MnATargetMidInd,TR.MnATargetNation,TR.MnAAcquirorPe"&"rmId,TR.MnAAcquirorMacroInd,TR.MnAAcquirorMidInd,TR.MnAAcquirorNation,TR.MnATargetFinAdvisor(Concat="&"'|'),TR.MnAAcquirorFinAdvisor(Concat='|'),TR.MNADealId,TR.MnAStatus,TR.MnADateUnconditional ","Curn=USD CH=Fd")

that "&" is what I believe was causing the issue on the formula paste.

if so far so good, try in code:

syntax = 'SCREEN(U(IN(DEALS)), \
IN(TR.MnANationHQ,"AL","BY","CZ"), IN(TR.MnAStatus,"U","C"), BETWEEN(TR.MnADateUnconditional,20190101,20220430), \
TR.MnAAcqIsBlankCheckCo==false, CURN=USD)'
fields = ['TR.MnASDCDealNumber','TR.MnAAnnDate','TR.MnARankDate']
df, e = ek.get_data(syntax, fields)
df

If this works for you and returns the result of 580 rows ?

Next, replace false with true:

syntax = 'SCREEN(U(IN(DEALS)), \
IN(TR.MnANationHQ,"AL","BY","CZ"), IN(TR.MnAStatus,"U","C"), BETWEEN(TR.MnADateUnconditional,20190101,20220430), \
TR.MnAAcqIsBlankCheckCo==true, CURN=USD)'
fields = ['TR.MnASDCDealNumber','TR.MnAAnnDate','TR.MnARankDate']
df, e = ek.get_data(syntax, fields)
df

and add your complete list of required HQs back.

You should have your required expression in Python

(the reason I suggest the test with TR.MnAAcqIsBlankCheckCo as false and not true first, is that for these 3 HQs, true returns no valid results, so no way to confirm the the request is valid. I wish to confirm what you run is valid at every step, till the complete expression is ready.

Let us know how this works on your side

All comments

zoya faberov

Hello @laura.rossi2710 ,

Are you seeing any issue applying this screen expression in your Eikon/Workspace Excel?

When I run it, I get back:

"You cannot use real-time fields with a SCREEN universe.".

The screening expression works for me, fields is where the issue appears to reside, so if I use the same screener expression and replace fields, I get results back:

syntax = 'SCREEN(U(IN(DEALS)), IN(TR.MnANationHQ,"AL","BY","CZ","LV","LT","MK","ME",\
"PL","RS","SI","AD","BE","DK","FI","FR","DE","GR","GL","GG","IS","IE","IM","IT","LI","LU",\
"M&T","SM","SJ","CH","AT","CY","FO","GI","JE","MC","NL","NO","PT","ES","SE","GB","VA"),\
IN(TR.MnAStatus,"U","C"), BETWEEN(TR.MnADateUnconditional,20190101,20220430), \
TR.MnAAcqIsBlankCheckCC==o, URN=USD)'
fields = ['TR.MnASDCDealNumber','TR.MnAAcqIsBlankCheckC']
df,e = ek.get_data(syntax, fields)
df

and my result looks like:

I d like to recommend an insightful article on the subject Find Your Right Companies with SCREENER | Eikon Data APIs(Python), it goes over deriving and converting screener expressions step by step, and can be of help in tuning your expression.

laura.rossi2710

Hello @zoya faberov

many thanks for your reply, I tried to replicate your code, but it returns no output, see image below:

Do you see any mistakes I could have made?

In addition, in my excel file where I downloaded the screener template from Refinitiv, I have only 60 entries (associated to 60 different M&A deals involving blanck check companies in Europe), how is it possible that in your output you have 43595 raws. Maybe I am using a wrong code for the analysis I want to perform.

I would like to retrieve the total returns of M&A deals in Europe which involved a blank check company (time frame: 1-Jan-2019 to 30-Apr-2022), maybe I am approaching the coding in Python in the wrong way. Let me know if you have some suggestions.

May thanks for your help.

zoya faberov

Hello @laura.rossi2710 ,

I do not see the typo on the screenshot. I agree with your line of thinking that there likely is one.

Unfortunately, I was not able to run your Excel formula as is by pasting it into my Excel- causes "You cannot use real-time fields with a SCREEN universe", i.e. there should be a discrepancy somewhere, comma, extraneous character, etc.

In my personal opinion, both screener in RW Excel, and screener-generated expressions in code, are very helpful but are quite temperamental to use- as even a tiny typo, missing or invisible character will lead to error, or worse- to an incorrect result.

I suggest we start with something small and working, and build up to your requirement.

1. I have generated a very small screener with Workspace, in Deals, along the same lines as yours, I believe, only three TR.MnANationHQ selected so as to make the test quick:

=@TR(&quot;SCREEN(U(IN(DEALS)/*UNV:DEALSMNA*/), IN(TR.MnANationHQ,""AL"",""BA"",""CZ""), IN(TR.MnAStatus,""U"",""C""), BETWEEN(TR.MnADateUnconditional,20190101,20220430)/*dt:Date*/, CURN=USD)","TR.MnASDCDealNumber,TR.MnAAnnDate,TR.MnARankDate,TR.MnARankValueIncNetDebt(Curn=USD,Scale=6),TR.MnAT"&"arget,TR.MnATargetPermId,TR.MnATargetMacroInd,TR.MnATargetMidInd,TR.MnATargetNation,TR.MnAAcquirorPe"&"rmId,TR.MnAAcquirorMacroInd,TR.MnAAcquirorMidInd,TR.MnAAcquirorNation,TR.MnATargetFinAdvisor(Concat="&"'|'),TR.MnAAcquirorFinAdvisor(Concat='|'),TR.MNADealId,TR.MnAStatus,TR.MnADateUnconditional ","Curn=USD CH=Fd")

runs for me, generates 586 hits. If you paste it into your Excel, does it run for you? Does it generate 586 hits? If not, let's try to discuss why. If yes, we can try to build up from here?

2. On the python side ( are you testing from CodeBook?) , try:

syntax = 'SCREEN(U(IN(DEALS)), \
IN(TR.MnANationHQ,"AL","BY","CZ"), IN(TR.MnAStatus,"U","C"), CURN=USD)'
fields = ['TR.MnASDCDealNumber','TR.MnAAnnDate','TR.MnARankDate']
df, e = ek.get_data(syntax, fields)
df

this is very simple and generates 4312 rows, but hope it exemplifies the approach in brief.

In general, as with any long list inside screener expressions, I would:

for tuning, start with <=three items (HQs), come up with a minimal working request
try to tune request's screener expression and fields to your requirement
and last putting the full HQ required list back in, so as not to spend time typing and increase the chance to mistyping

Let me know how this works for you?

laura.rossi2710

Thank you @zoya faberov now it works.

zoya faberov

Hello @laura.rossi2710,

Both work for you, right?

In that case, on the Eikon forumla, try adding the last part of the filter:

Then use "export as Formula" button to transfer the screener formula into Eikon Excel:

You should (with comments removed) have something like:

=@TR(&quot;SCREEN(U(IN(DEALS)/*UNV:DEALSMNA*/), IN(TR.MnANationHQ,""AL"",""BA"",""CZ""), IN(TR.MnAStatus,""U"",""C""), BETWEEN(TR.MnADateUnconditional,20190101,20220430)/*dt:Date*/, TR.MnAAcqIsBlankCheckCo==true, CURN=USD)","TR.MnASDCDealNumber,TR.MnAAnnDate,TR.MnARankDate,TR.MnARankValueIncNetDebt(Curn=USD,Scale=6),TR.MnAT"&"arget,TR.MnATargetPermId,TR.MnATargetMacroInd,TR.MnATargetMidInd,TR.MnATargetNation,TR.MnAAcquirorPe"&"rmId,TR.MnAAcquirorMacroInd,TR.MnAAcquirorMidInd,TR.MnAAcquirorNation,TR.MnATargetFinAdvisor(Concat="&"'|'),TR.MnAAcquirorFinAdvisor(Concat='|'),TR.MNADealId,TR.MnAStatus,TR.MnADateUnconditional ","Curn=USD CH=Fd")

that "&" is what I believe was causing the issue on the formula paste.

if so far so good, try in code:

syntax = 'SCREEN(U(IN(DEALS)), \
IN(TR.MnANationHQ,"AL","BY","CZ"), IN(TR.MnAStatus,"U","C"), BETWEEN(TR.MnADateUnconditional,20190101,20220430), \
TR.MnAAcqIsBlankCheckCo==false, CURN=USD)'
fields = ['TR.MnASDCDealNumber','TR.MnAAnnDate','TR.MnARankDate']
df, e = ek.get_data(syntax, fields)
df

If this works for you and returns the result of 580 rows ?

Next, replace false with true:

syntax = 'SCREEN(U(IN(DEALS)), \
IN(TR.MnANationHQ,"AL","BY","CZ"), IN(TR.MnAStatus,"U","C"), BETWEEN(TR.MnADateUnconditional,20190101,20220430), \
TR.MnAAcqIsBlankCheckCo==true, CURN=USD)'
fields = ['TR.MnASDCDealNumber','TR.MnAAnnDate','TR.MnARankDate']
df, e = ek.get_data(syntax, fields)
df

and add your complete list of required HQs back.

You should have your required expression in Python

Let us know how this works on your side

EXPLORE OUR SITES