Skip to main content

Excel Add-In Overview

This project represents a simple, yet powerful Enterprise Resource Planning (ERP) bridge built within Excel. It transforms a standard spreadsheet into a powerful middleware tool that synchronizes real-time marketplace data and warehouse logistics.

By integrating these specific modules, I have built a system that handles the entire lifecycle of an eCommerce order, from marketplace listing to warehouse fulfillment.

Technical Architecture: Modular VBA Framework

Key Integrations: Takealot Seller API, MintSoft WMS API, Windows Credential Manager

Primary Objective: To automate high-volume retail operations by synchronizing cloud-based marketplace data with local fulfillment workflows.

Core Engineering Modules

1. Marketplace Data

  • The Technology: Developed a REST API client that consumes Takealot’s Seller API as well as MintSoft WMS API. Since VBA does not natively handle modern web formats, I integrated a custom JSON parsing engine to translate complex data structures into actionable Excel records.
  • The Impact: Automated the retrieval of Takealot Offers, Takealot Sales, MintSoft Stock Levels and MintSoft Product information including real-time pricing & sales, TSIN catalog data stock levels and product attributes. This replaces manual CSV downloads with a live, authenticated data stream.

2. Inventory Syncronization

  • The Technology: Engineered a bidirectional link with the MintSoft WMS API. This module handles complex authentication headers and paginated data requests to pull comprehensive product lists and inventory snapshots.
  • The Impact: Creates a timed snapshot and "Single Source of Truth" by matching warehouse stock against marketplace listings, significantly reducing the risk of overselling or stockouts.

3. Intelligent Replenishment Engine

  • The Technology: Designed a custom User Interface (UI) that allows operators to set logic-based replenishment parameters (e.g., "Target Stock Days" or "Limit by SoH").
  • The Impact: Moves the business from reactive to proactive inventory management. The system calculates exactly how much stock to move to the DC based on velocity and lead times, rather than relying on guesswork and manual calculation which can be prone to error.

4. Intelligent Clearance Promotion Engine

  • The Technology: I engineered a logic-driven engine that evaluates live marketplace "Offers" against internal inventory and margin requirements. Utilizing the Takealot Seller API, the system identifies promotion-eligible SKUs and cross-references them with MintSoft WMS stock levels.
  • The Impact: Transformed promotion management from a reactive, manual task into a proactive revenue driver. By tethering marketplace visibility to real-time warehouse data, I mitigate the risk of incurring storage fees and marketplace penalties. This automation optimized stock turnover and significantly reduced dead stock and associated removal order and storage fees.

5. Secure Credential Management

  • The Technology: Implemented a professional-grade security layer using the Win32 API to interface with the Windows Credential Manager.
  • The Impact: Instead of hardcoding sensitive API keys or passwords in the VBA code (a major security risk), the system securely retrieves encrypted credentials from the user's Windows profile. This demonstrates a high level of security awareness and technical maturity.

Metric

Data Integrity

Security

Speed

Scalability

Manual Process (Before)

High risk of SKU/Price typos.

Passwords stored in plain text.

30–60 mins per replenishment cycle.

Limited by staff hours

Automated Suite (After)

100% accurate API-driven data.

Encrypted Windows-level security.

< 2 mins with "One-Click" logic.

Unlimited; handles thousands of SKUs

I didn't just write macros; I developed a scalable business solution. By leveraging the Win32 API for security, REST APIs for data, and modular architecture for maintenance, I built a tool that allows a small team to operate at the scale of a much larger enterprise. This project demonstrates my ability to take a technical requirement and turn it into a robust, secure, and user-friendly business asset.

Inventory Synchronization

These two modules handle the critical task of keeping marketplace stock levels in sync with internal warehouse stock (MintSoft), but they utilize different technical architectures to achieve it.


Legacy Workbook Sync

