Combine two datasets and estimate values in relation to dates

[Ali Hassan]

Hello,

I am doing an event study and posted a question a few days ago, and was able to solve the problem and complete the event study thanks to help from here. However, I need to estimate the market value of the acquiring companies 4 days prior to an acquisition announcement.

I currently have the following datasets:

1) Contains information for all companies ever listed on a particular stock exchange

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 company_id int firm str40 securityname float price double sharesissued float(date stockreturn)
"NO0005327270" 1117 "Saga Petroleum B"       79  34083487 13146 .
"NO0003194201" 2272 "Tide"                 92.5    759000 13146 .
"NO0003053605" 1955 "Storebrand"             35 276418702 13146 .
"NO0005418004" 2369 "Skiens Aktiemølle"    220   1132369 13146 .
"NO0003083107" 2218 "Awilco ser. A"          60  17472000 13146 .
"NO0005525808" 2379 "Stentofon"             2.5  42000000 13146 .
"NO0003029803" 2345 "Protector Forsikring"  148   2389014 13146 .
"NO0003049306" 5023 "Selmer"                135   5390299 13146 .
"NO0003200800" 1795 "Havtor"               19.6 182784858 13146 .
"NO0003285009" 2307 "Dual Invest A"          46   7415016 13146 .
end
format %td date

2)Events: contains Dates of m&a announcements, with additional variables, such as isin- number, name of the acquiring company, public status etc. etc. (here i only show the most important variables)

* Example generated by -dataex-. To install: ssc install dataex
clear
input int event_date str12 company_id str30 Bidder
13529 "NO0004951104" "Rieber & Son ASA"     
13530 "NO0004306408" "Hafslund ASA"         
13545 "NO0003679102" "Blom ASA"             
13549 "NO0003390205" "Smedvig ASA"          
13562 "NO0004822503" "Merkantildata ASA"    
13594 "NO0010031479" "Den Norske Banken ASA"
13605 "NO0003042608" "RingCom ASA"          
13623 "NO0003060303" "Wenaas ASA"           
13640 "NO0004822503" "Merkantildata ASA"    
13642 "NO0005668905" "Tomra Systems ASA"    
end
format %td event_date

I am wondering if someone could help me estimate the market value of the bidders equity (price*shares issued) 4 days prior to the announcement of an m&a and add it to the events dataset

To clarify I want to generate the market value of the bidder 4 days prior to the annoucnement of an m&a and then add this "bidder market value" to my events dataset so that for each event date i also have the bidders market value in the "events" dataset.

If I try to estimate the market value in dataset 1 and then try to

Use dataset2
joinby company_id date, using (dataset1)

But this would probably only get me the market value on the actual date of the annoucnement?


Thanks in advance

[Clyde Schechter]

Well, your example data clearly shows the organization of your data, but, unfortunately, the stock exchange data in your example does not include any of the bidders in your events examples, nor do any of the dates correspond to four days before the event. To try to test this code, I have modified your stock exchange data set by adding a first observation that does contain such a match.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int event_date str12 company_id str30 Bidder
13529 "NO0004951104" "Rieber & Son ASA"    
13530 "NO0004306408" "Hafslund ASA"        
13545 "NO0003679102" "Blom ASA"            
13549 "NO0003390205" "Smedvig ASA"         
13562 "NO0004822503" "Merkantildata ASA"   
13594 "NO0010031479" "Den Norske Banken ASA"
13605 "NO0003042608" "RingCom ASA"         
13623 "NO0003060303" "Wenaas ASA"          
13640 "NO0004822503" "Merkantildata ASA"   
13642 "NO0005668905" "Tomra Systems ASA"   
end
format %td event_date
tempfile events
save `events'

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 company_id int firm str40 securityname float price double sharesissued float(date stockreturn)
"NO0004951104" 9999 "Rieber & Son ASA"      100   1234567 13525 .// ADDED OBSERVATION THAT MATCHES
"NO0005327270" 1117 "Saga Petroleum B"       79  34083487 13146 .
"NO0003194201" 2272 "Tide"                 92.5    759000 13146 .
"NO0003053605" 1955 "Storebrand"             35 276418702 13146 .
"NO0005418004" 2369 "Skiens Aktiemølle"    220   1132369 13146 .
"NO0003083107" 2218 "Awilco ser. A"          60  17472000 13146 .
"NO0005525808" 2379 "Stentofon"             2.5  42000000 13146 .
"NO0003029803" 2345 "Protector Forsikring"  148   2389014 13146 .
"NO0003049306" 5023 "Selmer"                135   5390299 13146 .
"NO0003200800" 1795 "Havtor"               19.6 182784858 13146 .
"NO0003285009" 2307 "Dual Invest A"          46   7415016 13146 .
end
format %td date
tempfile exchange
save `exchange'

