**************************************************
**************************************************
******* We first create input data ***************
******* based on downloaded data   ***************
**************************************************
**************************************************


//This do-file is used to create input data for the calculations based on downloaded data

//Create bridge table where all economy names in the CDIS and corresponding ISO alpha-3 country codes are included
//When updating: If the CDIS list of economies has been expanded, remember to add the ISO codes
import excel "U:\The Global FDI Network Dataset\Downloaded data\CDIS_economies_ISO_codes.xlsx", firstrow clear 
save "U:\The Global FDI Network Dataset\Input data\CDIS_economies_ISO_codes.dta", replace

//Create data template with all combinations (246 economies x 247 counterpart economies x 8 years (2009-2016))
// (247 counterpart economies = all 246 economies + "Not Specified (including Confidential)")
//When updating: Insert extra years available in the Year column (remember to add economies if they are added to the CDIS list of economies)
import excel "U:\The Global FDI Network Dataset\Downloaded data\Data_template.xlsx", firstrow clear 
fillin Year Economy Investment_from
drop _fillin
save "U:\The Global FDI Network Dataset\Input data\Data_template.dta", replace

//List of low-tax econonomies as defined in Table 1 in Damgaard and Elkjaer (2017)
import excel "U:\The Global FDI Network Dataset\Downloaded data\Low_tax_economies_list.xlsx", firstrow clear 
save "U:\The Global FDI Network Dataset\Input data\Low_tax_economies_list.dta", replace

//Import aggregate CDIS data
//When updating: Go to the CDIS website and download Table 4-i(http://data.imf.org/regular.aspx?key=60564263)
import excel "U:\The Global FDI Network Dataset\Downloaded data\CDIS_aggregate.xlsx", firstrow clear 
save "U:\The Global FDI Network Dataset\Input data\CDIS_aggregate.dta", replace

//Import detailed CDIS data
//When updating: Go the CDIS website (http://data.imf.org/?sk=40313609-F037-48C1-84B1-E1F1CE54D6D5), sign in, and perform "bulk download" (top right-hand corner)
import delimited "U:\The Global FDI Network Dataset\Downloaded data\CDIS_detailed.csv", delimiter(comma) varnames(1) clear
rename ïcountryname Economy
replace Economy = "Venezuela, República Bolivariana de" if Economy == "Venezuela, República Bolivariana de"
rename counterpartcountryname Investment_from
replace Investment_from = "Venezuela, República Bolivariana de" if Investment_from == "Venezuela, República Bolivariana de"
drop if timeperiod == ""
drop if timeperiod == "C"
destring timeperiod, generate(Year)
drop timeperiod
save "U:\The Global FDI Network Dataset\Input data\CDIS_detailed.dta", replace

//Import WEO data
//When updating: Go to the latest World Economic Outlook Database and download data for GDP (USD billions in current prices),
// GDP per capita (USD in current prices), populations (millions), and general government gross debt (percent of GDP)
import excel "U:\The Global FDI Network Dataset\Downloaded data\WEO_data.xlsx", firstrow clear 
rename Country Economy
replace Economy = "Korea, Republic of" if Economy == "Korea"
save "U:\The Global FDI Network Dataset\Input data\WEO_data.dta", replace

//Import GDP data for non-WEO economies
//When updating: Use the source described in the Excel sheet and download data for GDP (USD billions in current prices),
import excel "U:\The Global FDI Network Dataset\Downloaded data\GDP_data_non-WEO_economies.xlsx", firstrow clear 
save "U:\The Global FDI Network Dataset\Input data\GDP_data_non-WEO_economies.dta", replace

//Import merchandise trade data
//When updating: Go to https://www.wto.org/english/res_e/statis_e/merch_trade_stat_e.htm 
import excel "U:\The Global FDI Network Dataset\Downloaded data\WTO_merchandise.xlsx", firstrow clear 
rename Reporter_desc Economy
replace Economy = "United States" if Economy == "United States of America"
rename Value Merchandise_trade
collapse (sum) Merchandise_trade, by(Year Economy)
save "U:\The Global FDI Network Dataset\Input data\WTO_merchandise.dta", replace

//Import commercial services trade data
//When updating: Go to https://www.wto.org/english/res_e/statis_e/tradeserv_stat_e.htm 
import excel "U:\The Global FDI Network Dataset\Downloaded data\WTO_services.xlsx", firstrow clear 
rename Reporter_desc Economy
replace Economy = "United States" if Economy == "United States of America"
rename Value Services_trade
collapse (sum) Services_trade, by(Year Economy)
save "U:\The Global FDI Network Dataset\Input data\WTO_services.dta", replace

//Import aggregate OECD data
//When updating: Go to the OECD website (http://stats.oecd.org/index.aspx) and download "FDI positions, main aggregates BMD4" 
import excel "U:\The Global FDI Network Dataset\Downloaded data\OECD_aggregate.xlsx", firstrow clear 
replace Economy = "Korea, Republic of" if Economy == "Korea"
// Compilers from countries who have reported zeros for SPEs have indicated that most often such data are not available
replace Inward_NonSPEs = . if Inward_SPEs == 0
replace Inward_SPEs = . if Inward_SPEs == 0
replace Outward_NonSPEs = . if Outward_SPEs == 0
replace Outward_SPEs = . if Outward_SPEs == 0
// DNB has made a substantial revision of FDI data with a strict implementation of the 5 employee criterion for SPE identification.
// The revision has only been applied to data for 2014 onwards. Therefore, we move 15% of total inward FDI from SPEs to non-SPEs and 
// 17% of outward FDI from SPEs to non-SPEs to ensure data consistency over time.
replace Inward_NonSPEs = Inward_NonSPEs + 0.15*Inward_all if Economy == "Netherlands" & Year < 2014
replace Inward_SPEs = Inward_SPEs - 0.15*Inward_all if Economy == "Netherlands" & Year < 2014
replace Outward_NonSPEs = Outward_NonSPEs + 0.17*Inward_all if Economy == "Netherlands" & Year < 2014
replace Outward_SPEs = Outward_SPEs - 0.17*Inward_all if Economy == "Netherlands" & Year < 2014
save "U:\The Global FDI Network Dataset\Input data\OECD_aggregate.dta", replace

//Import detailed inward OECD data
//When updating: Go to the OECD website (http://stats.oecd.org/index.aspx) and select "FDI positions, FDI positions by partner country BMD4, 
//Inward and outward FDI by partner country". Click "Customise", "Selection" and pick the following:
//Reporting country: Select all
//Currency: Select "USD"
//Measurement principle: Select "Directional principle: Inward"
//Type of FDI: Select "FDI positions - Total"
//Type of entity: Select all
//Accounting entry: Select "Net"
//Level of counterpart: Select all
//Partner country/territory: Select all
//Year: Select the relevant time period 
//Finally, click "Export" and then "Text file (CSV)"
import delimited "U:\The Global FDI Network Dataset\Downloaded data\OECD_detailed.csv", delimiter(comma) varnames(1) clear
save "U:\The Global FDI Network Dataset\Input data\OECD_detailed.dta", replace

//Import detailed outward OECD data (only to be used for checking assumption that same SPE adjustment 
//factor for a given economy can be applied to outward FDI to all counterpart economies.
//When updating: Go to the OECD website (http://stats.oecd.org/index.aspx) and select "FDI positions, FDI positions by partner country BMD4, 
//Inward and outward FDI by partner country". Click "Customise", "Selection" and pick the following:
//Reporting country: Select all
//Currency: Select "USD"
//Measurement principle: Select "Directional principle: Outward"
//Type of FDI: Select "FDI positions - Total"
//Type of entity: Select all
//Accounting entry: Select "Net"
//Level of counterpart: Select all
//Partner country/territory: Select all
//Year: Select the relevant time period 
//Finally, click "Export" and then "Text file (CSV)"
import delimited "U:\The Global FDI Network Dataset\Downloaded data\Outward_OECD_detailed.csv", delimiter(comma) varnames(1) clear
save "U:\The Global FDI Network Dataset\Input data\Outward_OECD_detailed.dta", replace

