Search for date and then apply mutliple search criteria in huge dataset

**maxtymo** · 2013-12-01, 07:55 AM

Hi,
I am trying since couple of hours to construct a formula. However, unsuccessful. Do you guys have a solution? It’s for my Master Thesis.

My Problem:

I have 1000 firms that engaged in M&A activities. For those firms I need a Benchmark-Firm selected via Size (Market Capitalization) and Book-to-Market Value (BTMV) in the respective month in which the initial company engaged in M&A. All of those 1000 active Companies were active in different months spanning from 1997 to 2012.

In other words, I have 1000 firms and I would like to find another company for each initial company, which best fulfills two criteria in a specified month.

Criterion 1: The size (market capitalization) of the benchmark-firm needs to represent 70% to 130% of the size of the active firm. This criterion leads to a pre-selection, however there are still various potential benchmark-firms left.

Criterion 2: Out of the Benchmark-firms which are 70% to 130% of the size of the active firm, the final single benchmark-firm is found. This final selection is performed by choosing the benchmark-firm with the closest BTMV-ratio to the active firm.

From a datasets a pre-selection regarding Size is performed and in the next step the final selection is performed according to the BTMV ratio from another dataset. Both criterions need to be representing the month of the respective M&A activity. The matching between those two datasets (worksheets) can be performed by company name or a identifying code.

Short example:
Firm A is active in March 2001 and has a size of 100 and a BTMV ratio of 2 in March 2001. The size of the firms B,C,D is in the range of 70% to 130% in March 2001. Of these firms, firm D has the closest BTMV with 2,2 in March 2001.

The sample consists of 1000 active M&A companies and there are 2000 potential benchmark-firms, with values for each month from 1997 to 2012.
The size and the BTMV are given in two different worksheets with two common fields (firm name and firm code). In the end I require for further analysis just the name of the benchmark firm for each of the active firms.

