[Guest]

Clyde,

Yes, once I am through the portfolio selection process, the rest only includes average calculations. Thank you for offering me your help on this.

Regarding your possibilities:

1. I pulled the data myself. I set up a new dataset to avoid past possible mistakes. Indeed, there are duplicate observations, which actually have the same market cap and permno on the same date. But that's how I got the data from CRSP. After pulling the data again, the duplicates have the same values for all of the variables now, instead of different values for all of the other variables like before.

2. Since the duplicates are exactly the same, the solution to this problem is to delete the duplicate observations, don't you think? In terms of a correct analysis, it doesn't make sense to have duplicates in the portfolios.

3. Maybe we can simply connect permno with the referring date (after deleting the duplicates). This should create unique identifiers.

So all in all, the same permno has the same value of MarketCap on the same date now and not a different one like before.

Please find below the requested data sample:

* Example generated by -dataex-. To install: ssc install dataex
clear
input long(permno date) byte exchcd double ret float MktCap
10000 19860131 3        .      16.1
10000 19860228 3 -.257143     11.96
10000 19860331 3  .365385     16.33
10000 19860430 3 -.098592    15.172
10000 19860530 3 -.222656 11.793879
10000 19860630 3 -.005025 11.734593
10000 19860731 3 -.080808 10.786344
10000 19860829 3 -.615385  4.148594
10000 19860930 3 -.057143  3.911531
10000 19861031 3 -.242424  3.002344
10000 19861128 3      .06  3.182504
10000 19861231 3 -.377358  1.981566
10000 19870130 3 -.212121 1.5815313
10000 19870227 3        0 1.5815313
10000 19870331 3 -.384615    .97325
10000 19870430 3   -.0625  .9124414
10000 19870529 3 -.066667  .8515937
10000 19870630 3        .  .8515937
10001 19860131 3        .  6.033125
10001 19860228 3  .020408   6.15625
10001 19860331 3    .0252  6.217813
10001 19860430 3  .009901  6.279375
10001 19860530 3 -.009804  6.217813
10001 19860630 3 -.013069  6.033125
10001 19860731 3 -.010204  5.971562
10001 19860829 3  .072165    6.4025
10001 19860930 3 -.003077  6.317625
10001 19861031 3  .039216  6.565375
10001 19861128 3  .056604     6.937
10001 19861231 3     .015     6.937
10001 19870130 3 -.035714   6.68925
10001 19870227 3 -.074074   6.19375
10001 19870331 3    .0368  6.317625
10001 19870430 3 -.039216  6.069875
10001 19870529 3 -.071429  5.636312
10001 19870630 3  .051429  5.822125
10001 19870731 3  .021277     5.946
10001 19870831 3  .083333    6.4415
10001 19870930 3 -.022308       6.2
10001 19871030 3      .02     6.324
10001 19871130 3 -.029412     6.138
10001 19871231 3 -.033535     5.828
10001 19880129 3   .06383       6.2
10001 19880229 3      .08     6.696
10001 19880331 3 -.076296     6.076
10001 19880429 3  .030612     6.262
10001 19880531 3  .019802     6.386
10001 19880630 3 -.012039       6.2
10001 19880729 3      .03     6.386
10001 19880831 3  .029126     6.572
10001 19880930 3 -.021132   6.36225
10001 19881031 3  .039216   6.61175
10001 19881130 3        0   6.61175
10001 19881230 3 -.021132   6.36225
10001 19890131 3  .019608     6.487
10001 19890228 3  .038462    6.7365
10001 19890331 3  .017778    6.7365
10001 19890428 3  .074074    7.2355
10001 19890531 3 -.034483     6.986
10002 19890630 1  .017143     7.007
10001 19890731 3  .035714   7.25725
10001 19890831 3  .275862   9.25925
10001 19890929 3 -.027027  9.043625
10001 19891031 3  .070423    9.6805
10001 19891130 3  .039474 10.062625
10001 19891229 3  .037975  10.34775
10001 19900131 3 -.018519 10.156125
10001 19900228 3 -.006289  10.09225
10001 19900330 3  .012658 10.141625
10001 19900430 3        0 10.141625
10001 19900531 3 -.012658  10.01325
10002 19900629 1  .014103  10.05225
10001 19900731 3  .025641     10.31
10001 19900831 3     -.05    9.7945
10001 19900928 3  .040789    10.179
10001 19901031 3 -.012821   10.0485
10001 19901130 3        0   10.0485
10001 19901231 3  .001299    10.013
10001 19910131 3  .013158  10.14475
10001 19910228 3  .012987   10.2765
end

