Automate exporting data subsets to different sheets in the same excel file

[Sunita Rao]

I have used the following command to subset my data by country

by country: list CompanyName country Ticker TotalassetsFY2016 NetSalesFY2016 , sep(0)

* Example generated by -dataex-. To install: ssc install dataex
clear
input str88 CompanyName byte country double(TotalassetsFY2016 NetSalesFY2016)
"Central Costanera S.A."                         1    280.27472   124.23352
"Ledesma Sociedad Anónima Agrícola Industrial" 1    438.53456   541.63719
"Grupo Clarín S.A."                             1   2292.23789   719.18135
"Telecom Argentina S.A."                         1   3028.31501  3364.93491
"Banco Hipotecario S.A."                         1   3293.39591   192.48957
"Banco Patagonia S.A."                           1   4558.01967    165.4581
"BBVA Banco Francés S.A."                       1   9591.24708    337.5091
"Grupo Financiero Galicia S.A."                  1  15310.99889   604.07743
"Resource Generation Limited"                    2    128.98499           0
"Spicers Limited"                                2    168.32942   292.53324
"Medusa Mining Limited"                          2      284.625      128.09
"Horizon Oil Limited"                            2      286.672      75.952
"Magellan Financial Group"                       2    292.30752   234.39714
"Emeco Holdings Limited"                         2    318.61437   125.13205
"Blackmores Limited"                             2    330.28793   445.81518
"Senex Energy Limited"                           2    338.29106     51.6162
"Mount Gibson Iron Limited"                      2    352.04082   175.20617
"Ridley Corporation Limited"                     2    361.19492   679.82344
"Sandfire Resources NL"                          2    416.24986   370.36913
"AWE Limited"                                    2    560.03799    76.26104
"Navitas Limited"                                2    611.51712   752.89679
"McMillan Shakespeare Limited"                   2    634.79569   375.95709
"Karoon Gas Australia Ltd"                       2    683.26988     1.19812
"Perseus Mining Limited"                         2    702.55522   182.46806
"JB Hi-Fi Limited"                               2    739.22598  2945.95299
"Paladin Energy Ltd"                             2        791.1       185.4
"Infigen Energy"                                 2    858.59277   129.04906
"DuluxGroup Limited"                             2     916.5692  1315.49381
"Pact Group Holdings Ltd"                        2    1022.8614  1029.04459
"Beach Energy Limited"                           2   1210.93605    415.6889
"Premier Investments Limited"                    2    1262.9651   796.89059
"Myer Holdings Limited"                          2   1485.84818  2005.20564
"Abacus Property Group"                          2   1825.37341   113.91664
"Sims Metal Management Limited"                  2    1915.2233  3474.42917
"Cleanaway Waste Management Limited"             2   2167.69099  1083.99449
"Ansell Limited"                                 2       2290.6      1352.8
"Tabcorp Holdings Limited"                       2   2460.46113  1630.49875
"Nufarm Limited"                                 2   2628.74568  2119.93848
"GrainCorp Limited"                              2   2740.81171  3187.29161
"The Star Entertainment Group Limited"           2   3423.39926  1689.64875
"WorleyParsons Limited"                          2   3740.30618  5792.15555
"Tatts Group Limited"                            2   3898.66503  2181.16214
"Treasury Wine Estates Limited"                  2   3938.24263  1745.66991
"Seven Group Holdings Limited"                   2   3971.31896  1666.62942
"Boral Limited"                                  2    4321.1532  2939.02484
"Virgin Australia Holdings Limited"              2   4500.16762  3124.96741
"Tronox Limited"                                 2         4950        2093
"Orica Limited"                                  2   5055.60879  3902.88583
"Sonic Healthcare Limited"                       2   5490.83249   3738.0117
"Brambles Limited"                               2       7896.3      4900.1
"Aurizon Holdings Limited"                       2   8096.99408  2575.93027
"Mirvac Group"                                   2   8320.48273  1729.05725
"Amcor Limited"                                  2       8682.1      9421.3
"Dexus Property Group"                           2   8777.74053   491.82404
"AGL Energy Limited"                             2  10879.42787  8306.32845
"APA Group"                                      2  11057.23172  1547.53008
"Newcrest Mining Limited"                        2        11191        3295
"Vicinity Centres"                               2  11807.27828   988.11785
"Qantas Airways Limited"                         2  12444.59343 11033.63504
"Stockland Corporation Limited"                  2  12621.14947           0
"LendLease Group"                                2  13851.00756 11240.36205
"Challenger Limited"                             2  15835.36335  1384.43774
"Transurban Group"                               2  17163.92893  1646.36645
"Woolworths Limited"                             2  17519.99702 39862.76044
"Westfield Corporation Limited"                  2      18765.5       567.2
"Fortescue Metals Group Limited"                 2        19337        7083
"Origin Energy Limited"                          2  21533.13219  8534.28688
"Woodside Petroleum Ltd"                         2        24753        4075
"Telstra Corporation Limited"                    2  32246.43349 19302.71538
"QBE Insurance Group Limited"                    2        41583       11578
"Suncorp Group Limited"                          2  71328.64006  9906.50724
"Rio Tinto Limited"                              2        89263       33781
"BHP Billiton Limited"                           2       118953       30912
"Macquarie Group Limited"                        2 151216.23174   5997.7712
"Westpac Banking Corporation"                    2 643239.18318  4473.99686
"Commonwealth Bank of Australia"                 2 695050.47103  5693.74604
"SW Umwelttechnik Stoiser & Wolschner AG"        3     83.54903    64.09796
"FACC AG"                                        3    722.26712   650.69578
"AMAG Austria Metall AG"                         3   1467.04001   956.66209
"Palfinger AG"                                   3   1621.81674  1432.50502
"Österreichische Post AG"                       3    1627.5731  2143.46036
"Wienerberger AG"                                3   3839.51969  3139.26845
"Andritz AG"                                     3   6543.42658  6380.36103
"BKS Bank AG"                                    3   8002.80376     86.7381
"Telekom Austria AG"                             3   8385.08815   4445.7532
"VERBUND AG"                                     3  12180.07812  2754.00296
"OMV Aktiengesellschaft"                         3  33898.44823 20331.46839
"Erste Group Bank AG"                            3 219811.11588  3041.78507
"Hamon & Cie (International) SA"                 4    519.97783   460.84134
"Compagnie Immobilière de Belgique S.A."        4    756.07727   276.75921
"Barco NV"                                       4    1223.7211  1163.66727
"bpost SA/NV"                                    4    2417.7135  2532.88293
"Ontex Group NV"                                 4   2585.24227  2103.87417
"Nyrstar NV"                                     4   3309.30012  2916.92178
"Etex SA"                                        4   3432.57574  2889.07105
"Cofinimmo S.A."                                 4   3864.96569   271.01657
"D'Ieteren SA"                                   4   4044.75879  6831.73229
"Umicore S.A."                                   4   4376.38657 11024.53394
"Etn. Fr. Colruyt NV"                            4   4577.41586 10452.13826
"Telenet Group Holding NV"                       4   5286.02872  2564.25631
end
label values country country
label def country 1 "Argentina", modify
label def country 2 "Australia", modify
label def country 3 "Austria", modify
label def country 4 "Belgium", modify