It might be helpful to actually see the data, hence: """"""dropbox.com/s/h7bwcjrvjys5022/Forum.xlsx""""

Thank you guys so much!!!!
Max

**Hercules1946** · 2013-12-01, 12:29 PM

Hello Max, and welcome to the forum
Ive spent a few minutes looking at your data, and I thought I would try to use it as a test to see if I understand correctly.

1. Im assuming that your "initial companies" are those listed on the Control /Matching Tab, so I picked the first one,
RMR PLC Size 5.51 and MTMV 4.74 (engagement Oct 2001).

2. So in the other 2 tabs,Im looking at the entries in the Oct 2001 column.
3. Taking SIZE first, starting from the top, the first two qualifiers are Albena (4.52) and Pannoflax (4.595)
4. But.... moving to MTBV both would be rejected. (Albena NA and Pannoflax most unlikely with only 0.31).

Did I get this right ?

Are you going to want to select an initial company and find suitable "partners" or are you wanting to do this for them all?

Hercules

**maxtymo** · 2013-12-01, 01:05 PM

1. Yes you are right.
2. Yes you go to the size-worksheet and look for companies that have a size between 70% and 130% of 5.51. These are probably multiple companies.
3. No quite sure, if you have sorted the data another way. But all companies that have a size between 3.86 (=70%) and 7.16 (=130%) qualify for the next selection round. When I look at the data the first two which qualify are GO-AHEAD GROUP and INTERMEDIATE CAPITAL GP.
4. In this "round" there is no rejection. The company that has the closest MTBV ratio to the initial firm is selected. Assuming that only GO-AHEAD GROUP and INTERMEDIATE CAPITAL fulfill the 70%-130% criterium, GO-AHEAD would be selected as the MTBV value of 2,94 is closer to 4.74 than 1,72 (INTERMEDIATE CAPITAL GP.)

I need this procedure for all initial companies. Hence suitable partners need to be find for all the initial companies. The results should be given in ControlMatching table in column F and G next to each of the initial companies.

I hope this helps.
And thanks alot for your help
Max

**Hercules1946** · 2013-12-01, 02:16 PM

Hi Max
Im OK with the SIZES, and when I talked about rejection in 4 above I was assuming that better results would be available elsewhere in the list.
I understand in my example that if Albena had been the only company in the correct size bracket it would be put forward even without the MTBV data.
I did'nt alter the order of your data. I just checked what the MTBV ratios are for Albena and Pannoflex (NA and 0.31 against 4.74 for RMR PLC)
When you talk about the first round, you do want to test both criteria in a single stage and come up with one answer, dont you?

I think this might need a VBA solution with quite a bit of work, so please can you keep me advised of any alternative solution, and Ill keep you
uptodate with my progress?

**Hercules1946** · 2013-12-01, 02:35 PM

Sorry - a couple more questions:
1. The Control Firm Name - Is that the Name in ColA of the SIZE and the MTBV Sheets ?
2. Where/What is the ISIN?

**Hercules1946** · 2013-12-01, 02:57 PM

Originally Posted by maxtymo

But all companies that have a size between 3.86 (=70%) and 7.16 (=130%) qualify for the next selection round. When I look at the data the first two which qualify are GO-AHEAD GROUP and INTERMEDIATE CAPITAL GP.
Assuming that only GO-AHEAD GROUP and INTERMEDIATE CAPITAL fulfill the 70%-130% criterium, GO-AHEAD would be selected as the MTBV value of 2,94 is closer to 4.74 than 1,72 (INTERMEDIATE CAPITAL GP.)

Max

How do GAG and ICG qualify with Oct 2001 figures of 638.5 and 549.5 against 5.51 for RMR PLC ? Are we talking different units of measure or am I misunderstanding?

**maxtymo** · 2013-12-01, 03:10 PM

Yes I would like to test both criteria within one go. Hence select the company which fulfills the 70%130% criterium and is the closest to the MTBV ratio. I illustrated it in two steps, as the closest MTBV ratio is selected of the companies which are left after applying the 70%130% criterium.

Sure I will keep you updated about alternative solutions. However, so far I did not get any other suggestions or comments. At least not feasible ones, seems that this problem might be more difficult than I expected.

No, the control firm name is given in column E of SIZE and MVBT...ISIN is given in Column F in SIZE and in Column G in MTBV (However if necessary MTBV Column G can be transferred to be Column F, if it helps the current content of Column F in MTBV is not required and hence could be deleted. If it helps)

ISIN is the identifify number for the companies. The company names might not match across different databases. However the ISIN is always the same. However in this file, the company name is also the same across the SIZE and MVBT worksheet.

Ok I forgot, the initial firm should not be equal to the control firm...Hence the ISIN of the initial firm should not be equal to the ISIN of the selected control firm.

Hope this helps

**Hercules1946** · 2013-12-01, 04:05 PM

Max
I think we are getting there!

Sorry about all the questions but I like to ensure that I understand properly otherwise a lot of unnecessary/unhelpful work can result.
From your last point, I gather your saying that that the same company (based on ISIN) could be in both lists. I hadn't realised from what Ive seen, but I can make provision
Im a bit confused with terminology:

The Control Firm is listed in the Control/Matching Sheet and we are looking to match the Initial firms listed in the MTBV and SIZE Sheets - Is that right?
Within one sheet is the ISIN number unique, or might it be repeated on other rows of the sheet?

Also: Please can you answer my query in Post#6

Sorry

and thanks

Hercules

**maxtymo** · 2013-12-01, 04:30 PM

Just saw post 6....
and your are completly right. Its my mistake sorry for the confusion. GAG and ICG do not qualify. The size of the sample firm is 5.51 and hence the range is between 3.86 and 7,16. GAG and ICG have values of 638.5 and 549.6 and hence are way out of the range. My mistake.

The ControlMatching sheet lists the initial companies. The matching/control firms are stated in SIZE and MVBT. The selected control firm after applying the program or formula should appear (thus the result) in the ControlMatching-Sheet in colum F and G (name and ISIN respectively)...The company stated in ControlMatching in Column B and C are the initial firms for which we search a matching partner.

The ISIN number 123456 will be in the SIZE sheet once and in the MVBT sheet once. It might be that the ISIN 123456 appears in the ControlMatching Sheet various times or not a single time. However the matching results shown in ControlMatching-sheet should not match ISIN 123456 (Column C) with ISIN 123456 in Column G. In other words the initial company should not be matched by itself. Thus within the same row there should not be the same ISIN. However it is OK if ISIN 123456 in C3 is matched by ISIN 999999 in G3 but also ISIN 123456 is used in e.g. in G54 as a control firm to match the initial firm ISIN 546689 in C54.

Sorry for being confusing

**Hercules1946** · 2013-12-01, 06:02 PM

Don't worry about the confusion. Its quite a complicated matter. When I take on a task, I expect to make the odd error but I don't like it if it happens because I havent understood the requirements properly.
The good news is I think that Im OK now and I can get on with writing the VBA. Ill do it based on the data you have given me and I'll message you later tomorrow on how its going (we are on GMT here).

Cheers
Hercules