Develop Customized Data Profiling Solutions with Excel VBA

Data profiling involves analyzing the data in a dataset to understand its structure, quality, and content. This process helps identify inconsistencies, patterns, or errors within the data.

Here’s a step-by-step guide on how to implement a custom data profiling solution using VBA:

Step 1: Data Preparation

Before you begin writing VBA code, ensure that the dataset you want to analyze is well-prepared. The data should be structured in a way that is easy to process.

1. Dataset Organization:
   • Data should be organized in columns with headers. Each column represents a variable or field, and each row represents an observation or record.
   • Ensure there are no merged cells, as this could interfere with the data processing.
   • Clean the data: Remove or handle missing values, outliers, and duplicates.
2. Load the data into Excel:
   • Your data can come from various sources like CSV files, databases, or other Excel workbooks. Make sure the data is properly imported into Excel for processing.
   • This can be done manually or via Excel functions like Get & Transform (Power Query) to load data from external sources.

Step 2: Open Excel and Access the VBA Editor

To write and execute the VBA code, you first need to access the VBA editor within Excel:

1. Open Excel:
   • Launch Excel and open the workbook that contains the data you wish to profile.
2. Access the VBA Editor:
   • Press Alt + F11 to open the VBA Editor. This is where you’ll write the custom code to perform data profiling tasks.
   • In the VBA Editor, you can create a new module to house your code. To do this, right-click on « VBAProject (YourWorkbookName) » in the left panel, select Insert, and then choose Module.

Step 3: Write VBA Code

Now that you’re in the VBA editor, you can start writing the code for your custom data profiling solution. Below is an example of a basic code structure for data profiling.

What will the code do?

• It will generate a summary of the data.
• It will count the total number of rows and columns.
• It will check for missing values.
• It will provide basic statistics for numerical columns like the average, minimum, maximum, and standard deviation.
• It will output the results to a new worksheet.

Example VBA Code:

Sub DataProfiling()
    Dim ws As Worksheet
    Dim resultWs As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim i As Long, j As Long
    Dim totalRows As Long, totalCols As Long
    Dim cell As Range
    Dim isEmpty As Boolean
    Dim sum As Double, count As Long
    Dim minVal As Double, maxVal As Double, avg As Double, stdDev As Double
    ' Set the worksheet containing the data
    Set ws = ThisWorkbook.Sheets("Data") ' Replace with your sheet name
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    ' Create a new worksheet for the results
    Set resultWs = ThisWorkbook.Sheets.Add
    resultWs.Name = "Data Profiling Results"
    ' Add headers for the profiling report
    resultWs.Cells(1, 1).Value = "Column"
    resultWs.Cells(1, 2).Value = "Total Rows"
    resultWs.Cells(1, 3).Value = "Missing Values"
    resultWs.Cells(1, 4).Value = "Min Value"
    resultWs.Cells(1, 5).Value = "Max Value"
    resultWs.Cells(1, 6).Value = "Average"
    resultWs.Cells(1, 7).Value = "Standard Deviation"
    ' Loop through each column
    For j = 1 To lastCol
        totalRows = lastRow - 1 ' Exclude header row
        count = 0
        sum = 0
        minVal = 1E+30 ' Large initial value
        maxVal = -1E+30 ' Small initial value
        isEmpty = False
        ' Loop through each row in the current column
        For i = 2 To lastRow ' Start from row 2 (assuming row 1 has headers)
            Set cell = ws.Cells(i, j)
            ' Check for empty or missing values
            If IsEmpty(cell.Value) Or IsError(cell.Value) Then
                count = count + 1
                isEmpty = True
            Else
                ' Collect data for numerical columns
                If IsNumeric(cell.Value) Then
                    sum = sum + cell.Value
                    If cell.Value < minVal Then minVal = cell.Value
                    If cell.Value > maxVal Then maxVal = cell.Value
                End If
            End If
        Next i       
        ' Calculate statistics if there is data in the column
        If Not isEmpty Then
            avg = sum / (totalRows - count)
            stdDev = Application.WorksheetFunction.StDev(ws.Range(ws.Cells(2, j), ws.Cells(lastRow, j)))
        Else
            avg = "N/A"
            stdDev = "N/A"
        End If       
        ' Write the profiling results to the result worksheet
        resultWs.Cells(j + 1, 1).Value = ws.Cells(1, j).Value
        resultWs.Cells(j + 1, 2).Value = totalRows
        resultWs.Cells(j + 1, 3).Value = count
        resultWs.Cells(j + 1, 4).Value = minVal
        resultWs.Cells(j + 1, 5).Value = maxVal
        resultWs.Cells(j + 1, 6).Value = avg
        resultWs.Cells(j + 1, 7).Value = stdDev
    Next j   
    ' Auto-fit columns for better visibility
    resultWs.Columns("A:G").AutoFit   
    MsgBox "Data profiling complete! Results are in the 'Data Profiling Results' sheet."   
End Sub

Explanation of the Code:

• Set ws and resultWs: These variables represent the worksheet with the original data and a new worksheet where the profiling results will be stored, respectively.
• lastRow and lastCol: These variables determine the size of the data (number of rows and columns) to loop through.
• Looping through each column: The code loops through each column to calculate various statistics like missing values, minimum, maximum, average, and standard deviation.
• Missing Values: The code checks if a cell is empty or contains an error using IsEmpty(cell.Value) or IsError(cell.Value).
• Numerical Analysis: For numerical data, the code calculates the sum, average, minimum, and maximum values. It uses the Excel function StDev to calculate the standard deviation.
• Output: After processing all the columns, the results are displayed in a new worksheet. The columns are auto-fitted for better readability.

Step 4: Run the Code

To run the code, follow these steps:

1. Save your workbook with macros enabled (as .xlsm file).
2. Press Alt + F8 to open the « Macro » dialog box.
3. Select the macro DataProfiling and click « Run. »

Output:

The output of this code will be displayed in a newly created worksheet titled « Data Profiling Results. » This sheet will contain the following details:

• Column: The name of the data column being analyzed.
• Total Rows: The number of data rows (excluding the header).
• Missing Values: The count of missing or empty values in the column.
• Min Value: The smallest numerical value in the column.
• Max Value: The largest numerical value in the column.
• Average: The average of the numerical values in the column.
• Standard Deviation: The standard deviation of the numerical values in the column.

Conclusion:

By following the above steps, you can easily create a customized data profiling solution using Excel VBA. This process helps you quickly analyze large datasets and generate useful statistical information, improving data quality assessment and preparation for further analysis.