Problem reshaping time series data imported from excel

[Koen Groenendijk]

I have been trying to import daily time series data through Datastream/Excel. As Datastream sorts the data differently, I have been trying to use the reshape function accoding to this blog post: https://researchfinancial.wordpress....am-data-stata/. However, this post concerns an older version of Stata (while I am using Stata 15) and is using monthly data, whereas I need to use daily data. I have added a letter to the dates, like shown in the blog post. My excel file therefore looks as follows.

I am trying to sort the data by ISIN (the company code) in the first column and then add the dates (a period of 11 days) for each company, so that the values in this file are shown per company and date. The commands I have used are the following: import excel "D:\Data\A+D.xlsx", sheet("Price1") firstrow allstring and reshape long D, i(ISIN) j(dag).

The imported data, before reshape, looks like this:

after reshape long D, i(ISIN) j(dag), it looks like this:

So the dates are now included in the rows and the columns (sort of) and apparently I have managed to let Stata believe that the dates are normal numeric values. I have also tried importing the data from excel with a different notation for the dates, namely D13nov2013 and 13nov2013. However, using these other notations, the data was either imported wrongly or the reshape command resulted in error r(498) "variable dag contains all missing values".

I hope I have provided enough information to make my situation clear. Thank you in advance.

[Nick Cox]

You have presented data using images. Please don't do that. FAQ Advice #12 explains: use dataex to show examples. But it seems that your import and reshape worked. You just need to destring some variables and get your daily date variable into better form.

Showing us the results of

dataex ISIN dag SIC

would allow suggestion of precise syntax.

[Koen Groenendijk]

My apologies, I should have read a bit more carefully.

