Develop Customized Recommendation Systems with VBA

Creating a customized recommendation system with VBA in Excel involves building an algorithm that can suggest items based on user preferences or past behaviors. Since Excel VBA is not inherently designed for creating recommendation systems (as it doesn’t support advanced machine learning algorithms natively), we’ll focus on creating a simpler, rule-based system that makes recommendations based on the data available in your Excel workbook. We can later expand it with more complex logic depending on your needs.

Let’s break down the solution into three main steps:

1. Data Preparation: Collect and organize the data you want to use for recommendations.
2. Algorithm Development: Develop an algorithm to generate recommendations based on certain rules or heuristics.
3. User Interface: Create a user-friendly interface for interacting with the recommendation system.

Example Overview

In this example, we’ll assume you’re building a recommendation system for a store that suggests products to customers based on their previous purchases. The data may contain customer purchase history, product details, and customer profiles.

Data Preparation

We assume that you have two key pieces of data in an Excel sheet:

1. Product List: Contains information about available products.
   • Column A: Product ID
   • Column B: Product Name
   • Column C: Product Category
   • Column D: Price
2. Customer Purchase History: Contains data on which customer bought which product.
   • Column A: Customer ID
   • Column B: Product ID

This is a simplified version, but in a real-world scenario, you could expand this to include factors like product ratings, time of purchase, and customer demographic data.

Algorithm Development

Here, we’ll build a simple rule-based recommendation system. The idea is to recommend products in the same category as the items a customer has already purchased.

We’ll use the following logic:

• For a given customer, identify the products they’ve purchased.
• Recommend other products in the same category as the products they’ve already bought (excluding the already purchased products).

Sample VBA Code

Sub GenerateRecommendations()
    Dim wsProducts As Worksheet
    Dim wsPurchases As Worksheet
    Dim wsRecommendations As Worksheet
    Dim customerID As Long
    Dim productID As Long
    Dim productCategory As String
    Dim productName As String
    Dim recommendedProducts As String
    Dim rowIndex As Long
    Dim lastRowProducts As Long
    Dim lastRowPurchases As Long
    Dim lastRowRecommendations As Long
    Dim i As Long, j As Long
    ' Define worksheets
    Set wsProducts = ThisWorkbook.Sheets("Products")
    Set wsPurchases = ThisWorkbook.Sheets("Purchases")   
    ' Create or clear Recommendations sheet
    On Error Resume Next
    Set wsRecommendations = ThisWorkbook.Sheets("Recommendations")
    On Error GoTo 0   
    If wsRecommendations Is Nothing Then
        Set wsRecommendations = ThisWorkbook.Sheets.Add
        wsRecommendations.Name = "Recommendations"
    Else
        wsRecommendations.Cells.Clear ' Clear previous data
    End If   
    ' Define headers for Recommendations sheet
    wsRecommendations.Cells(1, 1).Value = "Customer ID"
    wsRecommendations.Cells(1, 2).Value = "Recommended Products"   
    ' Get last row of data in Products and Purchases sheets
    lastRowProducts = wsProducts.Cells(wsProducts.Rows.Count, "A").End(xlUp).Row
    lastRowPurchases = wsPurchases.Cells(wsPurchases.Rows.Count, "A").End(xlUp).Row   
    ' Loop through all customers in Purchases sheet
    For i = 2 To lastRowPurchases
        customerID = wsPurchases.Cells(i, 1).Value
        productID = wsPurchases.Cells(i, 2).Value       
        ' Get the product category for the purchased product
        productCategory = ""
        For j = 2 To lastRowProducts
            If wsProducts.Cells(j, 1).Value = productID Then
                productCategory = wsProducts.Cells(j, 3).Value ' Category in Column C
                Exit For
            End If
        Next j       
        ' Now find other products in the same category that haven't been purchased
        recommendedProducts = ""
        For j = 2 To lastRowProducts
            ' Check if the product belongs to the same category and hasn't been purchased by the customer
            If wsProducts.Cells(j, 3).Value = productCategory Then
                ' Check if customer has purchased this product
                If Not IsProductPurchased(customerID, wsProducts.Cells(j, 1).Value, wsPurchases) Then
                    If recommendedProducts = "" Then
                        recommendedProducts = wsProducts.Cells(j, 2).Value
                    Else
                        recommendedProducts = recommendedProducts & ", " & wsProducts.Cells(j, 2).Value
                    End If
                End If
            End If
        Next j       
        ' Add the recommendations to the Recommendations sheet
        lastRowRecommendations = wsRecommendations.Cells(wsRecommendations.Rows.Count, "A").End(xlUp).Row + 1
        wsRecommendations.Cells(lastRowRecommendations, 1).Value = customerID
        wsRecommendations.Cells(lastRowRecommendations, 2).Value = recommendedProducts
    Next i
    MsgBox "Recommendations have been generated!"
End Sub

' Helper function to check if a product has been purchased by a customer
Function IsProductPurchased(customerID As Long, productID As Long, wsPurchases As Worksheet) As Boolean
    Dim lastRow As Long
    Dim i As Long   
    lastRow = wsPurchases.Cells(wsPurchases.Rows.Count, "A").End(xlUp).Row   
    For i = 2 To lastRow
        If wsPurchases.Cells(i, 1).Value = customerID And wsPurchases.Cells(i, 2).Value = productID Then
            IsProductPurchased = True
            Exit Function
        End If
    Next i   
    IsProductPurchased = False
End Function

Explanation of the Code

1. Worksheet Setup:
   • We define three worksheets: Products, Purchases, and Recommendations. The Products sheet holds product data, while Purchases stores which products customers have bought. The Recommendations sheet will display the final product suggestions.
2. Loop Through Customers:
   • The code loops through the list of customers in the Purchases sheet and checks which products each customer has bought.
3. Identify Product Category:
   • Once we know what product a customer has purchased, we look up the product category using the Products sheet.
4. Generate Recommendations:
   • We find all products in the same category and check whether the customer has already purchased them. If not, those products are added to the list of recommendations.
5. Displaying Results:
   • After generating the recommendations, the results are written into the Recommendations sheet, with the customer ID and a list of recommended products.
6. Helper Function:
   • The IsProductPurchased function checks whether a customer has already bought a particular product to avoid recommending already purchased items.

User Interface

You can add a button to trigger this code from the Excel interface. Here’s how to create one:

1. Go to the « Developer » tab in Excel (if it’s not enabled, enable it via Excel Options).
2. Insert a button (ActiveX control) on your worksheet.
3. Right-click the button, select « View Code, » and paste the following code inside the button’s click event:

Private Sub CommandButton1_Click()
    GenerateRecommendations
End Sub

Now, when you click the button, the GenerateRecommendations subroutine will run, and recommendations will be displayed.

Possible Enhancements

• Advanced Filtering: You could refine recommendations by considering factors like product ratings or customer demographics.
• Collaborative Filtering: Instead of simple category-based recommendations, you could implement a collaborative filtering approach, where you find similarities between customers and recommend products based on similar user behavior. However, implementing this requires more advanced logic and possibly integrating external libraries or services.
• Dynamic Inputs: Allow users to filter recommendations based on additional criteria like price range or product attributes.

Conclusion

This Excel VBA-based recommendation system is a simple, rule-based approach for suggesting products based on customer purchase history.