//Orbis data (includes all firms in Orbis where economy of residence is not equal to economy of immediate shareholder 
//and where information about immediate shareholder is not missing. Focus on 2016 data in this analysis. 
use "U:\The Global FDI Network Dataset\Downloaded data\FDI_Orbis.dta", replace
keep country countryisocode bvdmajorsector guocountryisocode ishcountryisocode numberofemployees2016 totalassetsmusd2016 shareholdersfundsmusd2016 ishdirect
rename numberofemployees2016 employees
rename totalassetsmusd2016 assets
rename shareholdersfundsmusd2016 equity
replace countryisocode = "XK" if countryisocode == "KV"
replace guocountryisocode = "XK" if guocountryisocode == "KV"
replace ishcountryisocode = "XK" if ishcountryisocode == "KV"
save "U:\The Global FDI Network Dataset\Input data\Orbis.dta", replace


**************************************************
**************************************************
*********** We now create output data ************
**************************************************
**************************************************

// Output data consists of the following sub-datasets:
*1a.  Total inward FDI (economy-by-economy)
*1b.  Inward FDI into resident SPEs (economy-by-economy)
*1c.  Inward FDI into resident non-SPEs (economy-by-economy)
*1d.  Inward FDI into resident non-SPEs (economy-by-ultimate economy)
*2a.  Inward FDI from non-resident SPEs as share of total inward FDI (by economy)
*2b.  Outward FDI to non-resident SPEs as share of outward FDI (by economy)

**************************************************
**************************************************
************* Create sub-dataset 1a **************
**************************************************
**************************************************

// The use of CDIS data can be split into two groups: (i) reported data are available for an an economy for a given year (use these data), and 
// (ii) reported data are not available for an an economy for a given year (use mirror data instead). 

// We identify the data that are directly available, i.e. group (i), based on aggregate CDIS data
use "U:\The Global FDI Network Dataset\Input data\CDIS_aggregate", replace
drop if Inward_FDI == . 
drop if Economy == "Total Investment"
drop Inward_FDI
gen data_available = 1
save "U:\The Global FDI Network Dataset\Intermediate data\Reported_data_available.dta", replace

// Information about data availability is merged on the detailed CDIS data
use "U:\The Global FDI Network Dataset\Input data\CDIS_detailed.dta", replace
rename value Inward_FDI
drop if Year == 2008
drop if Economy == "World"
drop if Economy == "Not Specified (including Confidential)"
drop if Economy == "Central and South Asia"
drop if Economy == "East Asia"
drop if Economy == "Economies of Persian Gulf"
drop if Economy == "Europe"
drop if Economy == "North Africa"
drop if Economy == "North Atlantic and Caribbean"
drop if Economy == "North and Central America"
drop if Economy == "Oceania and Polar Regions"
drop if Economy == "Other Near and Middle East Economies"
drop if Economy == "South America"
drop if Economy == "Sub-Saharan Africa"
drop if Investment_from == "Central and South Asia"
drop if Investment_from == "East Asia"
drop if Investment_from == "Economies of Persian Gulf"
drop if Investment_from == "Europe"
drop if Investment_from == "North Africa"
drop if Investment_from == "North Atlantic and Caribbean"
drop if Investment_from == "North and Central America"
drop if Investment_from == "Oceania and Polar Regions"
drop if Investment_from == "Other Near and Middle East Economies"
drop if Investment_from == "South America"
drop if Investment_from == "Sub-Saharan Africa"
merge m:1 Year Economy using "U:\The Global FDI Network Dataset\Intermediate data\Reported_data_available.dta"
replace data_available = 0 if data_available == .
save "U:\The Global FDI Network Dataset\Intermediate data\CDIS_detailed.dta", replace

// We extract the data for group (i)
use "U:\The Global FDI Network Dataset\Intermediate data\CDIS_detailed.dta", replace
keep if data_available == 1 & indicatorcode == "IIW_BP6_USD"
keep Year Economy Investment_from Inward_FDI status
gen Source = "Reported"
save "U:\The Global FDI Network Dataset\Intermediate data\CDIS_reported_detailed", replace

// We then extract the data for group (ii)
use "U:\The Global FDI Network Dataset\Intermediate data\CDIS_detailed.dta", replace
keep if data_available == 0 & indicatorcode == "IIW_DV_BP6_USD"
keep Year Economy Investment_from Inward_FDI status
gen Source = "Mirror data"
save "U:\The Global FDI Network Dataset\Intermediate data\CDIS_mirror_detailed", replace

// Reported inward FDI tends to be higher than the mirror estimate, i.e., outward FDI reported by counterpart economies.
// There could be different reasons for this, e.g., many economies do not report outward FDI, 
// and "Not Specified (including Confidential)" is not captured in mirror/derived estimates for a specific economy.
// Therefore, to supplement the mirror data, we use the relationship between reported and mirror data for 
// the reporting economies and add the weighted average difference to the mirror data for non-reporting economies 
// under the category "Not Specified (including Confidential)"

// We calculate the reported-to-mirror adjustment factor based on data from reporting economies
use "U:\The Global FDI Network Dataset\Intermediate data\CDIS_detailed.dta", replace
keep if data_available == 1 & indicatorcode == "IIW_BP6_USD"
keep if Investment_from == "World"
rename Inward_FDI Inward_FDI_reported
collapse (sum) Inward_FDI_reported, by(Year)
save "U:\The Global FDI Network Dataset\Intermediate data\CDIS_reported_aggregate", replace
use "U:\The Global FDI Network Dataset\Intermediate data\CDIS_detailed.dta", replace
keep if data_available == 1 & indicatorcode == "IIW_DV_BP6_USD"
keep if Investment_from == "World"
rename Inward_FDI Inward_FDI_mirror
collapse (sum) Inward_FDI_mirror, by(Year)
save "U:\The Global FDI Network Dataset\Intermediate data\CDIS_mirror_aggregate", replace
merge 1:1 Year using "U:\The Global FDI Network Dataset\Intermediate data\CDIS_reported_aggregate"
gen mirror_adj = Inward_FDI_reported/Inward_FDI_mirror-1
drop _merge Inward_FDI_reported Inward_FDI_mirror
save "U:\The Global FDI Network Dataset\Intermediate data\Mirror_adj", replace

// The adjustments are calculated and assigned to "Not Specified (including Confidential)" for group (ii)
use "U:\The Global FDI Network Dataset\Intermediate data\CDIS_mirror_detailed", replace
keep if Investment_from == "World"
merge m:1 Year using "U:\The Global FDI Network Dataset\Intermediate data\Mirror_adj"
replace Inward_FDI = Inward_FDI * mirror_adj
replace Source = "Own estimate"
replace Investment_from = "Not Specified (including Confidential)"
drop mirror_adj _merge
save "U:\The Global FDI Network Dataset\Intermediate data\CDIS_mirror_not_specified", replace

// We construct the full dataset (excluding Investment_from == "World")
use "U:\The Global FDI Network Dataset\Intermediate data\CDIS_mirror_detailed", replace
append using "U:\The Global FDI Network Dataset\Intermediate data\CDIS_reported_detailed"
append using "U:\The Global FDI Network Dataset\Intermediate data\CDIS_mirror_not_specified"
drop if Investment_from == "World"
save "U:\The Global FDI Network Dataset\Intermediate data\Total_detailed", replace

// The full dataset is merged with the data template 
use "U:\The Global FDI Network Dataset\Input data\Data_template", replace
merge 1:1 Year Economy Investment_from using "U:\The Global FDI Network Dataset\Intermediate data\Total_detailed"
replace Source = "No data" if Source == ""
drop _merge
replace Inward_FDI = Inward_FDI/1000000 // Data are converted from USD to USD millions
rename status Inward_FDI_confidential
rename Source Inward_FDI_source

// ISO country codes are added to the dataset
merge m:1 Economy using "U:\The Global FDI Network Dataset\Input data\CDIS_economies_ISO_codes"
drop _merge
rename Economy Ec
rename Investment_from Economy
rename ISO Economy_ISO
merge m:1 Economy using "U:\The Global FDI Network Dataset\Input data\CDIS_economies_ISO_codes"
drop _merge
rename Economy Investment_from 
rename Ec Economy
rename ISO Investment_from_ISO
drop ISO2DIGIT
order Year Economy_ISO Economy Investment_from_ISO Investment_from Inward_FDI Inward_FDI_confidential Inward_FDI_source 
sort Year Economy_ISO Investment_from_ISO
drop if Economy_ISO == "ZZZ"
save "U:\The Global FDI Network Dataset\Intermediate data\Sub-dataset_1a", replace


**************************************************
**************************************************
*********** Create sub-dataset 1b + 1c ***********
**************************************************
**************************************************

