Compustat Execucomp output and Excel

One of the few databases that covers compensation data (on CEO’s and others) for companies is the Compustat Execucomp database. This database has data fro S&P 1500 companies from 1992 to now. Most of these companies are American companies. When you download CEO data using a list of companies (Tickers or CIK codes) you may have to filter the result in Excel to limit the data from the database to just CEO’s.

Even when you have limited the download in Excel to just CEO’s it still is important to check the data using the Execucomp variables for Date Became CEO & Date left as CEO against the Fiscal Year date for the reported remuneration. Using Excel you can use simple formulas to check for outliers or weird values.

First you can do a check on whether the CEO started before or in the fiscal year for which we have data. In this case we need to check the year of the starting date against the value of the column with the fiscal year: =IF(YEAR(I2)<=H2,TRUE,FALSE)
See example:

Next you do a check on whether the CEO has left before the fiscal year for which we have data. This time the formula is a bit more complicated as we only need to check cells that have an ending date value. When the End date is NOT zero (= if there is a date) we need to check if the year when the CEO left the company (as CEO) is before the Fiscal Year. This can be done using a nested conditional formula as follows: =IF(LEN(J2)=0,TRUE,IF(YEAR(J2)>=H2,TRUE,FALSE))

LEN() = Length of the string of characters in a Cell
IF() = Condition
YEAR() = Gets the Year value for the date in a Cell

The first condition checks whether the field has an End date or is empty, which means that the CEO is still the current CEO. If there is a date, the second condition checks if the year of the End Date lies before the (current) fiscal year.

See example:

NB: If you quickly need to scan the check columns for the FALSE rows (indicating outliers and such) you may use the Excel Conditional formatting option to colour the FALSE values. See for the option at:

Email

%d bloggers like this: