*This sheet takes the data from Milesi-Fareti's external wealth of nations database and converts it from by country to by year

*First, let's get world GDP summed by year

clear all

import excel "Raw Data.xlsx", sheet("Dataset") firstrow 
drop if Country == "Euro Area" | Country == "ECCU" 	

*To ensure that GDP values are only kept if the other variables are also present
replace GDPUS = . if Totalassetsexclgold == . | Totalliabilities == .


keep GDPUS Year Country

encode Country, gen(CountryString)


drop Country

reshape wide GDPUS, i(Year) j(CountryString)

egen GDP_Total = rowtotal(GDPUS*)

keep Year GDP_Total

save worldGDP, replace

*Now, let's do the same for Total assets

clear all

import excel "Raw Data.xlsx", sheet("Dataset") firstrow  

drop if Country == "Euro Area" | Country == "ECCU" 	

*To ensure that Total Assets values are only kept if the other variables are also present
replace Totalassetsexclgold = . if GDPUS == . | Totalliabilities == .

keep Totalassetsexclgold Year Country

encode Country, gen(CountryString)

drop Country

reshape wide Totalassetsexclgold, i(Year) j(CountryString)

egen Assets_total = rowtotal(Totalassetsexclgold*)

keep Year Assets_total

save worldAssets, replace

*Now, let's do the same for total liabilities

clear all

import excel "Raw Data.xlsx", sheet("Dataset") firstrow 

drop if Country == "Euro Area" | Country == "ECCU" 	
*To ensure that Total Assets values are only kept if the other variables are also present
replace Totalliabilities = . if GDPUS == . | Totalassetsexclgold == .


keep Totalliabilities Year Country

encode Country, gen(CountryString)

drop Country

reshape wide Totalliabilities, i(Year) j(CountryString)

egen Liabilities_total = rowtotal(Totalliabilities*)

keep Year Liabilities_total

save worldLiabilities, replace
*Now that we have the same for all three, we merge it into one big spreadsheet with just GDP, liabilities, and assets for all countries each year
clear all

use worldGDP
merge 1:1 Year using worldAssets
drop _merge
merge 1:1 Year using worldLiabilities
drop _merge

*We divide through to create variables showing liabilities and GPD as a percentage of GPD

gen Asset_World = Assets_total/GDP * 100
label var Asset_World "Total World Assets Excluding Gold as a Percentage of World GDP"
gen Liabilities_World = Liabilities_total/GDP * 100
label var Liabilities_World "Total World Liabilities as a Percentage of World GDP"

keep Year Asset_World Liabilities_World

export excel using "Calculated Data.xlsx", firstrow(varlabels) replace
