Creating a dynamic portfolio of returns

[Leo Wimm]

Dear Statalist members,

I am using Stata for the first time for my Masters Thesis and have tried to learn as much as possible by reading this forum and testing different things. However, I am now stuck on a problem that I don't have any solution for.
I have a list of monthly stock returns for ~2000 securities. I want to analyze the performance of these stock after the IPO for a 3 and a 5-year period. Therefore, I want to create a portfolio of stocks that continually adds stocks to it once they IPO (this is recognized by being the first month were data is available, i have filtered out data with missing values for IPO months beforehand). After 3 (5) years, the stocks should drop out and no longer count to the performance. I have added an extract of my data below.

Unfortunately I can't provide any code as I am stomped and don't really have any idea how to tackle this. I have tried with the tsegen command and rprod, but can't figure out how to do this for the portfolio. I have also tried to put the data from long to wide with reshape, which leaves me with a different structure of the data but I still don't know how to do anything else with the data then.

Any help would be greatly appreciated.
Thanks a lot!

* Example generated by -dataex-. For more info, type help dataex
clear
input str8 ID float(DataDate MonthlyReturn)
"89903510" 10988   .807339
"89903510" 11016         1
"89903510" 11046  1.090909
"89903510" 11077    .96875
"89903510" 11108   .903226
"89903510" 11137         1
"89903510" 11169   .952381
"89903510" 11200     .8125
"89903510" 11228   .923077
"89903510" 11261   .983333
"89903510" 11291  1.016949
"89903510" 11322  1.366667
"89903510" 11353   .963415
"89903510" 11381  1.189873
"89903510" 11409  1.074468
"89903510" 11442  1.306931
"89903510" 11473   .984848
"89903510" 11501   .938462
"89903510" 11534   1.04918
"89903510" 11564  1.015625
"89903510" 11595  1.269231
"89903510" 11626  1.345455
"89903510" 11655   .918919
"89903510" 11687  1.176471
"89903510" 11718      1.15
"89903510" 11746   .985507
"89903510" 11778   .764706
"89903510" 11808  1.076923
"89903510" 11837   .946429
"89903510" 11869  1.075472
"89903510" 11900  1.052632
"89903510" 11931       1.1
"89903510" 11961   .772727
"89903510" 11991  1.098039
"89903510" 12022      .875
"89903510" 12053   .846939
"89903510" 12082  1.144578
"89903510" 12110   .968421
"89903510" 12143   .934783
"89903510" 12173         1
"89903510" 12201   .930233
"89903510" 12234        .9
"89903510" 12264  1.166667
"89903510" 12296  1.261905
"89903510" 12326         1
"89903510" 12355   .698113
"89903510" 12387   .891892
"89903510" 12418   .666667
"89903510" 12449  1.181818
"89903510" 12477   .884615
"89903510" 12508   .847826
"89903510" 12537   .923077
"89903510" 12569  1.027778
"89903510" 12599   .756757
"89903510" 12628  1.214286
"89903510" 12661  1.147059
"89903510" 12691   1.25641
"89903510" 12722   .938776
"89903510" 12752  1.195652
"89903510" 12782   .854545
"89903510" 12814  1.021277
"89903510" 12842  1.041667
"89903510" 12873       .98
"89903510" 12901  1.091837
"89903510" 12934   .915888
"89903510" 12964   .918367
"89903510" 12995  1.111111
"89903510" 13026       .99
"89903510" 13055  1.010101
"89903510" 13087       .92
"89903510" 13117         1
"89903510" 13146   .956522
"89903510" 13179  1.068182
"89903510" 13208  1.234043
"89903510" 13237 1.1724141
"89903510" 13269  1.411765
"89903510" 13300  1.083333
"89903510" 13328  1.028846
"89903510" 13361   .869159
"89903510" 13391  1.150538
"89903510" 13422  1.373832
"89903510" 13453  1.054422
"89903510" 13482  1.109677
"89903510" 13514   .994186
"89903510" 13545  1.116959
"89903510" 13573  1.196335
"89903510" 13604   1.12035
"89903510" 13634   .882812
"89903510" 13664   .946903
"89903510" 13695  1.128505
"89903510" 13726  1.006211
"89903510" 13755   1.08642
"89903510" 13787  1.071023
"89903510" 13818  1.029178
"89903510" 13846  1.025773
"89903510" 13879  1.005025
"13322V10" 18443         .
"13322V10" 18473         .
"13322V10" 18505  1.407303
"13322V10" 18535  1.168995
end
format %tdCCYY-NN DataDate

