Omptimizing the code

[Olena Onishchenko]

Dear Stata Users

My dataset is a panel with daily observations over seven years for 5,000 firms. I use the following code to estimate risk-adjusted returns, but it runs for more than 24 hours without showing any signs of finishing. How can I optimise it to run faster? Thanks.

import delimited "temp_To_matlab_FamaFrench.out", clear
local min_days_per_estimation = 21
local estimation_frequency = 1

* Model definitions:
* 3F = mkt smb hml
* 4F = mkt smb hml mom
* 5F = mkt smb hml rmw cma
* 6F = mkt smb hml mom rmw cma
local model1 "mktexcess smb hml"
local model2 "mktexcess smb hml mom"
local model3 "mktexcess smb hml rmw cma"
local model4 "mktexcess smb hml mom rmw cma"

local model_list `model1' `model2' `model3' `model4'

if `estimation_frequency' == 1 {
gen group_split = dateyearq
}
else {
gen group_split = dateyear
}

sort permno datenum

local model_index = 0

foreach mdl of local model_list {
local model_index = `model_index' +1

local factors `mdl'
local nfactors: word count `factors'

gen ret_FF`nfactors' = .
gen ret_FFrf`nfactors' = .

levelsof permno, local(permnos)

foreach p of local permnos {

display "Processing permno = `p'"
preserve

keep if permno == `p'
tempvar groupid
egen `groupid' = group(group_split)

su `groupid', meanonly
local max_group = r(max)
scalar last_alpha = 0
scalar last_beta_mkt = 1

foreach f of local factors {
if "`f'" != "mktexcess" {
scalar last_beta_`f' = 0
}
}

forvalues g = 1/`=`max_group'-1' {
tempvar sel
gen byte `sel' = (`groupid' == `g')

count if `sel' & !missing(ret_ex)
local nobs = r(N)

if `nobs' >= `min_days_per_estimation' {

qui reg ret_ex `factors' if `sel'

scalar last_alpha = _b[_cons]

foreach f of local factors {
if "`f'" == "mktexcess" {
scalar last_beta_mkt = _b[`f']
}
else {
scalar last_beta_`f' = _b[`f']
}
}
}
else {
display "No min obs for permno `p', group `g': `nobs'"
}


tempvar sel_next pred

gen byte `sel_next' = (`groupid' == `=`g'+1')
gen double `pred' = 0 if `sel_next'

foreach f of local factors {
if "`f'" == "mktexcess" {
replace `pred' = `pred' + last_beta_mkt * `f' if `sel_next'
}
else {
replace `pred' = `pred' + last_beta_`f' * `f' if `sel_next'
}
}

replace ret_FF`nfactors' = `pred' if `sel_next'
replace ret_FFrf`nfactors' = rf + `pred' if `sel_next'

drop `pred'
}

restore
// preserve
}

export delimited permno datenum ret_FF`nfactors' ret_FFrf`nfactors' ///
using "matlab_FF`nfactors'_returns.csv", replace
}


save "Done_FamaFrenchAbnormal.dta", replace

[Clyde Schechter]

The overall structure of your code has three levels of nested loops. You are looping over models, and within each model, you loop over permnos and within permnos you loop over groups. You have only four models, but if this is typical of this kind of application in finance, the number of permnos will be in the hundreds or thousands. And I have no idea how many groups are in the typical permno, but it, too might be large. And deep inside these nested loops you have a regression command. My guess is that you are asking Stata to do hundreds of thousands of regressions, perhaps even more than that. That is going to be slow under the best of circumstances.

But the circumstances in your code are not the best. Your permno loop selects the permno by -preserve-ing the data, then keeping only the focal permno, with a -restore- at the bottom of the loop. That's pretty much the slowest possible way to do this. You have to read and write the entire data set to disk for each permno. Disk operations are orders of magnitude slower than computing. Were there no other way to do this, at the very least, you could change the -restore- command at the bottom to -restore, preserve-, so that the data set would be written to disk only on the first loop, with the same copy of the data being maintained throughout and re-read in once at the bottom of the loop.

Then your loop over the values of group further selects the the observations for the regression by an -if- clause. But that is also a very slow process because the condition it checks has to be evaluated for every observation in the focal permno every time you go through that inner loop. So this, too, is an inefficiency.

I think you can get a significant performance improvement by restructuring this code using the -runby- command, written by Robert Picard and me, and available from SSC.

To do that you will have to take the code that is currently in the -foreach p of local permnos- loop, modify it, and wrap that inside a program. The modifications needed are: rewrite this part of the code as if there were only one permno and one group in memory when the program runs--because that is what will happen under -runby-. So you would get rid of the -preserve-, -keep if permno == `p'-, and -restore- commands. Similarly, since there will be only one group in memory when the program runs, you don't need the whole apparatus that loops over group values or creates local macro sel. For example,

            tempvar sel
            gen byte `sel' = (`groupid' == `g')

            count if `sel' & !missing(ret_ex)
            local nobs = r(N)

            if `nobs' >= `min_days_per_estimation' {

                qui reg ret_ex `factors' if `sel'

simplifies to just

            count if !missing(ret_ex)
            local nobs = r(N)

            if `nobs' >= `min_days_per_estimation' {

                qui reg ret_ex `factors' if `sel'

You will also need to make some modifications outside the looping structure. For example, instead of creating a `groupid' tempvar inside the loop, you should, before the program that you will use with -runby-, create the variable groupid: -egen `c(obs_t)' groupid = group(permno group_split)-.

Another modification you will need to make is to recognize that local macros that are defined outside the program you write will not be recognized inside the program. So local macros model1 through model4 and model_list will need to be defined inside the program you write, and the -foreach mdl of local model_list- loop content should be moved inside the program.

When the program has been written, you then run the program using -runby my_program, by(groupid). While you are writing and debugging the program, you should also use the -verbose- option on -runby- so that you can see what problems may be arising. Also, test your program on a smaller subset of the data, say just 50 permno's worth of data, first. Once you have your program working properly under -runby-, you can remove the -verbose- option. I recommend that you then add the -status- option to the -runby command: that way you will get periodic updates from -runby- about how many groupid's have been processed, the elapsed time, and an estimate of remaining time to completion.

There are many details you will need to change in the code that goes into the program you will create. I have outlined the most important ones, but there are others. I do not have time to do them all here, and even if I did, without a data set to test my work on, I couldn't be sure I'd found all the problems myself.

While I'm confident this will substantially reduce the runtime needed, don't expect miracles. You won't reduce 24 hours of execution to 10 seconds. But you probably will get it down to a small number of hours this way. No matter how you slice it, just doing hundreds of thousands of regressions takes time!