Good morning everyone,
I would like to use (if possible) the putexcel command in a loop to create this result in excel (for each province I would like to calculate the r(mean) of EBITDA for each sector):
EBITDA 2020 2019 2018 2017 2016
agriculture 1 2 3 4 5
electronic 5 4 3 2 1
commerce 6 7 8 9 10
finance 10 9 8 7 6
To create this table in excel I wrote this code (which unfortunately doesn't work):
Code:
global Year1=2016
global YearN=2020
cd "C:\Users\Ricky\Desktop\Interlocking2020\ISP2021\Dati2021\Andamenti"
global route = "C:\Users\Ricky\Desktop\Interlocking2020\ISP2021\Dati2021\Andamenti"


foreach provinces in "Belluno" "Padua" "Rovigo" "Treviso" "Venice" "Verona" "Vicenza" "Gorizia" "Pordenone" "Trieste" "Udine" "Trento" "Bolzano" {    
foreach sector in "agriculture" "electronic" "commerce" "finance" {
forvalues y=$YearN(1)$Year1 {
forvalues j = 1/4 {
tokenize "B C D E F"
forvalues n = 1/5 {
preserve
keep if Provincia=="`provinces'"
keep if SETTORI=="`sector'"
putexcel set "${route}\Andamento`provinces'.xlsx", sheet("Andamento") modify
summarize EBITDA`y'
return list
putexcel ``n''`=1+`j''= `r(mean)'
restore
}
}
}
}
}

putexcel set "${route}\Andamento`provinces'.xlsx", sheet("Andamento") modify
putexcel A1="EBITDA"
putexcel A2="agriculture"
putexcel A3="electronic"
putexcel A4="commerce"
putexcel A5="finance"
putexcel B1="2020"
putexcel C1="2019"
putexcel D1="2018"
putexcel E1="2017"
putexcel F1="2016"
In your opinion, is it possible to achieve the result I would like?
Thank you for any kind of help.