// We use aggregate inward FDI data from reporting OECD countries to estimate an SPE adjustment model.
// The model is estimated as an OLS regression with non-SPE-to-total FDI as the dependent variable and total FDI-to-GDP as the explanatory variable.
// A log-log transformation is made to achieve linearity.
// Extended model specifications show that total FDI-to-GDP has far more explanatory power than variables such as GDP, GDP per capita, public debt-to-GDP, and trade-to-GDP.
// The model is estimated as a pooled OLS for the period 2013-2017 where the panel is almost balanced (14-16 observations per year). 
// Models for each year show that the coefficients are stable during this time period. 

use "U:\The Global FDI Network Dataset\Input data\OECD_aggregate", replace
drop Outward_all Outward_SPEs Outward_NonSPEs I
drop if Inward_SPEs == .
merge 1:1 Year Economy using "U:\The Global FDI Network Dataset\Input data\WEO_data" // Merge GDP data
keep if _merge == 3
drop _merge 
keep if Year > 2012 // Only data from 2013-2017
replace GDP = GDP * 1000 // GDP data are converted from USD billions to USD millions
gen FDI_to_GDP = 100 * Inward_all / GDP
gen ln_FDI_to_GDP = ln(FDI_to_GDP)
gen NonSPEs_to_total_FDI = 100 * Inward_NonSPEs / Inward_all
gen SPE_adj_factor = "Reported"
gen ln_NonSPEs_to_total_FDI = ln(NonSPEs_to_total_FDI)
save "U:\The Global FDI Network Dataset\Intermediate data\OECD_aggregates", replace
regress ln_NonSPEs_to_total_FDI ln_FDI_to_GDP, cluster(Economy)
gen parameter_estimate = _b[ln_FDI_to_GDP]
gen constant = _b[_cons]
gen Average_NonSPEs_to_total_FDI = NonSPEs_to_total_FDI
collapse (mean) parameter_estimate constant Average_NonSPEs_to_total_FDI
gen Investment_from = "World"
save "U:\The Global FDI Network Dataset\Intermediate data\SPE_adjustment_model", replace

// We estimate a non-SPE-to-total FDI ratio for all economies.
use "U:\The Global FDI Network Dataset\Intermediate data\Sub-dataset_1a", replace
collapse (sum) Inward_FDI, by (Year Economy)
merge m:1 Economy using "U:\The Global FDI Network Dataset\Input data\CDIS_economies_ISO_codes" 
drop _merge
merge 1:1 Year ISO using "U:\The Global FDI Network Dataset\Input data\WEO_data"
keep Year Economy Inward_FDI ISO GDP
replace GDP = GDP * 1000 // GDP data are converted from USD billions to USD millions
merge 1:1 Year ISO using "U:\The Global FDI Network Dataset\Input data\GDP_data_non-WEO_economies"
replace GDP = GDP_non_WEO if GDP == .
keep Year Economy Inward_FDI ISO GDP
gen Investment_from = "World"
merge m:1 Investment_from using "U:\The Global FDI Network Dataset\Intermediate data\SPE_adjustment_model"
gen Inward_FDI_to_GDP = 100*Inward_FDI/GDP 
gen ln_Inward_FDI_to_GDP = ln(Inward_FDI_to_GDP) 
gen Estimated_NonSPEs_to_total_FDI = min(100,exp(parameter_estimate * ln_Inward_FDI_to_GDP + constant)) // The non-SPE to total FDI ratio cannot exceed 100 
replace Estimated_NonSPEs_to_total_FDI = Average_NonSPEs_to_total_FDI if GDP == .
keep Economy Year ISO Inward_FDI GDP Inward_FDI_to_GDP Estimated_NonSPEs_to_total_FDI
save "U:\The Global FDI Network Dataset\Output data\Inward_FDI-to-GDP_ratios", replace
export excel using "U:\The Global FDI Network Dataset\Output data\Inward_FDI-to-GDP_ratios.xlsx", replace firstrow(variables)
keep Economy Year ISO Estimated_NonSPEs_to_total_FDI
save "U:\The Global FDI Network Dataset\Intermediate data\Estimated_SPE_adjustment_factors", replace

// We use reported non-SPE-to-total FDI ratios for reporting economies and estimated ratios for non-reporting economies
use "U:\The Global FDI Network Dataset\Input data\OECD_aggregate", replace
drop Outward_all Outward_SPEs Outward_NonSPEs
drop if Inward_SPEs == .
gen Non_SPE_adjustment_factor = 100 * (Inward_NonSPEs / Inward_all)
gen Adjustment = "Reported"
keep Economy Year Non_SPE_adjustment_factor Adjustment
merge 1:1 Year Economy using "U:\The Global FDI Network Dataset\Intermediate data\Estimated_SPE_adjustment_factors"
drop _merge
replace Adjustment = "Estimated" if Adjustment == ""
replace Non_SPE_adjustment_factor = Estimated_NonSPEs_to_total_FDI if Non_SPE_adjustment_factor == .
drop Estimated_NonSPEs_to_total_FDI
save "U:\The Global FDI Network Dataset\Intermediate data\SPE_adjustment_factors", replace

// The reported/estimated non-SPE to total FDI ratios are used to make a split between inward FDI in SPEs and non-SPEs
use "U:\The Global FDI Network Dataset\Intermediate data\Sub-dataset_1a", replace
merge m:1 Economy Year using "U:\The Global FDI Network Dataset\Intermediate data\SPE_adjustment_factors"
gen Inward_FDI_SPEs = Inward_FDI * (100 - Non_SPE_adjustment_factor) / 100
gen Inward_FDI_NonSPEs = Inward_FDI * Non_SPE_adjustment_factor / 100
rename Adjustment SPE_adjustment 
drop _merge Non_SPE_adjustment_factor ISO 
sort Year Economy_ISO Investment_from_ISO
drop if Year == .
save "U:\The Global FDI Network Dataset\Intermediate data\Sub-dataset_1a-c_fixed_ratios", replace

// Next, we refine the dataset so that we use reported detailed non-SPE FDI data where possible for economies hosting SPEs.
// We identify reporting OECD countries who host resident SPEs ...
use "U:\The Global FDI Network Dataset\Intermediate data\SPE_adjustment_factors", replace
drop if Adjustment == "Estimated"
drop if Non_SPE_adjustment_factor == 100
rename ISO ïcou
rename Year year
keep year ïcou
// ... and merge this information onto the dataset for reported detailed inward FDI positions into resident non-SPEs
merge 1:m year ïcou using "U:\The Global FDI Network Dataset\Input data\OECD_detailed.dta"
keep if _merge == 3
drop _merge
*********************************************************************************************************************
**** We do not use detailed data for the Netherlands before 2014 as we have revised the aggregate SPE/non-SPE distribution
drop if year < 2014 & reportingcountry == "Netherlands"
*********************************************************************************************************************
keep if typeofentity == "Resident Operating Units (Non-SPEs)"
keep if level_counterpart == "IMC"
rename value Non_SPE_value
keep year ïcou counterpart_area Non_SPE_value
save "U:\The Global FDI Network Dataset\Intermediate data\Detailed_reported_non-SPE", replace
// The detailed data for reported inward FDI positions into resident SPE are merged with detailed data for total inward FDI positions
// so that non-SPE-to-total FDI ratios based on detailed reported data can be calculated.
use "U:\The Global FDI Network Dataset\Input data\OECD_detailed.dta", replace
keep if typeofentity == "All resident units"
keep if level_counterpart == "IMC"
rename value Total_value
keep year ïcou counterpart_area Total_value
merge 1:1 year ïcou counterpart_area using "U:\The Global FDI Network Dataset\Intermediate data\Detailed_reported_non-SPE"
keep if _merge == 3
drop if Non_SPE_value == .
drop if Total_value == .
gen New_non_SPE_ratio = Non_SPE_value / Total_value
drop if New_non_SPE_ratio == .
rename ïcou Economy_ISO
rename counterpart_area Investment_from_ISO
rename year Year
keep Economy_ISO Investment_from_ISO Year Total_value Non_SPE_value New_non_SPE_ratio
save "U:\The Global FDI Network Dataset\Intermediate data\Det_rep_non-SPE", replace