The "Manual Export" Approach This module is designed for a traditional workflow where a user prepares a bulk CSV/Excel file to be manually uploaded to the Takealot Seller Portal.

  • How it works: It uses Excel’s calculation engine to perform a VLOOKUP between the internal warehouse data and the marketplace export sheet.
  • Key Mechanism: It programmatically injects formulas into the spreadsheet, performs an "AutoFill" to the last row of data, and then converts those formulas into hard values.
  • Best For: Bulk auditing or manual oversight where a human needs to verify the data before it goes live on the marketplace.

Real-Time API Sync

The "Headless Automation" Approach This is a more sophisticated, modern implementation that eliminates human intervention by communicating directly with the Takealot REST API.

  • How it works: It aggregates data from three different sources (Warehouse Stock, Current Marketplace Offers, and Pending Sales) into memory using Scripting Dictionaries.
  • Key Mechanism: It calculates the "True Available Stock" (Internal Stock minus pending "New Lead Time" orders) and then hands that data to a BatchUpdate sub.
  • Technical Highlight: It transforms the calculated data into a complex Nested JSON structure (using a JsonConverter library) and transmits it via an MSXML2.XMLHTTP POST request.

Intelligent Replenishment Engine

Business Problem / Situation

Replenishments are a major part of selling on Marketplaces and streamlining this process have been a major win. This is a weekly operations task and I found that as the number of products we sell started increasing so did the effort and time to do accurately complete this every week. The more it grew the more likely the chance of human error became. Takealot provides a manual mechanism on the seller portal as well as a bulk replenishment excel file. Using the manual seller portal mechanism was very tedious and time consuming and we needed to evaluate each product in turn to determine what and how much to send in. The format of the Bulk Replenishment tool also made it error prone, very manual and time consuming. This led to the decision to develop an intelligent solution to auto populate the Bulk Replenishment file with the required information with the configuration of some basic inputs and the click of a single button.


How I solved the problem

I developed an automated inventory replenishment engine designed to optimize stock levels across multiple Takealot Distribution Centers (CPT, JHB, and DBN). It intelligently calculates the quantity of stock to transfer from an internal warehouse to the marketplace DCs to maintain a specific "Days of Cover" (buffer stock) based on real-time velocity.


How It Works

  • Dynamic Sales Velocity Calculation: The script builds a sales summary by processing the last 30 days of transactional data. It calculates the average daily sales for every SKU at each specific Distribution Center.
  • Configurable Target Buffer: It accepts a TargetStockDays parameter, allowing the business to fluctuate stock levels (e.g., targeting 30 days of stock during normal periods or 60 days before a major sale like Black Friday).
  • Prioritized Allocation Logic: To maximize logistics efficiency, the code follows a prioritized allocation order (JHB > CPT > DBN). It checks the available "Stock on Hand" (SoH) and fills the highest-priority regions first until internal stock is exhausted.
  • Business Constraint Integration:
    • Automated Guardrails: The logic respects "Blocked" status indicators, preventing stock from being sent to DCs that may have storage restrictions or are undergoing maintenance.
    • Smart Seeding: If a product has no sales history but zero stock at a DC, the script automatically allocates a single unit to "seed" the listing and establish market presence.
    • Quantity Capping: It integrates numeric limits to ensure shipments never exceed specific warehouse storage caps or promotional limits.

Technical Implementation

  • Data Structures: Utilizes Scripting Dictionaries for high-speed lookups of sales data and SKUs, ensuring the tool remains performant even with thousands of rows of data.
  • Algorithmic Accuracy: Employs geometric and floor functions (Floor_Precise) to ensure only whole, sellable units are allocated, preventing fractional stock errors.
  • Scalability: Designed to handle multi-sheet data integration, pulling from "MintSoft" (Internal Warehouse), "Takealot Offers" (Current Marketplace Stock), and "Takealot Sales" (Velocity) simultaneously.


Comparison: Mechanism vs Mechanism

Feature

Integration

Data Format

Speed

User Effort

Safety Logic

Workbook Sync (File-Based)

Excel Formulas (VLOOKUP)

Spreadsheet Rows / CSV