use `events', clear
clonevar date = event_date
clonevar securityname = Bidder
rangejoin date -4 -4 using `exchange', by(securityname)
gen mkt_value = price*sharesissued
drop *_U

Note: -rangejoin- is written by Robert Picard and is available from SSC.

[Ali Hassan]

Thanks,

I see that you create "securityname" and then
"rangejoin...by(securityname) "

However, bidder in "events" is not spelled exactly as securityname in "stocks", therefore I used

 
use events
clonevar date = event_date
rangejoin date -4 -4 using stocks, by(company_id)
gen mkt_value = price*sharesissued
 drop *_U

And I get "237 missing values generated" and the results are:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int event_date str12 company_id str30 Bidder float mkt_value
13529 "NO0004951104" "Rieber & Son ASA"               .
13530 "NO0004306408" "Hafslund ASA"                   .
13545 "NO0003679102" "Blom ASA"               544840832
13549 "NO0003390205" "Smedvig ASA"           4257855232
13562 "NO0004822503" "Merkantildata ASA"     2372702208
13594 "NO0010031479" "Den Norske Banken ASA" 1.9215e+10
13605 "NO0003042608" "RingCom ASA"                    .
13623 "NO0003060303" "Wenaas ASA"             735454976
13640 "NO0004822503" "Merkantildata ASA"     2001616128
13642 "NO0005668905" "Tomra Systems ASA"              .
end
format %td event_date

However, I have around 600 events. I think there is a problem that on same dates there are multiple m&a announcements