// The reported detailed inward FDI positions into resident non-SPEs are now merged with the dataset based
// on fixed SPE adjustment factors across counterpart economies.
use "U:\The Global FDI Network Dataset\Intermediate data\Sub-dataset_1a-c_fixed_ratios", replace
merge 1:1 Year Economy_ISO Investment_from_ISO using "U:\The Global FDI Network Dataset\Intermediate data\Det_rep_non-SPE"
drop if _merge == 2
drop _merge
// We use reported detailed non-SPE-to-total inward FDI ratios if the deviation between the total inward FDI position reported to the CDIS and 
// the OECD is less than 20%. We do not replace data if the deviation is 20% or larger because this is a sign of a significant revision, 
// in which case we will keep data based on the application of the aggregate non-SPE-to-total FDI ratios.
gen change = 0
replace change = 1 if (Inward_FDI < 1.2 * Total_value & Inward_FDI > 0.8 * Total_value & Total_value != .) 
replace change = 1 if (Inward_FDI > 1.2 * Total_value & Inward_FDI < 0.8 * Total_value & Total_value != .) // For negative bilateral positions
gen old_Inward_FDI_NonSPEs = Inward_FDI_NonSPEs
replace Inward_FDI_NonSPEs = New_non_SPE_ratio * Inward_FDI if change == 1
replace Inward_FDI_SPEs = Inward_FDI - Inward_FDI_NonSPEs if change == 1
replace SPE_adjustment = "Reported aggregate" if SPE_adjustment == "Reported"
replace SPE_adjustment = "Reported detailed" if change == 1
save "U:\The Global FDI Network Dataset\Intermediate data\Sub-dataset_1a-c_intermediate", replace
drop if change == 0
drop if change == 1 & Investment_from == "ZZZ"
collapse (sum) old_Inward_FDI_NonSPEs Inward_FDI_NonSPEs, by (Year Economy_ISO)
gen difference = old_Inward_FDI_NonSPEs - Inward_FDI_NonSPEs
drop old_Inward_FDI_NonSPEs Inward_FDI_NonSPEs
gen Investment_from_ISO = "ZZZ"
save "U:\The Global FDI Network Dataset\Intermediate data\Differences", replace
// The differences between the new and old estimates are allocated to "Not Specified (including Confidential)" to ensure consistency
use "U:\The Global FDI Network Dataset\Intermediate data\Sub-dataset_1a-c_intermediate", replace
merge 1:1 Year Economy_ISO Investment_from_ISO using "U:\The Global FDI Network Dataset\Intermediate data\Differences"
replace Inward_FDI_NonSPEs = Inward_FDI_NonSPEs + difference if _merge == 3
replace Inward_FDI_SPEs = Inward_FDI_SPEs - difference if _merge == 3
drop Total_value Non_SPE_value New_non_SPE_ratio change old_Inward_FDI_NonSPEs difference _merge
save "U:\The Global FDI Network Dataset\Intermediate data\Sub-dataset_1a-c", replace

**************************************************
**************************************************
************* Create sub-dataset 1d **************
**************************************************
**************************************************

// The majority of reporting OECD countries have only started reporting data on ultimate ownership from 2013 onwards.
// Therefore, we will not construct data for inward FDI broken down by the ultimate investing economy (UIE) before 2013.

// The use of CDIS data can be split into two groups: (i) reported data are available for an an economy for a given year (use these data), and 
// (ii) reported data are not available for an an economy for a given year (use CDIS data and use ultimate/immediate adjustment factor based on reported data). 

// We identify the data that are directly available, i.e. group (i):
use "U:\The Global FDI Network Dataset\Input data\OECD_detailed", replace
keep if levelofcounterpart == "Ultimate counterpart (Ultimate investor or ultimate host)"
keep if typeofentity == "Resident Operating Units (Non-SPEs)"
keep if year > 2012
rename ïcou Economy_ISO
rename year Year
rename value Inward_FDI_NonSPEs_UIE_Unadj
drop if partnercountryterritory == "AFRICA "
drop if partnercountryterritory == "AMERICA "
drop if partnercountryterritory == "AMERICA Excluding OECD countries"
drop if partnercountryterritory == "ASEAN countries"
drop if partnercountryterritory == "ASIA "
drop if partnercountryterritory == "ASIA Excluding OECD countries"
drop if partnercountryterritory == "AUSTRALIA, OCEANIA AND POLAR REGIONS"
drop if partnercountryterritory == "Australia, Oceania and Polar regions  Excluding OECD countries"
drop if partnercountryterritory == "Central America and Caribbean countries "
drop if partnercountryterritory == "Central America and Caribbean excluding OECD countries"
drop if partnercountryterritory == "EU15"
drop if partnercountryterritory == "EU25"
drop if partnercountryterritory == "EU27"
drop if partnercountryterritory == "EU28"
drop if partnercountryterritory == "EUROPE "
drop if partnercountryterritory == "EUROPE Excluding OECD countries"
drop if partnercountryterritory == "G20 countries excl. European Union"
drop if partnercountryterritory == "G20 Non-OECD countries "
drop if partnercountryterritory == "G20 OECD countries"
drop if partnercountryterritory == "Gulf Arabian countries "
drop if partnercountryterritory == "MENA countries"
drop if partnercountryterritory == "Near and Middle East "
drop if partnercountryterritory == "Near and Middle East Excluding OECD countries"
drop if partnercountryterritory == "Northern Africa"
drop if partnercountryterritory == "Northern America"
drop if partnercountryterritory == "Northern America Excluding OECD countries"
drop if partnercountryterritory == "ODA recipients -Africa"
drop if partnercountryterritory == "ODA recipients -Europe"
drop if partnercountryterritory == "ODA recipients-America"
drop if partnercountryterritory == "ODA recipients-Asia" 
drop if partnercountryterritory == "ODA recipients-Oceania" 
drop if partnercountryterritory == "OECD"
drop if partnercountryterritory == "Other African countries"
drop if partnercountryterritory == "Other Asian countries"
drop if partnercountryterritory == "Other Asian countries excluding OECD countries"
drop if partnercountryterritory == "Other NME Excluding OECD  countries"
drop if partnercountryterritory == "Other Near and Middle East (Western Asia)"
drop if partnercountryterritory == "South America"
drop if partnercountryterritory == "South America Excluding OECD"
drop if partnercountryterritory == "Total Official Development Assistance (ODA) recipients"
drop if partnercountryterritory == "WORLD"
drop if partnercountryterritory == "WORLD Excluding OECD countries"
save "U:\The Global FDI Network Dataset\Intermediate data\OECD_detailed_UIE", replace
collapse (sum) Inward_FDI_NonSPEs_UIE_Unadj, by(Economy_ISO Year)
save "U:\The Global FDI Network Dataset\Intermediate data\OECD_aggregate_UIE", replace

// We identify countries that report inward FDI excluding SPEs based on ultimate ownership, and where most data
// are not allocated to specific economies. If more than 50% of the total inward FDI positions are not allocated 
// to specific countries, we do not use the reported data, but construct our own estimates instead.
use "U:\The Global FDI Network Dataset\Input data\OECD_detailed", replace
keep if levelofcounterpart == "Ultimate counterpart (Ultimate investor or ultimate host)"
keep if typeofentity == "Resident Operating Units (Non-SPEs)"
keep if year > 2012
rename ïcou Economy_ISO
rename year Year
keep if partnercountryterritory == "WORLD unallocated and confidential"
keep Economy_ISO Year value
merge 1:1 Economy_ISO Year using "U:\The Global FDI Network Dataset\Intermediate data\OECD_aggregate_UIE"
gen Unallocated_share = value / Inward_FDI_NonSPEs_UIE_Unadj
replace Unallocated_share = 0 if Unallocated_share == .
gen High_Unallocated_share = 1 if Unallocated_share > 0.5
replace High_Unallocated_share = 0 if High_Unallocated_share == .
drop value Inward_FDI_NonSPEs_UIE_Unadj _merge Unallocated_share
save "U:\The Global FDI Network Dataset\Intermediate data\OECD_UIE_high_unallocated_share", replace

