Problem with calculating acquisition premiums using daily share prices from Datastream + matching to M&A Deal Datasheet

[Nils Brokmann]

Dear Stata community,

I am facing the following problem.
I would like to calculate acquisition premiums by using the targets share price minus the share price 4 weeks prior to deal announcement.

I retrieved my share price list from Datastream and it looks in the following way:
In the first column of the excel sheet I have the dates, and in the following columns I have the companies ISINs as headers and then the share prices (see attached screenshot).


Now I have to match the data with my Deal Datasheet using the target companies ISIN.
In my Deal Datasheet in Stata I already created the variable Target_ISIN. However, in the Share Price Excelsheet, the ISINs are in the first row, so if I import them to stata with first row as headers, I cannot match them with the variable of my master list "Target_ISIN".

When I transpose the data in the Excel sheet, I obtain a column with the firms' ISINs, however then I face a problem by having the dates as variable names.


Do you have any ideas, how I can transform and match these data and create my variable "Acquisition Premium"?

I did a lot of research to find solutions, but I could not find many information about calculating acquisition or bid premiums using daily share prices from Datastream.
Another problem is, that the date "30 days prior to deal announcement" might not exist, as the day might be a weekend. How can I code that precisely?

I hope you can understand my problems and give me a hint, how I can proceed.

The third screenshot with the black background is from my data that I have already imported to Stata.



Best regards,
Nils

[Clyde Schechter]

The Share Price Dataset is in wide layout, but you need to -reshape- it to long. That will go something like this:

ds Date, not
rename (`r(varlist)') price=
reshape long price, i(Date) j(ISIN_target) string

Then you should be able to -merge- the two data sets. (If the date variable in either file is not a true Stata internal format numeric date variable, you will need to transform it to that before you can carry out the merge properly.)

In the future, when showing data examples, please use the -dataex- command to do so. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Nils Brokmann]

Dear Clyde,

thank you very much for you answer. Your code worked perfectly.

Unfortunately, I am facing the next problem now...
As I want to calculate acquisition premiums by using the targets share price minus the share price 4 weeks prior to deal announcement, I matched the share prices of the deal announcement date into my M&A data using ISIN and Date. However, sometimes the deal announcement day seems to be on a weekend, which creates the problem that I have no share price available. My solution would be to use the next available share price (if Date is a saturday, then use the following monday).
The same problem appears again when using the share price 4 weeks prior to deal announcement. Here again I would need to use the next available share price (e.g. monday's share price).

Is there any solution to tell Stata that if the date does not exist, to use the next available share price?

My solution would be to lag the Date variable to the next day to see if a share price exists there. However, here I am facing the problem that I only lag the variables, not the date of the observation...
Do you have any solution for that?

This is my data example. The first two columns should normally be the Deal Announcement Dates in the format MM/DD/YYYY. In the third column "price", you can see some points, where the deal was announced during the weekend and no share price is available.

I hope this example can help you and you could understand my problems.

Example generated by -dataex-. For more info, type help dataex
clear
input str12 ISIN_target int(Deal_Announcement Date) double price
"JP3167640006" 18995 18995 358
"NO0005620856" 18171 18171 153.5
"SE0000221723" 20494 20494 86.05
"US0012041069" 20324 20324 61.41
"US0015471081" 21886 21886 3.01
"US00184X1054" 20093 20093 44.74
"US00234P1021" 21342 21342 21.25
"US0025671050" 21320 21320 83.34
"US00401C1080" 21739 21739 64.91
"US00790X1019" 21653 21653 .
"US0081901003" 18168 18168 53.86
"US01449J1051" 20485 20485 54.11
"US0173611064" 18304 18304 23.55
"US0184901025" 19834 19834 142
"US0204091088" 21139 21139 61.2
"US0214411003" 20174 20174 44.39
"US03211L1026" 21170 21170 .
"US0323461089" 19080 19080 23.77
"US03349M1053" 21304 21304 118
"US0352901054" 21852 21852 83.42
"US03822W4069" 20779 20779 8.1
"US04269X1054" 21717 21717 46.44
"US0495131049" 20351 20351 .
"US05463D1000" 20482 20482 17.93
"US0554821035" 18140 18140 16.06
"US05874B1070" 19936 19936 77.7
"US0673831097" 20932 20932 .
"US0733021010" 19847 19847 .
end
format %tdnn/dd/CCYY Deal_Announcement
format %tdnn/dd/CCYY Date
label var ISIN_target "Target ISIN"
label var Deal_Announcement "Date of Deal Announcement"
label var Date "Share Price on the day of Deal Announcement"

Best regards,
Nils

[Clyde Schechter]

In your example data, each ISIN_target has only a single observation. So there is nothing to lag (or, actually, forward). It is easy enough to calculate the date of the next Monday, but if there is no observation for that ISIN_target on any other date, then you are stuck anyway. So the data set you are working with, if the example is representative, cannot be used to solve this problem.

Let me assume that you have, or can get, some other data set that has daily prices on the ISINs you are working with for a wide range of dates, and it includes a daily date variable (Stata internal format) and a price. I will assume that dates like weekends and holidays do not appear in the data set. So here's what you can do with that:

xtset isin date
tsfill
sort isin date
rangestat (firstnm) ref_price = price, by(isin) interval(date 0 .)

This code will expand the data set to include new observations to fill the gaps for the missing dates (weekends and holidays), initially with the price missing. Then it will calculate a new variable, ref_price, which will be the same as price when that is not missing, and the next subsequent non-missing price when price is missing.

To use this code you must install -rangestat-, by Robert Picard, Nick Cox, and Roberto Ferrer, available from SSC.

Once you have done this, you can then merge it to your M&A data set and use the ref_price variable instead of the price variable.

[Nils Brokmann]

Once again thank you very much for your fast answer.
I solved the problem now, maybe a bit too complicated.

I was able to match 176 of 195 observations with the announcement days share price. Then I created two new date variables that was one day and two days after the deal announcement, and matched the share prices to these days. Finally I replaced the missing share prices with the share prices of the days after the announcement. In the end, I was able to gather all prices. I hope that this is a way that is correct to do.

Thank you again for your help Clyde. Without you I would be still stuck with the question how to reshape my share price dataset.
Best regards,
Nils

[Clyde Schechter]

[/quote]I hope that this is a way that is correct to do.[/quote]
It will be correct for most Saturdays and Sundays. It will fail for weekend days when the following Monday is a holiday, and will fail for most holidays. But maybe for your purposes those exceptions don't come up at all, or insufficiently frequently that they won't matter.