5
\$\begingroup\$

My code finds the best match between a private company and a listed company based on 3 ratios in order to report the ratio4 from the listed to the private company.

In order to do that first, I do the percentile of the ratio1 of listed company (as it is the most important).

Secondly, I check in which percentile the value of the ratio1 of the private company is in.

Then, there are two options:

  1. The perfect match, meaning that the sum of the difference of the 3 ratios equal to 0 then I report the ratio4 of this listed company to this private one.
  2. Find the minimum of the sum of the difference of all ratios.

Option Explicit

Sub EquityVol_Supplier()

Dim resultCell As Double
Dim CheckCell As Double, CheckCell2 As Double, checkCell3 As Double
Dim bestDiff4 As Double
Dim i As Integer, j As Integer
Dim dLowValue As Double
Dim LastSupplier As Long, LastListed As Long
Dim ratio1 As Double, ratio2 As Double, ratio3 As Double
Dim Name As String
Dim ebitsales9 As Double, ebitsales8 As Double, ebitsales7 As Double, ebitsales6 As Double
Dim ebitsales5 As Double, ebitsales4 As Double, ebitsales3 As Double, ebitsales2 As Double, ebitsales1 As Double
Dim arraylist

'Find the last row of suplier name
LastSupplier = Range("H" & Rows.Count).End(xlUp).Row
'Find the last row of Company listed
LastListed = Range("D" & Rows.Count).End(xlUp).Row


On Error Resume Next
For j = 2 To LastSupplier

dLowValue = 100000

'Ratio 1, 2 and 3 of supplier
        CheckCell = Range("H" & j).Value
        CheckCell2 = Range("I" & j).Value
        checkCell3 = Range("J" & j).Value

'Define the array of the ratio 1 (the same as the ratio1 of supplier) of Listed company
arraylist = Range("A2:" & "A" & LastSupplier)

'Define the percentile of ratio1 of listed companies
        ebitsales9 = Application.WorksheetFunction.Percentile(arraylist, 0.9)
        ebitsales8 = Application.WorksheetFunction.Percentile(arraylist, 0.8)
        ebitsales7 = Application.WorksheetFunction.Percentile(arraylist, 0.7)
        ebitsales6 = Application.WorksheetFunction.Percentile(arraylist, 0.6)
        ebitsales5 = Application.WorksheetFunction.Percentile(arraylist, 0.5)
        ebitsales4 = Application.WorksheetFunction.Percentile(arraylist, 0.4)
        ebitsales3 = Application.WorksheetFunction.Percentile(arraylist, 0.3)
        ebitsales2 = Application.WorksheetFunction.Percentile(arraylist, 0.2)
        ebitsales1 = Application.WorksheetFunction.Percentile(arraylist, 0.1)


    For i = 2 To LastListed

' Check the difference between ratio of Suppliers and listed companies
                ratio1 = Abs((CheckCell - Range("A" & i).Value))
                ratio2 = Abs((CheckCell2 - Range("B" & i).Value))
                ratio3 = Abs((checkCell3 - Range("C" & i).Value))


'Check if the ratio1 of the Supplier is above the 9th percentile of listed companies
        If CheckCell > ebitsales9 And Range("A" & i).Value > ebitsales9 Then

                    bestDiff4 = Application.WorksheetFunction.Min(ratio1 + ratio2 + ratio3)

'If bestdiff4=0 means perfect match so go next supplier
                    If bestDiff4 = 0 Then
                        dLowValue = bestDiff4
                        resultCell = Range("D" & i)
                        Range("K" & j).Value = resultCell
                        GoTo NextIteration

'if no perfect match so find the lowest sum of the difference of all ratios
                    ElseIf bestDiff4 < dLowValue Then
                       dLowValue = bestDiff4
                        resultCell = Range("D" & i)
                        Name = Range("E" & i)
                    End If