Now I want to export the data into an excel workbook. Specifically, I want one sheet for every country and each sheet should be labeled by the country name in the same excel workbook. Further, it would be great if TotalassetsFY2016 & NetSalesFY2016 are sorted from the smallest to the largest in every sheet.

How can I achieve this? I would be very grateful for some help.

[Clyde Schechter]

sort country TotalassetsFY2016 NetSalesFY2016

levelsof country, local(countries)
foreach c of local countries {
    export excel my_spreadsheet.xlsx if country == `c', firstrow(variables) ///
        sheet(`"`:label (country) `c''"') sheetreplace
}

[Sunita Rao]

Dear Prof. Schechter,

Thank you so much for your prompt response. I greatly appreciate that! I am now able to proceed with my work.

Your code is super useful and valuable! It works perfectly! I did not know the existence of the -levelsof- command. I learned something new today!

I used it exactly as you suggested. However, it was exporting all the variables in my dataset to the excel. So, I made a slight modification:

sort country TotalassetsFY2016 NetSalesFY2016

levelsof country, local(countries)
foreach c of local countries {
export excel CompanyName country Ticker TotalassetsFY2016 NetSalesFY2016 using "C:\Users\raosunit\Documents\Paper with Norma\By Country\ByCountryTA&NSv2.xlsx" if country == `c', firstrow(variables) ///
sheet(`"`:label (country) `c''"') sheetreplace
}

