TREInfo is the primary source of entity, identifier, and descriptor reference data. You may obtain this data directly from Refinitiv or through QA Direct Database.
As you know, I see the world with 3 date dimensions: effective dating, knowledge dating, and system dating.
There is no canonical "system effective date" for this table. This means you cannot safely determine the precise data that's been updated.
This table represents the "latest view of the world" so it does not offer a "knowledge dating", a given EstPermID will be listed only once, thus there's a unique constraint on the EstPermID.
This is critical to know because it means, if you really need point-in-time copy of this data, you either need to subscribe to a different set of tables, or you need to roll your own CDC process using MSSQL CDC or "_changes" table.
24k entities came into existence in 1900-01-01 the "null proxy start date" and the rest are spread out from 1994 onwards meaning this data set more than likely represents the actual date when this entity made it's way into the IBES data collection universe.
All of the times are thankfully in UTC which is the proper way to design data structures by storing datetime as UTC and translating upon consumption to user's timezone.
There is an "expiration date" (ExpireDate), which is meant to represent when IBES ceased to cover this entity in their data set.
There are 56448 entities with a non-null expiration date, this means over 50% of the entities, are entities that no longer exist, 29701 of those don't have any summary-level estimates data associated with them.
There are many entities whose ExpirationDate may be set to 2003-05-09 11:02:00.0000000 but estimates stopped showing up nearly two years before that expiration date.
There's plentiful examples like this which means if you are constructing a model that takes into consideration entities that are no longer covered, you shouldn't be using the expiration date from TREInfo.
If you're looking for the first date when a given entity is covered, the ActivationDate is not a good source of truth for that, it often represents the last time this entity was touched by an internal IBES process. The IBES documentation states: "Date/time the estimate is active in the database and becomes retrievable; system generated and cannot change (always in UTC". Again, it's best to calculate your own "coverage start date" by using the estimates and actuals tables.
Theere are 107095 distinct "estimate perm id" EstPermID's exist as of 1/21/2020. Thomson-Reuters provides an Open Perm ID however sadly EstPermID is not open, only the OrgPermID is, for example: https://permid.org/1-4296015197
An "IBES Ticker" which is pervasive in this space is available. Everyone of these instances IBESTicker exists, this is useful, because besides using GSecMapX and SecMapX, it's another way to triangulate into other data sets, like Worldscope, and Datastream using this identifier. There are no cases where IBESTicker is re-used in the coverage universe. The symbology behind the IBESTicker is always equal to or less than 5 characters long.
An "organization identifier" is provided, called OrgPermID. There's 79267 unique OrgPermID's which is pretty solid, considering there is useful reference data, that can be sourced from their free public (limits do apply) TR Open Perm ID.
An "instrument-level identifier" is provided, called InstrPermID. There's 82255 unique InstrPermID's, and they're accessible via permid.org. The InstrPermID example is "Enel Ordinary Shares" which means it's the unique entity representing the "issue".
An "quote-level identifier" is provided as well, called QuotePermID. There's 85407 unique QuotePermID's, once again they're all accessibel via permid.org. They represent "Enel Ordinary Shares" trading on a given exchange like XSGO including the ticker symbol.
The Currency and Country are provided as well, it represents the country assigned to the unique entity being forecasted or reported, not to be confused with the primary line's country or currency (so be careful using when mapping).
A Brief Overview of IBES V2 in QAI
The last 3 months, I've been asked by 5 clients about using IBES v2, so hence decided to write this...
Open-source data stack technologies in finance
A relatively common question that arises during small talk before the meeting starts is what...
Making qai.dbo.TREInfo Usable in QA Direct IBES v2
You may have seen our post about TREInfo. This post is about making it a little more usable.