Stata log returns for 300 companies

[Pieter Jan Max]

Dear,
I am a finance student and I have to make portfolios sorted on size and momentum for my thesis research. I have data of the dutch market containing of the share price and market value of almost 300 companies in the period 2012-2017.
Now, I am new to Stata and I want to first calculate the stock returns for every company at every data (monthly). I know the code to use and I can use it with just only one company, but not with almost 300 companies at the same time. In the excell file I have all the data where the MV1-MV298 stand for the marketvalue for all the companies and P1-P298 stand for the share price of all the companies.

Please can anyone help me

[William Lisowski]

Welcome to Statalist.

Please review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post, looking especially at sections 9-12 on how to best pose your question. It would be particularly helpful to post a small hand-made example, with just a few variables of your hundreds of and just a few observations. In particular, please read FAQ #12 and use dataex when sharing data on Statalist. To install dataex, type ssc install dataex and when that is complete type help dataex to read the simple instructions for using it. Using dataex will enable those who want to help you to quickly and easily create a 100% faithful replica of your situation to test their ideas and code on.

With that said, I have created a sample of your data using dataex, and post it here to benefit others who may be able to help.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int date double(MV1 MV2 MV3 P1 P2 P3)
19272 100518.5 43519.51  25352.6  26.89  28.09 6.619
19303 101097.9 44526.55 26348.47 27.045  28.74 6.879
19333 96780.31 45308.97 27532.03  25.89 29.245 7.188
19364 99930.44 45749.66  28313.4  26.49 29.395 7.392
19395 95818.56 45290.54 26225.89   25.4   29.1 6.847
19423  96233.5 47656.22 24172.86  25.51  30.62 6.311
19454 93259.13 48667.85 21629.72 24.555  31.27 5.646
19484 99734.69 50792.32 24591.06  26.26 32.635 6.419
19515 94227.63 47508.36 25988.64  24.81 30.525 6.771
19545 93495.56 47850.78  27842.5 24.465 30.745 7.254
19576  92597.5 47181.54 30168.47  24.23 30.315  7.86
19607 94317.19 44644.64 32280.26  24.68 28.685 8.408
19637 92887.81  43282.8    33679  24.06  27.81 8.772
19668 95841.19 44979.28 36858.93 24.825   28.9   9.6
19698 95281.38 44963.71 35426.66  24.68  28.89 9.225
end
format %tdnn/dd/CCYY date

[Clyde Schechter]

Thanks to William for providing a -dataex- example to work with.

I'm not in finance, and I don't know if the conventional definition of return is based on the next month's price, or the previous month's price. The code here assumes it's based on next month's price, the return you will receive if you hold the stock for the month coming.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int date double(MV1 MV2 MV3 P1 P2 P3)
19272 100518.5 43519.51  25352.6  26.89  28.09 6.619
19303 101097.9 44526.55 26348.47 27.045  28.74 6.879
19333 96780.31 45308.97 27532.03  25.89 29.245 7.188
19364 99930.44 45749.66  28313.4  26.49 29.395 7.392
19395 95818.56 45290.54 26225.89   25.4   29.1 6.847
19423  96233.5 47656.22 24172.86  25.51  30.62 6.311
19454 93259.13 48667.85 21629.72 24.555  31.27 5.646
19484 99734.69 50792.32 24591.06  26.26 32.635 6.419
19515 94227.63 47508.36 25988.64  24.81 30.525 6.771
19545 93495.56 47850.78  27842.5 24.465 30.745 7.254
19576  92597.5 47181.54 30168.47  24.23 30.315  7.86
19607 94317.19 44644.64 32280.26  24.68 28.685 8.408
19637 92887.81  43282.8    33679  24.06  27.81 8.772
19668 95841.19 44979.28 36858.93 24.825   28.9   9.6
19698 95281.38 44963.71 35426.66  24.68  28.89 9.225
end
format %tdnn/dd/CCYY date

reshape long MV P, i(date) j(stock)
sort stock date

gen month = mofd(date)
format month %tm
xtset stock month

gen return_pct = 100*(F1.P-P)/P

If you want backward return instead of forward, it's -gen return_pct = 100*(P-L1.P)/P-.

This is very basic Stata. I advise you to take some time out before proceeding with analyses and read the Getting Started [GS] and User's Guide [U] volumes of the PDF documentation that comes with your Stata installation. (Select PDF Documentation from the Help menu and then follow the appropriate links from there.) You will get a general sense of how Stata works with data, what the usual command syntax is, and an overview of the commands that are part of every day data management and analysis. You won't remember every detail, but you will, in most situations, be able to figure out which commands are most likely to be useful for a particular problem, and then you can consult the help files or PDF documentation for the details. Since you evidently will be working with longitudinal data you should also read -help xt- and the linked manual section, and -help datetime- and its linked manual section. Yes, this is a lot of reading, but the time will be more than repaid by your enhanced facility to get work done.

[Pieter Jan Max]

Thank you William Lisowski and Clyde Schechter for the information