Moderate (subject to Excel UI)

Requires manual file upload

Simple stock matching

API Sync (Direct-to-Server)

REST API (POST Request)

JSON (JavaScript Object Notation)

High (processed in-memory)

Fully automated "one-click" sync

Deducts pending orders to prevent overselling

The Workbook Sync demonstrates my ability to automate legacy business workflows using VBA's native spreadsheet manipulation tools. In contrast, the API Sync showcases my proficiency in modern software architecture, specifically handling asynchronous HTTP requests, JSON serialization, and complex data reconciliation across multiple external data sources.


Intelligent Clearance Promotion Engine

This module automates the selection and pricing strategy for "Clearance Sale" promotions. It scans the entire product catalog, identifies "stagnant" inventory based on storage fee eligibility, and generates a ready-to-submit promotion application with calculated discounts and optimized stock allocations.


How It Works

  • Strategic Filtering: The script acts as a filter, automatically identifying products marked as Storage Fee Eligible. This targets "stagnant" stock that is costing the business money, ensuring the promotion serves a clear financial objective (liquidation).
  • Algorithmic Pricing: Instead of a flat discount, it applies a two-step pricing logic:
    • Fixed Reduction: It reduces the current selling price by a base amount (e.g., R20).
    • Psychological Pricing: It applies a "Ending in 9" logic. If the new price ends in a zero (e.g., R180), it automatically adjusts it (to R179) to align with retail consumer psychology.
  • Inventory Hedging: To prevent selling out of an item too quickly or overselling, the module calculates a "Promotion Stock" limit. It takes the current stock level and rounds up to 50% (RoundUp(stock / 2, 0)), ensuring the business maintains a buffer for non-promotional sales.
  • Data Transformation & Formatting: The script handles the "heavy lifting" of data migration—clearing old application data, mapping columns from the master offer sheet to the template, and using Excel’s AutoFill methods to ensure technical fields (like "Participation Flags") are populated instantly for hundreds of rows.


Technical Implementation

  • AutoFilter Interface: Uses the Excel.AutoFilter object via VBA to handle large datasets efficiently without looping through every single row, significantly improving performance.
  • Dynamic Range Management: Employs Cells.End(xlUp).Row to ensure the script adapts to catalogs of any size, from 10 products to 10,000.
  • UI/UX Integration: Tied to a custom Ribbon Control (as seen in the Optional ByRef control As Office.IRibbonControl parameter), making the tool accessible to non-technical staff through a "one-click" interface.

This module demonstrates my ability to bridge the gap between Marketing Strategy and Software Engineering. By codifying retail psychology (9-ending prices) and financial constraints (storage fee eligibility) into a VBA tool, I created a system that turns a time consuming manual task into a 5-second automated process.

Secure Credential Management

This module acts as a security layer for the application. Instead of storing sensitive API keys (like Takealot or OpenAI keys) in plain text within the Excel file, this module interfaces directly with the Windows Credential Manager (Win32 API). It allows the application to securely retrieve encrypted passwords stored at the operating system level.

Before vs. After

  • The Risk: Storing a API keys, usernames and passwords in a spreadsheet means anyone who opens the file can steal it and modify pricing or orders.
  • The Solution: Using this Credential Manager Bridge, the sensitive information is never stored in the file. Only the authorized user on their specific computer can run the sync, making the tool safe for distribution.

