clear
set more off

* Get meta-data
***************
clear
gen filename = ""
tempfile tmp1
save `tmp1', replace

foreach i in 2009 2010 2011 2012 2013{
import delimited "meta_`i'PDF.txt", delimiter(";") clear case(preserve) encoding(utf8)
ren v1 filename
ren v2 AMF_datepublication

drop if filename == ""
append using `tmp1,'
save `tmp1', replace
}

replace filename = subinstr(filename," ","",.)
replace filename = subinstr(filename,"doc"," ",1)
replace filename = "209D2162.pdf" if filename == "209d2162.pdf"
replace filename = stritrim(strtrim(filename))
replace filename = "209D2899.pdf" if filename == "209D2899 .pdf"
replace filename = "209D5094.pdf" if filename == "209D5094 .pdf"
replace filename = "211D1884.pdf" if filename == "211D1884 .pdf"

duplicates drop filename, force

save `tmp1', replace

* Merge core data and meta-data
*******************************
use "clean/temp_2009_2013", clear

merge 1:1 filename using `tmp1'
drop if _merge == 2
drop _merge
replace AMF_datepublication = "Publié le 25 mai 2009" if filename == "209D2754.pdf"

* Turn dates in standard date format
************************************

replace AMF_datepublication = subinstr(AMF_datepublication,"Publié le ","",1)

replace receptiondate = "7 mars 2011" if receptiondate == "7mars 2011"
replace receptiondate = "28 juillet 2011" if receptiondate == "28 juillet" // Visual check

replace transactiondate_1 = "7 juin 2011" if transactiondate_1 == "7juin 2011"
replace transactiondate_1 = "2 juillet 2010" if transactiondate_1 == "2 juilllet 2010"

foreach var of varlist *date*{
foreach expression in "(1)" "(2)" "(3)"{
replace `var' = subinstr(`var',"`expression'","",.)
}
gen `var'_old = `var'
replace `var' = subinstr(`var', "1er", "01",.)
replace `var' = subinstr(`var', "1ER", "01",.)
replace `var' = subinstr(`var', " 006", "2006",.)
replace `var' = subinstr(`var', "20006", "2006",.)
replace `var' = lower(`var')
replace `var' = subinstr(`var',"é","e",.)
replace `var' = subinstr(`var',"û","u",.)
	replace `var' = subinstr(`var', "janvier", "01",.)
	replace `var' = subinstr(`var', "fevrier", "02",.)
	replace `var' = subinstr(`var', "mars", "03",.)
	replace `var' = subinstr(`var', "avril", "04",.)
	replace `var' = subinstr(`var', "mai", "05",.)
	replace `var' = subinstr(`var', "juin", "06",.)
	replace `var' = subinstr(`var', "juillet", "07",.)
	replace `var' = subinstr(`var', "aout", "08",.)
	replace `var' = subinstr(`var', "septembre", "09",.)
	replace `var' = subinstr(`var', "octobre", "10",.)
	replace `var' = subinstr(`var', "novembre", "11",.)
	replace `var' = subinstr(`var', "decembre", "12",.)
	gen `var'_Temp  = date(`var', "DMY")
	drop `var'
	rename `var'_Temp  `var'
	format `var' %td
}

drop *_old

foreach i in 1 2 3 4 5 6 {
replace status_`i' =    "Membre du Directoire" if status_`i' == "Membres du Directoire" 
replace status_`i' =    "Directeur Général" if status_`i' == "Directeur général" 
replace status_`i' =    "(Vice-)Président du Conseil de surveillance" if status_`i' == "(Vice-)Président du Conseil de surve" 
replace status_`i' =    "Membre du Conseil de Surveillance" if status_`i' == "Membre du Conseil de surveillance" 
replace status_`i' =    "Membre du Conseil de Gérance" if status_`i' == "Membre du Conseil de gérance" 
replace status_`i' =    "Membre du Conseil de Gérance" if status_`i' == "Membre du conseil de gérance" 
replace status_`i' =    "Membre du Comité éxécutif" if status_`i' == "Membre du comité éxécutif" 
}