// The aggregate OECD data for inward FDI excluding SPEs based on ultimate ownership are compared to the aggregate 
// CDIS/constructed data for inward FDI excluding SPEs based on immediate ownership. The data can differ slightly 
// (e.g., due to different data vintages). We calculate an adjustment factor that can be applied to the OECD data
// to make sure that the aggregates are consistent with the CDIS/constructed data.
use "U:\The Global FDI Network Dataset\Intermediate data\Sub-dataset_1a-c", replace
keep if Year > 2012
collapse (sum) Inward_FDI_NonSPEs, by(Economy_ISO Year)
rename Inward_FDI_NonSPEs Inward_FDI_NonSPEs_total
save "U:\The Global FDI Network Dataset\Intermediate data\CDIS_NonSPEs_total", replace
merge 1:1 Economy_ISO Year using "U:\The Global FDI Network Dataset\Intermediate data\OECD_aggregate_UIE"
keep if _merge == 3
drop _merge
merge 1:1 Economy_ISO Year using "U:\The Global FDI Network Dataset\Intermediate data\OECD_UIE_high_unallocated_share"
drop _merge
gen CDIS_OECD_adjustment = Inward_FDI_NonSPEs_total / Inward_FDI_NonSPEs_UIE_Unadj
drop if CDIS_OECD_adjustment < 0.8 // If the difference between OECD and CDIS/constructed data becomes too big ...
drop if CDIS_OECD_adjustment > 1.2 // ... we do not use the OECD data 
drop if High_Unallocated_share == 1 // We drop data for a country if most data are not allocated to specific counterpart countries
gen UIE_data_available = 1
drop Inward_FDI_NonSPEs_total Inward_FDI_NonSPEs_UIE_Unadj High_Unallocated_share
save "U:\The Global FDI Network Dataset\Intermediate data\CDIS_OECD_adjustment", replace

// We adjust the OECD data for reporting countries so that the totals match the total in the CDIS/constructed data
use "U:\The Global FDI Network Dataset\Intermediate data\OECD_detailed_UIE", replace
merge m:1 Economy_ISO Year using "U:\The Global FDI Network Dataset\Intermediate data\CDIS_OECD_adjustment"
drop if _merge == 1
gen Inward_FDI_NonSPEs_UIE = CDIS_OECD_adjustment * Inward_FDI_NonSPEs_UIE_Unadj
rename counterpart_area Investment_from_ISO
replace flagcodes = "C" if flagcodes == "d"
replace Investment_from_ISO = "ZZZ" if Investment_from_ISO == "C_W190"
replace Investment_from_ISO = "ROU" if Investment_from_ISO == "ROM" // The official ISO code for Romania is now "ROU"
drop if Investment_from_ISO == "ATA" // No inward FDI from Antarctica
drop if Investment_from_ISO == "SCG" // No inward FDI from Serbia and Montenegro (has been separated since 2006)
keep Inward_FDI_NonSPEs_UIE Year Economy_ISO Investment_from_ISO flagcodes // New line added
save "U:\The Global FDI Network Dataset\Intermediate data\OECD_detailed_UIE_adjusted", replace

// ADJUSTMENT FACTORS
// The adjusted data are merged with Sub-dataset_1a-c so that UIE adjustment factors can be calculated. The panel of reported data is 
// unbalanced, and we use 2016 data (the year with highest data availability) for our calculations.
merge 1:1 Year Economy_ISO Investment_from_ISO using "U:\The Global FDI Network Dataset\Intermediate data\Sub-dataset_1a-c"
drop _merge 
merge m:1 Year Economy_ISO using "U:\The Global FDI Network Dataset\Intermediate data\CDIS_OECD_adjustment"
keep if UIE_data_available == 1
keep if Year == 2016
drop if Inward_FDI_confidential == "C" // Observations are dropped if either inward FDI by immediate counterpart economhy or/and ...
drop if flagcodes == "C" // ... inward FDI by the UIE are confidential/non-publishable

// UIE adjustment factors are only calculated if a counterpart economy accounts for more than USD 100 million of FDI both in terms of 
// immediate and ultimate investing relationship. If values are missing or below USD 100 million, the adjustment factor is set to 1.
collapse (sum) Inward_FDI_NonSPEs_UIE Inward_FDI_NonSPEs, by(Investment_from_ISO)
replace Inward_FDI_NonSPEs_UIE = . if Inward_FDI_NonSPEs_UIE < 100 
replace Inward_FDI_NonSPEs = . if Inward_FDI_NonSPEs < 100
gen UIE_adj_factor = Inward_FDI_NonSPEs_UIE / Inward_FDI_NonSPEs
replace UIE_adj_factor = 1 if UIE_adj_factor == .

// The UIE adjustment factors are capped between 0.25 and 4.00 for regular economies and between 0.05 and 1.00 for low-tax economies
merge 1:1 Investment_from_ISO using "U:\The Global FDI Network Dataset\Input data\Low_tax_economies_list"
replace UIE_adj_factor = 4 if UIE_adj_factor > 4 & Low_tax_economy == 0
replace UIE_adj_factor = 0.25 if UIE_adj_factor < 0.25 & Low_tax_economy == 0
replace UIE_adj_factor = 1 if UIE_adj_factor > 1 & Low_tax_economy == 1
replace UIE_adj_factor = 0.05 if UIE_adj_factor < 0.05 & Low_tax_economy == 1
keep Investment_from_ISO UIE_adj_factor
save "U:\The Global FDI Network Dataset\Intermediate data\UIE_adjustment_factors", replace

// Next, we calculate the magnitude of round-tripping, i.e. how much FDI ultimately comes from own economy
use "U:\The Global FDI Network Dataset\Intermediate data\OECD_detailed_UIE_adjusted", replace
keep if Year == 2016
gen Round_trip = Inward_FDI_NonSPEs_UIE if Economy_ISO == Investment_from_ISO
collapse (sum) Inward_FDI_NonSPEs_UIE Round_trip
gen Round_trip_adj_factor = Round_trip / Inward_FDI_NonSPEs_UIE
keep Round_trip_adj_factor
gen merge_variable = 1
save "U:\The Global FDI Network Dataset\Intermediate data\Round_tripping_adjustment_factor", replace

// UIE and round-tripping adjustment factors are merged to the dataset, and the raw adjustments are made.
use "U:\The Global FDI Network Dataset\Intermediate data\Sub-dataset_1a-c", replace
keep if Year > 2012
merge m:1 Investment_from_ISO using "U:\The Global FDI Network Dataset\Intermediate data\UIE_adjustment_factors"
drop _merge
gen merge_variable = 1
merge m:1 merge_variable using "U:\The Global FDI Network Dataset\Intermediate data\Round_tripping_adjustment_factor"
drop _merge 
merge m:1 Year Economy_ISO using "U:\The Global FDI Network Dataset\Intermediate data\CDIS_NonSPEs_total"
drop _merge
gen Inward_FDI_NonSPEs_UIE_raw = UIE_adj_factor * Inward_FDI_NonSPEs
replace Inward_FDI_NonSPEs_UIE_raw = Round_trip_adj_factor * Inward_FDI_NonSPEs_total if Economy_ISO == Investment_from_ISO
save "U:\The Global FDI Network Dataset\Intermediate data\UIE_raw", replace

// The raw adjustments lead to total estimates for inward non-SPE FDI that deviate from the original estimates.
// Hence, an adjustment factor is calculated to make the final adjustment of the estimated UIE data.
collapse (sum) Inward_FDI_NonSPEs_UIE_raw, by(Year Economy_ISO)
merge m:1 Year Economy_ISO using "U:\The Global FDI Network Dataset\Intermediate data\CDIS_NonSPEs_total"
drop _merge
gen UIE_adj = Inward_FDI_NonSPEs_total / Inward_FDI_NonSPEs_UIE_raw
drop Inward_FDI_NonSPEs_total Inward_FDI_NonSPEs_UIE_raw
save "U:\The Global FDI Network Dataset\Intermediate data\UIE_adj", replace
merge 1:m Year Economy_ISO using "U:\The Global FDI Network Dataset\Intermediate data\UIE_raw"
gen Inward_FDI_NonSPEs_UIE = UIE_adj * Inward_FDI_NonSPEs_UIE_raw
keep Year Economy_ISO Investment_from_ISO Inward_FDI_NonSPEs_UIE 
order Year Economy_ISO Investment_from_ISO Inward_FDI_NonSPEs_UIE 
save "U:\The Global FDI Network Dataset\Intermediate data\Estimated_detailed_UIE_adjusted", replace

// We identify the reported data ...
use "U:\The Global FDI Network Dataset\Intermediate data\Sub-dataset_1a-c", replace
merge m:1 Year Economy_ISO using "U:\The Global FDI Network Dataset\Intermediate data\CDIS_OECD_adjustment"
keep if UIE_data_available == 1
drop _merge CDIS_OECD_adjustment UIE_data_available
merge 1:1 Year Economy_ISO Investment_from_ISO using "U:\The Global FDI Network Dataset\Intermediate data\OECD_detailed_UIE_adjusted"
drop _merge flagcodes
gen UIE_adjustment = "Reported"
save "U:\The Global FDI Network Dataset\Intermediate data\UIE_reported", replace

