calculate the number of holding months of the portfolio based on the conditions

[mws macekk]

I have the above example data regarding selling or buying assets. I would like to calculate the number of holding months of the portfolio a and b indicated in id (i.e. how many months did the individual hold the asset before fully sold since the first purchase). The variable "reset_zero" equals 1 if the asset at that time was sold completely, so the number of holding months has to be reset and recalculated. Basically, the number "1" in the variable "reset_zero" creates a circle. I tried different ways and still cannot generate desired results as the variable "holding_month", which was created manually. Any suggestions are highly appreciated. Thank you very much.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 id float(trd_month1 month_id) byte i_buy_sell float(reset_zero holding_month)
"a" 695 284 1 . 0
"a" 696 696 . . 1
"a" 697 697 . . 2
"a" 698 698 . . 3
"a" 699 699 . . 4
"a" 700 700 . . 5
"a" 702 290 0 1 7
"a" 701 290 1 . 0
"a" 702 291 0 1 1
"a" 705 294 1 . 0
"a" 705 294 1 . 0
"a" 706 295 0 . 1
"a" 707 707 . . 2
"a" 708 297 0 1 3
"b" 662 251 1 . 0
"b" 662 251 0 1 0
"b" 663 252 1 . 0
"b" 663 252 0 1 0
"b" 663 252 1 . 0
"b" 663 252 0 1 0
"b" 663 252 1 . 0
"b" 664 253 0 1 1
end
format %tm trd_month1
label values i_buy_sell buysell
label def buysell 0 "sell", modify
label def buysell 1 "buy", modify

[Clyde Schechter]

I am thoroughly confused by your question. First, what is the relevant time variable here: trd_month1 or month_id? I think it's trd_month1. But then what is month_id, and why does it skip around over a large range of values?

Next, and probably more important, For asset "a", in the seventh observation, in 2018m7 (or is it 290?)( there is a complete sell-off with reset_zero = 1. But then the next observation is a buy in 2018m6 which precedes the date in the 6th observation, yet you want to start an entirely new spell of holding here. Finally in observation 9 you have another complete sell-off of asset "a", but this is also in 2018m7. So what's going on here? I would think that observation 8 should preced observation 7 in the dasta, and only observation 9 would be the complete sell-off. Or something like that.

I'm completely confused and the chronology seems to contradict itself.

[Ken Chui]

gen buydate = trd_month1 if i_buy_sell == 1
bysort id: replace buydate = buydate[_n-1] if buydate == .
gen wanted = trd_month1 - buydate

Edit: I didn't notice that data errors mentioned in #2. OP please do check if your data are sorted or coded correctly.

[mws macekk]

I am thoroughly confused by your question. First, what is the relevant time variable here: trd_month1 or month_id? I think it's trd_month1. But then what is month_id, and why does it skip around over a large range of values?

Next, and probably more important, For asset "a", in the seventh observation, in 2018m7 (or is it 290?)( there is a complete sell-off with reset_zero = 1. But then the next observation is a buy in 2018m6 which precedes the date in the 6th observation, yet you want to start an entirely new spell of holding here. Finally in observation 9 you have another complete sell-off of asset "a", but this is also in 2018m7. So what's going on here? I would think that observation 8 should preced observation 7 in the dasta, and only observation 9 would be the complete sell-off. Or something like that.

I'm completely confused and the chronology seems to contradict itself.

Sorry, you are completely right. When I was trying to make the example data, I accidently modified something. Unfortunately, I cannot edit the original post. I list the example here.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 id float(trd_month1 month_id) byte i_buy_sell float(reset_zero holding_month)
"a" 695 284 1 . 0
"a" 696 285 . . 1
"a" 697 286 . . 2
"a" 698 287 . . 3
"a" 699 288 . . 4
"a" 700 289 . . 5
"a" 702 291 0 1 7
"a" 703 292 1 . 0
"a" 704 293 0 1 1
"a" 705 294 1 . 0
"a" 705 294 1 . 0
"a" 706 295 0 . 1
"a" 707 296 . . 2
"a" 708 297 0 1 3
"b" 662 251 1 . 0
"b" 662 251 0 1 0
"b" 663 252 1 . 0
"b" 663 252 0 1 0
"b" 663 252 1 . 0
"b" 663 252 0 1 0
"b" 663 252 1 . 0
"b" 664 253 0 1 1
end
format %tm trd_month1
label values i_buy_sell buysell
label def buysell 0 "sell", modify
label def buysell 1 "buy", modify

[Clyde Schechter]

Thanks for clearing that up.

sort id trd_month1, stable
by id: gen byte holding_spell = sum(reset_zero[_n-1] == 1)
sort id holding_spell, stable
by id holding_spell, sort: gen wanted = trd_month1 - trd_month[1]

[mws macekk]

gen buydate = trd_month1 if i_buy_sell == 1
bysort id: replace buydate = buydate[_n-1] if buydate == .
gen wanted = trd_month1 - buydate

Edit: I didn't notice that data errors mentioned in #2. OP please do check if your data are sorted or coded correctly.

Thanks a lot.

[mws macekk]

Thanks for clearing that up.

sort id trd_month1, stable
by id: gen byte holding_spell = sum(reset_zero[_n-1] == 1)
sort id holding_spell, stable
by id holding_spell, sort: gen wanted = trd_month1 - trd_month[1]

Thank you very much