I retrieve the following through dataex:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 ISIN float dag str4 SIC
"AU000000PTB9" 13112013 "3724"
"AU000000PTB9" 14112013 "3724"
"AU000000PTB9" 15112013 "3724"
"AU000000PTB9" 18112012 "3724"
"AU000000PTB9" 19112012 "3724"
"AU000000PTB9" 20112012 "3724"
"AU000000PTB9" 21112012 "3724"
"AU000000PTB9" 22112012 "3724"
"AU000000PTB9" 25112012 "3724"
"AU000000PTB9" 26112012 "3724"
"AU000000PTB9" 27112012 "3724"
"AU000000QHL1" 13112013 "3728"
"AU000000QHL1" 14112013 "3728"
"AU000000QHL1" 15112013 "3728"
"AU000000QHL1" 18112012 "3728"
"AU000000QHL1" 19112012 "3728"
"AU000000QHL1" 20112012 "3728"
"AU000000QHL1" 21112012 "3728"
"AU000000QHL1" 22112012 "3728"
"AU000000QHL1" 25112012 "3728"
"AU000000QHL1" 26112012 "3728"
"AU000000QHL1" 27112012 "3728"
"BE0003654655" 13112013 "3721"
"BE0003654655" 14112013 "3721"
"BE0003654655" 15112013 "3721"
"BE0003654655" 18112012 "3721"
"BE0003654655" 19112012 "3721"
"BE0003654655" 20112012 "3721"
"BE0003654655" 21112012 "3721"
"BE0003654655" 22112012 "3721"
"BE0003654655" 25112012 "3721"
"BE0003654655" 26112012 "3721"
"BE0003654655" 27112012 "3721"
"BMG0719V1063" 13112013 "3728"
"BMG0719V1063" 14112013 "3728"
"BMG0719V1063" 15112013 "3728"
"BMG0719V1063" 18112012 "3728"
"BMG0719V1063" 19112012 "3728"
"BMG0719V1063" 20112012 "3728"
"BMG0719V1063" 21112012 "3728"
"BMG0719V1063" 22112012 "3728"
"BMG0719V1063" 25112012 "3728"
"BMG0719V1063" 26112012 "3728"
"BMG0719V1063" 27112012 "3728"
"CA05354K3047" 13112013 "3761"
"CA05354K3047" 14112013 "3761"
"CA05354K3047" 15112013 "3761"
"CA05354K3047" 18112012 "3761"
"CA05354K3047" 19112012 "3761"
"CA05354K3047" 20112012 "3761"
"CA05354K3047" 21112012 "3761"
"CA05354K3047" 22112012 "3761"
"CA05354K3047" 25112012 "3761"
"CA05354K3047" 26112012 "3761"
"CA05354K3047" 27112012 "3761"
"CA0977512007" 13112013 "3721"
"CA0977512007" 14112013 "3721"
"CA0977512007" 15112013 "3721"
"CA0977512007" 18112012 "3721"
"CA0977512007" 19112012 "3721"
"CA0977512007" 20112012 "3721"
"CA0977512007" 21112012 "3721"
"CA0977512007" 22112012 "3721"
"CA0977512007" 25112012 "3721"
"CA0977512007" 26112012 "3721"
"CA0977512007" 27112012 "3721"
"CA1247651088" 13112013 "3621"
"CA1247651088" 14112013 "3621"
"CA1247651088" 15112013 "3621"
"CA1247651088" 18112012 "3621"
"CA1247651088" 19112012 "3621"
"CA1247651088" 20112012 "3621"
"CA1247651088" 21112012 "3621"
"CA1247651088" 22112012 "3621"
"CA1247651088" 25112012 "3621"
"CA1247651088" 26112012 "3621"
"CA1247651088" 27112012 "3621"
"CA5589122004" 13112013 "3731"
"CA5589122004" 14112013 "3731"
"CA5589122004" 15112013 "3731"
"CA5589122004" 18112012 "3731"
"CA5589122004" 19112012 "3731"
"CA5589122004" 20112012 "3731"
"CA5589122004" 21112012 "3731"
"CA5589122004" 22112012 "3731"
"CA5589122004" 25112012 "3731"
"CA5589122004" 26112012 "3731"
"CA5589122004" 27112012 "3731"
"CA8551571034" 13112013 "3562"
"CA8551571034" 14112013 "3562"
"CA8551571034" 15112013 "3562"
"CA8551571034" 18112012 "3562"
"CA8551571034" 19112012 "3562"
"CA8551571034" 20112012 "3562"
"CA8551571034" 21112012 "3562"
"CA8551571034" 22112012 "3562"
"CA8551571034" 25112012 "3562"
"CA8551571034" 26112012 "3562"
"CA8551571034" 27112012 "3562"
"DE0007030009" 13112013 "3714"
end

Thank you for your response.

[Daniel Bela]

From what your dataex sample shows us, all you need to do is convert the pseudo-date-variable "dag" to a true Stata date variable; you can do so by executing

replace dag=date(strofreal(dag,"%10.0g"),"DMY")
format dag %td

(assuming that the variable is coded DAYMONTHYEAR)

Probably, you may want to have a look at -help datetime- to learn how to deal with date variables.

However, in the first post (and your screenshots), you stated that there is still D######## variables left after reshaping; this should not happen. For further diagnosis on why this happens, we probably need a dataex sample from the source data after importing them from the Excel spreadsheet.

Regards
Bela

[Nick Cox]

Also, SIC is an example of a variable for which

destring SIC, replace

should work.

[Koen Groenendijk]

Thank you very much, the date transation has worked out very well.

