I provided a sample of the data I have under the 5c8d...csv file. In the other file, I provided an example of how I want my data to look. If you have a method on how to achieve this for thousands of firms, I am all ears!

Hello Niek van der Schaaf,

If you can use Excel, the transformation you want is a very straightforward application of Pivot Table. You basically select all your data, Ctrl+a on your spreadsheet, then Insert >> Pivot Table. You can then choose Date as rows, your company ID as columns and the variable of interests in values. (attached the illustration from your own file)

To some extent, I believe Open Office does it too.

This creates heavy datafile, you can copy-paste in value your Pivot Table to work with later (Paste Special >> Values).

Check any tutorial on Pivot Table, it's a basic and useful Excel function.

Then if you need more professional work, it is a better practice to code your analysis so that all steps of data manipulation are recorded: you can check online how to pivot data with R, Python, or other tools (I guess Stata and SPSS must do that quite directly too).

I hope this is helpful,

Good Luck with your work,

Mathieu