How can I copy the entire Stata code *.do I'm running in an excel file

[Francois Durant]

Hi,

I am running multiple simulations per day and I would like to get all necessary information enclosed in 1 single results file. I already have the dataset and results saved in excel. Specific formatting does not matter, but I would like to save the entire code in Excel in a way that I could easily use it to copy/paste in a *.do file if I need to go back to an older version of the code. Any idea?

[Nick Cox]

I don't know much about MS Excel, which I gather is a spreadsheet application, but can't you store a do-file as text in Excel in a single column of a worksheet? It's not much use, however, outside Stata and/or for people who don't know or use Stata.

I suspect I am not really understanding your situation or goals here.

[Joro Kolev]

I am also pretty sure this exporting to, and importing back from, a foreign program like Excel is not wise at all..

What I do in your situation is to keep everything in the same directory, and with adopted meaningful name convention. Say do files are SimulationDo, log files are SimulationLog, the resulting dta files SimulationDta, and then simulations are typically indexed by some parameters, which I change in different experimental designs. Say if I am doing simulations on autoregression under heteroskedasticity and two parameters define this, the log and dta files become something like Simulation_Rho0.9_Het2Dta and Simulation_Rho0.9_Het2Log.

Note also that you can pass arguments to your do files, and this can avoid proliferation of do files with different names which do the same thing in principle, but for different parameter values ((see the documentation in -[R] do- and Gould and Lv, Stata
FAQ How can I pass arguments to my do-files?).

[Paul Dickman]

I don't either know much about ME Excel, but I understand a single MS Excel file can contain multiple "worksheets".

If I understand the question, you have a .do file that writes the data and simulation results to an Excel file (presumably the data to one worksheet and results to another worksheet) and would like to also write the content of the .do file to another sheet in the same Excel file.

I don't have an answer for you, but here are some ideas. Here's a recent thread on how to get access to the path/filename of the .do file from within the .do file (turns out this is not trivial).

https://www.statalist.org/forums/for...n-that-do-file

If you know how to do what you want from your OS command line then, once you have the filename of the do file, you could use the Stata -shell- command to write the do file to your Excel worksheet.

I was interested in your question and a quick google search showed that one can write to Excel sheets via bash or python. With Stata 16 you can embed and execute python code in your do file, so that might to an option. There may be a simple solution, but I am not aware of it.

Your OS and Stata version may be relevant here.

[Mike Lacy]

I share some of the wonderings about the usefulness of doing this, but that being said, here's a Stata code snippet to read a text file and write it to an Excel file with one line per row. My version of Excel complains a little about the resulting file, but reads it ok. The main trick here is to use -fileread()-.

clear
set obs 1
gen strL f = fileread("My Do File.do")
// end of line characters used in my do file.
local eoln = char(13) + char(10)
// every line becomes a variable
split f, gen(s) parse("`eoln'")
local nlines = r(nvars)
putexcel set "My Excel File.xslx"
forval i = 1/`nlines' {
    quiet putexcel A`i'= s`i'
}
putexcel close

[Leonardo Guizzetti]

This idea of saving your source code inside your results file seems to be solving a problem that doesn't exist, so to speak. Moreover, it's going to be cumbersome to extract the source that goes with the simulations should that need ever arise. What I would recommend is to structure your output such that the do-file and its results exist in one common directory. That way, the results and its originating do file coexist in one place, and both files are readable in their respective native formats. If you need to send these results somewhere to someone, then it's just two files instead of one, or if desired, you can zip the directory and send the zip-file.

[Francois Durant]

I am running multiple simulations, modifying the Stata code as I go and currently saving data & results in one unique Excel file. Sometimes, I want to go back to an earlier version of the code that needs to correspond exactly to the results I see in the Excel archive. It takes me sometimes hours to understand which modification of the code generated previous results because I have operated many changes in the .do file between different sets of results.

One way to make sure that the code, results and data all correspond one to another is to save it in one single file (different tabs in Excel). Hence my initial question.

I am sure there are different ways to achieve that purpose. Currently, implementing what I describe would demand minimum re-organization of my files and should demand minimum effort to code (I'm hoping at least). Hope it makes sense!

[John DSouza]

Hi

If the aim is to avoid spending hours understanding which modification of the code produced the results, you can try something along these lines. Put the code doing the simulations in a file called "Simulations.do", but have the code saving the results outside that file.

This code doesn't save the syntax in an Excel, as you asked. But I think it's sufficient for the purposes you suggested in #1.

local ctime = c(current_time)
local ctime = subinstr("`ctime'",":","-",.)      // Filenames can't include ":".
local cdate = c(current_date)
copy "Simulations.do" "Syntax-`cdate'-`ctime'.do"  // Create a copy of the current version of the syntax file.
include "Syntax-`cdate'-`ctime'.do"
export excel "Results.xlsx", sheet("Sim-`cdate'-`ctime'")

It should save the syntax file with a time and date attached and have the Excel worksheet labelled with the same time and date.

[Francois Durant]

Thanks a lot!

[Francois Durant]

John DSouza and others: How can I modify this code to save 1 copy of the code per day? I'm running the code sometimes 100x per day and I would like to avoid creating 1000s of files that I would have a hard time to keep track of. 1 file per day would be ideal. Please let me know. Thanks!

Actually, I got it. Thanks anyways!