Lapis Lazuli | Level 10

## sorting Securities

Can you help me to program this step:

For each month t from July of year y-1  to June of year y, we rank the stocks based  their return between t-12 and t-2

suppose that I have the historical monthly return of each secrutities such that

GVKEY  date            mret

1           20050630     0.12

1

...

1           20071130     0.23

2

2

...

2

8 REPLIES 8
Rhodochrosite | Level 12

## Re: sorting Securities

So some records have no date or mret? And GVKEY is the stock key...or...?
Lapis Lazuli | Level 10

## Re: sorting Securities

I gave only an example of data

Rhodochrosite | Level 12

## Re: sorting Securities

without any example of what output you want.
Lapis Lazuli | Level 10

## Re: sorting Securities

this is an example of input. I need your help to do understand

 GVKEY date mret 1 200506 0.012 1 200507 0.0554 1 200508 0.0086 1 200509 0.0235 1 200510 0.0384 1 200511 0.0533 1 200512 0.0682 1 200601 0.0831 1 200602 0.098 1 200603 0.1129 1 200604 0.1278 1 200605 0.1427 1 200606 0.1576 1 200607 0.1725 1 200608 0.1874 1 200609 0.2023 2 200806 0.0856 2 200807 0.0563 2 200808 0.1505 2 200809 0.1545 2 200810 0.1585 2 200811 0.1625 2 200812 0.1665 2 200901 0.1705 2 200902 0.1745 2 200903 0.1785 2 200904 0.1825 2 200905 0.1865 2 200906 0.1905 2 200907 0.1945 2 200908 0.1985 2 200909 0.2025 2 200910 0.2065 2 200911 0.2105 2 200912 0.2145
Rhodochrosite | Level 12

## Re: sorting Securities

again, no example of what output you want.
Lapis Lazuli | Level 10

## Re: sorting Securities

I did not understand this sentence to be able to design my output.

For each month t from July of year y-1  to June of year y, we rank the stocks based  their return between t-12 and t-2

Rhodochrosite | Level 12

## Re: sorting Securities

So something like this?

1 200508 1 0.0086
1 200509 2 0.0235
1 200510 3 0.0384
1 200511 4 0.0533
1 200507 5 0.0554
1 200512 6 0.0682
1 200601 7 0.0831
1 200602 8 0.098
1 200603 9 0.1129
1 200604 10 0.1278
1 200605 11 0.1427
1 200606 12 0.1576
Rhodochrosite | Level 12

## Re: sorting Securities

Another attempt:

Because you're missing all of 2007 I looked at 200606 because it has the needed records, from what I can tell.

So for GVKEY 1, 200606 should sum mref for months 200506 - 200604, resulting in 0.6812, correct?
Discussion stats
• 8 replies
• 427 views
• 0 likes
• 2 in conversation