18 min read

A Brief Overview of IBES V2 in QAI

Featured Image

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!

IbSumRec

IBQSumL3

IBQSumL2

IBQSumL1

IBQSrmL3

IBQSrmL2

IBQSrmL1

IBQSpl

IBQSIGA

IBQSIG

IBQRSum

IBQRsActL3

IBQRsActL2

IBQRsActL1

IBQRDet

IBQPSum

IBQPDet

IBQId2

IBQGuExch

IBQGuData

IBQFXR

IBQFDetL3

IBQFDetL2

IBQFDetL1

IBQFCo

IBQExDt

IBQExch

IBQEst2ML1

IBQDetL3

IBQDetL2

IBQDetL1

IBQCXDt

IBQCurChg

IBQCur

IBQCty

IBQAdj

IBQActL3

IBQActL2

IBQActL1

IBGSSurp

IBGSSrmL3

IBGSSrmL2

IBGSSrmL1

IBGSSIG

IBGSRsEPSL3

IBGSRsEPSL2

IBGSRsEPSL1

IBGSRec

IBGSPSum

IBGSInfo3

IBGSHist3

IBGSFCo

IBGSEstLC

IBGSEstL3

IBGSEstL2

IBGSEstL1

IBGSEst2ML3

IBGSEst2ML2

IBGSEst2ML1

IBGSEPSLC

IBGSEPSL3

IBGSEPSL2

IBGSEPSL1

IBGSCur

IBGSCmbDate

IBGSAdj

IBGSActLC

IBGSActL3

IBGSActL2

IBGSActL1

IBGS2ndRsEPSL3

IBGS2ndRsEPSL2

IBGS2ndRsEPSL1

IBGS2ndEstLC

IBGS2ndEstL3

IBGS2ndEstL2

IBGS2ndEstL1

IBGS2ndEPSLC

IBGS2ndEPSL3

IBGS2ndEPSL2

IBGS2ndEPSL1

IBGS2ndActLC

IBGS2ndActL3

IBGS2ndActL2

IBGS2ndActL1

IBGQSumL3

IBGQSumL2

IBGQSumL1

IBGQSrmL3

IBGQSrmL2

IBGQSrmL1

IBGQSpl

IBGQSIGA

IBGQSIG

IBGQRSum

IBGQRsActL3

IBGQRsActL2

IBGQRsActL1

IBGQRDet

IBGQPSum

IBGQPrimMsr

IBGQPDet

IBGQId

IBGQGuExch

IBGQGuData

IBGQFXR

IBGQFDetL3

IBGQFDetL2

IBGQFDetL1

IBGQFDetF

IBGQFCo

IBGQExDt

IBGQEst2ML3

IBGQEst2ML2

IBGQEst2ML1

IBGQDetL3

IBGQDetL2

IBGQDetL1

IBGQDetF

IBGQCXDt

IBGQCurChg

IBGQAlrtL3

IBGQAlrtL2

IBGQAlrtL1

IBGQAdj

IBGQActL3

IBGQActL2

IBGQActL1

IBGDStpL3

IBGDStpL2

IBGDStpL1

IBGDRsActL3

IBGDRsActL2

IBGDRsActL1

IBGDRecStp

IBGDRecCode

IBGDRec

IBGDPStp

IBGDPDet

IBGDFXR

IBGDExcL3

IBGDExcL2

IBGDExcL1

IBGDEur

IBGDEstL3

IBGDEstL2

IBGDEstL1

IBGDCur

IBGDBrk

IBGDAnl

IBGDActL3

IBGDActL2

IBGDActL1

IBGD2ndStpL3

IBGD2ndStpL2

IBGD2ndStpL1

IBGD2ndRsActL3

IBGD2ndRsActL2

IBGD2ndRsActL1

IBGD2ndExcL3

IBGD2ndExcL2

IBGD2ndExcL1

IBGD2ndEstL3

IBGD2ndEstL2

IBGD2ndEstL1

IBGD2ndActL3

IBGD2ndActL2

IBGD2ndActL1

IBESUSDlyRec

IBESUSDlyPTg

IBESUSDlyEst

IBESSurp

IBESSrmL3

IBESSrmL2

IBESSrmL1

IBESSIG

IBESRsEPSL3

IBESRsEPSL2

IBESRsEPSL1

IBESPSum

IBESPerCode

IBESMsrCode

IBESIntDlyRec

IBESIntDlyPTg

IBESIntDlyEst

IBESInfo3

IBESHist3

IBESFCo

IBESEstLC

IBESEstL3

IBESEstL2

IBESEstL1

IBESEst2ML1

IBESEPSLC

IBESEPSL3

IBESEPSL2

IBESEPSL1

IBESDesc

IBESCurrCode

IBESCur

IBESCode

IBESCmbDate

IBESAdj

IBESActLC

IBESActL3

IBESActL2

IBESActL1

IBES2ndRsEPSL3

IBES2ndRsEPSL2

IBES2ndRsEPSL1

IBES2ndEstLC

IBES2ndEstL3

IBES2ndEstL2

IBES2ndEstL1

IBES2ndEPSLC

IBES2ndEPSL3

IBES2ndEPSL2

IBES2ndEPSL1

IBES2ndActLC

IBES2ndActL3

IBES2ndActL2

IBES2ndActL1

IBDStpL3

IBDStpL2

IBDStpL1

IBDRsActL3

IBDRsActL2

IBDRsActL1

IBDRecStp

IBDRecCode

IBDRec

IBDPStp

IBDPDet

IBDfxr

IBDExcL3

IBDExcL2

IBDExcL1

IBDEstL3

IBDEstL2

IBDEstL1

IBDCur

IBDBrk

IBDAnl

IBDActL3

IBDActL2

IBDActL1

IBBrkMap

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)

https://www.library.kent.edu/files/IBES_GuideUS.pdf

https://datateamoftheeur.files.wordpress.com/2016/09/forecasts-ibes-analysts.pdf

https://www.fm.wi.tum.de/fileadmin/w00bno/www/IBES_on_Datastream_ver_5.0.pdf

https://www.tilburguniversity.edu/sites/tiu/files/download/IBESonWRDS_2.pdf