Creating a new Portfolio based on the 5 lowest/highest Betas

[Benjamin Kneuss]

Dear Stata Community,

I have a panel dataset with monthly excess returns for 25 portfolios starting from 1927 until 2020. I calculated rolling betas over 60 months between the returns of the portfolio and the market, then moving 1 month ahead and so on, so there is a beta for every month and portfolio starting from 1932 (after the first 5 years). The next step, where I am struggling, is to create a new portfolio where I take in every month the average return of the 5 portfolios with the lowest/highest beta. To be clear, the 5 portfolios can change every month until 2020. So there is a new portfolio that takes in every month the average return of the 5 portfolios with the lowest/highest beta. The variables I have are time (months from 1927 until 2020, the 25 portfolios (with excess returns for each month) and last the betas.
It is actually not that complicated but I am new to Stata and sadly found nothing specific in the FAQ that would address my problem.
Maybe one of you has an idea how to code this or which command I could use?

I appreciate your time and thank you very much in advance

[Clyde Schechter]

Even the best description of a data set is no substitute for a data example. Your post has gone unanswered for nearly two days now, and I suspect the absence of example data is the reason why. So I suggest you open your data set in Stata, and use the -dataex- command to post example data here. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use -dataex-.

In addition, there is a problem with the question itself. It may happen that in some months there are ties for fifth highest or fifth lowest beta. You need to say how you want to handle that situation.