Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • putexcel

    Dear All,

    I nested putexcel into a loop. I need to estimate the same model for 500 assets and i would like to export main results in excel using putexcel. Apparently, after 221 replications, stata stops to export results to excel. I have read something about this issue, but i cannot remember where and, eventually, i could be wrong. Could you please give me some suggestions about? Is it true that after 221 estimations, stata fails to export results to excel?

    Thanks a lot.

    Dario

  • #2
    It is difficult to comment on code and results we cannot see. Please show the code you are running that fails, and explain exactly what happens after 221 replications - an error message and your loop stops? your loop continues but no more worksheets are added to your workbook? something else?

    What version of Stata are you using?



    Comment


    • #3
      The following example was run on my Mac with Stata 17.0
      Code:
      . about
      
      Stata/SE 17.0 for Mac (Intel 64-bit)
      Revision 05 Oct 2021
      and Excel for Mac Version 16.54 (21101001).
      Code:
      . capture erase wbtest
      
      . forvalues i=1/300 {
        2.     display "sheet `i'"
        3.     putexcel set wbtest, modify sheet(ws_`i')
        4.     quietly putexcel a1 = `i'
        5.     quietly putexcel b2 = 42
        6.     putexcel close
        7. }
      sheet 1
      sheet 2
      sheet 3
        ...
      sheet 298
      sheet 299
      sheet 300
      
      .
      Which produced a workbook whose last worksheet is
      Click image for larger version

Name:	image_24468.png
Views:	1
Size:	431.7 KB
ID:	1633079

      Comment


      • #4
        William Lisowski thanks. I try to put the results of more estimations into the same sheets. After making and storing 221 results, stata simply stops in feeding the excel file. I can run all the 300 estimations, but it stores only 221 results. That is why I was wondering whether there is a limit of 221 estimates that can be stored in a single sheet. In the example you made you generated 300 sheets. Instead I am trying to include everything in one single sheet.

        I use stata 17 MP4.

        Comment


        • #5
          It remains difficult to comment having yet to see your code. Does your code match my code from post #3? Is your loop preceded by an erase command to ensure that you are starting from a fresh workbook? Does your loop begin with putexcel set that includes the modify option and end with putexcel close? What version of Excel are you running? Did you try running my code from post #3 on your system?

          A simple web search found a reference to an Excel limit of around 225 worksheets the first time a workbook is opened, but then the number can be incresed beyond that after saving, closing, and re-opening the workbook.

          https://www.skipperwbreeders.com/rec...book-have.html

          Based on that potential flaw, I decided to be sure the workbook was closed and at the end of each loop.

          Note that if you are using estimates store to first run and save all your estimates, and then looping over the saved estimates to transfer them to Excel, Stata limits you to storing 300 estimates,
          Last edited by William Lisowski; 24 Oct 2021, 13:27.

          Comment


          • #6
            I see now that you are trying to store all your estimates in the same sheet. This is not something you told us in post #1.

            I still have no idea what your code is or what the estimation results are that you are trying to store.

            This example stores the results table r(table) from each of 300 regressions into a single worksheet, and the picture shows the 300th sheet.

            I have now responded foiur times to your topic. If you seek further help, help me help you by responding to my request that you show your code.
            Code:
            putexcel set wbtest, replace
            sysuse auto, clear
            local row 1
            forvalues i=1/300 {
                display "sheet `i'"
                quietly regress price weight i.foreign
                quietly putexcel a`row' = `i'
                quietly putexcel b`row' = matrix(r(table))
                local row = `row'+10
            }
            putexcel close
            Click image for larger version

Name:	estimates300.png
Views:	1
Size:	317.6 KB
ID:	1633192

            Comment

            Working...
            X