'Check if the ratio1 of the Supplier is between the 8th and 9th percentile of listed companies
        ElseIf CheckCell > ebitsales8 And Range("A" & i).Value > ebitsales8 And CheckCell <= ebitsales9 And Range("A" & i).Value <= ebitsales9 Then

                    bestDiff4 = Application.WorksheetFunction.Min(ratio1 + ratio2 + ratio3)

                    If bestDiff4 = 0 Then
                        dLowValue = bestDiff4
                        resultCell = Range("D" & i)
                        Range("K" & j).Value = resultCell
                        GoTo NextIteration

                    ElseIf bestDiff4 < dLowValue Then
                        dLowValue = bestDiff4
                        resultCell = Range("D" & i)
                        Name = Range("E" & i)
                    End If


  'Check if the ratio1 of the Supplier is between the 7th and 8th percentile of listed companies
        ElseIf CheckCell > ebitsales7 And Range("A" & i).Value > ebitsales7 And Range("A" & i).Value <= ebitsales8 And CheckCell <= ebitsales8 Then

                    bestDiff4 = Application.WorksheetFunction.Min(ratio1 + ratio2 + ratio3)

                    If bestDiff4 = 0 Then
                        dLowValue = bestDiff4
                        resultCell = Range("D" & i)
                        Range("K" & j).Value = resultCell
                        GoTo NextIteration

                    ElseIf bestDiff4 < dLowValue Then
                       dLowValue = bestDiff4
                        resultCell = Range("D" & i)
                        Name = Range("E" & i)
                    End If


 'Check if the ratio1 of the Supplier is between the 6th and 7th percentile of listed companies
        ElseIf CheckCell > ebitsales6 And Range("A" & i).Value > ebitsales6 And CheckCell <= ebitsales7 And Range("A" & i).Value <= ebitsales7 Then

                    bestDiff4 = Application.WorksheetFunction.Min(ratio1 + ratio2 + ratio3)

                    If bestDiff4 = 0 Then
                        dLowValue = bestDiff4
                        resultCell = Range("D" & i)
                        Range("K" & j).Value = resultCell
                        GoTo NextIteration

                    ElseIf bestDiff4 < dLowValue Then
                       dLowValue = bestDiff4
                        resultCell = Range("D" & i)
                        Name = Range("E" & i)
                    End If

 'Check if the ratio1 of the Supplier is between the 5th and 6th percentile of listed companies
            ElseIf CheckCell > ebitsales5 And Range("A" & i).Value > ebitsales5 And CheckCell <= ebitsales6 And Range("A" & i).Value <= ebitsales6 Then

                    bestDiff4 = Application.WorksheetFunction.Min(ratio1 + ratio2 + ratio3)

                    If bestDiff4 = 0 Then
                        dLowValue = bestDiff4
                        resultCell = Range("D" & i)
                        Range("K" & j).Value = resultCell
                        GoTo NextIteration

                    ElseIf bestDiff4 < dLowValue Then
                       dLowValue = bestDiff4
                        resultCell = Range("D" & i)
                        Name = Range("E" & i)
                    End If

 'Check if the ratio1 of the Supplier is between the 4th and 5th percentile of listed companies
             ElseIf CheckCell > ebitsales4 And Range("A" & i).Value > ebitsales4 And CheckCell <= ebitsales5 And Range("A" & i).Value <= ebitsales5 Then

                    bestDiff4 = Application.WorksheetFunction.Min(ratio1 + ratio2 + ratio3)

                    If bestDiff4 = 0 Then
                        dLowValue = bestDiff4
                        resultCell = Range("D" & i)
                        Range("K" & j).Value = resultCell
                        GoTo NextIteration

                    ElseIf bestDiff4 < dLowValue Then
                       dLowValue = bestDiff4
                        resultCell = Range("D" & i)
                        Name = Range("E" & i)
                    End If

 'Check if the ratio1 of the Supplier is between the 3th and 4th percentile of listed companies
                ElseIf CheckCell > ebitsales3 And Range("A" & i).Value > ebitsales3 And CheckCell <= ebitsales4 And Range("A" & i).Value <= ebitsales4 Then

                    bestDiff4 = Application.WorksheetFunction.Min(ratio1 + ratio2 + ratio3)

                    If bestDiff4 = 0 Then
                        dLowValue = bestDiff4
                        resultCell = Range("D" & i)
                        Range("K" & j).Value = resultCell
                        GoTo NextIteration

                    ElseIf bestDiff4 < dLowValue Then
                       dLowValue = bestDiff4
                        resultCell = Range("D" & i)
                        Name = Range("E" & i)
                    End If


  'Check if the ratio1 of the Supplier is between the 2nd and 3rd percentile of listed companies
                ElseIf CheckCell > ebitsales2 And Range("A" & i).Value > ebitsales2 And CheckCell <= ebitsales3 And Range("A" & i).Value <= ebitsales3 Then

                    bestDiff4 = Application.WorksheetFunction.Min(ratio1 + ratio2 + ratio3)

                            If bestDiff4 = 0 Then
                                    dLowValue = bestDiff4
                                    resultCell = Range("D" & i)
                                    Range("K" & j).Value = resultCell
                                    GoTo NextIteration

                            ElseIf bestDiff4 < dLowValue Then
                                    dLowValue = bestDiff4
                                    resultCell = Range("D" & i)
                                    Name = Range("E" & i)
                            End If

  'Check if the ratio1 of the Supplier is below the 2nd percentile of listed companies
                ElseIf CheckCell < ebitsales2 And Range("A" & i).Value < ebitsales2 Then

                    bestDiff4 = Application.WorksheetFunction.Min(ratio1 + ratio2 + ratio3)


                    If bestDiff4 = 0 Then
                        dLowValue = bestDiff4
                        resultCell = Range("D" & i)
                        Range("K" & j).Value = resultCell
                        GoTo NextIteration

                    ElseIf bestDiff4 < dLowValue Then
                       dLowValue = bestDiff4
                        resultCell = Range("D" & i)
                        Name = Range("E" & i)
                    End If

            End If