[William Lisowski]

I'm not in finance, so I can't directly address your problem, because I don't have any idea what it is you want to do with the data. But I can point out a fundamental misunderstanding of Stata apparent in your question.

Your dates are Stata Internal Format daily dates, as the %td format tells us. But if we change the format, which only shows the year and month, to %td, we see that each date is the final business day of a month. This is consistent with your description of monthly stock returns. Hiding the day of the month with your format does not turn your data into Stata Internal Format monthly dates. Instead, you need the following.

generate DataMonth = mofd(DataDate)
format %tm DataMonth

This gives you Stata Internal Format monthly dates, that differ by 1 in successive observations for the same ID, which is what you want for a monthly time series, not dates that differ by something in the high 20's or low 30's. The format only affects the way dates are displayed, not the way they are stored by Stata.

Some advice.

First, I'm not sure why, at the point of writing your Master's thesis, you're choosing to use a different tool than what you have used elsewhere in your coursework, either as a graduate student or as an undergrad. I think you underestimate the effort involved in mastering a new tool.

Second, Stata's "date and time" variables are complicated and there is a lot to learn. If you have not already read the very detailed Chapter 24 (Working with dates and times) of the Stata User's Guide PDF, do so now. If you have, it's time for a refresher. After that, the help datetime documentation will usually be enough to point the way. You can't remember everything; even the most experienced users end up referring to the help datetime documentation or back to the manual for details. But at least you will get a good understanding of the basics and the underlying principles. An investment of time that will be amply repaid.

All Stata manuals are included as PDFs in the Stata installation and are accessible from within Stata - for example, through the PDF Documentation section of Stata's Help menu.

Third, the experienced users here generally agree that, with few exceptions, Stata makes it much more straightforward to accomplish complex analyses using a long layout of your data rather than a wide layout of the same data. You should try to achieve what you need with the data organized as it currently is, and seek the help of Statalist in doing so. It is much easier, for example, to compare the second observation to the first, the third to the second, and so on, than it is to compare the second variable to the first, the third to the second, etc.

Fourth, what you are looking to do seems fiarly basic, so you should be spending time learning the basics of Stata before launching off into community-contributed extensions like tsegen and rprod. I'm sympathetic to you as a new user of Stata - there is quite a lot to absorb. And even worse if perhaps you are under pressure to produce a Master's thesis quickly. Nevertheless, I'd like to encourage you to take a step back from your immediate tasks.

When I began using Stata in a serious way, I started, as have others here, by reading my way through the Getting Started with Stata manual relevant to my setup. Chapter 18 then gives suggested further reading, much of which is in the Stata User's Guide, and I worked my way through much of that reading as well. All of these manuals are included as PDFs in the Stata installation and are accessible from within Stata - for example, through the PDF Documentation section of Stata's Help menu.

The objective in doing the reading was not so much to master Stata - I'm still far from that goal - as to be sure I'd become familiar with a wide variety of important basic techniques, so that when the time came that I needed them, I might recall their existence, if not the full syntax, and know how to find out more about them in the help files and PDF manuals.

Stata supplies exceptionally good documentation that amply repays the time spent studying it - there's just a lot of it. The path I followed surfaces the things you need to know to get started in a hurry and to work effectively.

Stata also supples YouTube videos, if that's your thing.