The last 3 months, I've been asked by 5 clients about using IBES v2, so hence decided to write this article.
Refinitiv, formerly known as, Thomson-Reuters, producer/owner of QA Direct, aka QAI, provides a SQL Server database, `qai` to the institutional investment community, consisting of a wide variety of raw data, mostly for Equities, but includes some Fixed Income, and Multi-Asset Class data.
Many of you may know of a product I/B/E/S, which is a database of earnings estimates sourced from leading Brokers, and it's got a very deep history, its Detail data is used in quant models across the industry.
QAI provides both "IBES V1" and "IBES v2".
IBES v1 consists of 247 tables and is not easy to use, there's a lot of stitching, and interpretation, tons of joins to say the least, but the data itself is invaluable when you get it right. As living proof, here’s that list of 247 tables!
A few years back, TR did a huge favor for their clients, and they undertook a major project to shape the raw data into a much more usable form, with a lot less tables, better identifiers, and more convenient data structures in MSSQL, they now call this IBES V2. Here’s a list of all tables available. A lot less and as you dig deeper, much easier to understand.
There’s 31 tables, 80% less tables, at around 700GB of data (yes, including indexes).
One of they key advantages of the data, is that is incrementally updated, every 15 minutes or so, which is quite true, by taking a peek at their update_log table, we can see it’s updated sometimes even more often than that (just wish we had real point-in-time capture of the data).
As we dig deeper, the data is shaped significantly better, and while this article isn’t about enumerating all of the differences, I’ll take as an example TRESumPER, TRESumHzn, and TRESumAper. Those are the only 3 tables you need to work with for Summary level estimates, instead of over a dozen tables in v1 like IBESEstL1, IBESEstL2, IBESEstL3, IBGSEstL1, IBGSEstL2, IBGSEstL3, and this doesn’t take into account the QFS (daily updated) tables as well, so already thumbs up to that!
These three tables are all very similar, and technically can be unioned together, into a single table, but since TRQA prefers dense data structure rather than sparse (lots of NULLs) then we have to join to each table depending on what we want.
- TRESumPer: The TRESumPer table contains summary data for all periodic periods. 3,4,5 (Quarter, Year, HalfYear).
- TRESumHzn: The TRESumHzn table contains summary data for all horizon periods. 2 (Month).
- TRESumAper: The TRESumAper table contains summary data for aperiodic periods. 1 (LongTerm).
Similar to v1, each row represents a given instrument, a measure like EPS, whether it’s parent or consolidated basis reporting, the periodicity like Quarterly, the fiscal period end date for the estimate, the fiscal year end month for that given time, and the effective date for this estimate.
There’s so much more to dive into with this data, but this is a good start to highlight some of the high level differences, digging into the data and constructing a time series of fiscal period aligned estimates using their estimate date times as the anchor for the time series is even better, I’ll aim for doing a review of that next.
The key takeaway here, IBES v2, way less tables, less hokey business rules, more frequently updated, better time granularity, better identifiers, and better database index layout.
If you're looking for find out some more about IBES then check out a few of these links (provided they don't break after posting this - haha)
Anatomy of qai.dbo.TREInfo in QA Direct IBES v2
TREInfo is the primary source of entity, identifier, and descriptor reference data. You may obtain...
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.
FinFlo Financial Holdings Data Sets
What's FinFlo? It's the first software-enabled service, where we onboard any kind of data, in any...