1. Import accounting data downloaded from Thomson Financial database using the excel addin.
2. Transpose the datasets to Panel Data format.
and then you can export them to other formats such as excel, stata, sas, etc.
/*--------------------------------------------------------------------------------------------------------------------- Current Version: 0.1 Please do not remove this information; Originally Created by Helix Lee, Centre for Advanced Studies in Finance, Leeds University Business School, Leeds, UK. Email: buszl@leeds.ac.uk You can modify the codes as you wish. How to use it.
1. Make sure you copy them to the c:temp, or you can use other folder and please make sure you change all the c:test to you folder in this code
2. Download the data from Thomson One Financial database using the Excel Add-in, say you save it as accounting.xls;
3. Copy the header from the worksheet in the test.xls inside the zip file, replace the header information in the accounting.xls;
the current header information is: Entity Name: code Currency: Y1999 Y2000 Y2001 Y2002 Y2003 Y2004 Y2005 Y2006 Please note that the current code suports year from 1999 to 2006 only, if you want to extend it, please add Y1998 etc. in the front, and Y2007 in the end. And add these information in the %Transpose macro
4. Run the codes. a. These two commands import and transpose them into the Panel Data format %create_acc_data(var=Sales,data=test.Sales); %create_acc_data(var=TotalAssets,data=test.TotalAssets); b. Merge the two dataset; %merge_datax(data1=test.TotalAssets,data2=test.Sales,sort_ids=code cyear,output=test.AccData); c. Remove the raw datasets %drop_table(tablename=test.Sales); %drop_table(tablename=test.TotalAssets); -----------------------------------------------------------------------------------------------------------------------*/
Codes:
libname test 'c:temp'; *Change the library name and this folder if you want; run; /*---------------------------------------------------------------------------------------------------------------------- Change the file name, if you excel file is accounting.xls, change the DATAFILE= "c:temptest.xls" TO DATAFILE= "c:tempaccounting.xls" -----------------------------------------------------------------------------------------------------------------------*/ %macro import_acc_data(var=,table=); PROC IMPORT OUT= &table. DATAFILE= "c:temptest.xls" DBMS=EXCEL REPLACE; SHEET=&var.; GETNAMES=YES; MIXED=YES; SCANTEXT=YES; USEDATE=YES; SCANTIME=YES; RUN; %mend ; %macro sort_datax(data=,sort_ids=); Proc sort data= &data.; by &sort_ids.; RUN; %mend; %macro merge_datax(data1=,data2=,sort_ids=,output=); %sort_datax(data=&data1.,sort_ids=&sort_ids.); %sort_datax(data=&data2.,sort_ids=&sort_ids.); DATA &output.; merge &data1. &data2.; by &sort_ids.; run; %mend; %macro drop_table(tablename=); proc sql; drop table &tablename.; %mend; %macro transpose_data(indata=,outdata=,var=,period=); %if &period.=year %then %do; %sort_datax(data=&indata.,sort_ids=code); data &indata.; set &indata.; keep code Y1999 Y2000 Y2001 Y2002 Y2003 Y2004 Y2005 Y2006; run; proc transpose data=&indata. out=work.TEMP1; by code; run; data work.TEMP1; set work.TEMP1; *------------------------------------ You can extend the periods here; -------------------------------------* if _NAME_='Y1999' then cyear=year('01dec99'd); if _NAME_='Y2000' then cyear=year('01dec00'd); if _NAME_='Y2001' then cyear=year('01dec01'd); if _NAME_='Y2002' then cyear=year('01dec02'd); if _NAME_='Y2003' then cyear=year('01dec03'd); if _NAME_='Y2004' then cyear=year('01dec04'd); if _NAME_='Y2005' then cyear=year('01dec05'd); if _NAME_='Y2006' then cyear=year('01dec06'd); rename COL1=&var.; RUN; data &outdata.; length code $10; set work.TEMP1 (where=(code is not missing)); keep code cyear &var.; run; %drop_table(tablename=work.Temp1); %end; %mend; %macro create_acc_data(var=,data=,sp500=); %import_acc_data(var=&var.,table=work.Temp_var1); %transpose_data(indata=work.Temp_var1,outdata=&data.,var=&var.,period=year); %drop_table(tablename=work.Temp_var1); %mend; /* ---------------------------------------------------------------------------------------------------------- Example to run the codes ----------------------------------------------------------------------------------------------------------*/ %create_acc_data(var=Sales,data=test.Sales); %create_acc_data(var=TotalAssets,data=test.TotalAssets); %merge_datax(data1=test.TotalAssets,data2=test.Sales,sort_ids=code cyear,output=test.AccData); %drop_table(tablename=test.Sales); %drop_table(tablename=test.TotalAssets);
Literature Review: review the relevant literature in the area that you are interested in. Determines the model that you are going to use. And then identify the variables that you are going to use. Relevant variables include:
Board Size
The number of directors serving on the company's board at the beginning of the financial year
Committee Size
The number of directors serving on the company's committee (audit, compensation, nomination, governance) at the beginning of the financial year
Board Independence
The percentage of independent directors on the company's board at the beginning of the financial year
Committee Independence
The percentage of independent directors on the company's committee (audit, compensation, nomination, governance) at the beginning of the financial year
Board Meetings
The number of board meetings held at the end of the financial year
Committee Meetings
The number of committee meetings held at the end of the financial year
CEO Duality
CEO of the company who also serves as the chairman of the board
Inside Director Ownership
Total percentage of shareholdings of inside and affiliate directors serving on the board
Outside Director Ownership
Total percentage of shareholdings of independent directors serving on the board
etc.
Step 2
Collecting data.
At this stage, use the existing data set or collect it yourself.
A) If you want to use the existing data set, you can use the data from the Corporate Library (available in the WRDS, U.S. companies).
The first choice reduce your time in collecting and cleaning your data, but it might be not affordable for individual researchers.
The second choice would cost you some time but it is a cheaper choice. It may cost you 3-month-time to collect about 5-6 years data for 300 companies (See here as well: Link).
Step 3
Analyzing the model you specified in Step 1, and revise them if necessary. At this stage, you may need to control for the following sensitivities:
A) Endogeneity
B) Firm and time effects if you use panel data
C) Other sensitivities may incur in your model.
Step 4
Revise your hypotheses, discover the reasons for the unexpected outcomes and write up your reports.
Revising the hypotheses always happen since there would be unexpected outcomes after you have controlled for different factors. The results may not support your hypothesis and you have to discover the reason behind this from a lot of sources other than academic journals.
Governance Index Data by Firm, 1990-2006 For details on the construction of the Governance Index, see Gompers, Paul A., Joy L. Ishii, and Andrew Metrick, "Corporate Governance and Equity Prices", The Quarterly Journal of Economics 118(1), February 2003, 107-155.
Helping People SurviveProfileFriendsFiles
Tools 
Print
