Mass reshape

[Nima Behnejad]

Hi,

Hope all is well. I am quite new to STATA and was using this forum a lot while I was studying a course in Empirical Finance. Now I am working on an event study but I'm having trouble with my data and I can't figure it out. It is very basic but it is driving me nuts.

My data set is all stocks listed on Nasdaq Copenhagen. Currently, each firm is a separate variable. My problem is that I'd like all firms to be in one single variable, but I don't know if there is a way I can mass reshape all of the variables.

Please the picture below for my current data set(parts of it).

And this is the way I'd like the data set to look(please ignore the price vs return difference).



Sorry for the trouble.

Best regards,
Nima

[Nick Hart]

Hello,

I don't know where your data is coming from but the simplest way I can think of is to transpose your sheet in excel. You should end up with all the companies in the first column and each price (with the corresponding date as a header) in a new column. Then just write "company" in the header of the column where your company names are in.
After you have done this you can work with a little trick by just renaming the header of each date column like "price06-06-2019" (use a simple formula in excel to do that). If you import this sheet into STATA you can just use the reshape command and you end up with your company name in the first column, date in the second column and the price in the third column:

reshape long price, i(company) j(year) string

Your all-in-one variable should then be "company". It is surely not the most efficient way to do this, but it works.

Note: it is quite difficult to understand your data structure without an example, so I am not sure if your data is eligible for this approach. You can use datex in STATA to produce a short data example to post it here. But do not worry, I made this mistake as well.

[Nima Behnejad]

Hi Nick,

Thank you for the help!! Your approach almost worked but I got this error when I tried reshaping:
"variable price03122018 type mismatch with other price variables"

I got the same error but for a different date if I removed that entire column. I'd like to be more useful but I am not sure what "datex" is. When I type "help datex" in STATA, I find nothing. What should I do?

Thanks again!

[Nick Hart]

sorry I had a typo there.. it is dataex. Sorry for that

[Nima Behnejad]

Ahh, my bad! Thanks, I got it. Here's the data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int date double APMOLLERMAERSKA
18991 6211.71
18994  6336.5
18995 6385.03
18996 6364.23
18997 6482.09
18998 6378.09
19001 6360.77
19002 6395.43
19003 6353.83
19004 6475.15
19005 6447.42
19008 6426.63
19009 6440.49
19010 6523.68
19011 6530.62
19012 6478.62
19015 6478.62
19016 6544.48
19017 6669.27
19018 6814.86
19019 6662.34
19022 6613.81
19023 6929.25
19024 7251.62
19025 7161.49
19026 7327.88
19029 7327.88
19030 7355.61
19031 7383.34
19032 7452.67
19033  7397.2
19036 7584.39
19037 7591.32
19038 7736.91
19039 7612.12
19040  7882.5
19043 7979.55
19044  7861.7
19045 7390.27
19046 7390.27
19047 7549.72
19050 7279.35
19051 7265.48
19052  7438.8
19053 7702.25
19054 7605.19
19057  7480.4
19058 7196.16
19059 7168.43
19060 7168.43
end
format %td date

[Nick Hart]

I am not sure what kind of data this is..
Is this the result after reshaping it? Because there is only one company with numbers as values (price I think?).

Just give me an example of your original data you were talking about in your first post.

Are you using the same kind of prices for every company (i.e. daily unadjusted closing prices)?

Additionally you could post an example of the data after editing it in excel but before you used the reshape command.

[Nima Behnejad]

This is the example before reshaping it. If you look in my original screenshot, this is the result after I use the code

dataex date APMOLLERMAERSKA, count(50).

I only did 1 variable as my data set was too large to do all of them.

Yes, it is the same kind of prices for every firm, and they are all retrieved from Datastream.