Again, I am so grateful to you.

--Sunita

PS: is there a good website or a book to learn about using macros along with the -foreach- command? I just want to make those more intuitive for myself.

[Clyde Schechter]

I would start with the PDF documentation that comes with your Stata installation. Run -help macro- and then click on the link "View complete PDF manual entry" that appears near the top of that window. That chapter includes extensive tutorial material.

[Sunita Rao]

Dear Prof. Schechter,

I have 341 sectors in my dataset and I need the same information for each sector in different sheets of the same excel workbook. I used the following code that is similar to what you suggested, but, it is not working:

sort sector TotalassetsFY2016 NetSalesFY2016

levelsof sector, local(sectors)
foreach c of local sectors {
export excel CompanyName sector country Ticker TotalassetsFY2016 NetSalesFY2016 using "C:\Users\raosunit\Documents\Paper with Norma\By Country\BySectorTA&NSv2.xlsx" if sector == `c', firstrow(variables) ///
sheet(`"`:label (sector) `c''"') sheetreplace
}

I am getting the following error:

_xlshwritestr(): 9901 Stata returned error
export_excel_write_file(): - function returned error
export_excel_export_file(): - function returned error
<istmt>: - function returned error
r(9901);

When I click on r(9901), Stata is giving me the following message: No entries found for search on "r(9901), local.

Do you know why? Again , I would be very grateful for your help.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int sector
 1
 1
 1
 1
 2
 3
 4
 5
 6
 6
 6
 7
 7
 7
 7
 7
 7
 7
 7
 7
 7
 7
 7
 7
 7
 7
 7
 7
 7
 7
 7
 7
 8
 9
 9
 9
 9
 9
10
11
11
11
12
12
12
12
12
12
12
12
12
12
12
12
12
12
12
12
12
12
12
12
12
12
12
12
13
13
13
13
13
13
14
14
14
14
15
15
15
16
16
17
17
17
17
18
19
19
20
20
20
20
20
21
21
21
21
21
21
22
end
label values sector sector
label def sector 1 "Abrasive, asbestos, and miscellaneous nonmetallic mineral products", modify
label def sector 2 "Accident and health insurance", modify
label def sector 3 "Accounting, auditing, and bookkeeping", modify
label def sector 4 "Adhesives and sealants", modify
label def sector 5 "Advertising", modify
label def sector 6 "Advertising agencies", modify
label def sector 7 "Agricultural chemicals", modify
label def sector 8 "Agricultural production, livestock and animal specialties", modify
label def sector 9 "Agricultural production-crops", modify
label def sector 10 "Agricultural services", modify
label def sector 11 "Air courier services", modify
label def sector 12 "Air transportation, scheduled", modify
label def sector 13 "Aircraft", modify
label def sector 14 "Aircraft and parts", modify
label def sector 15 "Aircraft engines and engine parts", modify
label def sector 16 "Aircraft parts and equipment", modify
label def sector 17 "Airports, flying fields, and services", modify
label def sector 18 "Amusement and recreation services", modify
label def sector 19 "Amusement parks", modify
label def sector 20 "Analytical instruments", modify
label def sector 21 "Apparel and accessory stores", modify
label def sector 22 "Apparel and other finished products made from fabrics and similar materials", modify

[Sunita Rao]

Dear Prof. Schechter,

I will do exactly what you suggest with the pdf documentation. Thank you one more time.

--Sunita

[Clyde Schechter]

Re #5

The problem is that the labels associated with the variable sector are not legal names for tabs in Excel. So you will have to devise some other scheme for naming the separate sheets within the Excel file.

Added: To be clear, this is an Excel restriction, not a Stata issue.

[Sunita Rao]

Dear Prof. Schechter,

I understand. The sector labels do look very long.

Thank you so much! I am very grateful to you.

--Sunita