// ... and the estimated data
use "U:\The Global FDI Network Dataset\Intermediate data\Sub-dataset_1a-c", replace
merge m:1 Year Economy_ISO using "U:\The Global FDI Network Dataset\Intermediate data\CDIS_OECD_adjustment"
drop _merge 
merge 1:1 Year Economy_ISO Investment_from_ISO using "U:\The Global FDI Network Dataset\Intermediate data\Estimated_detailed_UIE_adjusted"
drop if UIE_data_available == 1
drop _merge CDIS_OECD_adjustment UIE_data_available
gen UIE_adjustment = "Estimated"
save "U:\The Global FDI Network Dataset\Intermediate data\UIE_estimated", replace

// We then construct the full dataset
use "U:\The Global FDI Network Dataset\Intermediate data\UIE_reported", replace
append using "U:\The Global FDI Network Dataset\Intermediate data\UIE_estimated"
sort Year Economy_ISO Investment_from_ISO
order Year Economy_ISO Economy Investment_from_ISO Investment_from Inward_FDI Inward_FDI_SPEs Inward_FDI_NonSPEs Inward_FDI_NonSPEs_UIE Inward_FDI_confidential Inward_FDI_source SPE_adjustment UIE_adjustment 
replace UIE_adjustment = "Not estimated" if Year < 2013
recast str38 Investment_from
recast str38 Economy
recast str6 Economy_ISO
save "U:\The Global FDI Network Dataset\Intermediate data\Dataset_1_OECD", replace

// Next, we replace the UIE estimates based on OECD data with UIE estimates based on Orbis data
use "U:\The Global FDI Network Dataset\Input data\Orbis.dta", replace
drop if guocountryisocode == "n.a."
drop if guocountryisocode == "-"
drop if guocountryisocode == "II"
drop if ishcountryisocode == "II"
replace employees = "" if employees == "n.a."
destring employees, replace ignore(",")
replace assets = "" if assets == "n.a."
destring assets, replace ignore(",")
replace equity = "" if equity == "n.a."
destring equity, replace ignore(",")
drop if equity == .
drop if ishdirect == "-"
drop if ishdirect == "NG"
drop if ishdirect == "T"
replace ishdirect = "30.00" if ishdirect == "<60.00"
replace ishdirect = "33.33" if ishdirect == "<66.67"
replace ishdirect = "75.00" if ishdirect == ">50.00"
replace ishdirect = "75.005" if ishdirect == ">50.01"
replace ishdirect = "75.50" if ishdirect == ">51.00"
replace ishdirect = "76.00" if ishdirect == ">52.00"
replace ishdirect = "77.50" if ishdirect == ">55.00"
replace ishdirect = "79.00" if ishdirect == ">58.00"
replace ishdirect = "79.50" if ishdirect == ">59.00"
replace ishdirect = "80.00" if ishdirect == ">60.00"
replace ishdirect = "81.00" if ishdirect == ">62.00"
replace ishdirect = "81.50" if ishdirect == ">63.00"
replace ishdirect = "82.00" if ishdirect == ">64.00"
replace ishdirect = "82.50" if ishdirect == ">65.00"
replace ishdirect = "83.00" if ishdirect == ">66.00"
replace ishdirect = "83.50" if ishdirect == ">67.00"
replace ishdirect = "84.50" if ishdirect == ">69.00"
replace ishdirect = "85.00" if ishdirect == ">70.00"
replace ishdirect = "87.50" if ishdirect == ">75.00"
replace ishdirect = "89.00" if ishdirect == ">78.00"
replace ishdirect = "89.50" if ishdirect == ">79.00"
replace ishdirect = "90.00" if ishdirect == ">80.00"
replace ishdirect = "91.00" if ishdirect == ">82.00"
replace ishdirect = "92.50" if ishdirect == ">85.00"
replace ishdirect = "93.50" if ishdirect == ">87.00"
replace ishdirect = "94.00" if ishdirect == ">88.00"
replace ishdirect = "94.50" if ishdirect == ">89.00"
replace ishdirect = "95.00" if ishdirect == ">90.00"
replace ishdirect = "95.50" if ishdirect == ">91.00"
replace ishdirect = "96.50" if ishdirect == ">93.00"
replace ishdirect = "97.00" if ishdirect == ">94.00"
replace ishdirect = "97.50" if ishdirect == ">95.00"
replace ishdirect = "98.50" if ishdirect == ">97.00"
replace ishdirect = "99.00" if ishdirect == ">98.00"
replace ishdirect = "99.50" if ishdirect == ">99.00"
replace ishdirect = "99.95" if ishdirect == ">99.90"
replace ishdirect = "99.99" if ishdirect == ">99.98"
replace ishdirect = "99.995" if ishdirect == ">99.99"
replace ishdirect = "75.00" if ishdirect == "MO"
replace ishdirect = "100.00" if ishdirect == "WO"
replace ishdirect = "75.00" if ishdirect == "±75.00"
replace ishdirect = "90.00" if ishdirect == "±90.00"
destring ishdirect, gen(ownership_share)
drop ishdirect 
drop if ownership_share <= 10 // Only interested in FDI (i.e., equity shares of at least 10%)
gen Inward_FDI_equity = equity * (ownership_share/100)
// We assume that 20% of nonfinancial entities' debt is intra-group cross-border debt.
// The 20% share has been calibrated so that debt equals around 20% of global FDI in the Orbis estimates as they do in the CDIS.
gen Inward_FDI_debt = 0.2 * (ownership_share/100) * (assets - equity) if bvdmajorsector != "Banks" & bvdmajorsector != "Insurance companies"
gen Inward_FDI = Inward_FDI_equity + Inward_FDI_debt
replace Inward_FDI = Inward_FDI_equity if Inward_FDI == .
// We remove SPEs -- based on a crude definition on number of employees (max 5) and assets per employee (min USD 10 million) -- as the UIE
// correction factors are applied to FDI into non-SPEs. Because Orbis coverage of tax havens is below average and number of employees is 
// missing for approximately two-thirds of the FDI enterprises in Orbis, we remove a significantly lower SPE share(approximately 8%)than in the 
// global FDI network dataset (almost 40%).
gen assets_employee = assets / employee
drop if employees <= 5 & assets_employee > 10
// 3-digit ISO codes are merged onto the datasets
rename countryisocode ISO2DIGIT
merge m:1 ISO2DIGIT using "U:\The Global FDI Network Dataset\Input data\CDIS_economies_ISO_codes.dta"
drop if _merge == 2
drop _merge ISO2DIGIT
rename ISO Economy_ISO
rename ishcountryisocode ISO2DIGIT
merge m:1 ISO2DIGIT using "U:\The Global FDI Network Dataset\Input data\CDIS_economies_ISO_codes.dta"
drop if _merge == 2
drop _merge ISO2DIGIT
rename ISO Investment_from_ISO
rename guocountryisocode ISO2DIGIT
merge m:1 ISO2DIGIT using "U:\The Global FDI Network Dataset\Input data\CDIS_economies_ISO_codes.dta"
drop if _merge == 2
drop _merge ISO2DIGIT
rename ISO Ultimate_ISO
save "U:\The Global FDI Network Dataset\Intermediate data\Filtered_Orbis.dta", replace


// Construction of detailed UIE correction factors based on Orbis
// A dataset for inward FDI by immediate investor is constructed (economy-by-immediate counterpart economy) 
collapse (sum) Inward_FDI, by (Economy_ISO Investment_from_ISO)
rename Inward_FDI Immediate_FDI
save "U:\The Global FDI Network Dataset\Intermediate data\Immediate_FDI_e-b-e.dta", replace
// A dataset for inward FDI by ultimate investor is constructed (economy-by-ultimate counterpart economy) 
use "U:\The Global FDI Network Dataset\Intermediate data\Filtered_Orbis.dta", replace
drop if Economy_ISO == Ultimate_ISO
collapse (sum) Inward_FDI, by (Economy_ISO Ultimate_ISO)
rename Ultimate_ISO Investment_from_ISO
rename Inward_FDI Ultimate_FDI
// The two datasets are merged to construct detailed UIE correction factors based on Orbis data
merge 1:1 Economy_ISO Investment_from_ISO using "U:\The Global FDI Network Dataset\Intermediate data\Immediate_FDI_e-b-e.dta"
keep if _merge == 3
drop _merge
// We only construct detailed correction factors if Ultimate_FDI > 1 & Immediate_FDI > 1
keep if Ultimate_FDI > 1 & Immediate_FDI > 1
gen Detailed_UIE_factor = Ultimate_FDI / Immediate_FDI
// The detailed correction factors are capped between 0.05 and 10
replace Detailed_UIE_factor = 0.05 if Detailed_UIE_factor < 0.05
replace Detailed_UIE_factor = 10 if Detailed_UIE_factor > 10
rename Immediate_FDI Immediate_FDI_Orbis
drop Ultimate_FDI
save "U:\The Global FDI Network Dataset\Intermediate data\Detailed_UIE_factors.dta", replace


