Export to Excel problem/bug with quarterly data

[Sergiy Radyakin]

Dear All, there seems to be a problem with exporting quarterly formatted data to Excel from Stata 13.1 (Windows)

Take data from here:
http://www.stata-press.com/data/r7/friedman2.dta
Last variable was added with:

generate time_val=time

Export to Excel via a dialog with default settings.

Obtain the following:

The constant 21916 is rounded by Excel, but saved with some variation in the fractional part:

Given that the numbers are increasing in column B it seems that the values are somehow distorted original quarterly values. Perhaps a transformation was applied similar to what is described here: http://blog.stata.com/2011/01/05/usi...ther-software/ where it isn't applicable.

Anyhow, it is difficult to work with such an export format, and formatting the data as a number (hence column F corresponding to time_val) makes more sense.

For curious, 21916 is the number of days from Jan 1, 1900 (Excel base date) toJan 1, 1960 (Stata base date).

There was already a problem fixed in 13.0-->13.1 regarding the %d variables:

but it is not clear which formats were covered under the %d designation (just one format or all date formats collectively).

The Stata version I am using is as of: 06 Aug 2014
Is this already a known issue? Is this fixed in the most recent update (I know there is more available)?

Thank you, Sergiy Radyakin

[Clyde Schechter]

I have the latest Stata update (19 Dec 2014), and the same things happens.

[Sergiy Radyakin]

Dear Clyde, thank you very much for taking time to reconfirm this.

I should add that exporting as string works in the above example, but it applies to all variables (or I haven't found how to specify individual formats for each variable during serialization of date to string). So if some variables contain dates with day precision, and some variables with quarter precision, then all of them will be output as quarter, which will be wrong. Or is it a separate second bug in string serialization? It would be reasonable to expect that I can configure separately day format, time format, month format, quarter format, and number format. Then Stata should use a proper Excel format depending on the %-format of the variable in memory.

Best, Sergiy Radyakin

[Kevin Crow (StataCorp)]

Thanks for bringing this to our attention.

This is a bug in export excel when exporting weekly, monthly, quarterly, half-yearly, and yearly date variables. The date resolution is mistakenly treated as daily. The bug is in both Stata 13 and Stata 14. We will fix it in a future executable update in both Stata 13 and 14.

[Sergiy Radyakin]

Dear Kevin,
thank you very much.
Sergiy Radyakin