Export Excel Loop unable to save Excel file

[Dennis Hamilton]

I am using export excel

in Stata 15.0.

My data consists of 1,815 standardized daily trade summaries, which I currently have in 1,815 different Excel files (see May 16, 2011 example below).

* Example generated by -dataex-. To install: ssc install dataex
clear
input str58 A str102 B str16 C str8 D str10 E
""                                                           ""                                                                                                       ""                 ""         ""         
""                                                           ""                                                                                                       ""                 ""         ""         
""                                                           ""                                                                                                       ""                 ""         ""         
""                                                           ""                                                                                                       ""                 ""         ""         
"FINRA-INTERACTIVE DATA: Structured Trading Activity Report" ""                                                                                                       ""                 ""         ""         
""                                                           "DATA AS OF:"                                                                                            "16may2011"        ""         ""         
""                                                           ""                                                                                                       ""                 ""         ""         
""                                                           "ASSET CLASS"                                                                                            "FNMA"             ""         ""         
""                                                           ""                                                                                                       "TRADE"            "UNIQUE"   "$ TRADES" 
""                                                           ""                                                                                                       "COUNT"            "SEC ID'S" "(000'S)"  
""                                                           "AGENCY PASS-THRU (TBA, STIP, $ ROLLS)"                                                                  ""                 ""         ""         
""                                                           "SINGLE FAMILY 15Y"                                                                                      "865"              "16"       "15018790.3"
""                                                           "SINGLE FAMILY 30Y"                                                                                      "2374"             "23"       "58189420.3"
""                                                           "OTHER"                                                                                                  "*"                "*"        "*"        
""                                                           "AGENCY PASS-THRU (SPECIFIED)"                                                                           ""                 ""         ""         
""                                                           "SINGLE FAMILY 15Y"                                                                                      "424"              "298"      "1549436.5"
""                                                           "SINGLE FAMILY 30Y"                                                                                      "625"              "351"      "3383242.5"
""                                                           "ADJUSTABLE/HYBRID"                                                                                      "64"               "57"       "396725.5" 
""                                                           "OTHER"                                                                                                  "0"                "0"        "0"        
""                                                           "AGENCY CMO"                                                                                             ""                 ""         ""         
""                                                           "P&I"                                                                                                    "325"              "148"      "223160.9" 
""                                                           "IO/PO"                                                                                                  "15"               "5"        "127288.7" 
""                                                           ""                                                                                                       ""                 ""         ""         
""                                                           "ASSET CLASS"                                                                                            "INVESTMENT GRADE" ""         ""         
""                                                           ""                                                                                                       "TRADE"            "UNIQUE"   "$ TRADES" 
""                                                           ""                                                                                                       "COUNT"            "SEC ID'S" "(000'S)"  
""                                                           "NON-AGENCY CMO"                                                                                         ""                 ""         ""         
""                                                           "P&I"                                                                                                    "274"              "112"      "55396.6"  
""                                                           "IO/PO"                                                                                                  "0"                "0"        "0"        
""                                                           "CMBS"                                                                                                   ""                 ""         ""         
""                                                           "P&I"                                                                                                    "108"              "78"       "493221.9" 
""                                                           "IO/PO"                                                                                                  "61"               "11"       "5682863.4"
""                                                           "ABS"                                                                                                    "146"              "99"       "393618.9" 
""                                                           "CBO/CDO/CLO"                                                                                            "18"               "13"       "606495.2" 
""                                                           "OTHER"                                                                                                  "*"                "*"        "*"        
""                                                           ""                                                                                                       ""                 ""         ""         
""                                                           "* Indicates trade count is less than 5"                                                                 ""                 ""         ""         
""                                                           "† Includes Unrated Securities and Unrated New Issues "                                                ""                 ""         ""         
""                                                           "Note:  Data updated with current day's trades at approximately 8PM New York time"                      ""                 ""         ""         
""                                                           "These reports can be found online at http://www.finra.org and http://www.interactivedata.com"           ""                 ""         ""         
""                                                           "For additional information regarding the reports please contact TRACE Data Services at (888) 507-3665." ""                 ""         ""         
end