* Example generated by -dataex-. To install: ssc install dataex
clear
input int event_date str12 company_id str30 Bidder float mkt_value
13529 "NO0004951104" "Rieber & Son ASA"                         .
13530 "NO0004306408" "Hafslund ASA"                             .
13545 "NO0003679102" "Blom ASA"                         544840832
13549 "NO0003390205" "Smedvig ASA"                     4257855232
13562 "NO0004822503" "Merkantildata ASA"               2372702208
13594 "NO0010031479" "Den Norske Banken ASA"           1.9215e+10
13605 "NO0003042608" "RingCom ASA"                              .
13623 "NO0003060303" "Wenaas ASA"                       735454976
13640 "NO0004822503" "Merkantildata ASA"               2001616128
13642 "NO0005668905" "Tomra Systems ASA"                        .
13682 "NO0004306408" "Hafslund ASA"                    3038943232
13755 "NO0004822503" "Merkantildata ASA"               2702548992
13772 "NO0003028607" "Actinor Shipping AS"              932644224
13807 "NO0010199151" "Petroleum Geo-Services ASA"     15460043776
13817 "NO0003054405" "Visma ASA"                                .
13852 "NO0003052508" "Agresso Group ASA"                        .
13852 "NO0005638858" "Olav Thon Eiendomsselskap ASA"            .
13857 "NO0004822503" "Merkantildata ASA"               4026173952
13864 "NO0003095002" "Industrifinans Naeringseiendom"           .
13866 "NO0003052508" "Agresso Group ASA"                        .
13867 "NO0004306408" "Hafslund ASA"                    3038943232
13891 "NO0003111700" "Borgestad ASA"                    137391104
13906 "NO0003514002" "Aker RGI Holding ASA"            6612536320
13923 "NO0003048308" "Norsk Vekst ASA"                   1.68e+09
13930 "NO0004822503" "Merkantildata ASA"               5803162112
13969 "NO0004135609" "Norske Skogindustrier ASA"                .
13975 "NO0003143604" "DSND"                            4952403456
13990 "NO0004677006" "Kverneland ASA"                  1514854144
14011 "NO0004951104" "Rieber & Son ASA"                3584211200
14017 "NO0004822503" "Merkantildata ASA"              10470928384
14031 "NO0003052508" "Agresso Group ASA"               1344173184
14056 "NO0010199151" "Petroleum Geo-Services ASA"     18774423552
14070 "NO0003028904" "Schibsted ASA"                    9.141e+09
14101 "CY0100470919" "Prosafe ASA"                     2463809024
14108 "NO0003044703" "Braathens ASA"                   1561818880
14108 "NO0003042202" "Elkjop ASA"                      2792403968
14110 "NO0003514002" "Aker RGI Holding ASA"                     .
14110 "NO0004135609" "Norske Skogindustrier ASA"                .
14116 "NO0003062507" "Aker Maritime ASA"               5501638656
14124 "NO0004822503" "Merkantildata ASA"                        .
14126 "NO0003052508" "Agresso Group ASA"               1234409728
14181 "NO0003052508" "Agresso Group ASA"                        .
14188 "NO0004822503" "Merkantildata ASA"                        .
14199 "NO0003062804" "Thrane-Gruppen ASA"               264425664
14202 "NO0005598706" "Avenir ASA"                               .
14209 "NO0003052508" "Agresso Group ASA"                        .
14236 "NO0004822503" "Merkantildata ASA"                        .
14249 "NO0003072506" "Choice Hotels Scandinavia ASA"            .
14266 "NO0004951104" "Rieber & Son ASA"                2588596992
14287 "NO0003073108" "Synnove Finden ASA"               133469000
14292 "NO0004822503" "Merkantildata ASA"                        .
14293 "NO0004822503" "Merkantildata ASA"                        .
14329 "NO0003168908" "Fosen Trafikklag ASA"                     .
14330 "NO0005806802" "Veidekke ASA"                    1310500864
14333 "NO0003109407" "Eltek ASA"                        359439680
14363 "NO0004822503" "Merkantildata ASA"                        .
14363 "NO0005806802" "Veidekke ASA"                             .
14396 "NO0003081101" "Evercom Network ASA"              254468048
14400 "NO0004822503" "Merkantildata ASA"               8348500992
14406 "NO0005625103" "Technor"                           93875216
14424 "NO0003058901" "Software Innovation ASA"          360499488
14445 "NO0003109308" "Profdoc ASA"                      110555520
14447 "NO0004677006" "Kverneland ASA"                           .
14458 "NO0003049306" "Selmer ASA"                      1468671360
14483 "NO0003057705" "Narvesen A/S"                      1.85e+09
14501 "NO0003057705" "Narvesen A/S"                      1.87e+09
14511 "NO0003070906" "TANDBERG Television ASA"         3010563840
14520 "NO0003143604" "DSND"                            2670540288
14545 "NO0005598706" "Avenir ASA"                               .
14574 "NO0003054108" "Pan Fish ASA"                     939033024
14586 "NO0003098402" "Enitel ASA"                               .
14591 "NO0010001118" "Itera ASA"                        284830272
14594 "NO0004845405" "Moelven Industrier ASA"                   .
14598 "NO0010001118" "Itera ASA"                        337763360
14631 "NO0005806802" "Veidekke ASA"                    1549809792
14647 "NO0003058901" "Software Innovation ASA"         1128431232
14662 "NO0010019649" "EDB Business Partner ASA"        7309444608
14670 "NO0010001118" "Itera ASA"                                .
14686 "NO0003054736" "SuperOffice ASA"                  844098688
14687 "NO0004951104" "Rieber & Son ASA"                1692544128
14696 "NO0010001118" "Itera ASA"                       1692138368
14703 "NO0004135609" "Norske Skogindustrier ASA"                .
14705 "NO0004822503" "Merkantildata ASA"                        .
14714 "NO0003399909" "Odfjell SE"                      1903841280
14717 "NO0004822503" "Merkantildata ASA"               9331522560
14728 "NO0010010473" "StepStone ASA"                            .
14733 "NO0003043309" "Kongsberg Gruppen ASA"                    .
14740 "NO0003058901" "Software Innovation ASA"                  .
14761 "NO0003058901" "Software Innovation ASA"                  .
14774 "NO0003054108" "Pan Fish ASA"                    3909596672
14787 "NO0004822503" "Merkantildata ASA"               8613258240
14810 "NO0003068306" "Norsk Lotteridrift ASA"                   .
14816 "NO0005052605" "Norsk Hydro ASA"                93842022400
14853 "NO0005806802" "Veidekke ASA"                             .
14854 "NO0003054108" "Pan Fish ASA"                    8201410048
14854 "NO0010010473" "StepStone ASA"                   6986569728
14867 "NO0003089104" "Expert Eilag ASA"                         .
14873 "NO0003109407" "Eltek ASA"                                .
14885 "NO0003058901" "Software Innovation ASA"          983534272
14901 "NO0003046203" "Steen & Strom ASA"                        .
end
format %td event_date

