Generating cumulative sum of deal values for 3 year intervals

[Ray]

Hello,

i'm trying to generate cumulative sum of deal values (£ mn.) for acquisitions of target companies in an interval of 3 years prior to any acquisition announcement. I've tried using a similar code as given in Stata Journal (2007) 7: Events in intervals, but can't get desired result. Basically, i'm trying to see the cumulative sum of the amounts paid for prior acquisitions during a span of 3 years of any acquisition by each acquiring company in the sample period. The code i've used to generate this sum (i'm not sure how to generate cumulative sum) as yr3_cum_dealval in an interval of 3 years:
gen event_date = .
quietly forval i = 1/`= _N' {
sum (deal_value_mn) if lspdno == lspdno[`i'] & inrange(event_date[`i'] – event_date, 1, 1098)
replace yr3_cum_dealval = r(sum) in `i'
}

Here, deal_value_mn is theamount paid in £ mn. for each acquisition, lspdno is the unique id for each acquiring company, event_date date of announcement of each deal/acquisition, 1098 = 366*3 (3 years interval).

I don't understand in the column yr3_cum_dealval, why i always get '0' as starting deal value in the span of 3 years, when it should rather be the actual amount paid in the first acquisition, the sum of 1st deal amount and 2nd deal amount for the second acquisition, likewise the sum of first two amounts paid plus that paid for the 3rd in case of cumulative deal value for third acquisition (provided the first two acquisitions took place within 3 years of the third deal) and so on.
Also, when there were 2 acquisitions announced on the same date, the cumulative deal value for each of these dates should be different (i get same yr3_cum_dealval for 2 same date acquisitions), the 1st same date should have the sum of amounts paid for the prior 3 year deals as well as current deal amount, whereas the 2nd same date should have the first same event_date amount paid included in the cumulative sum with prior 3 year amounts, as well as the amount corresponding to the current acquisition.

I'm providing the dropbox link for a snapshot of my data for just 1 company doing many acquisitions from year 1994 until 2006, where the column with name yr3_cum_dealval shows what i got usingthe above code, while Correct_yr3_cum_dealval shows what i need to get. Here it is: https://dl.dropboxusercontent.com/u/...alue_ranks.dta

Can anybody please help me to understand where i'm going wrong?Thanks,
Suparna Ray

[Clyde Schechter]

gen event_date = .
quietly forval i = 1/`= _N' {
sum (deal_value_mn) if lspdno == lspdno[`i'] & inrange(event_date[`i'] – event_date, 1, 1098)
replace yr3_cum_dealval = r(sum) in `i'
}

So you have several problems here. First of all, you start by setting event_date to missing, and you never change it. So when you get to the -sum- command in your loop. So the inrange() condition will never be met, because event_date[`i']-event_date will always be missing value and therefore not in range. When -sum- runs with 0 observations, r(sum) is returned as zero. So yr3_cum_dealval will be set to zero everywhere.

I suspect this is not the code you actually ran, because it seems you actually got some non-zero results. Perhaps the first line of code was actually

gen yr3_cum_dealval =.

Assuming that is the case,

I don't understand in the column yr3_cum_dealval, why i always get '0' as starting deal value in the span of 3 years

Well, assuming your observations are sorted by lspdno and event_date (admittedly you don't say this, I'm just guessing since the link to your data set does not work for me), in the first observation for each lspdno, event_date[`i'] will be <= event_date, so the difference event_date[`i']-event_date will be <= 0, and therefore not between 1 and 1098. Accordingly there will be no observations for that -sum- command.

Also, when there were 2 acquisitions announced on the same date, the cumulative deal value for each of these dates should be different (i get same yr3_cum_dealval for 2 same date acquisitions),

Well, no. If you have two observations with the same lpsdno and the same value of event_date, everything in the -if- qualifier for the -sum- command will be the same for both of those observations, so the corresponding value for yr3_cum_dealval will also be the same.

Unfortunately, because I could not see your data, and I find your description of what you're trying to do confusing, all I can do is point out why this code won't do what you're looking for and why it would produce precisely the kind of results you're describing. At this time I have no suggestions for how to accomplish what you seek. But perhaps with this understanding of your existing code you will see how to fix it yourself.

[Ray]

Thanks, Clyde - i see that the code got posted wrongly while pasting: it was indeed gen yr3_cum_dealval = .

I didn't sort my observations before using that code - i just modified the code for counting events in intervals in Stata Journal 7 to use them for generating cumulative sum in intervals - i really don't understand how to fix this code with what you say.
1) how to fix the problem in event_date so as to make it count within 1098 days, i.e. 3 years?
2) what changes need to be made in the above code to get 2 different cumulative sums according to different deal values on 2 same event_dates by the same company?

