New to stata - need to prepare data for my Dissertation

[Michel Figueiredo]

Hi everybody. I am writing you guys to ask if any of you could help me out. I am currently writing my Master Thesis in Banking & Finance and I am currently facing a huge problem regarding how to start with my data. I downloaded Balance Sheet information from Banks for 5 years (2016-2011) from Orbis Bank Focus. The file that I downloaded is an excel file, however from then on I do not know how to proceed with it. I do not understand how to continue, if I should clean the file in excel of "n.a." values, if I should prepare every column with f.x. "id", "bank", "year" etc. before opening it in Stata or should I do the whole thing in Stata? I am seriously facing a huge challenge here on how to proceed. I will attach hereby the original file how I downloaded it from the database, and a second file how I cleaned the sheet and reduced the number of banks.

I really much appreciate any help on how to proceed with it.

The file with "new" in the end is the original file. The "final" is the one i was trying to edit on excel. There I already reduce the number of banks, however I am not sure if I should continue the way to edit the file as I was doing for the first 3 banks or if I should let it be as the original file. Does anybody know how to proceed with it?

Kind regards,
Michel

[Marcos Almeida]

Welcome to the Stata Forum / Statalist.

Please read the FAQ, particularly on how to share data and command. This is the best way to entail helpful replies.

I gather your question is too broad and scarcely detailed.

That said, IMHO, when dealing with data management, we had to muster skills in at least one package. You may do everything in Stata, if you will, for at least 2 reasons: Stata is really awesome when it comes to data management; there is a specific manual on the main procedures.

That said, you may prepare your data in Excel as well. For example, you may replace "NA" for blank spaces, and Stata will recognize it is a missing value.

All in all, the more you feel at easy with data management within Stata, the more comfortable you will feel with the analysis.

[Michel Figueiredo]

Dear Marcos,

Please excuse me for the way I shared my files. Unfortunately I tried a couple of times to share my excel files and it did not work. That's the reason why I posted them as a link through my mediafire account.

Would you mind explaining me what you mean by "muster skills in at least one package" and could you also share with me the manual you were referring too?

Muito obrigado.

Best regards,
Michel

[Marcos Almeida]

Hello Michel,

You may find here the manual as well as other resources on data management.

The best way to share data in this forum is by using CODE delimiters or installing the SSC dataex. The FAQ is a must read, concerning this point.

Finally, with regards to my comment on "mastering skills in at least one package", I meant: generally, the data set sent to the analysis needs to undertake several steps in order to reach a point of being appropriate for the estimations, hence we need to apply data-management strategies beforehand. Provided we can do this efficiently in at least one package, the estimations may eventually be done, not necessarily in the same package.

That said, I believe Stata excels (no pun intended here) in terms of data management.

Hopefully that helps!

[Bruce Weaver]

UCLA's Institute for Digital Research and Education also has some great resources on basic and intermediate data management using Stata. For example:

• https://stats.idre.ucla.edu/stata/modules/
• https://stats.idre.ucla.edu/stata/se...ta-management/

HTH.

[Michel Figueiredo]

I have a silly question, however I still could not attempt to do the transformation I want on Stata. I have on the column list of my Excel file for example "Total Assets EUR2016" and "Total Assets EUR2015" etc. I would like to reshape my file in Stata, so that I have for each bank the years from 2016-2011 and for each Variable, such as Total Assets the total for each year and each bank. Does someone know a solution on how I can do this in Stata?

[Michel Figueiredo]

If it helps somebody to have an example to compute it or in excel or Stata I would really much appreciate if someone could help me! Hope it is clear with what I mean from the above post!

Bank Name	Country code	Cons. code	Index number	Country Name	Total assets EUR2016	Total assets EUR2015	Total assets EUR2014	Total assets EUR2013	Total assets EUR2012	Total assets EUR2011	Total equity EUR2016
BNP Paribas	FR	C2	10931	FRANCE	2076959000	1994193000	2077759000	1810522000	1907200000	1965283000	96790000
Deutsche Bank AG	DE	C2	13216	GERMANY	1590546000	1629130000	1708703000	1611400000	2012329000	2164103000	64819000
Barclays Bank Plc	GB	C2	24151	UNITED KINGDOM	1416760561	1525494086	1746682524	1605126646	1780555454	1868132052	82776195,45
Société Générale SA	FR	C2	11150	FRANCE	1382241000	1334391000	1308138000	1214193000	1250889000	1181372000	65706000
Banco Santander SA	ES	C2	47560	SPAIN	1339125000	1340260000	1266296000	1115638000	1269628000	1251526000	102699000
Lloyds Bank Plc	GB	C2	49369	UNITED KINGDOM	969743197,3	1114098375	1113871625	1023776765	1127579913	1181013075	58646024,75

[Marcos Almeida]

Does someone know a solution on how I can do this in Stata?

You need to - reshape long - your data.

You may wish to type:

. help reshape

Please do really read the FAQ, as recommended in #2, and also recommended in #4.

The very fact that you relied on snapshots (plus starting a different theme on the same thread) unveils that you didn't take a close look at the FAQ, unfortunately.

No sooner do members follow the FAQ advices than they (usually) get truly helpful replies.