'Next Listed company
    Next i

NextIteration:
'Report the equity volatility of the best match based on the 3 ratios
        Range("K" & j).Value = resultCell
        Range("O" & j).Value = ratio1
        Range("P" & j).Value = ratio2
        Range("Q" & j).Value = ratio3
        Range("N" & j).Value = Name
Next j


End Sub
\$\endgroup\$
3
\$\begingroup\$

Firstly, this would be much easier to understand if you included an example/screenshot of the underlying data. But anyway,

Things I like

Option Explicit - always the first thing I check for. I honestly don't know why it was ever optional in the first place.

Frequent comment signposting - For really good code, the code should signpost itself, but for this, it makes your code very easy to follow and understand.

Good use of indenting and whitespace - Again, makes your code very readable.


Variable Naming

Your naming is pretty decent but could be better.

VBA Naming Conventions:

Procedure-level variables use Dim and are spelt in camelCase

Module-Level are Private and in PascalCase

Global-Level are Public and also PascalCase

Constants are Public Const and SHOUTY_SNAKE_CASE

Personally, I do Sheets, Books, Modules, Classes etc. like so ws_PascalCase but that's just personal preference. As long as you're consistent, it doesn't really matter how you do it.

So, referencing your code, resultCell, bestDiff4, dLowValue are good, CheckCell, ebitsales9, Name are not.

Meaningful Variable Names

Variable names should always be Clear, Consise and, above all, Unambiguous.

If I see resultCell, I'm going to assume it's, well, a Cell. I'm certainly not going to think it's a number. resultCellValue is suddenly very clear about what it is. Similarly LastSupplier, LastListed are not clear about what they are. lastSupplierRow, lastListedRow and suddenly I know exactly what they are. ratio1, ratio2, ratio3. I at least know they're ratios, but ratios of what? Numbered variables are almost never appropriate. If you can name them, you should. I can't tell how your ratios are calculated, but they should be named something like ratioValueToValue. Similarly, ebitsales1 etc. is both numbered and unclear. If I see that, I'm going to assume it's some kind of sales figure, not a percentile value. 10thPctlEbit would be much clearer. bestDiff4. Where's bestDiff 1 to 3?


Don't Repeat Yourself

A.K.A. DRY. Whenever you find yourself copy-pasting a block of code: Stop. Go to the bottom of your code module. Write a new Sub/Function to do what your code does.