And here is an example of the data in excel after reshaping:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str36 company str17 price02012012
"Company"                       "  1/2/2012"      
"A P MOLLER MAERSK A"           "6336.5"          
"A P MOLLER MAERSK B"           "6712.82"          
"AALBORG BOLDSPILKLUB"          "178.89"          
"AGAT EJENDOMME"                "9.970000000000001"
"AGF B"                         ".68"              
"ALK-ABELLO B"                  "325"              
"ALM BRAND"                     "8.25"            
"AMBU B"                        "7.4"              
"ANDERSEN & MARTINI B"          "38.5"            
"ORSTED"                        "NA"              
"ATHENA INVESTMENTS"            "18"              
"ATLANTIC PETROLEUM"            "163.5"            
"BANG AND OLUFSEN"              "56.5"            
"BANKNORDIK"                    "80.5"            
"BAVARIAN NORDIC"               "40.3"            
"BIOPORTO"                      "5.62"            
"BLUE VISION A"                 "14.4"            
"BOSTAD B"                      "4.48"            
"BRD KLEE B"                    "1625"            
"BROEDRENE HARTMANN B"          "102.5"            
"BRONDBY IF"                    "2.56"            
"CARLSBERG A"                   "434"              
"CARLSBERG B"                   "424"              
"CBRAIN"                        "3.95"            
"CEMAT"                         ".52"              
"CHEMOMETEC"                    "4.75"            
"CHRISTIAN HANSEN HOLDING"      "125.5"            
"COLOPLAST B"                   "163.6"            
"COLUMBUS"                      "1.46"            
"DMPKBT.NORDEN"                 "136.2"            
"DANSKE ANDELSKASSERS BANK"     "13.71"            
"DANSKE BANK"                   "76.3"            
"DANTAX RADIO"                  "106"              
"DEMANT"                        "94.78"            
"DEN JYSKE SPAREKASSE"          "NA"              
"DFDS"                          "72.8"            
"DJURSLANDS BANK"               "132"              
"DALHOFF LAR.& HORNEMAN"        "6.5"              
"DSV 'B'"                       "104"              
"F E BORDING B"                 "440"              
"FAST EJENDOM DANMARK"          "NA"              
"FIRSTFARMS"                    "47.4"            
"FLUGGER GROUP B"               "310"              
"FLSMIDTH AND CO."              "345"              
"FYNSKE BANK"                   "62"              
"G4S (CSE)"                     "24.5"            
"GABRIEL HOLDING"               "93"              
"GENMAB"                        "37.79"            
"GERMAN HIGH STREET PROPERTIES" "55"              
end

[Nick Hart]

Okay got it.
The data looks perfect to me except for the first line where you have company and the date name as the first values. Did you import the excel with the firstrow clear option?

import excel using "$path\example.xlsx", firstrow clear

this should solve the problem. I do not see any reason why your reshape should not work.

after deleting the first row you should recieve this:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str36 company str8 year str17 price
"A P MOLLER MAERSK A"           "02012012" "6336.5"          
"A P MOLLER MAERSK B"           "02012012" "6712.82"          
"AALBORG BOLDSPILKLUB"          "02012012" "178.89"          
"AGAT EJENDOMME"                "02012012" "9.970000000000001"
"AGF B"                         "02012012" ".68"              
"ALK-ABELLO B"                  "02012012" "325"              
"ALM BRAND"                     "02012012" "8.25"            
"AMBU B"                        "02012012" "7.4"              
"ANDERSEN & MARTINI B"          "02012012" "38.5"            
"ATHENA INVESTMENTS"            "02012012" "18"              
"ATLANTIC PETROLEUM"            "02012012" "163.5"            
"BANG AND OLUFSEN"              "02012012" "56.5"            
"BANKNORDIK"                    "02012012" "80.5"            
"BAVARIAN NORDIC"               "02012012" "40.3"            
"BIOPORTO"                      "02012012" "5.62"            
"BLUE VISION A"                 "02012012" "14.4"            
"BOSTAD B"                      "02012012" "4.48"            
"BRD KLEE B"                    "02012012" "1625"            
"BROEDRENE HARTMANN B"          "02012012" "102.5"            
"BRONDBY IF"                    "02012012" "2.56"            
"CARLSBERG A"                   "02012012" "434"              
"CARLSBERG B"                   "02012012" "424"              
"CBRAIN"                        "02012012" "3.95"            
"CEMAT"                         "02012012" ".52"              
"CHEMOMETEC"                    "02012012" "4.75"            
"CHRISTIAN HANSEN HOLDING"      "02012012" "125.5"            
"COLOPLAST B"                   "02012012" "163.6"            
"COLUMBUS"                      "02012012" "1.46"            
"DALHOFF LAR.& HORNEMAN"        "02012012" "6.5"              
"DANSKE ANDELSKASSERS BANK"     "02012012" "13.71"            
"DANSKE BANK"                   "02012012" "76.3"            
"DANTAX RADIO"                  "02012012" "106"              
"DEMANT"                        "02012012" "94.78"            
"DEN JYSKE SPAREKASSE"          "02012012" "NA"              
"DFDS"                          "02012012" "72.8"            
"DJURSLANDS BANK"               "02012012" "132"              
"DMPKBT.NORDEN"                 "02012012" "136.2"            
"DSV 'B'"                       "02012012" "104"              
"F E BORDING B"                 "02012012" "440"              
"FAST EJENDOM DANMARK"          "02012012" "NA"              
"FIRSTFARMS"                    "02012012" "47.4"            
"FLSMIDTH AND CO."              "02012012" "345"              
"FLUGGER GROUP B"               "02012012" "310"              
"FYNSKE BANK"                   "02012012" "62"              
"G4S (CSE)"                     "02012012" "24.5"            
"GABRIEL HOLDING"               "02012012" "93"              
"GENMAB"                        "02012012" "37.79"            
"GERMAN HIGH STREET PROPERTIES" "02012012" "55"              
"ORSTED"                        "02012012" "NA"              
end

It works for me

[Nima Behnejad]

For some reason it works if I import all of the variables as string variables but not otherwise. Thanks for all your help Nick!! Works great!

[Nick Hart]

No problem. You're welcome