# Reshape to long: how to prepare data?

#### PhilH

##### New Member
Hi,

I really need your help. I'm new to Stata so it might be easy to answer for you guys... I have an excel sheet that lists stock prices for specific firms (about 800) on each trading day for 10 years. Firms (coded by their German stock exchange code) are arranged in ROW and dates in COLUMN.
It looks like this:

date firm1 firm2 ....... firmN
01.01.2001 56,35 13,59
02.01.2001 57,90 13,99
03.01.2001 57,95 11,02
.
.
.
.
10.10.2010 100,05 23,89

I have already imported the file to Stata. But how can I reshape it to long now? "Help reshape" doesn't really help or at least I assume that I can't use it, right?

My dataset should look like this:

firm_id date Price
1 01.01.2001 56,35
1 02.01.2001 57,90
. .
. .
1 10.10.2010 100,05

2 01.01.2001 13,59
2 02.01.2001 13,99
.
.
3
.
.
N

#### RedOwl

##### New Member
I believe the following will work for you.

Code:
clear
input str10 stockdate firm1 firm2
01.01.2001 56.35 13.59
02.01.2001 57.90 13.99
03.01.2001 57.95 11.02
end

reshape long firm, i(stockdate) j(firmid)

reorder firmid

list, noobs sepby(stockdate)

#### PhilH

##### New Member
Thank you very much for your quick response. However, I think I haven't explained my problem well enough:

I have around 800 firms in the first ROW of my excel sheet and they are all named by random numbers (i.e. their German stock code), so they are not named "firm1", "firm2" etc. but more like "23490", "A09237", "B92384" etc.. Could I just insert a random phrase before the name of all firms to use this as a stub then? like "23490" --> "price23490" etc.?

The COLUMN exists of approx. 4000 observations (trading days). I need each of this observation for each firm. Firms is the id if you will and date is the observation...

Also, what do I have to pay attention to when importing the excel file into Stata?

Thank you so much!

#### RobertoFerrer

##### New Member
This might help (comments in the code):

Code:
*clear all
set more off

* The MS Excel file is attached to the post (download it)
import excel using "D:\Datos\rferrer\Desktop\statatemps\wnames.xlsx", firstrow
describe

/*
-import excel- will change ilegal variable names for a legal one, not

In this example, the problem is that variables names can't start with a number,
so they are changed to -A- and -D-. All variable names in the MS Excel sheet
also end up as variable labels after the import. I'll use that to
rename to something that looks more like the original names. Note the use of
-strtoname()- and macro extended function -: variable label-.
See -help strtoname- and -help extended_fcn- for details.
*/

foreach v of varlist _all {
rename v' price=strtoname(:variable label v'')'
}

* Add a date to the dataset to complete example data
gen dat = _n
format dat %td

list

* Do the -reshape-
reshape long price, i(dat) j(firm) string

* Pretty-list
order firm
sort firm dat
list, sepby(firm)