Calculating Auditor Tenure

[Ercan Simsek]

Dear all,

I have the following data in excel transported from AuditAnalytics (WRDS):
- Last reported auditor fkey
- Last reported auditor name
- Last reported auditor since event date
- Last reported auditor since event type

The first two identify a specific auditor. The last two identify how long the tenure was/is.

Last reported auditor fkey = 1
Last reported auditor name = PricewaterhouseCoopers LLP
Last reported auditor since event date = 1950-12-31
Last reported auditor since event type = S

The question is; how to calculate auditor tenure for each company for each firm-year. Sample period is 2008-2013

Thanks

[Clyde Schechter]

Well, I have no idea what AuditAnalytics or WRDS are, which may be why the answer isn't obvious to me. But to calculate a tenure you need a start date and a finish date (which might be a current date if the auditor's tenure isn't over.) But you only mention one date (and I can't discern whether it's a start or a finish). What is the other date? And which is which?

[Loo Ortega]

Hi,

Unfortunately, I could not find the answer here because I would also like STATA to calculate the "tenure".
DateAnnounced is the end date (merger announced), this variable is nummeric
DateBecameCEO is the start date, this variable results to be a string but when I

destring DateBecameCEO, replace
DateBecameCEO: contains nonnumeric characters; no replace

How do I get the right format, to substract both dates from each other? And can I just use the command :

Tenure = DateAnnounced - DateBecameCEO

?


Data:

DateBecameCEO    DateAnnounced
01/08/1993    27mar2001
01/01/1996    23feb2005
29/03/1999    20may2004
01/01/1984    26jan1999
26/02/1991    18feb2003
01/08/1992    03aug1998
03/03/2000    09jul2001
11/02/2003    27jun2007

Thanks in advance!

[Clyde Schechter]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 datebecameceo str9 dateannounced
"01/08/1993" "27mar2001"
"01/01/1996" "23feb2005"
"29/03/1999" "20may2004"
"01/01/1984" "26jan1999"
"26/02/1991" "18feb2003"
"01/08/1992" "03aug1998"
"03/03/2000" "09jul2001"
"11/02/2003" "27jun2007"
end

foreach v of varlist datebecameceo dateannounced {
    gen _`v' = daily(`v', "DMY")
    assert missing(`v') == missing(_`v')
    drop `v'
    rename _`v' `v'
    format `v' %td
}
gen tenure = dateannounced - datebecameceo

Note: tenure here is calculated in days. If you want it in years, divide by 365, or 365.25 (whichever is fashionable in your field.)

-destring-'s only function is to take string variables that contain things that look to the human eye like pure numbers and turn them into actual numeric variables. These strings don't look like numbers: they look like dates. -destring-, on seeing the / characters knows that this is not it's job, and complains and refuses.

The -daily- function is what you need here, as shown above.

Also shown above is the -dataex- command in operation. It is the best way to show example data here on the Forum. If you are running version 15.1 of Stata, or a completely updated version 14.2, you already have it in your official installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- and then read the simple instructions for using it. Not only is using -dataex- simpler for you, it has the huge advantage of creating a completely faithful replica, including details about storage types and formatting that don't show up in other types of data posting. This means that those who try to help you can write and test code for the data you actually have, instead of guessing what you have and hoping the code will work (which it often does not.) So it's a win-win.

Whenever you want help with code, show example data. Whenever you show example data, use -dataex-.