How It Works

  • Deep System Integration (Win32 API): This isn't standard VBA; it uses Windows API declarations (advapi32.dll and kernel32.dll) to step outside of Excel and talk directly to the Windows OS.
  • Memory Management: The script utilizes direct memory manipulation (RtlMoveMemory) and pointers to retrieve data from protected system memory addresses.
  • Cross-Architecture Compatibility: The code is written with Conditional Compilation (#If VBA7), ensuring it works seamlessly on both modern 64-bit and legacy 32-bit versions of Microsoft Office.
  • Encapsulation: It simplifies a complex system call into a single, reusable function: GetGenericCredential("TargetName"). This makes it incredibly easy for other modules to authenticate securely without needing to know how the "under the hood" encryption works.


Key Technical Challenges Solved

  • Pointer Handling: Managed the transition of system pointers to VBA strings, a high-level programming task that prevents application crashes when dealing with unmanaged memory.
  • Security Best Practices: Implemented the CredFree call, ensuring that sensitive data is cleared from memory as soon as it is retrieved, minimizing the "attack surface" for memory-scraping malware.
  • Unicode Support: Used CredReadW (the Wide/Unicode version of the API) to ensure that special characters in passwords or usernames are handled correctly without corruption.

This module highlights my commitment to Application Security. By integrating with the Windows Credential Vault, I ensured that the business's sensitive information including API keys, usernames and passwords remain encrypted and tied to the user's Windows profile. It demonstrates the professional discipline required to handle sensitive data in a corporate environment.


Additional Task Automation Projects:

Automated Marketplace Fulfillment Engine

Core Function: A high-performance logistics bridge that automates the transformation of marketplace picklists (Takealot) into warehouse orders (MintSoft) and handles stakeholder communication in a single, unified execution.

Technical Deep-Dive

1. API-Driven Order Injection

  • The Technology: Developed a robust integration with the MintSoft WMS REST API. This module handles complex JSON payload construction—including nested OrderItems arrays—and executes authenticated POST requests to instantiate live warehouse orders.
  • The Impact: Transitioned the business from manual file uploads to "Real-Time Fulfillment." By bypassing the MintSoft web interface, the system eliminates human data-entry errors and provides immediate server-side feedback on order status.

2. Heuristic Logic & Data Cleaning

  • The Technology: Implemented a "Smart Importer" that utilizes FileSystemObject (FSO) to parse CSV picklists. It includes custom heuristic logic to auto-detect the target Distribution Center (JHB, CPT, DBN) based on filename patterns and applies a specialized CleanSkuColumnLoop to preserve critical leading zeros in SKUs.
  • The Impact: Standardized messy marketplace data into a rigorous template. The auto-detection of delivery addresses and contact details ensures that every order is routed to the correct geographical hub without manual intervention.

3. Automated Stakeholder Communication (Email.bas)

  • The Technology: Engineered an SMTP-based notification system using CDO (Collaboration Data Objects). This module dynamically generates HTML-formatted emails with conditional body text tailored to the specific warehouse being contacted.
  • The Information Flow: Upon successful API injection, the system automatically triggers a professional notification to the warehouse staff, attaching the original picklist and providing packing instructions specific to that DC’s requirements.

Business Impact & Efficiency

  • Process Consolidation: Compressed a disjointed, multi-step workflow (Import → Clean → Upload → Email) into a single "Select File" operation.
  • Operational Velocity: Reduced the time from receiving a marketplace picklist to notifying the warehouse from ~20 minutes to under 15 seconds.
  • Reliability: Integrated a professional error-handling layer that parses API response messages, ensuring that if a SKU or courier service is invalid, the user is notified immediately before the email is sent.

This project demonstrates my ability to take a legacy manual process and apply modern API automation to create a 'Zero-Touch' fulfillment workflow. By combining RESTful web services with advanced file handling and automated SMTP communications, I built a tool that allows for enterprise-scale logistics management within the familiar Excel environment.

Semi-Automated Procurement & Order Generation

Core Function: An integrated system that triggers data refreshes across marketplace and warehouse APIs, evaluates inventory health, and generates standalone suppliers orders ready for review before sending for a quote or purchase order..

1. Cross-Workbook API Orchestration

  • The Technology: This module leverages Application.Run to call the specialized Takealot and MintSoft API functions from the main Excel Add-In. It implements a structured status dashboard that logs record counts, timestamps, and execution duration for each sync.
  • The Impact: Centralizes control. It ensures that before an order is calculated, the system is working with the most recent sales velocity and stock-on-hand data, eliminating the "stale data" risk inherent in manual spreadsheets.

2. Automated Workbook Decoupling & Export

  • The Technology: Developed a robust export engine that programmatically creates a new, sanitized workbook (xlWBATWorksheet). It clones specific supplier sheets, strips away unnecessary macros or background data, and handles file I/O using dynamic naming conventions (Format(Now, "yyyymmdd")).
  • The Impact: Streamlines the supplier communication loop. It generates a professional, clean Excel file ready for email attachment, ensuring suppliers receive only relevant order data without exposing your internal business logic or API structures.

3. Performance Monitoring & UX

  • The Technology: Integrated execution timers (StartTime = Now) and confirmation dialogs to provide a controlled user experience. The code manages screen updating and background calculations to maintain stability during intensive pivot table refreshes.
  • The Impact: Provides transparency. The user sees exactly how long data refreshes take and receives confirmation of successful exports, reducing "black box" uncertainty and increasing trust in the automated results.

Business Impact

  • Procurement Speed: Reduced the lead time for generating supplier orders from hours of manual collation to a verified, one-click process.
  • Data Consistency: By forcing a refresh of Takealot and MintSoft data as part of the order routine, it ensures that purchase quantities are perfectly aligned with current marketplace demand and warehouse gaps.
  • Professionalism: Standardized the output format for all suppliers, ensuring consistency in the brand’s procurement documentation.

Multi-Channel Financial Reconciliation & Inventory Valuation Engine

Core Function: A comprehensive month-end sub-ledger system that consolidates disparate financial and inventory data from Takealot, Amazon, and MintSoft WMS into a unified, audit-ready dataset for Sage One Cloud Accounting.

1. Multi-Source ETL Pipeline

  • The Technology: Developed a resilient data ingestion layer using Power Query (M language) injected via VBA. The module dynamically manages connection strings to process CSV and Excel exports from three different platforms, featuring specialized "Coerce" functions to handle data-type mismatches and region-specific numeric formatting (e.g., dot-decimal detection).
  • The Impact: Standardizes heterogeneous data (different headers, date formats, and currencies) into a flat-file structure. This ensures that a "SKU" or "Transaction Fee" from Amazon is treated identically to one from Takealot, enabling a true "apples-to-apples" financial comparison.

2. Automated Inventory Valuation

  • The Technology: Engineered an orchestration engine that sequences the refresh of marketplace offers, warehouse stock-on-hand, and cost-price lookups. It utilizes a status-tracking dashboard to monitor the progress of nine distinct data loads, ensuring referential integrity before final calculations are performed.
  • The Impact: Provides an accurate "snapshot in time" of total capital tied up in stock across multiple locales. By automatically calculating inventory values based on current landing costs and SoH, it eliminates the manual labor of cross-referencing thousands of rows across different portals.

3. Transactional Consolidation & Sage Integration

  • The Technology: Implemented a Pivot-based consolidation layer that aggregates thousands of micro-transactions (sales, storage fees, returns, and referrals). The system includes a "Month-End Totals" module that archives results and increments accounting periods using custom DateSerial logic.
  • The Impact: Reduces "Accounting Friction." The system outputs a clean, consolidated summary of debits and credits ready for manual entry into Sage One. This transforms a 4 hour reconciliation headache into a 15-minute verification task.

Business Impact

  • Audit Readiness: Created a transparent, repeatable audit trail for month-end figures, moving away from "black-box" manual spreadsheets.
  • Financial Accuracy: By automating the cost-price lookup and fee consolidation, the system identifies margin leakages and ensures stock valuations are never based on stale data.
  • Operational Scalability: Designed to handle the increasing complexity of multi-channel retail; adding a new marketplace simply requires a new import module rather than a rebuild of the accounting logic.

Portfolio Perpective

These projects showcases my ability to take complex eCommerce operational tasks and turning them into simple, repeatable processes, saving time and and increasing accuracy by eliminating error prone manual tasks. This has freed up time and resources and has directly led to increased sales and revenue.