// Construction of aggregate UIE correction factors based on Orbis
// A dataset for inward FDI by immediate investor is constructed 
use "U:\The Global FDI Network Dataset\Intermediate data\Filtered_Orbis.dta", replace
collapse (sum) Inward_FDI, by (Investment_from_ISO)
rename Inward_FDI Immediate_FDI
save "U:\The Global FDI Network Dataset\Intermediate data\Immediate_FDI.dta", replace
// A dataset for inward FDI by ultimate investor is constructed 
use "U:\The Global FDI Network Dataset\Intermediate data\Filtered_Orbis.dta", replace
drop if Economy_ISO == Ultimate_ISO
collapse (sum) Inward_FDI, by (Ultimate_ISO)
rename Ultimate_ISO Investment_from_ISO
rename Inward_FDI Ultimate_FDI
// The two datasets are merged to construct detailed UIE correction factors based on Orbis data
merge 1:1 Investment_from_ISO using "U:\The Global FDI Network Dataset\Intermediate data\Immediate_FDI.dta"
keep if _merge == 3
drop _merge
// We only construct aggregate correction factors if Ultimate_FDI > 50 & Immediate_FDI > 50
keep if Ultimate_FDI > 50 & Immediate_FDI > 50
gen Aggregate_UIE_factor = Ultimate_FDI / Immediate_FDI
// The aggregate correction factors are capped between 0.05 and 10
replace Aggregate_UIE_factor = 0.05 if Aggregate_UIE_factor < 0.05
replace Aggregate_UIE_factor = 10 if Aggregate_UIE_factor > 10
drop Immediate_FDI Ultimate_FDI
save "U:\The Global FDI Network Dataset\Intermediate data\Aggregate_UIE_factors.dta", replace

// We calculate round-tripping factors by economy
use "U:\The Global FDI Network Dataset\Intermediate data\Filtered_Orbis.dta", replace
keep if Economy_ISO == Ultimate_ISO
collapse (sum) Inward_FDI, by (Economy_ISO)
rename Inward_FDI roundtripping
save "U:\The Global FDI Network Dataset\Intermediate data\Roundtripping_detailed.dta", replace
use "U:\The Global FDI Network Dataset\Intermediate data\Filtered_Orbis.dta", replace
collapse (sum) Inward_FDI, by (Economy_ISO)
merge 1:1 Economy using "U:\The Global FDI Network Dataset\Intermediate data\Roundtripping_detailed.dta"
// Round-tripping factors only calculated if aggregate inward FDI in the economy exceeds USD 50 million
keep if Inward_FDI > 50
gen Detailed_RT_factor = roundtripping / Inward_FDI
replace Detailed_RT_factor = 0 if Detailed_RT_factor == . 
// The round-tripping factors are capped between 0 and 0.25
replace Detailed_RT_factor = 0 if Detailed_RT_factor < 0
replace Detailed_RT_factor = 0.25 if Detailed_RT_factor > 0.25
gen RT = 1
gen RT_detailed = 1
drop Inward_FDI roundtripping _merge 
save "U:\The Global FDI Network Dataset\Intermediate data\Detailed_RT_factors.dta", replace

// We calculate an aggregate round-tripping factor
use "U:\The Global FDI Network Dataset\Intermediate data\Filtered_Orbis.dta", replace
keep if Economy_ISO == Ultimate_ISO
collapse (sum) Inward_FDI
gen Level = "aggregate"
rename Inward_FDI roundtripping
save "U:\The Global FDI Network Dataset\Intermediate data\Roundtripping_aggregate.dta", replace
use "U:\The Global FDI Network Dataset\Intermediate data\Filtered_Orbis.dta", replace
collapse (sum) Inward_FDI
gen Level = "aggregate"
merge 1:1 Level using "U:\The Global FDI Network Dataset\Intermediate data\Roundtripping_aggregate.dta"
gen Aggregate_RT_factor = roundtripping / Inward_FDI
gen RT = 1
keep RT Aggregate_RT_factor
save "U:\The Global FDI Network Dataset\Intermediate data\Aggregate_RT_factor.dta", replace

// The UIE correction factors are merged on to the global FDI network dataset 
use "U:\The Global FDI Network Dataset\Intermediate data\Dataset_1_OECD", replace
drop if Year < 2013
merge m:1 Investment_from_ISO using "U:\The Global FDI Network Dataset\Intermediate data\Aggregate_UIE_factors.dta"
drop _merge
merge m:1 Economy_ISO Investment_from_ISO using "U:\The Global FDI Network Dataset\Intermediate data\Detailed_UIE_factors.dta"
drop _merge
gen RT = .
replace RT = 1 if Economy_ISO == Investment_from_ISO
merge m:1 RT using "U:\The Global FDI Network Dataset\Intermediate data\Aggregate_RT_factor.dta"
drop _merge
merge m:1 Economy_ISO RT using "U:\The Global FDI Network Dataset\Intermediate data\Detailed_RT_factors.dta"
drop _merge
// If we do not have any information from Orbis, the UIE correction factor will be set to 1
// If Orbis data are available, we will the most detailed UIE correction factors as calculated above
gen New_UIE_factor = 1
gen New_UIE_source = "No Orbis data"
replace New_UIE_factor = Aggregate_UIE_factor if Aggregate_UIE_factor != .
replace New_UIE_source = "Aggregate Orbis data" if Aggregate_UIE_factor != .
replace New_UIE_factor = Detailed_UIE_factor if Detailed_UIE_factor != . & Immediate_FDI_Orbis >= 0.5*Inward_FDI_NonSPEs & Immediate_FDI_Orbis <= 1.5*Inward_FDI_NonSPEs
replace New_UIE_source = "Detailed Orbis data" if Detailed_UIE_factor != . & Immediate_FDI_Orbis >= 0.5*Inward_FDI_NonSPEs & Immediate_FDI_Orbis <= 1.5*Inward_FDI_NonSPEs
replace New_UIE_factor = Aggregate_RT_factor if RT == 1
replace New_UIE_source = "Aggregate Orbis data" if RT == 1
replace New_UIE_factor = Detailed_RT_factor if RT_detailed == 1
replace New_UIE_source = "Detailed Orbis data" if RT_detailed == 1
save "U:\The Global FDI Network Dataset\Intermediate data\Pre_UIE_Orbis.dta", replace
// Raw UIE estimates are generated
collapse (sum) Inward_FDI_NonSPEs, by(Year Economy_ISO)
rename Inward_FDI_NonSPEs Agg_Inward_FDI_NonSPEs
merge 1:m Year Economy_ISO using "U:\The Global FDI Network Dataset\Intermediate data\Pre_UIE_Orbis.dta"
drop _merge
gen New_UIE_raw = New_UIE_factor * Inward_FDI_NonSPEs
replace New_UIE_raw = New_UIE_factor * Agg_Inward_FDI_NonSPEs if RT == 1
save "U:\The Global FDI Network Dataset\Intermediate data\Raw_UIE_Orbis.dta", replace