For example date 13852 appears twice and for both of these I dont get a market value and I know that I have the stock price and shares issued for both of these companies in the stock data set, any suggestions on how to solve this?

[Clyde Schechter]

In order to troubleshoot this I need data sets that exhibit the problem. While you have pointed out the issue with date 13852 in the events data, without a corresponding example for the exchange data that includes those firms on those dates, I can't help you.

[Ali Hassan]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 company_id int firm str40 securityname float price double sharesissued float(date stockreturn)

"NO0005638858"  1308 "Olav Thon Eiendomsselskap"                     235    11897922 13842    -.02105341
"NO0005638858"  1308 "Olav Thon Eiendomsselskap"                     235    11897922 13843             0
"NO0005638858"  1308 "Olav Thon Eiendomsselskap"                     235    11897922 13844             0
"NO0005638858"  1308 "Olav Thon Eiendomsselskap"                     235    11897922 13845             0
"NO0005638858"  1308 "Olav Thon Eiendomsselskap"                     235    11897922 13846             0
"NO0005638858"  1308 "Olav Thon Eiendomsselskap"                     235    11897922 13849             0
"NO0005638858"  1308 "Olav Thon Eiendomsselskap"                     230    11897922 13850   -.021506205
"NO0005638858"  1308 "Olav Thon Eiendomsselskap"                     235    11897922 13851    .021506205
"NO0005638858"  1308 "Olav Thon Eiendomsselskap"                     235    11897922 13852             0
end
format %td date

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 company_id int firm str40 securityname float price double sharesissued float(date stockreturn)
"NO0003052508"  5025 "Agresso Group"                                23.5   35127000 13844    -.02105341
"NO0003052508"  5025 "Agresso Group"                                23.5   35127000 13845             0
"NO0003052508"  5025 "Agresso Group"                                23.5   35127000 13846             0
"NO0003052508"  5025 "Agresso Group"                                23.5   35127000 13849             0
"NO0003052508"  5025 "Agresso Group"                                  25   35127000 13850      .0618754
"NO0003052508"  5025 "Agresso Group"                                24.5   35127000 13851    -.02020271
"NO0003052508"  5025 "Agresso Group"                                25.5   35127000 13852     .04000533
"NO0003052508"  5025 "Agresso Group"                                24.5   35127000 13853    -.04000533
end
format %td date

I was able to retrieve stock data around the announcement date for the two companies that make acquisitions on date 13852, as you see I have sharesissued and stockprice 4 days before the eventdate but using the code you wrote in your first post I get blank on market value of equity 4 days prior to the m&a.

I think there is a problem when two companies announce an m&a on the same day, and I also think there is a problem with companies that announce more than one m&a (I have made sure that one company cannot make multiple acquisitions on the same date).

[Clyde Schechter]

Oh, it is clear now what is going on. Your exchange data set does not have any observations for those firms on date 13852-4 = 13848. That date is skipped in your exchange data file. So -rangejoin- is correct in not returning any matches.

[Ali Hassan]

Ah, that makes sense, it also makes my life more difficult. Is it possible to write a code that would give me the market value on the closest day prior to the event date, which has stockprice?

[Clyde Schechter]

Well, yes, but then for most of your events, the date will be just the day before. I have no idea if that's a problem--finance is definitely not my area. But I assume you had a good reason for picking 4 days originally. So here's a different idea. This code will look back four days from the event date and it will pick the earliest market date available in that range. So if you have information from four days before, you will get that. If not you will get 3 days before (if available), or 2 days before (if that's the best we can do) or 1 day before (if that's the best we can do). You will end up with missing values only if all four days preceding the event date are missing. It also creates a new variable, mkt_date, that indicates what the date of the market valuation actually is. Does that sound viable?

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 company_id int firm str40 securityname float price double sharesissued float(date stockreturn)