ret = return

In June of each year, is it possible to compute decile breakpoints using only stocks that trade on the New York Stock Exchange (stocks, which have “1” as exchange code (see variable exchcd), and then use those breakpoints to sort all stocks in the sample (incl. stocks that trade on other exchange stocks) into 10 portfolios (e.g. 1 breakpoint = 10, data with MktCap <= 10 should go into the first portfolio)??

Thank you very much again.

Best,
Guest

[Clyde Schechter]

Yes, dropping duplicate observations with the -duplicates drop- command is quite sensible here.

I believe the following code, which is only minimally modified from before, will work now.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long(permno date) byte exchcd double ret float MktCap
10000 19860131 3        .      16.1
10000 19860228 3 -.257143     11.96
10000 19860331 3  .365385     16.33
10000 19860430 3 -.098592    15.172
10000 19860530 3 -.222656 11.793879
10000 19860630 3 -.005025 11.734593
10000 19860731 3 -.080808 10.786344
10000 19860829 3 -.615385  4.148594
10000 19860930 3 -.057143  3.911531
10000 19861031 3 -.242424  3.002344
10000 19861128 3      .06  3.182504
10000 19861231 3 -.377358  1.981566
10000 19870130 3 -.212121 1.5815313
10000 19870227 3        0 1.5815313
10000 19870331 3 -.384615    .97325
10000 19870430 3   -.0625  .9124414
10000 19870529 3 -.066667  .8515937
10000 19870630 3        .  .8515937
10001 19860131 3        .  6.033125
10001 19860228 3  .020408   6.15625
10001 19860331 3    .0252  6.217813
10001 19860430 3  .009901  6.279375
10001 19860530 3 -.009804  6.217813
10001 19860630 3 -.013069  6.033125
10001 19860731 3 -.010204  5.971562
10001 19860829 3  .072165    6.4025
10001 19860930 3 -.003077  6.317625
10001 19861031 3  .039216  6.565375
10001 19861128 3  .056604     6.937
10001 19861231 3     .015     6.937
10001 19870130 3 -.035714   6.68925
10001 19870227 3 -.074074   6.19375
10001 19870331 3    .0368  6.317625
10001 19870430 3 -.039216  6.069875
10001 19870529 3 -.071429  5.636312
10001 19870630 3  .051429  5.822125
10001 19870731 3  .021277     5.946
10001 19870831 3  .083333    6.4415
10001 19870930 3 -.022308       6.2
10001 19871030 3      .02     6.324
10001 19871130 3 -.029412     6.138
10001 19871231 3 -.033535     5.828
10001 19880129 3   .06383       6.2
10001 19880229 3      .08     6.696
10001 19880331 3 -.076296     6.076
10001 19880429 3  .030612     6.262
10001 19880531 3  .019802     6.386
10001 19880630 3 -.012039       6.2
10001 19880729 3      .03     6.386
10001 19880831 3  .029126     6.572
10001 19880930 3 -.021132   6.36225
10001 19881031 3  .039216   6.61175
10001 19881130 3        0   6.61175
10001 19881230 3 -.021132   6.36225
10001 19890131 3  .019608     6.487
10001 19890228 3  .038462    6.7365
10001 19890331 3  .017778    6.7365
10001 19890428 3  .074074    7.2355
10001 19890531 3 -.034483     6.986
10002 19890630 1  .017143     7.007
10001 19890731 3  .035714   7.25725
10001 19890831 3  .275862   9.25925
10001 19890929 3 -.027027  9.043625
10001 19891031 3  .070423    9.6805
10001 19891130 3  .039474 10.062625
10001 19891229 3  .037975  10.34775
10001 19900131 3 -.018519 10.156125
10001 19900228 3 -.006289  10.09225
10001 19900330 3  .012658 10.141625
10001 19900430 3        0 10.141625
10001 19900531 3 -.012658  10.01325
10002 19900629 1  .014103  10.05225
10001 19900731 3  .025641     10.31
10001 19900831 3     -.05    9.7945
10001 19900928 3  .040789    10.179
10001 19901031 3 -.012821   10.0485
10001 19901130 3        0   10.0485
10001 19901231 3  .001299    10.013
10001 19910131 3  .013158  10.14475
10001 19910228 3  .012987   10.2765
end

duplicates drop

//    GET A REAL DATE VARIABLE
gen int day = mod(date, 100)
replace date = (date - day)/100
gen int month = mod(date, 100)
replace date = (date - month)/100
rename date year
gen date = mdy(month, day, year)
format date %td
gen YM = mofd(date)
format YM %tm

//    GET A "FUNCTIONAL" YEAR THAT RUNS FROM JUNE THROUGH MAY
gen functional_year = year(dofm(YM-6))

//    SAVE ORIGINAL DATA
tempfile copy
save `copy'

//    KEEP ONLY THE JUNE DATA TO IDENTIFY PORTFOLIOS
keep if month(dofm(YM)) == 6
isid permno functional_year

//    IDENTIFY PORTFOLIOS IN EACH YEAR
levelsof functional_year, local(years)
gen int portfolio = .
foreach y of local years {
    pctile cutoffs = MktCap if functional_year  == `y' & exchcd == 1, nq(10)
    xtile pf = MktCap, cutpoints(cutoffs)
    replace portfolio = pf if functional_year == `y'
    drop pf cutoffs
}

//    NOW MERGE THESE RESULTS BACK WITH THE ORIGINAL DATA
merge 1:m permno functional_year using `copy'

Now, it doesn't, in fact, work with the example data. But that's because once we restrict to June observations with exchcd == 1, there are only 2 observations left, and it is therefore impossible to compute deciles! But this is just a short extract from your data, so, presumably, you won't encounter that problem. Assuming you don't, I expect that you will get through the loop, and then the -merge- will function correctly and you will have your full data back, with the assignment to portfolios done.

Note that the restriction of calculating the portfolios for all firms based only on the deciles of market capitalization for those with exchcd == 1 requires an extra step in the calculation. First the -pctile- command is restricted to the exchcd == 1 observations and calculates the cutpoints, placing them in the variable cutoffs. Then the -xtile- command uses the values in variable cutoffs to allocate all of the stocks into groups (variable pf) based on those.

[Guest]

Understood. The drop command deleted a couple of observations:

. duplicates drop

Duplicates in terms of all variables

(245 observations deleted)

Unfortunately, permno functional_year still doesn't work. As a result, the following commands were not executed.

. isid permno functional_year
variables permno functional_year do not uniquely identi
> fy the observations

I thought the duplicate demand should have solved the problem. I wonder why it is not working.

[Robson Glasscock]

Something is fishy here. I just downloaded monthly CRSP data for all firms, based on PERMNO and searching the entire database, from 1963-2016. There are 3,934,117 observations. Before any modifications to the data there are no duplicates based on PERMNO and DATE using duplicates. There are duplicates in terms of PERMCO and DATE, but this is for firms with multiple security issuances.

This is pure speculation, but the original "YMD" variable that Guest listed as being from CRSP is not in the CRSP database manual. I also did not see it listed as a variable contained in the back-end SAS tables that WRDS web queries pull from. I'm wondering if the original CRSP data was modified in some way, particularly the date, before processing was attempted.

This is what a web query download of unmodified CRSP data looks like for several firms:

* Example generated by -dataex-. To install: ssc install dataex
clear
input double permno long date double(shrcd permco)
14403  9891 30 2
14403 13969 30 2
14403 12355 30 2
12160  6419 10 3
12160  7637 10 3
12160  7973 10 3
63845 13117 11 4
63845  7517 11 4
63845 12418 11 4
15580  5782 11 5
end
format %d date

The " // Get a Real Date Variable // " code results in missing observations for "date" and "YM" on the CRSP data I pulled, and I think the reason for this is that the CRSP data used by the post author was modified somewhere along the way.

[Guest]

Hi Robson,

thank you for getting into this. During my discussion with Clyde, I noticed that the YMD variable is useless (I created that YMD date, you are right). Hence, I pulled the data again and did not modify anything except for MktCap (altprc*shrout). But when I run the code provided by Clyde, I still don't get the portfolio results. Did it work for you then?

Many thanks Stata Experts for all the help!

[Devra Golbe]

Guest,

While I believe it is an unwritten rule, so that you may be unaware, listers generally refrain from doing students' homework. On the off chance that I am wrong, and this is not homework, or perhaps worse, part of a final exam or project, let me offer some advice.

Statalist helps those who help themselves. Read, and heed, the FAQ. In particular:

Stata's manuals are extraordinarily good. Use them. If you don't find the answer there, search the forum or the many excellent Stata help sites around the web. (I think it's UCLA which has a particularly clear and complete one.)

Pose a discrete question as clearly as possible. Not "Clyde's code didn't work," but rather, "I typed......and Stata responded with ....."

Assuming your question is still about merging, if your command "didn't work" in the sense that Stata exited with an error message, read the error message and the manual. You probably got the syntax wrong in some way. If it "didn't work" in the sense that observations you thought would match did not, then examine those observations to figure out what went wrong.

Good luck!

[Nick Cox]

On Devra's points in #21

http://www.statalist.org/forums/help#adviceextras #4 lays out generic advice on homework questions. An unsubtle summary is "Please don't ask and please don't answer".

http://www.statalist.org/forums/help#stata is quite explicit that "didn't work" is fairly useless as a problem report, however honest it is and however difficult it may seem to explain.

[Guest]

Understood. Thank you for your help!

[Jae Li]

Clyde Schechter Hi Clyde, can I ask you a simple question about one line of your code

// GET A "FUNCTIONAL" YEAR THAT RUNS FROM JUNE THROUGH MAY
gen functional_year = year(dofm(YM-6))

Why not like this following way? I feel YM should be plus rather than minus 6. This is because the portfolio is constructed every June and July will be the first month for monthly stock returns to be merged back and through to the following May as an annual return. Why did you use minus 6 months? I am quite confused about how to merge the complete stock return back to the portfolio dataset. Hope to hear your thoughts! Many thanks in advance!

gen functional_year = year(dofm(YM+6))

[Clyde Schechter]

You may be right, or I may be right. It depends on whether the original problem requires pairing up the calculations based on portfolio year, say, 1 JUN 2015-31 MAY 2016 with other data from 2015 or from 2016. There may be some conventions in financial analysis about this, but I don't work in finance and would not be aware of them. And I didn't see a clear statement about that in the problem as posted.

[Jae Li]

@Clyde Schechter Hi Clyde, thank you for your explanations! I've been thinking about this issue for last few days but haven't found a real cure for it. What if it requires pairing up the calculations based on portfolio year? Should the code be "minus 6 months"? For example, I want to match data of JUL 2015 - JUN 2016 with other data of JAN 2015 - DEC 2015 for firm i.

Many thanks for your help and hope to hear from you!

[Clyde Schechter]

You need to explain in more detail. I don't know what a "portfolio year" is and how it relates to the dates you're talking about. I don't know what specific calculations you have in mind. This is a very long thread by now, and I can't remember the details of what went before. So I think it is best to present the problem as if it were a brand new thread and start from the beginning.