Portfolio Construction: How to include stocks that had at least one purchase transaction within the past six months in my portfolio?

[Maria Silverado]

Hello,

I understand that this may seem trivial since there are many posts addressing similar issues. However, after thoroughly reviewing them and making multiple attempts, I still haven't found a solution. Hence, this post.

I have a dataset consisting of monthly insider purchases and sales. I am trying to perform a calendar-time portfolio regression using the Fama-French three-factor model. Specifically, I want to construct a purchase portfolio that includes stocks with at least one purchase transaction in the past six months.
I am following previous research approach for portfolios that state: "purchase portfolios contain all stocks that had at least one purchase transaction, respectively, during the previous 6 months."

I am comfortable with building monthly portfolios and conducting Fama-French regressions in general. However, my main challenge is incorporating stocks with at least one purchase transaction during the past six months, given that my dataset consists of monthly insider trade data.

So far, I have created monthly portfolios for purchases and sales (including returns). I have also merged this file with the Fama-French factors and performed a regression of excess monthly portfolio returns on the Fama-French factors (SMB, HML, Mkt-RF). However, I am still missing the critical step of ensuring that my purchase portfolio correctly includes stocks that had at least one purchase transaction within the past six months.

Below are my code and data. Any guidance would be greatly appreciated.

LPERMNO is the firm identifier.
DirectorID is the insider identifier.
trade_month is the monthly trade date. Example,2013m10.
buy=1 for a buy
sell=1 for a sale
ret is the monthly return.

* Example generated by -dataex-. For more info, type help dataex
clear
input double(DirectorID LPERMNO) float(trade_month buy sell ret)
449772 54594 645 0 1 .06891969
1264823 54594 719 0 1 .009132484
1264823 54594 720 0 1 -.05749759
1264823 54594 731 0 1 .2442792
1264823 54594 734 0 1 .0459371
1264823 54594 755 0 1 -.03630401
1264823 54594 746 0 1 .0747912
1264823 54594 747 0 1 -.030397477
1264823 54594 753 0 1 .21292965
1264823 54594 756 0 1 .13597828
1264823 54594 764 0 1 -.034181483
1264823 54594 763 0 1 .02965621
1264823 54594 758 0 1 .002937399
1264823 54594 762 0 1 .034709167
1264823 54594 765 0 1 -.002859788
54556 50906 500 1 0 -.2248613
54556 50906 512 1 0 -.10709813
54556 50906 505 1 0 -.3042114
203115 50906 569 0 1 .0901408
66902 27991 490 0 1 .06947237
66902 27991 527 0 1 .016375225
66902 27991 538 0 1 .036401164
66902 27991 558 0 1 .07480573
66902 27991 564 0 1 -.03820328


**code
keep if buy==1
keep LPERMNO trade_month ret
**below gives in my opinion monthly equal weighted portfolio return
collapse (mean) ret , by(trade_month)

**the following step would be merging with fama-french data and running a regression on the portfolio returns with fama-french factor. Which i am comfortable with.
**As is, i don't think my portfolios are reflection the definition bolded above.

[Clyde Schechter]

Well, not being involved in finance myself, I'm not sure about what is intended by "a purchase portfolio that includes stocks with at least one purchase transaction in the past six months." If I interpret it as, for each month (call it portfolio_month) in the data set, identify all LPERMNOs that have a buy == 1 observation in the data set whose value of month is within the 6 months leading up to (and including) that portfolio month, the following code will do the trick:

preserve
keep LPERMNO trade_month buy ret
keep if buy
tempfile buys
save `buys'

restore
keep trade_month
duplicates drop
rangejoin trade_month -5 0 using `buys'

keep trade_month LPERMNO trade_month_U ret
keep if !missing(LPERMNO)
rename trade_month portfolio_month

-rangejoin- is written by Robert Picard and is available from SSC. To use it, you must also install -rangestat-, by Robert Picard, Nick Cox, and Roberto Ferrer, also from SSC.
If the "past 6 months" really should exclude the current month and look at the 6 months preceding it, then you can just change -5 0 to -6 -1 in the -rangejoin- command.

Now, in the example data shown, most months have no such stocks. But there are a few that do, and they appear to be correctly captured by this code.

If that is not what you want, let me suggest that when you post back you hand-calculate what you want the results from the example data to look like, and show that.

[Maria Silverado]

Thank you very much for your feedback