Generating a time-varying market share measure over the previous three years

[Stijn Groeningen]

Dear Statalisters,

I am trying to calculate a time-varying market share measure for each company (TargetPrimaryAdvisor) over the total (sample) market in the previous three years measured by transaction value. My data has the following form and contains approximately 20,000 observations over 28 years:

* Example generated by -dataex-. To install: ssc install dataex
clear
input double ValueofTransactionmil str30(TargetPrimaryAdvisor AquirorPrimaryAdvisor) float YearAnnounced
22.6 "" "PaineWebber" 1991
182.5 "" "PaineWebber" 1991
20.466 "" "Morgan Grenfell & Co Ltd" 1991
391.755 "Goldman Sachs & Co" "Smith Barney & Co Inc" 1991
169.914 "" "" 1991
25.455 "" "" 1991
44.539 "Samuel Montagu & Co Ltd" "Barclays de Zoete Wedd Ltd" 1991
20.563 "Samuel Montagu & Co Ltd" "Barclays de Zoete Wedd Ltd" 1991
758.604 "" "Morgan Guaranty Trust Co of NY" 1991
51.84 "J Henry Schroder Wagg & Co Ltd" "J Henry Schroder Wagg & Co Ltd" 1991
82.31 "Lehman Brothers" "Dillon, Read & Co Inc" 1991
27.847 "NM Rothschild & Sons Ltd" "Baring Brothers & Co Ltd" 1991
246.711 "" "Goldman Sachs International" 1991
208.456 "" "Sundal Collier Montagu A/S" 1991
46 "" "Wertheim Schroder" 1991
30 "Lazard Freres & Co LLC" "" 1991
820.928 "Goldman Sachs & Co" "Morgan Guaranty Trust Co of NY" 1991
20.871 "" "SG Warburg France SA" 1991
95.925 "Alex Brown & Sons Inc" "" 1991
130 "Goldman Sachs & Co" "Morgan Guaranty Trust Co of NY" 1991
712.225 "UBS Phillips & Drew Capital" "Goldman Sachs International" 1991
end
[/CODE]

I want to compute two variables, TargetMarketShare and AcquirorMarketShare, where, for example TargetMarketShare = sum(ValueofTransactionmil of TargetPrimaryAdvisor in year x) / sum(ValueofTransactionmil in year x-2, x-1 and x)

My concrete question is, how do I compute the total transaction value per advisor per year and how do I compute the total transaction value of the previous three years? I was able to compute a variable summing the total transaction value for each year, but I was unable to compute the total market value over the previous three years for each year in the following way:

bysort YearAnnounced: egen TotalTransactionValue1=total(ValueofTransactionmil )

Thanks in advance,

Stijn

[Stijn Groeningen]

Dear all,

I am hoping someone can help me, if there is anything I need to clearify, please let me know!

Best,
Stijn

[Phil Bromiley]

You didn't get a quick answer. You'll increase your chances of a useful answer by following the FAQ on asking questions - provide Stata code in code delimiters, readable Stata output, and sample data using dataex.

If you xtset your data for advisor and year, you can use lag operators (L., L2., L3.) to refer to transactions in previous years. You probably want to use egen by various cuts of the data. If the egen won't accept lag operators, just make a lag variable using generate: g lag1x=L1.x

g tottrans3= L.trans + L2.trans + L3trans

bysort year: egen tottransbyyr=total(trans)
Phil

[Stijn Groeningen]

Dear Phil,

First of all, thank you.
I unfortunately have multiple observations per advisor per year, this does not allow for lags. I adjusted my code to first compute the annual transaction value advised on by each advisor an then sum them each previous three years in te following way:

// Calculate one year deal value for each advisor
gen TargetAdvisorSum=.
egen TargetAdvisorID = group(TargetPrimaryAdvisor)
levelsof TargetAdvisorID, local(taradvlist)
set trace on
quietly foreach n in `taradvlist'{
bysort YearAnnounced: egen TargetAdvisorValue1=total(ValueofTransactionmil)
replace TargetAdvisorSum=TargetAdvisorValue1 if `n'==TargetAdvisorID
}

// Calculate three year deal value
quietly by TargetPrimary Advisor YearAnnounced: gen dup = cond(_N==1,0,_n)
drop if dup>1
drop dup
xtset TargetPrimaryAdvisor YearAnnounced
gen TargetAdvisorSum3=TargetAdvisorSum+l.TargetAdvisorSum+l2.TargetAdvisorSum

This however gives me an error because because it tries to redefine TargetAdvisorValue1 each TargetAdvisorID, which is not possible. Do you have any advise on solving that problem?

Stijn