// The raw UIE estimates are adjusted so that they sum up to the total in Sub-dataset 1c
use "U:\The Global FDI Network Dataset\Intermediate data\Raw_UIE_Orbis.dta", replace
collapse (sum) Inward_FDI_NonSPEs New_UIE_raw, by(Year Economy_ISO)
gen Raw_to_total = Inward_FDI_NonSPEs / New_UIE_raw
drop Inward_FDI_NonSPEs New_UIE_raw
merge 1:m Year Economy_ISO using "U:\The Global FDI Network Dataset\Intermediate data\Raw_UIE_Orbis.dta"
gen New_Inward_FDI_NonSPEs_UIE = Raw_to_total * New_UIE_raw
drop Agg_Inward_FDI_NonSPEs RT RT_detailed _merge
order Year Economy_ISO Economy Investment_from_ISO Investment_from Inward_FDI Inward_FDI_SPEs Inward_FDI_NonSPEs Inward_FDI_NonSPEs_UIE Inward_FDI_confidential /*
*/ Inward_FDI_source SPE_adjustment UIE_adjustment New_Inward_FDI_NonSPEs_UIE New_UIE_source Detailed_UIE_factor Immediate_FDI_Orbis Aggregate_UIE_factor Detailed_RT_factor /*
*/ Aggregate_RT_factor New_UIE_factor New_UIE_raw Raw_to_total
replace Inward_FDI_NonSPEs_UIE = New_Inward_FDI_NonSPEs_UIE if UIE_adjustment != "Reported"
replace UIE_adjustment = New_UIE_source if UIE_adjustment != "Reported"
drop New_Inward_FDI_NonSPEs_UIE New_UIE_source Detailed_UIE_factor Immediate_FDI_Orbis Aggregate_UIE_factor Detailed_RT_factor /*
*/ Aggregate_RT_factor New_UIE_factor New_UIE_raw Raw_to_total
sort Year Economy_ISO Investment_from_ISO
save "U:\The Global FDI Network Dataset\Intermediate data\UIE_Orbis.dta", replace

// The Orbis-based dataset for 2013-2017 is appended to the 2009-2012 dataset
use "U:\The Global FDI Network Dataset\Intermediate data\Dataset_1_OECD", replace
keep if Year < 2013
append using "U:\The Global FDI Network Dataset\Intermediate data\UIE_Orbis.dta"
save "U:\The Global FDI Network Dataset\Output data\Dataset_1", replace
export excel using "U:\The Global FDI Network Dataset\Output data\Dataset_1.xlsx", replace firstrow(variables)


**************************************************
**************************************************
************** Create sub-dataset 2a *************
**************************************************
**************************************************


// The shares for inward FDI coming from non-resident SPEs are calculated.

//First step is to calculate outward FDI from resident SPEs as a share of total outward FDI for reporting OECD countries
use "U:\The Global FDI Network Dataset\Input data\OECD_aggregate", replace
drop Inward_all Inward_SPEs Inward_NonSPEs
drop if Outward_SPEs == .
gen Outward_SPE_share_OECD = 100 * (Outward_SPEs / Outward_all)
drop Outward_all Outward_SPEs Outward_NonSPEs
save "U:\The Global FDI Network Dataset\Intermediate data\Outward_SPE_share_OECD", replace

//For all economies, outward FDI from resident SPEs can be calculated.
// Inward FDI in resident SPEs has already been calculated, and we know from reporting OECD countries that outward FDI from resident SPEs 
// tends to be slightly higher, e.g. because some SPEs can get additional bank funding.
use "U:\The Global FDI Network Dataset\Input data\OECD_aggregate", replace
collapse (sum) Inward_SPEs Outward_SPEs, by(Year)
gen Outward_inward_SPE_ratio = Outward_SPEs / Inward_SPEs
drop Inward_SPEs Outward_SPEs
merge 1:m Year using "U:\The Global FDI Network Dataset\Output data\Dataset_1"
gen Outward_FDI_SPEs = Outward_inward_SPE_ratio * Inward_FDI_SPEs
collapse (sum) Outward_FDI_SPEs, by(Year Economy)
save "U:\The Global FDI Network Dataset\Intermediate data\Outward_SPEs", replace

//Total outward FDI is now calculated by using mirror data 
use "U:\The Global FDI Network Dataset\Output data\Dataset_1", replace
drop Economy
rename Investment_from Economy 
rename Inward_FDI Outward_FDI
collapse (sum) Outward_FDI, by(Year Economy)

//We can now calculate outward FDI from resident SPEs as a share of total outward FDI for all countries
merge 1:m Year Economy using "U:\The Global FDI Network Dataset\Intermediate data\Outward_SPEs"
drop _merge
replace Outward_FDI = . if Outward_FDI <= 0 // We only calculate outward SPE shares for economies with positive outward FDI
replace Outward_FDI_SPEs = . if Outward_FDI_SPEs < 0 // ... and we also exclude economies with negative outward FDI from resident SPEs
gen Outward_SPE_share = 100 * Outward_FDI_SPEs / Outward_FDI
replace Outward_SPE_share = 0 if Outward_SPE_share == . // If the share cannot be calculated, it is set to zero.
// Outward FDI is calculated net so the share can be higher than 100%, which we do not allow here.
// We set the upper limit to 95%, which is roughly equal to Luxembourg's share.
replace Outward_SPE_share = 95 if Outward_SPE_share > 95 
save "U:\The Global FDI Network Dataset\Intermediate data\Outward_SPE_share_all", replace

//We create a dataset with the reported outward FDI from resident SPEs as a share of total outward FDI for OECD countries
// and the estimated share for other countries.
use "U:\The Global FDI Network Dataset\Intermediate data\Outward_SPE_share_OECD", replace
merge 1:1 Year Economy using "U:\The Global FDI Network Dataset\Intermediate data\Outward_SPE_share_all"
replace Outward_SPE_share_OECD = Outward_SPE_share if Outward_SPE_share_OECD == .
keep Economy Year Outward_SPE_share_OECD
rename Economy Investment_from
rename Outward_SPE_share_OECD Outward_SPE_share
save "U:\The Global FDI Network Dataset\Intermediate data\Outward_SPE_share_final", replace

//Information about outward FDI from resident SPEs as a share of total outward FDI is now used to calculate 
//shares for inward FDI coming from non-resident SPEs 
use "U:\The Global FDI Network Dataset\Output data\Dataset_1", replace
merge m:1 Year Investment_from using "U:\The Global FDI Network Dataset\Intermediate data\Outward_SPE_share_final"
gen Inward_FDI_nonresident_SPEs = Inward_FDI * Outward_SPE_share / 100
collapse (sum) Inward_FDI Inward_FDI_nonresident_SPEs, by(Year Economy)
replace Inward_FDI = . if Inward_FDI <= 100 // We only calculate SPE shares for economies with inward FDI of at least USD 100 million
replace Inward_FDI_nonresident_SPEs = . if Inward_FDI_nonresident_SPEs < 0 // ... and we also exclude economies with negative inward FDI from nonresident SPEs
gen Inward_SPE_share = 100 * Inward_FDI_nonresident_SPEs / Inward_FDI
replace Inward_SPE_share = . if Inward_SPE_share > 100 // Inward FDI is calculated net so the share can be higher than 100%, which we do not allow here.
drop Inward_FDI Inward_FDI_nonresident_SPEs
save "U:\The Global FDI Network Dataset\Intermediate data\Sub-dataset_2a", replace

**************************************************
**************************************************
************* Create sub-dataset 2b **************
**************************************************
**************************************************

// The shares of outward FDI going to non-resident SPEs are calculated.
use "U:\The Global FDI Network Dataset\Output data\Dataset_1", replace
drop Economy
rename Investment_from Economy // We are looking at mirror data when calculating the SPE shares for outward FDI. Therefore, the counterpart economy becomes the economy in focus.
rename Inward_FDI Outward_FDI
rename Inward_FDI_SPEs Outward_FDI_nonresident_SPEs
drop if Economy == "Not Specified (including Confidential)"
collapse (sum) Outward_FDI Outward_FDI_nonresident_SPEs, by(Year Economy)
replace Outward_FDI = . if Outward_FDI <= 100 // We only calculate SPE shares for economies with outward FDI of at least USD 100 million
replace Outward_FDI_nonresident_SPEs = . if Outward_FDI_nonresident_SPEs < 0 // ... and we also exclude economies with negative outward FDI in nonresident SPEs
gen Outward_SPE_share = 100 * Outward_FDI_nonresident_SPEs / Outward_FDI
replace Outward_SPE_share = . if Outward_SPE_share > 100 // Outward FDI is calculated net so the share can be higher than 100%, which we do not allow here.
drop Outward_FDI Outward_FDI_nonresident_SPEs
merge 1:1 Year Economy using "U:\The Global FDI Network Dataset\Intermediate data\Sub-dataset_2a"
drop _merge
merge m:1 Economy using "U:\The Global FDI Network Dataset\Input data\CDIS_economies_ISO_codes" 
drop _merge ISO2DIGIT
drop if ISO == "ZZZ"
rename ISO Economy_ISO
order Year Economy_ISO Economy Inward_SPE_share Outward_SPE_share
sort Year Economy_ISO
drop if Year == .
save "U:\The Global FDI Network Dataset\Output data\Dataset_2", replace
export excel using "U:\The Global FDI Network Dataset\Output data\Dataset_2.xlsx", replace firstrow(variables)