How to Export Stata Database to Excel with Custom Numeric Formatting

[Chiara Tasselli]

Good afternoon,

I would like to know if there is a direct command in Stata that allows me to export an entire database to Excel with a specific numeric format (ideally I use export excel) . Ideally, I would like the numeric variables to be displayed as follows:

• Thousands separator: period (.)
• Decimal separator: comma (,)
• Decimal places: two (e.g., 20.000,34)

Is there a way to achieve this directly during the export process without additional formatting in Excel?

Thanks in advance for your help!

[Nick Cox]

I think I can guess reasons why you're asking this and there may be a great answer based on Excel expertise, which I do not claim. I do not use Excel for any purpose beyond providing data which I transfer to Stata as soon as possible.

Sometimes such questions arise because of data in currency units, i.e. some currency unit is in turn composed of 100 smaller units, as in cents and USD, pence and GBP and so on. I believe that many programs using such data work by multiplying up to integers.

The non-answer below may tell you what you already know, but even if so, this thread may be read by others.

But the question perhaps confuses or conflates two related but nevertheless distinct levels. A numeric display format in Stata is almost nothing to do with how numbers are stored. Of the real numbers rounded to 2 decimal places (outside a computer) only those ending in .00, .25, .50 and .75 may be held as exact binary approximations. All else is held to some degree of approximation with error associated.

You can choose between float and double for holding numbers in Stata. If you are using float, you have already lost some precision. That is unlikely to matter for most statistical purposes.

The strongest negative of all is that assigning a display format, say %4.2f, has no bearing at all on how numbers are stored. There is no "rounding to 2 decimal places", except when numbers are displayed.

[Clyde Schechter]

As Nick has pointed out, you can't accomplish this by applying some display format to the numbers in Stata.

If I were in your situation, here's how I would approach it:

//    CREATE A DEMONSTRATION DATA SET WITH NUMBERS VARYING OVER A WIDE RANGE
//    OF MAGNITUDES
clear*
set obs 100
gen x = rnormal(3, 5)
replace x = exp(x)
sort x

//    CREATE STRING VARIABLES WITH 2 DECIMAL PLACES AND COMMAS SEPARATING
//    GROUPS OF 3 DIGITS IN THE INTEGER PART
tostring x, gen(x_str) format(%15.2fc) force

//    EXCHANGE COMMAS AND PERIODS
replace x_str = subinstr(x_str, ",", "#", .)
replace x_str = subinstr(x_str, ".", ",", 1)
replace x_str = subinstr(x_str, "#", ".", .)

-tostring- with the -format()- option does the hard work. The only problem is that it uses . as the decimal point and , as the digit-group separator. But those can be swapped with some applications of -subinstr-. Then you can export the string versions of the variables to Excel using -export excel-.

[Andrew Musau]

Whether Excel uses the American or European format depends on your regional settings. For formatting, you can use Mata functions. Below, I use functions that modify cell formats, but there are also functions to format cell ranges.

clear
input float(value1 value2)
10000.3484 188390.222
99500.443 99999.999
1234.001 7809839.519
end

local rows `=_N+1'
local cols `=c(k)'

export excel myfile, firstrow(variable) replace 
mata: xl = xl()
mata: xl.load_book("myfile.xlsx")
mata: xl.set_sheet("Sheet1")
forval row= 2/`rows'{
    forval col= 1/`cols'{
        mata xl.set_number_format(`row', `col', "number_sep_d2")
    }
}
mata: xl.close_book()

Res.: