Format Excel Sheet Using Stata

[Pascal Meier]

Hello everyone,

for replication purposes, I am preparing a code that is exactly reflecting the results (i.e., tables and figures) in my paper.

The results are produced using the following code:

rdrobust Y X, c(0) vce(cluster Z) all
outreg2 using test.xls, replace nonote dec(3) addtext(FE, No, Controls, No) addstat(Bandwidth Regression, e(h_l), #L, e(N_h_l), #R, e(N_h_r)) symbol(***,**,*) alpha(0.001, 0.01, 0.05)

Data example:

input float(Y X Z)
1  -8 1
0  -8 1
1 -10 1
1 -11 1
1  -8 1

The output in Excel is the same in terms of the content. However, the format is (obviously) different. This is not bad per se, but I want to align the format more closely with my tables in the paper. For instance, in the paper, the number of observations is in the last row of the corresponding table (in the excel output, it is before the "FE" row). Additionally, I want to delete two rows in the excel output, which are not necessary but produced by the command rdrobust.

Thus, is there a STATA command (such as putexcel) where I can modify an existing excel file, in particular, move one row and delete two rows. Or can this be done already at the stage of using outreg2?

If of help, I can provide a dataset.

Best regards and thanks for the help,
Pascal

[Daniel Schaefer]

Many things are possible given enough time and energy, but let me ask you this: why not just go into the excel file and manually delete the two extra rows and move the one row?

[Pascal Meier]

Many things are possible given enough time and energy, but let me ask you this: why not just go into the excel file and manually delete the two extra rows and move the one row?

Because the do-file serves replication purposes. Independent researchers / analysts should be able to exactly replicate the findings when running the do-file, preferably in a similar format than displayed in the paper. I would therefore like to delete unnecessary rows and arrange the rows accordingly.

[Daniel Schaefer]

Okay, sure, that makes a certain amount of sense. You can indeed get low level control of the output with -putexcel-. You can collect the results and information from the return values of the regression manually, then use -putexcel- to place that information in a particular cell, and procedurally format the cell yourself. This will give you very tight control over how the table is formatted, but there are two drawbacks to attempting this kind of thing. First, this will probably require you to spend quite a bit of time writing a low level algorithm to build your table. It's far from impossible to do this, but it will likely be a little tedious. Second, this will make your do file more difficult to follow for any reader trying to understand what you've done in order to reproduce your results. It is important to keep the readability of your code in mind, especially if you plan to make it public. I see this as being a lot of work for everyone involved with little upside, but you may be perfectly justified if you feel differently.

Unfortunately, I'm afraid I don't know of a straightforward one-command way to do this - although maybe some other poster does. In the mean time, you might want to take a close look at Stata 17's built-in table building features, but I'm still not necessarily sure this will give you the kind of low level control you seem to be looking for. Sorry I couldn't be of more help.

EDIT: I should be very clear about this since I think it is at the heart of what you are asking: I just reread the -putexcel- documentation, and I don't think there is a procedure to delete or move a row. I think you will have to build the table yourself if you decide to go this rout. I also looked over the low level xl class documentation and came to the same conclusion.

[Pascal Meier]

Okay, sure, that makes a certain amount of sense. You can indeed get low level control of the output with -putexcel-. You can collect the results and information from the return values of the regression manually, then use -putexcel- to place that information in a particular cell, and procedurally format the cell yourself. This will give you very tight control over how the table is formatted, but there are two drawbacks to attempting this kind of thing. First, this will probably require you to spend quite a bit of time writing a low level algorithm to build your table. It's far from impossible to do this, but it will likely be a little tedious. Second, this will make your do file more difficult to follow for any reader trying to understand what you've done in order to reproduce your results. It is important to keep the readability of your code in mind, especially if you plan to make it public. I see this as being a lot of work for everyone involved with little upside, but you may be perfectly justified if you feel differently.

Unfortunately, I'm afraid I don't know of a straightforward one-command way to do this - although maybe some other poster does. In the mean time, you might want to take a close look at Stata 17's built-in table building features, but I'm still not necessarily sure this will give you the kind of low level control you seem to be looking for. Sorry I couldn't be of more help.

EDIT: I should be very clear about this since I think it is at the heart of what you are asking: I just reread the -putexcel- documentation, and I don't think there is a procedure to delete or move a row. I think you will have to build the table yourself if you decide to go this rout. I also looked over the low level xl class documentation and came to the same conclusion.

Dear Daniel,

thanks a lot for your effort and help! I agree, the readability of the code might be more important than the table being format exactly as in the paper. I will take that into account.

Anyways, thanks again, this will also help me in the future!

Best regards,
Pascal