The datex code directly after importing from excel is as follows (I will only include it partly):

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 ISIN str4 SIC str17(D13112013 D14112013 D15112013 D18112013 D19112013 D20112013 D21112013 D22112013 D25112013 D26112013 D27112013)
"NL0000235190" "3721" "51.76000000000001" "52.74"             "51.95"             "53.21"             "52.36"             "52.18"             "51.41"             "51.74"             "52.23"             "51.53"             "52.37"            
"FR0000073272" "3724" "47.34"             "48.035"            "46.18"             "46.68"             "46.435"            "46.3"              "46.595"            "46.7"              "47.40000000000001" "48.15000000000001" "48.645"           
"FR0000121329" "3663" "45.475"            "45.785"            "45.22"             "45.62"             "44.865"            "44.53"             "43.95500000000001" "43.995"            "44.235"            "44.1"              "44.3"             
"FR0000121725" "3721" "884.7998"          "895.8499"          "890.25"            "897.95"            "886"               "875.3999"          "869"               "872"               "875"               "880.2998"          "876.6499"         
"DE000A0D9PT0" "3724" "73.151"            "72.807"            "72.437"            "72.431"            "72.08500000000001" "72.10899999999999" "71.254"            "71.98"             "72.636"            "71.167"            "68.901"           
"IT0003856405" "3812" "5.27"              "5.17"              "5.25"              "5.5"               "5.365"             "5.245"             "5.260000000000001" "5.28"              "5.275"             "5.28"              "5.315"            
"DE0007030009" "3714" "43.185"            "43.147"            "44.43"             "44.336"            "44.002"            "44.2"              "44.352"            "44.82"             "45.042"            "45.021"            "45.203"           
"FR0000050353" "3728" "21.22"             "21.598"            "21.668"            "21.068"            "21.398"            "21.5"              "21.432"            "21.7"              "22.2"              "22.31"             "22.4"             
"FR0000032278" "3728" "5.7999"            "5.924300000000001" "5.7999"            "6.043900000000001" "5.8381"            "5.6898"            "5.8716"            "6.264"             "6.221"             "6.173100000000001" "6.182700000000001"
"FR0007317813" "7373" "2.37"              "2.43"              "2.36"              "2.36"              "2.39"              "2.27"              "2.22"              "2.22"              "2.22"              "2.22"              "2.23"             
"FR0010099515" "3812" "8.950000000000001" "8.98"              "9.360000000000001" "9.32"              "9.390000000000001" "9.370000000000001" "9.470000000000001" "9.440000000000001" "9.33"              "9.050000000000001" "8.92"             
"FR0000033466" "NA"   "88.01000000000001" "88.01000000000001" "88.01000000000001" "88.01000000000001" "88.01000000000001" "88.01000000000001" "88.01000000000001" "88.01000000000001" "88.01000000000001" "88.01000000000001" "88.01000000000001"
"FR0011522168" "3812" "13.25"             "15.05"             "14.53"             "13.17"             "14.43"             "13.74"             "12.65"             "12.34"             "11.34"             "10.74"             "10.76"            
"FR0010976746" "3482" "10.8"              "10.8"              "10.8"              "10.8"              "10.8"              "10.8"              "10.8"              "10.8"              "10.8"              "10.8"              "10.8"             
"FR0010978924" "NA"   "5"                 "5"                 "5"                 "5"                 "5"                 "5"                 "5"                 "5"                 "5"                 "5"                 "5"                
"FR0004065639" "3812" "10.85"             "10"                "10.01"             "10.01"             "10.25"             "10.24"             "10"                "10.25"             "10.25"             "10.25"             "10.25"            
"BE0003654655" "3721" "40"                "40"                "40"                "39.99"             "40.95"             "40.95"             "38.16"             "38.16"             "38.16"             "41"                "38.8"             
"MEFANPRA1MK0" "3721" ".5"                ".5"                ".5"                ".5"                ".5"                ".5"                ".5"                ".5"                ".5"                ".5"                ".5"               
"FR0006174496" "3949" "8"                 "8"                 "8"                 "8"                 "8"                 "8"                 "8"                 "8"                 "8"                 "8"                 "8"                
"US0003611052" "3721" "22.25"             "22.35"             "22.17"             "21.83"             "21.68"             "21.52"             "22.03"             "22.72"             "22.98"             "22.98"             "22.87"            
"US0078001056" "3761" "12.78"             "12.88"             "12.83"             "12.85"             "12.71"             "12.46"             "12.56"             "12.78"             "12.79"             "13.09"             "13.07"            
"US0080731088" "3721" "20.17"             "20"                "19.96"             "19.94"             "20.02"             "20.03"             "20"                "19.92"             "20"                "19.97"             "20.26"            
"US02874P1030" "3721" "52.14"             "52.69"             "52.05"             "53.3"              "52.37"             "52.31"             "51.3"              "51.6"              "52.21"             "51.63"             "52.44"            
"US0426822039" "3484" "8.33"              "8.15"              "8.199999999999999" "8.26"              "8.220000000000001" "8.199999999999999" "8.31"              "8.6"               "8.77"              "8.65"              "8.69"             
"US0464331083" "3691" "1.55"              "1.53"              "1.41"              "1.38"              "1.42"              "1.39"              "1.39"              "1.54"              "1.52"              "1.72"              "1.63"             
"US0464842006" "3728" "22.19"             "22.32"             "22.12"             "21.93"             "21.9"              "21.53"             "21.3"              "21.62"             "21.7"              "21.92"             "22.05"              
"US9130171096" "3812" ".02"               ".02"               ".03"               ".02"               ".02"               ".01"               ".02"               ".02"               ".02"               ".02"               ".03"              
end

