Not too long ago I had a question from someone who was having trouble working with data from Compustat Execucomp. He wanted the yearly tenure for a specific group of people with the function of Chief Financial Officer. The research spanned a period of **2009-2014** (post-crisis). The data that was downloaded looked something like this:

**Step 1: Data cleaning**

One of the first steps to take in this case is to make sure to have the right kind of data to work with. In this case the **columns H** and **I** needed to be checked and cleaned. In **column I** you see the date when a person left as CFO working for the company. In this situation we see items like **n/a** where the data is unavailable and this means that the person still continues to work as CFO for the company. We first need to replace such values with the value 2014 for the last year of our research as we are looking for the tenure within the time frame 2009-2014. Any other years after 2014 can in this case also be replaced with 2014. You can use the **search and replace function** in Excel to do this step by step. Afterwards you can use the Filter option in Excel to check for weird data or outliers. In principle you have to check both columns with start year and left year to be sure there are no outliers (weird values).

In **column H** you see the year when a person joined the company. I am assuming that this was also the startyear for each person when he came to work as CFO at the company (I have not personally checked this). You see in the screenshot that not every year is seen as a numerical value: Excel shows **little green triangle dots** in the cells where it thinks the **data is text**. To ensure that a year is seen as a numerical value you can add a new column and use a trick to create numerical values in this column: **devide cell by 1**. See screenshot **column J** for the original data and column K for the new years. In the top left corner you see the “formula” you can copy downwards for all years.

**Step 2: Calculate tenure for each year**

In the example for this blog I only calculated the tenure for the final years of the research time frame (2013 and 2014). You can figure out the formulas for the other years. First I started calculating the CFO tenure for 2014. In this example I assume that if the startyear matches the lastyear someone has worked in this capacity for less then a full year making the tenure less then 1 and thus zero. In this case I get the right number of years of the tenure by substracting the startyear from the lastyear (= research year 2014). See example:

Now for the tenure of the previous year (2013) the If statement comes in handy to figure out the tenure for this year. The full formula is:

**=IF(K3<=(L3-1);(P3-1);FALSE)**

K3 = *start year tenure within the research window (or before)*

L3 = *last year for the research window (2014)*

P3 = *tenure for the final year of the research window (= 2014)*

The formula in essence does the following: if the previous year (in this case 2013) matches the start year (or is smaller), then the tenure is that of 2014 minus 1. If not then put the word False there. This last condition prevents outliers from causing problems. Screenshot:

The same formula can also be used for the previous years. All you need to do is change the formula for the right numbers. 2012 example: =IF(K3<=(L3-2);(P3-2);FALSE)

**Step 3: Figure out the relevant years**

* This step is essentially not necessary as the filter option of excel is already available to make an annual selection by year but you then have to add the tenure year manually for each year after copying the relevant tenure data by year (to a new sheet).*

The formulas in step two will provide a tenure of 0 (or more) as long as the end year for the tenure (within the time frame 2009-2014) is equal to (or higher) then the start year for the tenure (within the time frame or earlier). To know the tenure by year we create columns to show which tenure applies to what year. That allows us to use a filter in Excel to more easily get the relevant data where there is a tenure of more then zero. I created the columns N and O to get the tenure years for 2014 and 2013. The formula I used for 2014 is: **=IF((P4>0);RIGHT($N$1;4);FALSE)**

where: P4 = calculated number of tenure years for 2014. I have put the year in the name in the first cell at the top of the column (first) as the last 4 digits making it possible to use $N$1.

Example:

For 2013 all you need to change is the cell P4 into Q4 and the header $N$1 into $O$1. You also need to put the year in the name of the variable at the top of the column). Subsequent years work the same way.

**Step 4: Filter the data for the relevant years**

As the final step you can now use the standard filter option to copy the relevant data by year to a new sheet.

I would then also remove irrelevant data for other years which do not apply to the specific year I have filtered for. The end result would look something like this:

Filed under: Compustat, Data management, Excel |