clear
set more off

********************************************************************************
* Creation of a correspondance table to replace AMF `mis-specified' ISIN codes *
********************************************************************************

foreach count in 1 2 3 4{
if `count' == 1{
local file = "Datastream_downloads_complements_2018_02_04"
}
if `count' == 2{
local file = "Datastream_downloads_complements_2018_10_05"
}
if `count' == 3{
local file = "Datastream_downloads_complements_2018_10_09"
}
if `count' == 4{
local file = "Datastream_downloads_complements_2019_06_02"
}
*

import excel "$source\Datastream/`file'.xlsx", sheet("Firms") clear allstring
drop in 1/2
ren A isin
ren D Code
keep isin Code 
replace isin = lower(isin)
save tmp`count', replace

import excel "$source\Datastream/`file'.xlsx", sheet("Market value") clear allstring
drop in 1/6
ren B Code
drop if Code == ""
replace Code = subinstr(Code,"(MV)","",1)
ren H ISIN

replace Code = "29068W" if ISIN == "FR0010080895" // Missing merging code
keep Code ISIN 
duplicates drop Code, force
replace ISIN = lower(ISIN)
merge 1:n Code using tmp`count'
drop if _merge != 3
keep isin ISIN 
save tmp`count', replace
}
*
use tmp1
append using tmp2
append using tmp3
append using tmp4

duplicates drop isin, force
erase tmp1.dta
erase tmp2.dta
erase tmp3.dta
erase tmp4.dta

save "$temp/isin_issues_correspondence_table", replace