[Nick Cox]

You'll need

destring SIC, replace force

given the NA values.

[Jorrit Gosens]

Daniel's warning in #4 is important here. Your reshape hasnt worked like you wanted. Some variables like D18112013 remain wide.
I am guessing that this has to do with precision in Stata. Your list of numbers (the bits following the stub D for your variables) gets read in as:
(note: j = 13112013 14112013 15112013 18112012 19112012 20112012 21112012 22112012 25112012 26112012 27112012)
So D18112013 gets it stub removed, leaving 18112013, which gets read in as D18112012.

Use the string option to avoid this, as in below code:

reshape long D, i(ISIN) j(dag) string
destring dag, replace
replace dag=date(strofreal(dag,"%10.0g"),"DMY")
format dag %tdDD.NN.CCYY

[Koen Groenendijk]

Thank you again, your help is much appreciated.
After including the destring, only part of the problem is fixed. The reshape command now gives the proper results for only the first 3 dates. The first 3 D######## variables have now been accounted for, but the others remain. I thought that this might depend on the fact that there are a few dates missing in between (since these are Saturdays and Sundays), but including these days does not make a difference. The variable D15112013 is the last one it processes correctly, after this variable the old problem remains. Do you have an idea what I am doing wrong here?

This is the data after the destring command and partly fixing the problem.
Code:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 ISIN float dag int SIC str17(D18112013 D19112013 D20112013 D21112013 D22112013 D25112013 D26112013 D27112013 D)
"AU000000PTB9" 19675 3724 "4.16"   "4.46"   "4.25"  "4.5"    "4.5"   "4.64"   "4.72"   "4.71"              "4.08"  
"AU000000PTB9" 19676 3724 "4.16"   "4.46"   "4.25"  "4.5"    "4.5"   "4.64"   "4.72"   "4.71"              "4.16"  
"AU000000PTB9" 19677 3724 "4.16"   "4.46"   "4.25"  "4.5"    "4.5"   "4.64"   "4.72"   "4.71"              "4.16"  
"AU000000PTB9" 19315 3724 "4.16"   "4.46"   "4.25"  "4.5"    "4.5"   "4.64"   "4.72"   "4.71"              ""      
"AU000000PTB9" 19316 3724 "4.16"   "4.46"   "4.25"  "4.5"    "4.5"   "4.64"   "4.72"   "4.71"              ""      
"AU000000PTB9" 19317 3724 "4.16"   "4.46"   "4.25"  "4.5"    "4.5"   "4.64"   "4.72"   "4.71"              ""      
"AU000000PTB9" 19318 3724 "4.16"   "4.46"   "4.25"  "4.5"    "4.5"   "4.64"   "4.72"   "4.71"              ""      
"AU000000PTB9" 19319 3724 "4.16"   "4.46"   "4.25"  "4.5"    "4.5"   "4.64"   "4.72"   "4.71"              ""      
"AU000000PTB9" 19322 3724 "4.16"   "4.46"   "4.25"  "4.5"    "4.5"   "4.64"   "4.72"   "4.71"              ""      
"AU000000PTB9" 19323 3724 "4.16"   "4.46"   "4.25"  "4.5"    "4.5"   "4.64"   "4.72"   "4.71"              ""      
"AU000000PTB9" 19324 3724 "4.16"   "4.46"   "4.25"  "4.5"    "4.5"   "4.64"   "4.72"   "4.71"              ""      
"AU000000QHL1" 19675 3728 "6.02"   "6"      "5.98"  "5.9"    "5.94"  "5.97"   "5.9"    "5.84"              "5.99"  
"AU000000QHL1" 19676 3728 "6.02"   "6"      "5.98"  "5.9"    "5.94"  "5.97"   "5.9"    "5.84"              "6.05"  
"AU000000QHL1" 19677 3728 "6.02"   "6"      "5.98"  "5.9"    "5.94"  "5.97"   "5.9"    "5.84"              "6.09"  
"AU000000QHL1" 19315 3728 "6.02"   "6"      "5.98"  "5.9"    "5.94"  "5.97"   "5.9"    "5.84"              ""      
"AU000000QHL1" 19316 3728 "6.02"   "6"      "5.98"  "5.9"    "5.94"  "5.97"   "5.9"    "5.84"              ""      
"AU000000QHL1" 19317 3728 "6.02"   "6"      "5.98"  "5.9"    "5.94"  "5.97"   "5.9"    "5.84"              ""      
"AU000000QHL1" 19318 3728 "6.02"   "6"      "5.98"  "5.9"    "5.94"  "5.97"   "5.9"    "5.84"              ""      
"AU000000QHL1" 19319 3728 "6.02"   "6"      "5.98"  "5.9"    "5.94"  "5.97"   "5.9"    "5.84"              ""      
"AU000000QHL1" 19322 3728 "6.02"   "6"      "5.98"  "5.9"    "5.94"  "5.97"   "5.9"    "5.84"              ""      
"AU000000QHL1" 19323 3728 "6.02"   "6"      "5.98"  "5.9"    "5.94"  "5.97"   "5.9"    "5.84"              ""      
"AU000000QHL1" 19324 3728 "6.02"   "6"      "5.98"  "5.9"    "5.94"  "5.97"   "5.9"    "5.84"              ""      
"BE0003654655" 19675 3721 "39.99"  "40.95"  "40.95" "38.16"  "38.16" "38.16"  "41"     "38.8"              "40"    
"BE0003654655" 19676 3721 "39.99"  "40.95"  "40.95" "38.16"  "38.16" "38.16"  "41"     "38.8"              "40"    
"BE0003654655" 19677 3721 "39.99"  "40.95"  "40.95" "38.16"  "38.16" "38.16"  "41"     "38.8"              "40"    
"BE0003654655" 19315 3721 "39.99"  "40.95"  "40.95" "38.16"  "38.16" "38.16"  "41"     "38.8"              ""      
"BE0003654655" 19316 3721 "39.99"  "40.95"  "40.95" "38.16"  "38.16" "38.16"  "41"     "38.8"              ""      
"BE0003654655" 19317 3721 "39.99"  "40.95"  "40.95" "38.16"  "38.16" "38.16"  "41"     "38.8"              ""      
"BE0003654655" 19318 3721 "39.99"  "40.95"  "40.95" "38.16"  "38.16" "38.16"  "41"     "38.8"              ""      
"BE0003654655" 19319 3721 "39.99"  "40.95"  "40.95" "38.16"  "38.16" "38.16"  "41"     "38.8"              ""      
"BE0003654655" 19322 3721 "39.99"  "40.95"  "40.95" "38.16"  "38.16" "38.16"  "41"     "38.8"              ""      
"BE0003654655" 19323 3721 "39.99"  "40.95"  "40.95" "38.16"  "38.16" "38.16"  "41"     "38.8"              ""      
"BE0003654655" 19324 3721 "39.99"  "40.95"  "40.95" "38.16"  "38.16" "38.16"  "41"     "38.8"              ""      
end
format %td dag

[Koen Groenendijk]

Thank you Jorrit, I have only read your answer after my previous post.
This has completely fixed the problem indeed.

Thank you all for your help!