3 min read

Anatomy of qai.dbo.TREInfo in QA Direct IBES v2

Featured Image

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.

Dating

Dimensions

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.

Current

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.

Sampling

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.

Year|Rows
1900|62
1957|1
1963|1
1965|1
1967|1
1968|1
1970|5
1972|2
1973|4
1974|1
1975|4
1976|11
1978|1
1979|2
1980|1
1981|2
1982|1
1983|2
1984|2
1985|1
1986|56
1987|4
1988|9
1989|3
1990|24064
1992|5
1993|1632
1994|6087
1995|2831
1996|5567
1997|3191
1998|2626
1999|2217
2000|2727
2001|1993
2002|1829
2003|4331
2004|11971
2005|6036
2006|5438
2007|4406
2008|3426
2009|1499
2010|2613
2011|2238
2012|1096
2013|1054
2014|1463
2015|1503
2016|1177
2017|1406
2018|1296
2019|1138
2020|57

Timezone

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.

EstPermID|EffectiveDate|EffectiveOffset
30064771093|2004-01-30 15:46:22.0730000|-300

Expiration

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.

Activation

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.

Entities

EstPermID

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

IBESTicker

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.

OrgPermID

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.

InstrPermID

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".

QuotePermID

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.

Currency

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).