"NO0005638858"  1308 "Olav Thon Eiendomsselskap"                     235    11897922 13842    -.02105341
"NO0005638858"  1308 "Olav Thon Eiendomsselskap"                     235    11897922 13843             0
"NO0005638858"  1308 "Olav Thon Eiendomsselskap"                     235    11897922 13844             0
"NO0005638858"  1308 "Olav Thon Eiendomsselskap"                     235    11897922 13845             0
"NO0005638858"  1308 "Olav Thon Eiendomsselskap"                     235    11897922 13846             0
"NO0005638858"  1308 "Olav Thon Eiendomsselskap"                     235    11897922 13849             0
"NO0005638858"  1308 "Olav Thon Eiendomsselskap"                     230    11897922 13850   -.021506205
"NO0005638858"  1308 "Olav Thon Eiendomsselskap"                     235    11897922 13851    .021506205
"NO0005638858"  1308 "Olav Thon Eiendomsselskap"                     235    11897922 13852             0
"NO0003052508"  5025 "Agresso Group"                                23.5   35127000 13844    -.02105341
"NO0003052508"  5025 "Agresso Group"                                23.5   35127000 13845             0
"NO0003052508"  5025 "Agresso Group"                                23.5   35127000 13846             0
"NO0003052508"  5025 "Agresso Group"                                23.5   35127000 13849             0
"NO0003052508"  5025 "Agresso Group"                                  25   35127000 13850      .0618754
"NO0003052508"  5025 "Agresso Group"                                24.5   35127000 13851    -.02020271
"NO0003052508"  5025 "Agresso Group"                                25.5   35127000 13852     .04000533
"NO0003052508"  5025 "Agresso Group"                                24.5   35127000 13853    -.04000533
end
format %td date
tempfile exchange
save `exchange'

* Example generated by -dataex-. To install: ssc install dataex
clear
input int event_date str12 company_id str30 Bidder
13529 "NO0004951104" "Rieber & Son ASA"            
13530 "NO0004306408" "Hafslund ASA"                
13545 "NO0003679102" "Blom ASA"                    
13549 "NO0003390205" "Smedvig ASA"                  
13562 "NO0004822503" "Merkantildata ASA"            
13594 "NO0010031479" "Den Norske Banken ASA"        
13605 "NO0003042608" "RingCom ASA"                  
13623 "NO0003060303" "Wenaas ASA"                  
13640 "NO0004822503" "Merkantildata ASA"            
13642 "NO0005668905" "Tomra Systems ASA"            
13682 "NO0004306408" "Hafslund ASA"                
13755 "NO0004822503" "Merkantildata ASA"            
13772 "NO0003028607" "Actinor Shipping AS"          
13807 "NO0010199151" "Petroleum Geo-Services ASA"  
13817 "NO0003054405" "Visma ASA"                    
13852 "NO0003052508" "Agresso Group ASA"            
13852 "NO0005638858" "Olav Thon Eiendomsselskap ASA"
13857 "NO0004822503" "Merkantildata ASA"            
13864 "NO0003095002" "Industrifinans Naeringseiendom"
13866 "NO0003052508" "Agresso Group ASA"            
13867 "NO0004306408" "Hafslund ASA"                
13891 "NO0003111700" "Borgestad ASA"                
13906 "NO0003514002" "Aker RGI Holding ASA"        
13923 "NO0003048308" "Norsk Vekst ASA"              
13930 "NO0004822503" "Merkantildata ASA"            
13969 "NO0004135609" "Norske Skogindustrier ASA"    
13975 "NO0003143604" "DSND"                        
13990 "NO0004677006" "Kverneland ASA"              
14011 "NO0004951104" "Rieber & Son ASA"            
14017 "NO0004822503" "Merkantildata ASA"            
14031 "NO0003052508" "Agresso Group ASA"            
14056 "NO0010199151" "Petroleum Geo-Services ASA"  
14070 "NO0003028904" "Schibsted ASA"                
14101 "CY0100470919" "Prosafe ASA"                  
14108 "NO0003044703" "Braathens ASA"                
14108 "NO0003042202" "Elkjop ASA"                  
14110 "NO0003514002" "Aker RGI Holding ASA"        
14110 "NO0004135609" "Norske Skogindustrier ASA"    
14116 "NO0003062507" "Aker Maritime ASA"            
14124 "NO0004822503" "Merkantildata ASA"            
14126 "NO0003052508" "Agresso Group ASA"            
14181 "NO0003052508" "Agresso Group ASA"            
14188 "NO0004822503" "Merkantildata ASA"            
14199 "NO0003062804" "Thrane-Gruppen ASA"          
14202 "NO0005598706" "Avenir ASA"                  
14209 "NO0003052508" "Agresso Group ASA"            
14236 "NO0004822503" "Merkantildata ASA"            
14249 "NO0003072506" "Choice Hotels Scandinavia ASA"
14266 "NO0004951104" "Rieber & Son ASA"            
14287 "NO0003073108" "Synnove Finden ASA"          
14292 "NO0004822503" "Merkantildata ASA"            
14293 "NO0004822503" "Merkantildata ASA"            
14329 "NO0003168908" "Fosen Trafikklag ASA"        
14330 "NO0005806802" "Veidekke ASA"                
14333 "NO0003109407" "Eltek ASA"                    
14363 "NO0004822503" "Merkantildata ASA"            
14363 "NO0005806802" "Veidekke ASA"                
14396 "NO0003081101" "Evercom Network ASA"          
14400 "NO0004822503" "Merkantildata ASA"            
14406 "NO0005625103" "Technor"                      
14424 "NO0003058901" "Software Innovation ASA"      
14445 "NO0003109308" "Profdoc ASA"                  
14447 "NO0004677006" "Kverneland ASA"              
14458 "NO0003049306" "Selmer ASA"                  
14483 "NO0003057705" "Narvesen A/S"                
14501 "NO0003057705" "Narvesen A/S"                
14511 "NO0003070906" "TANDBERG Television ASA"      
14520 "NO0003143604" "DSND"                        
14545 "NO0005598706" "Avenir ASA"                  
14574 "NO0003054108" "Pan Fish ASA"                
14586 "NO0003098402" "Enitel ASA"                  
14591 "NO0010001118" "Itera ASA"                    
14594 "NO0004845405" "Moelven Industrier ASA"      
14598 "NO0010001118" "Itera ASA"                    
14631 "NO0005806802" "Veidekke ASA"                
14647 "NO0003058901" "Software Innovation ASA"      
14662 "NO0010019649" "EDB Business Partner ASA"    
14670 "NO0010001118" "Itera ASA"                    
14686 "NO0003054736" "SuperOffice ASA"              
14687 "NO0004951104" "Rieber & Son ASA"            
14696 "NO0010001118" "Itera ASA"                    
14703 "NO0004135609" "Norske Skogindustrier ASA"    
14705 "NO0004822503" "Merkantildata ASA"            
14714 "NO0003399909" "Odfjell SE"                  
14717 "NO0004822503" "Merkantildata ASA"            
14728 "NO0010010473" "StepStone ASA"                
14733 "NO0003043309" "Kongsberg Gruppen ASA"        
14740 "NO0003058901" "Software Innovation ASA"      
14761 "NO0003058901" "Software Innovation ASA"      
14774 "NO0003054108" "Pan Fish ASA"                
14787 "NO0004822503" "Merkantildata ASA"            
14810 "NO0003068306" "Norsk Lotteridrift ASA"      
14816 "NO0005052605" "Norsk Hydro ASA"              
14853 "NO0005806802" "Veidekke ASA"                
14854 "NO0003054108" "Pan Fish ASA"                
14854 "NO0010010473" "StepStone ASA"                
14867 "NO0003089104" "Expert Eilag ASA"            
14873 "NO0003109407" "Eltek ASA"                    
14885 "NO0003058901" "Software Innovation ASA"      
14901 "NO0003046203" "Steen & Strom ASA"            
end
format %td event_date
tempfile events
save `events'