For instance this:

'If bestdiff4=0 means perfect match so go next supplier
    If bestDiff4 = 0 Then
        dLowValue = bestDiff4
        resultCell = Range("D" & i)
        Range("K" & j).Value = resultCell
        GoTo NextIteration

'if no perfect match so find the lowest sum of the difference of all ratios
    ElseIf bestDiff4 < dLowValue Then
       dLowValue = bestDiff4
        resultCell = Range("D" & i)
        Name = Range("E" & i)
    End If

Can just be a subroutine like so:

Public Sub ProcessMinRatio(byval bestDiff4 as double, byval i as long, byval j as long)

'If bestdiff4=0 means perfect match so go next supplier
    If bestDiff4 = 0 Then
        dLowValue = bestDiff4
        resultCell = Range("D" & i)
        Range("K" & j).Value = resultCell
        GoTo NextIteration

'if no perfect match so find the lowest sum of the difference of all ratios
    ElseIf bestDiff4 < dLowValue Then
       dLowValue = bestDiff4
        resultCell = Range("D" & i)
        Name = Range("E" & i)
    End If

End Sub

then you can write

    If CheckCell > ebitsales9 And Range("A" & i).Value > ebitsales9 Then
                bestDiff4 = Application.WorksheetFunction.Min(ratio1 + ratio2 + ratio3)
                ProcessMinRatio bestDiff4, i, j

    ElseIf CheckCell > ebitsales8 And Range("A" & i).Value > ebitsales8 Then
                bestDiff4 = Application.WorksheetFunction.Min(ratio1 + ratio2 + ratio3)
                ProcessMinRatio bestDiff4, i, j

    etc.

Then we can make it even cleaner:

If

ElseIf

ElseIf

Should be a Select... Case Statement like so:

Select Case CheckCell

Case Is > ebitsales9 
    bestDiff4 = Application.WorksheetFunction.Min(ratio1 + ratio2 + ratio3)
    ProcessMinRatio bestDiff4, i, j

Case is > ebitsales8
    bestDiff4 = Application.WorksheetFunction.Min(ratio1 + ratio2 + ratio3)
    ProcessMinRatio bestDiff4, i, j

etc.

You should also avoid using GoTo. How about using a boolean bestRatioIsFound and exiting your loop as soon as it's found?

bestRatioIsFound = false
i = 1
Do While i < lastListed and bestRatioIsFound = False
    i = i + 1

    ...

    If bestDiff4 = 0 Then
        dLowValue = bestDiff4
        resultCell = Range("D" & i)
        Range("K" & j).Value = resultCell 

        bestRatioIsFound = true

    ...

Loop

Why are you finding the percentile at all? As best I can tell, you perform the exact same operations regardless of which percentile it's in, and you never store or output the percentile information anywhere.

\$\endgroup\$
  • \$\begingroup\$ first of all, thank you. My code its a lot more cleaner. I followed all your recommendation. To answer your question about the percentile, I only need to determine in which percentile CheckCell is in. When I found it i need to find the Min(BestDiff4) inside this percentile and report the equity volatility of the listed to the supplier. \$\endgroup\$ – manu Nov 30 '15 at 15:58
  • \$\begingroup\$ Okay, but I literally cannot see where your code does anything differently once it's determined the percentile. How, exactly, would the output change if you removed the if elseif series and just carried out the operations? \$\endgroup\$ – Kaz Nov 30 '15 at 16:30
  • \$\begingroup\$ Also, if you've modified your code and want to get feedback on that, please feel free to post a follow-up question with links to/from this one. \$\endgroup\$ – Kaz Nov 30 '15 at 16:31
  • \$\begingroup\$ I need to find the percentile in which the supplier is in (based on the first ratio). Then I go through all the listed company which belongs to the corresponding percentile and report the equity volatility of the listed company where the difference of all ratios is the minimum. \$\endgroup\$ – manu Nov 30 '15 at 16:36
  • \$\begingroup\$ i am currently working on the code when i'll finish i will post a follow up. Thanks again @Zak \$\endgroup\$ – manu Nov 30 '15 at 16:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.