Reshape data in Excel for importing

[Emily91]

Hi everyone

I would like to ask you for your help. For my thesis, i’m struggling with something that seems easy, but yet i don’t know how to deal with it.

I have data for more than thousand companies, that is organised as followed:

But I want to reshape it, so it looks like this (for importing it into Eviews):

I tried using a pivot table but I didn’t end up with the result I wanted. And transposing every balanssheet item for more than thousand companies doesn’t seem the right way as it is too time-consuming.

If anyone could help me with this, i would be so grateful as my deadline is getting closer and closer.
Thank you in advance

[EViews Gareth]

Don't know about Excel, but in EViews...

Code: Select all

wfopen c:\temp\assets.xlsx byrow
alpha variable = @makevalidname(@trim(@left(companies, @length(companies)-4)))
series year = @val(@right(companies, 4))
pageunstack variable year @ *
d companies*
pagestack ?fixed_assets @ ?* *
rename var01 company
pagestruct @date(year) company
pagedelete untitled


[Emily91]

Thank you for your reply

I’m sorry but this is the first time I’m working with Eviews and I don’t quite understand your reply and what steps I should take. Correct me if I’m wrong; so it is possible to just import the data without reshaping it in Excel?

Thank you in advance!

[EViews Gareth]

It is, just run those lines of code, either as a program or from the command window.

[Emily91]

Thank you for your reply. I tried to run the different commands for my data but i’m facing some problems. The first 2 commands seem to work for the data. But as soon as I want to run the 3rd command, the one with ‘pagestack ?fixed_assets @ ?* *’ I get an error message saying ‘ID series do not uniquely identify workfile observations’ Do you know maybe what the cause of this might be?

Another question; if the different companies in my dataset have a name and are not named ‘company A B or C’ what should i replace the word ‘company’ with in the command?

Thank you!

[Emily91]

Thank you for your reply. I tried to run the different commands for my data but i’m facing some problems. The first 2 commands seem to work for the data. But as soon as I want to run the 3rd command, the one with ‘pagestack ?fixed_assets @ ?* *’ I get an error message saying ‘ID series do not uniquely identify workfile observations’ Do you know maybe what the cause of this might be?

Another question; if the different companies in my dataset have a name and are not named ‘company A B or C’ what should i replace the word ‘company’ with in the command?

Thank you!

Anyone?