use `events', clear
isid company_id event_date
clonevar date = event_date
rangejoin date -4 -1 using `exchange', by(company_id)
by company_id event_date (date_U), sort: keep if _n == 1
gen mkt_value = price*sharesissued
drop date
rename date_U mkt_date

[Ali Hassan]

Thank you, this last code did the job and it is fine as long as the market_value is before the event_date.

I have an additional question, if you would be kind to help me out with it:

In the events dataset some companies announce multiple events and I would like to eliminate the events that "overlap"

* Example generated by -dataex-. To install: ssc install dataex
clear
input int event_date str12 company_id str30 bidder
14287 "NO0003073108" "Synnove Finden ASA"            
14292 "NO0004822503" "Merkantildata ASA"             
14293 "NO0004822503" "Merkantildata ASA"             
14329 "NO0003168908" "Fosen Trafikklag ASA"          
14330 "NO0005806802" "Veidekke ASA"     
end
format %td event_date

I would like to eliminate the events where the same company makes another announcement within 2 days of the first announcement. In the example the same company announces events on the dates 14292 and 14293, I would like to eliminate the one on 14293.

[Clyde Schechter]

Well, it's not quite that simple. Suppose Merkantildata ASA also announces an event on date 14295. 14295 is within 2 days of 14293, and 14293 is within two days of 14292. But if we drop the event on 14293, 14295 is now more than 2 days later than 14291. So does 14295 stay or go?