Compute returns based on lag variable

[Astrid Koss]

Hello,

Can you please help me with the following issue:
I want to form quantiles based on the credit rating (quantile_cr_monthly) at time (month) t.
At time t+1, I would like to compute the average returns (ret_eom) for the observations within the quantile formed at month t.

I tried the following command, but it returns a "not sorted" error:
I first define each monthly observation as:
gen mdate = ym(yr, month)

To compute the average returns, I tried:
bysort mdate quantile_cr_monthly: egen ptf_ret_cr_lead=mean(f.ret_eom)

Thank you for your help!

[Clyde Schechter]

In order for the time-series operators f. (and l., d., etc.) to work, the data must be sorted on the panel and time variables that were used in the -xtset- (or -tsset-) command. But you over-rode that with your -bysort- command, so from the perspective of the f. operator, your data are not sorted properly.

Added: If you need more specific advice, please show example data when posting back. Be sure to use -dataex- to do that. If you are running version 15.1 or a fully updated version 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-.

[Astrid Koss]

Dear Clyde,

Thank you for your answer.
I also tried using tsset, but I don't want to sort based on the id for the different companies (as I would like to aggregate them), but rather on the quantile.
However,

 tsset quantile_cr_monthly mdate

returns the error: repeated time values within panel.

Please find below a snapshot of my data using dataex

* Example generated by -dataex-. To install: ssc install dataex
clear
input long date float mdate double(quantile_cr_monthly ret_eom) float id
16467 540 1 .0008300607390778186 13055
16467 540 1 -.0014965477467362713 40132
16467 540 2 -.0029423579031933335 41043
16467 540 2 .004339225189388585 13108
16467 540 3 .0013408358072408684 29013
16467 540 1 .009116802256292566 31410
16467 540 4 -.006267087009749546 1708
16467 540 4 -.01556425060107026 16807
16467 540 5 -.008827796011762324 7420
16467 540 1 -.0016729261101146899 8597
16467 540 1 -.005273338106843713 8882
16467 540 1 .006646625219986648 29304
end
format %d date
format %tm mdate

I only included a small snapshot, as you can see the date is the same (actually it stays the same for the first 100 obs). But inside each quantile, I have various firms/ids. How can I sort based on these quantiles at time t (540) in the above example, and take the average of the returns (ret_eom) at time t+1 (541)?

Thank you!

[Clyde Schechter]

I see. So what you need to do is first calculate the forward return as a separate variable, and then sort by quantile (and monthly date) to calculate the pft_ret_cr_lead.

xtset id mdate
gen forward_return = f.ret_eom

bysort mdate quantile_cr_monthly: egen ptf_ret_cr_lead=mean(forward_return)