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

M

maxtymo

New member
Joined
Dec 1, 2013
Messages
7
Reaction score
0
Points
0
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
 
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
 
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
 
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?
 
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?
 
maxtymo said:
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
Click to expand...

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?
 
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
 
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 :redface: and thanks

Hercules
 
Last edited:
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 ;)
 
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
 
Please post a link to the three other forums you cross posted to - Thx
 
Thanks Pecoflyer
i won't do anymore work until we get a response. I think the lesson to be learnt here is only to provide help to new users if its straightforward.
Im very grateful, and If I can help you in any way dont be afraid to ask will you?

Hercules
 
Max
I did promise you an update and despite my better judgement with the competition from other sites I have done some more work today.
Ive now got the first stage completed, giving me a routine that finds all the companies in the size sheet that meet the "between 70% and 130%" parameters.
I had a few problems with the data because of all the #VALUE! errors in the SIZES sheet but Ive sorted that.
Ive now got to do the extra coding to find the nearest ratios from the firms that qualify on size. I have got a few other tasks as well but I should complete
in the next couple of days. If you get an answer elsewhere Id still like to know if you can post on this site.

Hercules :)
 
Hello Max
I saw one or two responses on the Mr Excel and I know Aladin Akyurek - hes a genius with formula. But I had done quite a bit of work and it seemed a shame to waste it. So - Ive completed the first stage which takes the initial firm thats listed on the line where the formula is and finds all the control firms whos size meets the 70-130% parameters. In the case of the first one (RMR PLC) it found 23 Control Firms in the size sheet starting with Sopharma on row 32 (5.73160084).
If I can ask another couple of questions it will save me some time.
1. It looks like the two sheets might have originally been part of one wide dataset that youve split between two sheets because of the width. Im asking this because I want to know if a take a particular ISIN number, can I be
confident that it will be on the same row number on both sheets?
2. How will you update the sheets ? This is important because the programing has a record of the current size of the tables, and these will need updating. How this is done will depend on how, and how often you change it

Ill give you another update later today.
 
Hello Max
Done a little more work but had a few distractions today. Can you answer my questions from #16 and also:
If I have a Control firm that qualifies on size, but you dont have an MTBV Ratio would that be disregarded, or left in?
I was thinking if you had 6 size qualifiers and all their ratios are NA what do I do?
 
Hello Max
Ive been doing some testing, and Im almost there (if your still out there? ) :biggrin:

The attachment is an extract from the full workbook showing the results for RMR PLC. There were 22 firms that matched the size criteria, but 7 don't have any MTBV ratios (set to 0)
Of the other 15 Porceleyne Fles has the closest match.
At present I had to identify the winner manually, as Ive got this little bit of coding to do. Im going to wait now until I hear from you.
 

Attachments

  • MaxExample.xlsm
    24.9 KB · Views: 16
Hi,
Really sorry for the late reply. I already got a solution 2days ago and was able to test it yesterday. It works....However, I really appreciate your help!!! Thanks alot!!!!
In case you are interested in the solution (unfortunately in german):
basically its a index-match function.
excelsolution.jpg

So thanks again.
Max
 
Dont think you have anything to thank me for :)
Had a look at your solution, and I prefer the VBA one which in this case provides a more robust product.
with a simpler single UDF to do all the "donkey work"

Formula Refs should read K3, L3 and M3.
 

Attachments

  • Example.xlsm
    100.5 KB · Views: 19
Last edited:
You must log in or register to reply here.
Back
Top