Export to Excel: Complicated

[CHANDANA GUNATHILAKA]

How would I export results of the code:

forvalues i = 1/100 {
table HP`i'_quintile, by(HP`i'_quintile) c(mean KG`i')
}

Thanks

[Mike Lacy]

Can you show some sample data and a schematic example of how you would want your results to appear in Excel? I don't think -table- is likely to be very helpful for your purposes, so knowing what you want as the result would be helpful. (Among other things, -table- does not save results to a matrix or to the return list.) Per standard advice, posting actual spreadsheets is deprecated on StataList, so this might be one instance in which a screenshot of what you'd like in Excel could be relevant. Also, I your example code indicates you want HP`quintile' to be both the row and super-row variable of your table. Having the same variable as row and super-row is unusual. I'd make sure your example makes clear what you have in mind there.

[Mike Lacy]

I still am not certain what you want, and I don't know the structure of your data. And, among other things, I'm not familiar with your terminology of "sorted quintile," or "in raw."

I'm going to guess the following: "Your data is in wide format, with each observation pertaining to a particular equity, with 140 variables indicating the price and 140 variables indicating the return for month 1 to month 140. For each month, you want to categorize each equity's price into a quintile, those quintiles being determined across all equities for that month. Then, within each month and within each price quintile, you want a display of the mean return. You'd like the results for each quintile to be a row, with 140 means within quintile displayed in each row."

[It took longer for me to decipher your situation and intent than it did to solve the problem. I would not be surprised if I guessed wrong. Most of us are better able to show examples of our data and what we want than we are to describe it in words. This is why the FAQ for StataList strongly recommends example data, and why I requested such.]

There are likely nicer and faster solutions than the following (e.g., involving -putexcel-), but this appears to work.

clear
// Simulate some data in wide format.  This may or may
// not correspond to the structure the OP has.
set obs 1400
gen int id = _n
forval i = 1/140 {
    gen HP`i' = 100 * runiform()
    gen KG`i' = rnormal(0,1)
}
//
// Change to long format makes this easier.
reshape long HP KG, i(id) j(month)
//
// Categorize HP into quintiles within month.  
// Note that the -by- prefix doesn't work with -xtile-, which makes this harder.
gen price_quintile = .
forval i = 1/140 {
    if (mod(`i',10) == 0)  {  // entertainment
       display "Finished month `i'"
    }   
    xtile temp = HP if month == `i', nq(5)
    quiet replace price_quintile = temp if month == `i'
    drop temp
}
//
// -collapse- gives a dataset of means within month and price_quintile,
// which can then be exported to Excel
collapse (mean) mean_month = KG , by(month price_quintile)
describe // just to show what has happened
//

// OP wants wide format for results.
reshape wide mean_month, i(price_quintile) j(month)
//
export excel using "myfile.xls", sheetreplace firstrow(variables)

[Mike Lacy]

I'm sorry, but I'm still not understanding what you want. Rather than have someone here on the list go back and forth with you to clarify what you have in mind, I'd encourage you to get a friend or supervisor to help you rewrite your request.