How else can i post snapshot of my data, other than giving the link to dta.file so that someone could see it & help to fix this code?

Thanks again

[Clyde Schechter]

The best way to show some data would be to -list- a sample of relevant observations and variables, and then copy/paste the output from Stata's results window into a code block in the Forum's editor. (See the FAQ for how to set up a code block if you don't already know.)

[Nick Cox]

Now also cross-posted at http://stackoverflow.com/questions/2...year-intervals

The obligation to tell us about cross-posting works every which way.

Please just re-register as "Suparna Ray". Whenever you forget to add a signature, you are missing the request to use full real names.

[Suparna Ray]

I've re-registered as Suparna Ray. i'll now delete this ID - BTW, i had put my full name when i originally posted this query in statalist to avoid complications - i wasn't aware that i've got to keep repeating my name everytime i add a comment to my original post. Thanks for letting me know.

[Nick Cox]

Thanks but I really don't know how this was unclear. The FAQ Advice spells it out

You are asked to post on Statalist using your full real name, including one or more given names and a family name or surname, such as "Ronald Fisher" or "Gertrude M. Cox". Giving full names is one of the ways in which we show respect for others and is a long tradition on Statalist.

People can't be expected to read your first post or to remember that you signed it.

[Suparna Ray]

Many thanks - i've got the modified code from Nick in stackoverflow, although there still remains a slight problem to figure out - getting different cumulative deal values for the same day acqusitions, when amount paid in each case differs - the link of stack overflow is here:http://stackoverflow.com/questions/2...83979#28583979
Sorry, for the confusion with my login IDs.

[Nick Cox]

As also indicated on Stack Overflow, treating different transactions on the same date differently could not be done reproducibly. I'll expand on that here.

If Stata has two observations for the same date, then it is quite arbitrary which is first in the dataset even when sorted on date. As far as Stata is concerned two observations with daily date 17feb2015 are sorted correctly in either of the two possible orders. This is just a variant on homely problems: if Bill and Bob have the same height then Bill before Bob and Bob before Bill are both sorted in height order.

You have an idea of cumulative sums. I don't know why substantively, but that is not the key point.

If one value is 300 and another value is 700 for the same date, then if you cumulate them one way, you get 300 1000 and if you cumulate the other you get 700 1000 but 1000 is not even in the same observation.

The problem is compounded for three or more observations with the same daily date.

Doing all this within panels makes no difference.

So, there is no Stata problem here; you just are wanting to make your definitions arbitrary, and I advise against.

[Suparna Ray]

Thanks a lot, Nick - i understand now what you mean now as 'arbitrary definitions' & deal accordingly because there are indeed quite a few same day events by same companies.

[Minh Hang]

Hi guys. I am very new to Stata and is struggling with this code for days. Alreay search a lot from google and statalist but cannot find a way out, could anyone please help me out?

I have 3 variables which are: id (identifier), year, and amount(consumsion). I do exactly what you guys doing here and receive error message: 1 unknown weight type My Stata codes is as follow:

quietly forval i = 1/=_N' {
sum ( amount ) if id == id [i'] & inrange( year [i']- year , 0, 3)
replace wanted = r(sum) ini'
}

When I try the code without id == id [`i'], there is no error message.

Attachment is my Stata screenshot for more detail. Please please if someone has faced this problem, can you share with me the solution?

I use Stata 14.2 and I thank you very much in advance.

[Nick Cox]

Minh: On the evidence of #11 you aren't doing exactly what others do. Your implied reference is to the code in #2 and you're missing out the left or back-ticks and inserting a space which doesn't belong.

quietly forval i = 1/`=_N'  {
sum ( amount ) if id == id[`i'] & inrange(year[`i'] - year , 0, 3)
replace wanted = r(sum) in `i'
}

That said, this is an oldish thread (2015) and for the calculation of #2 I would now recommend rangestat (SSC)

rangestat (sum) amount, interval(year 0 3) by(id)

[Minh Hang]

oh man, it is really helpful. Thank you very much for your quick support, Nick.