Export observations to particular cells in Excel

[Iulian Ihnatov]

Dear All,
I have the following problem: I need to fill in some particular cells in an Excel sheet, with monthly observations from selected variables in my dataset.


The dataset looks like this:

Year Month    aa       bb       cc       dd      ee       ff  ...
2022   1      aa(1)    bb(1)    cc(1)    dd(1)   ee(1)    ff(1)...
2022   2      aa(2)    bb(2)    cc(2)    dd(2)   ee(2)    ff(2)...
......
2023   1      aa(13)   bb(13)   cc(13)   dd(13)  ee(13)   ff(13)...
2023   2      aa(14)   bb(14)   cc(14)   dd(14)  ee(14)   ff(14)...
........

The Excel sheet will look like this:

                    ColumnToFill
RowToFill        H        K        N         Q ....    
4                aa(13)   aa(14)   aa(15)    aa(16)
6                bb(13)   bb(14)   bb(15)    bb(16)
11               cc(13)   cc(14)   cc(15)    cc(16)
13               dd(13)   dd(14)   dd(15)    dd(16)
18               ee(13)   ee(14)   ee(15)    ee(16)
.....

I found the following solution (Stata 16.1 version).

use mydata

****** create pairs of variables and Excel rows - each variable will pair with a different row in the sheet
local var1 "aa bb cc dd ee"               // variables to be exported
local var2 "4 6 11 13 18"                  // Excel rows
local n : word count `var2'                // count no. of pairs (variable, row)

****** loop through each pair
forvalues i = 1/`n' {
    local ob : word `i' of `var1'            // select the variable to export
    local r : word `i' of `var2'              // select the row where the variable will be exported to
    local m=1                                 // month to start with
    foreach c in H K N Q T W {            // loop through the selected columns of the Excel sheet
        export excel `ob' using "Analysis.xlsx" if year==2023&month==`m', sheet("BVC", modify) cell(`c'`r') keepcellfmt
        local ++m                            // next month
    }
}

I have to fill cell-by-cell a maximum of 396 cells (33 rows x 12 columns) in a single sheet, so Stata opens and saves the Excel file for each cell. There is a second sheet in the same file that needs a similar approach.

I tested this code and it worked perfectly, but I wonder if there was a more efficient solution.

Thank you in advance!

Iulian

[Daniel Schaefer]

Yes, you can do this with putexcel. putexcel set with the open option will load the sheet into memory, then each subsequent call will edit the sheet, so the machine doesn't have to reopen the sheet each time a cell is written to. Here is some code to get you started, although I haven't tested this so it may contain errors or typos.

use mydata

****** create pairs of variables and Excel rows - each variable will pair with a different row in the sheet
local var1 "aa bb cc dd ee" // variables to be exported
local var2 "4 6 11 13 18" // Excel rows
local n : word count `var2' // count no. of pairs (variable, row)

putexcel set Analysis.xlsx, open replace 

****** loop through each pair
forvalues i = 1/`n' {
    local ob : word `i' of `var1' // select the variable to export
    local r : word `i' of `var2' // select the row where the variable will be exported to
    local m=1 // month to start with
    foreach c in H K N Q T W { // loop through the selected columns of the Excel sheet
        putexcel `c'`r'=`ob' if year==2023&month==`m'
        local ++m // next month
    }
}
putexcel save

Keep in mind, there is a known issue with putexcel that can sometimes prevent the set of commands from maintaining an open file connection. See here.

[Daniel Schaefer]

Looks like if isn't allowed with putexcel, so this line wont work.

putexcel `c'`r'=`ob' if year==2023&month==`m'

Are you looking up a single, specific value here? If so, you should be able to get that value like so:

quietly list `ob' if if year==2023&month==`m'
local value = `ob'[1]
di `value'

Putting it all together:

use mydata

****** create pairs of variables and Excel rows - each variable will pair with a different row in the sheet
local var1 "aa bb cc dd ee" // variables to be exported
local var2 "4 6 11 13 18" // Excel rows
local n : word count `var2' // count no. of pairs (variable, row)

putexcel set Analysis.xlsx, open replace

****** loop through each pair
forvalues i = 1/`n' {
    local ob : word `i' of `var1' // select the variable to export
    local r : word `i' of `var2' // select the row where the variable will be exported to
    local m=1 // month to start with
    foreach c in H K N Q T W { // loop through the selected columns of the Excel sheet
        quietly list `ob' if if year==2023&month==`m'
        putexcel `c'`r'=`ob'[1]
        local ++m // next month
    }
}
putexcel save

Again, the last code block is untested, but I hope that helps.

[Iulian Ihnatov]

Thank you very much, Daniel!
The code runs instantaneous.
However, the output is not as expected.
The - list- command displays the appropriate (single) value for each loop.
For example, while the -list- will display the obs no [256] for aa variable, which is correct, the H4 cell will be filled with the obs no [1].
The problem is:

 putexcel `c'`r'=`ob'[1]

I should somehow use `ob'[256], so I need to recall the line number from the -list- output. The -return list- won't show this in a scalar or macro. Is there a possibility to solve this issue?

[Daniel Schaefer]

Wow, I sure made a silly mistake there! I'm afraid I don't know of an elegant way to do this, although someone else here might. It looks like the list command doesn't actually give useful return values (and the above wouldn't be the way to extract them anyway.) I sincerely apologize. The following is an ugly way to get the index, but should still be pretty efficient in terms of processor time! O(N).

local index = -1
forv i = 1/`=_N'{
    if year[i] == 2023 & month[i] == `m' {
        local index = `i'
        continue, break
    }
}
di `index' // for debug purposes
putexcel `c'`r'=`ob'[`index']

Still assuming there is exactly one match of course.

[Iulian Ihnatov]

This is the solution, Daniel!

It works like a charm! About 10 times faster than my initial code, although it needs additional loops.
I integrated your last code to the initial one, with some minor changes and additions:
- I'm not replacing the Excel file/sheet, but I'm modifying it, as it is already formatted with nice colors and has formulae in many cells.
- I needed to add quotation marks for the "e" in the last loop (i.e., year[`e']).
That's all!

The Stata users that are searching for a similar solution may confidently use the following (integrated) code. I included some comments to make it easier to understand.

use mydata  

****** create pairs of Stata variables and Excel rows - each variable will pair with a different row in the sheet
local var1 "aa bb cc dd ee" // variables to be exported
local var2 "4 6 11 13 18" // Excel rows
local n : word count `var2' // count no. of pairs (variable, row)  

putexcel set Analysis.xlsx, open modify sheet("BVC") // the option modify will keep all the existing formatting (and existing formulae in the cells that will not be filled by Stata)  

****** loop through each pair
forvalues i = 1/`n' {    
    local ob : word `i' of `var1' // select the variable to export   
    local r : word `i' of `var2' // select the row where the variable will be exported to    
    local m=1 // month to start with   
         foreach c in H K N Q T W { // loop through the selected columns of the Excel sheet      
         local index = -1        
                forv e = 1/`=_N'{ //these two loops identify the unique line in the dataset, corresponding to year 2003, month `m'          
                        if year[`e'] == 2023 & month[`e'] == `m' {              
                               local index = `e'              
                               continue, break          
                         }        
                 }
                 di "`c'`r'" " " `index' " " "`ob'" " " `ob'[`index'] // for debug purposes        
                 putexcel `c'`r'=`ob'[`index']        
                 local ++m // next month    
          }
}
putexcel save