The download link is as follow:
http://www.survey-partner.co.uk/spsite/index.php/Details/10.html
or
http://www.ssrrn.com/index.php/Details/Transpose-Thomson-Financial-Data.html
This code can:
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);