My objective is to compile these 1,815 daily observations into a single Excel file in time series format. Finally, I can import that single file to Stata and perform my analyses. To do so, I've created the following loop.

  forval x = 1/1815 {
  
  clear
  import excel "$dailysum\FINRA_IDC_STAR-day`x'.xls", ///
      sheet("TradingActivity") firstrow
     
  /*Cleaning the data imported from Excel*/
      drop A
      drop if B == ""
  
  local vars C D E F G H I J K L M N
  
  foreach y of local vars {
      replace `y' = "1" if `y' == "*"
      replace `y' = "1" if `y' == "0"
 
  }

  /*Exporting desired variables from distinct Excel files into single file*/

  export excel C using "$dailysum\compilation.xlsx" if ///
  B == "SINGLE FAMILY 15Y" & B[_n-1] == "AGENCY PASS-THRU (TBA, STIP, $ ROLLS)", ///
  sheet("FNMA-TradeCount") cell(A`x') sheetmodify nolabel

}

I've tested it, and it does exactly what I want if I do one day at a time. The problem is, the loop only works for 2-10 days (i.e. loops) before giving me the following error message. Here you can see a couple successful saves followed by the error.


file Dropbox\Research_Data\TRACE\Daily Trade Reports - Structured Products\compilation.xlsx saved
(12 observations deleted)
(2 real changes made)
(1 real change made)
(2 real changes made)
(1 real change made)
(2 real changes made)
(1 real change made)
(0 real changes made)
(2 real changes made)
(0 real changes made)
(2 real changes made)
(0 real changes made)
(2 real changes made)
(0 real changes made)
(1 real change made)
(0 real changes made)
(1 real change made)
(0 real changes made)
(1 real change made)
(0 real changes made)
(7 real changes made)
(0 real changes made)
(7 real changes made)
(0 real changes made)
(7 real changes made)
file Dropbox\Research_Data\TRACE\Daily Trade Reports - Structured Products\compilation.xlsx saved
(12 observations deleted)
(1 real change made)
(2 real changes made)
(1 real change made)
(2 real changes made)
(1 real change made)
(2 real changes made)
(0 real changes made)
(3 real changes made)
(0 real changes made)
(3 real changes made)
(0 real changes made)
(3 real changes made)
(0 real changes made)
(1 real change made)
(0 real changes made)
(1 real change made)
(0 real changes made)
(1 real change made)
(0 real changes made)
(7 real changes made)
(0 real changes made)
(7 real changes made)
(0 real changes made)
(7 real changes made)
file Dropbox\Research_Data\TRACE\Daily Trade Reports - Structured Products\compilation.xlsx could not be saved

Thanks for you help!

[Carole J. Wilson]

It could be that Dropbox is locking the file to sync. Can you try to turn off Dropbox? Or, save the file to a path on your harddrive?

[Clyde Schechter]

Another possibility is that Stata is running through the loop faster than your OS can keep up with writing the file, and the OS at some point tells Stata that it can't handle it any more. Remember that calculations are orders of magnitude faster than read-write operations. I do think that Carole Wilson's diagnosis is more probable than mine, so I would try her solution first. But if that doesn't work out, you can solve this by putting a -sleep- command into the loop just before the -export excel- command. (See -help sleep-) There is no real science to how long a sleep to order because it really depends on the details of your system and varies with other demands the OS is facing at the time of execution. My own approach is to start with -sleep 250- and work up in increments of 250 until the loop runs without difficulty.

[Robert Picard]

Setting aside the issue of OS file syncs, why are you importing Excel data into Stata and then exporting it again into Excel so that you can import it back to Stata? Why not simply append the results as you go from within the loop?

Here's how I like to handle these problems using filelist (from SSC) and runby (also from SSC):

clear all
global dailysum "/Users/robert/Documents/statalist/2018/08/27/Daily Trade Reports - Structured Products"
filelist , dir($dailysum) pattern("*.xls")
list

program get_what_I_want
    local fpath = dirname + "/" + filename
    import excel "`fpath'", clear
    drop A
    drop if B == ""
    foreach v in C D E F {
        replace `v' = "1" if inlist(`v', "*", "0")        
    }
    keep if B == "SINGLE FAMILY 15Y" & B[_n-1] == "AGENCY PASS-THRU (TBA, STIP, $ ROLLS)"
end

runby get_what_I_want, by(dirname filename) verbose

Here's the results of the list command that shows what filelist picked up:

. filelist , dir($dailysum) pattern("*.xls")
Number of files found = 10

. list

     +----------------------------------------------------------------------------------------------------------------------------+
     | dirname                                                                                  filename                    fsize |
     |----------------------------------------------------------------------------------------------------------------------------|
  1. | /Users/robert/Documents/statalist/2018/08/27/Daily Trade Reports - Structured Products   FINRA_IDC_STAR-day1.xls    10,240 |
  2. | /Users/robert/Documents/statalist/2018/08/27/Daily Trade Reports - Structured Products   FINRA_IDC_STAR-day10.xls   10,240 |
  3. | /Users/robert/Documents/statalist/2018/08/27/Daily Trade Reports - Structured Products   FINRA_IDC_STAR-day2.xls    10,240 |
  4. | /Users/robert/Documents/statalist/2018/08/27/Daily Trade Reports - Structured Products   FINRA_IDC_STAR-day3.xls    10,240 |
  5. | /Users/robert/Documents/statalist/2018/08/27/Daily Trade Reports - Structured Products   FINRA_IDC_STAR-day4.xls    10,240 |
     |----------------------------------------------------------------------------------------------------------------------------|
  6. | /Users/robert/Documents/statalist/2018/08/27/Daily Trade Reports - Structured Products   FINRA_IDC_STAR-day5.xls    10,240 |
  7. | /Users/robert/Documents/statalist/2018/08/27/Daily Trade Reports - Structured Products   FINRA_IDC_STAR-day6.xls    10,240 |
  8. | /Users/robert/Documents/statalist/2018/08/27/Daily Trade Reports - Structured Products   FINRA_IDC_STAR-day7.xls    10,240 |
  9. | /Users/robert/Documents/statalist/2018/08/27/Daily Trade Reports - Structured Products   FINRA_IDC_STAR-day8.xls    10,240 |
 10. | /Users/robert/Documents/statalist/2018/08/27/Daily Trade Reports - Structured Products   FINRA_IDC_STAR-day9.xls    10,240 |
     +----------------------------------------------------------------------------------------------------------------------------+

.

In each of these files, I had added an F column with the same number as in the file's name (the rest of the file remains as you posted in #1). Here's the rest of the output:

. runby get_what_I_want, by(dirname filename) verbose
(12 observations deleted)
(4 real changes made)
(4 real changes made)
(4 real changes made)
(0 real changes made)
(28 observations deleted)
(12 observations deleted)
(4 real changes made)
(4 real changes made)
(4 real changes made)
(0 real changes made)
(28 observations deleted)
(12 observations deleted)
(4 real changes made)
(4 real changes made)
(4 real changes made)
(0 real changes made)
(28 observations deleted)
(12 observations deleted)
(4 real changes made)
(4 real changes made)
(4 real changes made)
(0 real changes made)
(28 observations deleted)
(12 observations deleted)
(4 real changes made)
(4 real changes made)
(4 real changes made)
(0 real changes made)
(28 observations deleted)
(12 observations deleted)
(4 real changes made)
(4 real changes made)
(4 real changes made)
(0 real changes made)
(28 observations deleted)
(12 observations deleted)
(4 real changes made)
(4 real changes made)
(4 real changes made)
(0 real changes made)
(28 observations deleted)
(12 observations deleted)
(4 real changes made)
(4 real changes made)
(4 real changes made)
(0 real changes made)
(28 observations deleted)
(12 observations deleted)
(4 real changes made)
(4 real changes made)
(4 real changes made)
(0 real changes made)
(28 observations deleted)
(12 observations deleted)
(4 real changes made)
(4 real changes made)
(4 real changes made)
(0 real changes made)
(28 observations deleted)

--------------------------------------
Number of by-groups    =            10
by-groups with errors  =             0
by-groups with no data =             0
Observations processed =            10
Observations saved     =            10
--------------------------------------

. 
end of do-file

. list

     +------------------------------------------------+
     |                 B     C    D            E    F |
     |------------------------------------------------|
  1. | SINGLE FAMILY 15Y   865   16   15018790.3    1 |
  2. | SINGLE FAMILY 15Y   865   16   15018790.3   10 |
  3. | SINGLE FAMILY 15Y   865   16   15018790.3    2 |
  4. | SINGLE FAMILY 15Y   865   16   15018790.3    3 |
  5. | SINGLE FAMILY 15Y   865   16   15018790.3    4 |
     |------------------------------------------------|
  6. | SINGLE FAMILY 15Y   865   16   15018790.3    5 |
  7. | SINGLE FAMILY 15Y   865   16   15018790.3    6 |
  8. | SINGLE FAMILY 15Y   865   16   15018790.3    7 |
  9. | SINGLE FAMILY 15Y   865   16   15018790.3    8 |
 10. | SINGLE FAMILY 15Y   865   16   15018790.3    9 |
     +------------------------------------------------+

.

[Dennis Hamilton]

Thank you all for your quick and useful responses. I saved to my hard drive instead of Dropbox, and that solved my issue. As for Robert, thanks for the efficiency boost! I thought there must be a more direct way, and you provided it. Thanks!