* Ad hoc ISIN change, ISIN that are not 12 digit
************************************************
ren isin ISIN
replace ISIN = lower(ISIN)

********************************************************************************
* Lines 100 to 124
* 24 anonymised corrections
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
********************************************************************************

ren ISIN isin

* ISIN integrity check
**********************
do clean_data_isin_integrity.do

* Correct negative values for price and amount due to untreated character issues
********************************************************************************
foreach var of varlist  price_* amount_*{
	replace `var' = abs(`var')
}

* Cases with more than one transaction in the same document
***********************************************************

* In 2012 and 2013, 4 documents report on 2 or more transactions. No date issues.
replace price_1 = (price_1 * amount_1 + price_2 * amount_2 + price_3 * amount_3 ) / (amount_1 + amount_2 + amount_3) if amount_3 != .
replace amount_1 = amount_1 + amount_2 + amount_3 if amount_3 != .
replace price_2 = . if amount_3 != .
replace amount_2 = . if amount_3 != .
replace price_3 = . if amount_3 != .
replace amount_3 = . if amount_3 != .
drop amount_3 price_3

replace price_1 = (price_1 * amount_1 + price_2 * amount_2 ) / (amount_1 + amount_2) if amount_2 != .
replace amount_1 = amount_1 + amount_2 if amount_2 != .
drop amount_2 price_2

* Correction of dates (visual check)
************************************

* Reasonably consistent errors in dates
replace receptiondate = date("3/1/2011","DMY") if inlist(filename,"211D0041.pdf","211D0042.pdf","211D0043.pdf","211D0044.pdf","211D0045.pdf")
replace receptiondate = date("24/2/2009","DMY") if filename == "209D1171.pdf"
replace receptiondate = date("4/1/2010","DMY") if filename == "210D0055.pdf"
replace transactiondate = date("9/10/2011","DMY") if filename == "211D4933.pdf"
replace datereleased = date("3/3/2009","DMY") if inlist(filename,"209D1341.pdf","209D1342.pdf","209D1343.pdf","209D1344.pdf","209D1345.pdf")
replace datereleased = date("15/5/2009","DMY") if filename == "209D2648.pdf"
replace datereleased = date("20/1/2010","DMY") if filename == "210D0341.pdf"
replace datereleased = date("2/1/2013","DMY") if inlist(filename,"213D0004.pdf","213D0015.pdf")

* Completely inconsistent dates (while other dates are consistent)
replace transactiondate = . if filename == "212D0516.pdf"
replace transactiondate = . if filename == "210D3101.pdf"

*  Miscellaneous corrections
****************************

********************************************************************************
* Lines 174 to 240
* 66 anonymised corrections
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
********************************************************************************


forvalues i = 1(1)3{
local j = `i' + 1
replace people_`i' = people_`j' if filename == "209D1452.pdf" 
replace people_`j' = "" if filename == "209D1452.pdf"
replace status_`i' = status_`j' if filename == "209D1452.pdf"
replace status_`j' = "" if filename == "209D1452.pdf"
}

foreach var of varlist status_*{
ren `var' a
replace a = "Membre du Conseil de gérance" if regexm(a,"érance")
replace a = "(Vice-)Président du directoire"  if a == "Président du Directoire" | a == "Président du directoire"
replace a = subinstr(a,"Directoire","directoire",1)
replace a = "Membre du Comité exécutif" if a == "Membre du comité exécutif"
replace a = "Gérant" if a == "Membre du Conseil de gérance"
replace a = "Membre du Conseil de surveillance" if a == "Membre du Conseil de Surveillance"
replace a = "Président Directeur général" if a == "Président Directeur Général"
ren a `var'
}

*  Miscellaneous
****************
ren transactiondate_1 transactiondate
ren price_1 price
ren amount_1 amount

* Save data
***********

* Basic data (document-level)
compress
save "clean/clean_2009-2013_document-level", replace
