putexcel: getting slower and slower

[Nick Bornschein]

Hi,

I've written Stata (14.1 SE) do-File for 500+ lines Excel tables via putexcel command, including formulas. Using it and putting it in the first Excel sheet of the Excel file takes 3min to run. Now I wanted to add another sheet in the same Excel file because of a group selection. Now it takes - for the same code - 7min to put the 2nd sheet to the Excel file. Sheet number 3 takes 15min (same file) and sheet number 4 takes 40min... Since I would like to put a lot sheets in the same Excel file it seems impossible to do so. So what's the problem for Stata and how to solve?

-Nick

[wbuchanan]

Hi @Nick Bornschein,

Not sure what specifically your problem is because you didn't include any of the code you are using. So it could be any one of myriad issues negatively affecting performance. That said, I would think there is a much more efficient solution than what it sounds like you are currently doing.

[Isaac Maddow-Zimet]

Hi Nick,

I'm not sure why Stata is getting slower with each iterative sheet, but if you are writing out each row individually, I'm fairly sure Stata is opening and closing Excel each time it executes the putexcel command (which can slow things down considerably). One way to make the code more efficient would be to build a matrix of your results row by row in Stata, and then -putexcel- the full matrix once it is built (or -putexcel- blocks of matrices, if formatting needs to apply to each differently). You would need to do a different matrix for each sheet, but it would probably speed up your code considerably.

The problem is that this solution won't work for outputting formulas (just numbers) - is there any way to do whatever calculations the formulas are doing within Stata?

Does this help?
Isaac

[Jeph Herrin]

An additional thought: I've found that -putexcel- is more efficient (and more stable) writing to .xls files than .xlsx files, so I always use the former.

Jeph

[Andrey Ampilogov]

Nick Bornschein, thank you for raising a hand about Stata performance on writing to Excel via -putexcel-.
I am facing exactly the same problem with my ado program - I have multiple tables of the same size, same labels, but with each new table it takes more time to write the results into Excel. I would say, it takes ~30% more time to write a table comparing to the previous one, making a huge cumulative effect.

I have already placed all labels into string matrices using mata, the table values are in matrices too but that does not solve the performance degradation issue.
Writing to XLS instead of XLSX incredibly boost the speed, however, Excel formatting is broken - cell borders are partially missing, cell alignment works only on the first table out of 5.

That's a riddle I can't solve - even the huge difference between writing to *.xls or *.xlsx file.