Automate a reshape of the dataset

[Francois Durant]

I am calculating a few portfolio performance metrics that I need to output in an Excel file. The Sharpe ratio (SR here) is one of them. I need to maintain the order (sorting done with "id" manually, mkt and BTC always appearing first) of the variables I have in order to output each metric to the corresponding variable in Excel. The SR is the deciding factor for me to keep a variable or not. I would like to automate this criteria in my Stata code in a way that I do not have to change the string name every time I have new input data (I have +100 variables and their name changes). If the SR is positive, then I keep the variable, otherwise I drop if from the table. Here is what I have manually programmed. Would you be able to help me automate it?

clear all
input SR_cpd SR_cpdpf SR_cpdpm
-.04414876 .08082671 .65427268
end

gen SR_BTC = runiform()
gen SR_mkt= runiform()
    *order w_date $SR_strat
    *collapse (mean) $SR_strat
     
    *order $SR_strat
    gen id = 1
    reshape long SR_ , i(id) j(SR) string
    sort SR_
    
    * Drop cpd because SR_ is negative:
    drop if (SR_<0 & SR!="BTC" & SR!="mkt")
    
    replace id = 0 if SR=="BTC"
    replace id = 0.5 if SR=="mkt"
    sort id SR_
    
* I do not want to have to change the string name is every time I backtest variables with different names:
    replace id = 1 if SR=="cpdpf"
    replace id = 2 if SR=="cpdpm"
    
    sort id
    drop id SR
    rename (SR_ ) (SR )
    
    putexcel set 0.Results.xlsx, sheet("Table 1") modify
    putexcel K2="SR"
    format SR %9.3g
    export excel using 0.Results.xlsx , sheetmodify sheet("Table 1") cell(K4) keepcellfmt

[Francois Durant]

Clyde Schechter : I am sorry to ask here, but I am really stuck. Would you happen to know how to do? You help is highly appreciated. Thanks.

PS: Note that like in a previous post, I have +100 variables that are stored in a global variable $strat (that I still do not want to change) to which I add "SR_" and their name is not all "cpd", it was just an example. Thanks.

[Clyde Schechter]

I would like to help, but I have to say that I do not understand what you want to do here. At first glance, it seems pretty simple:

// REMOVE BTC and mkt FROM VARIABLE LIST
local exclude BTC mkt
local target_vars $strat
local target_vars: list target_vars - exclude

// REMOVE TARGET VARIABLES IF FINAL VALUE IS NEGATIVE
foreach v of local target_vars {
    if `v'[_N] < 0 {
        drop `v'
    }
}

But the code you show does a lot of other things, and I don't grasp how those things might interact with the process in question. Moreover, you were already given essentially this same advice by Nick Cox in your other thread--so presumably the question here is something different that I am not grasping.