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)"
Best Answer
-
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("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)
dfIf 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)
dfand 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
0
Answers
-
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)
dfand 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.
2 -
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.
0 -
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("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)
dfthis 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?
0 -
Thank you @zoya faberov now it works.
0
Categories
- All Categories
- 6 AHS
- 37 Alpha
- 161 App Studio
- 4 Block Chain
- 4 Bot Platform
- 16 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
- 367 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
- 59 Workspace SDK
- 9 Element Framework
- 5 Grid
- 13 World-Check Data File
- Yield Book Analytics
- 46 中文论坛