← Back to Tony's Portfolio
Excel / Power Query / Python / Production Traceability

Production Traceability System

A connected production traceability system I developed to link batches, device serials, component usage, material lots, service records and shipment history into one searchable structure for production follow-up and investigation.

Problem

Traceability information was spread across several Excel sheets and workbooks covering production, component usage, shipments, service work and material lots. Investigating one finished product could therefore require following several separate records before the complete history became clear.

  • Which shipment contained a specific device serial number or production batch?
  • Which component lots and production records were used to build a finished product?
  • Which finished products or shipments were connected to a particular component lot?
  • How can production, service and shipment records be followed when different records use different levels of batch or serial-number tracking?

The challenge grew as more production types and historical records were added. Some devices are tracked individually by serial number, while older records may only contain batch-level traceability. The system therefore needed to connect related records without assuming that every product or production period was documented in exactly the same way.

Solution

I built the traceability system around connected Excel workbooks and Power Query. Production work orders, BOM structures and component usage are combined into a common data structure, while shipment records, service records and material-lot information provide the other parts of the traceability chain.

The central search layer can then follow those connections in both directions. A search can start from a finished-product batch, device serial number, article, component lot or shipment reference and show the related production records, component usage, parent records and shipment information.

Power Query is used to consolidate the underlying production data so that new work-order records can be included without manually rebuilding the search data. A separate Python helper application has also been developed experimentally to provide faster searches and connection paths, but the Excel and Power Query system remains the main traceability solution.

Part of a Connected Production Data System

The traceability system is one part of a wider production-data workflow. Production and BOM records create the manufacturing history, material and stock data support planning, FIFO Planning helps determine which stock should be picked, and the Shipment Entry Assistant records what was actually shipped. The Traceability System then brings those records together for search, follow-up and investigation.

FIFO Planning Shipment Entry Assistant Forecasting & Planning

Key Features

  • Search by device serial number, production batch, article, component lot or shipment reference
  • Follow parent and component relationships across production work orders and BOM structures
  • Trace component lots forward to finished products and finished products back to their component usage
  • Connect shipment records to the corresponding production batches and device serial numbers
  • Include service-related records so returned or reworked devices remain part of the traceability history
  • Include material-lot information with manufacturing and expiry data when those values are available
  • Consolidate production and component usage through Power Query for a common searchable data structure
  • Support both serial-level and batch-level traceability for records with different historical tracking methods

Recent Improvements

  • Expanded the original search workbook into a connected traceability system using structured Excel tables and Power Query
  • Combined production work orders, BOM structures, component usage, shipments, service records and material-lot information
  • Improved forward and backward tracing between component lots, parent production records and finished products
  • Added support for manufacturing and expiry information only when those values are available for the related lot
  • Added service-record handling so returned, serviced or updated devices can remain connected to their production history
  • Improved workbook portability so file connections can be updated when the system is moved between folders or computers
  • Created Traceability Lite for simpler shipment-focused serial and batch searches, including support for older batch-only pump records
  • Added lot aggregation logic that combines repeated lot numbers, sums quantities and shows whether records came from finished production or shipments
  • Developed an experimental Python helper with cached and CSV-based lookup logic for faster searches and smart connection paths

Current Status

The Excel and Power Query solution is the main traceability system and is used to connect production, component, shipment, service and material-lot records for searching and investigation.

Traceability Lite provides a simpler shipment-focused version for serial and batch searches, including historical records. The separate Python helper application is still under development and has not replaced the workbook-based system.

Tools Used

Excel Power Query Structured Tables Advanced Formulas VBA Python openpyxl CSV Caching BOM / Component Mapping Traceability Logic

Result

The system made it much easier to investigate production history without manually searching through several separate workbooks. A serial number, batch, article, component lot or shipment reference can now be used as a starting point for following related production and shipment records.

It also created a more consistent structure for connecting component usage, finished products, service activity and material-lot information, while still supporting older records that do not contain the same level of serial-number detail as newer production data.

Traceability Coverage

The connected data structure supports investigation from several different starting points, allowing traceability to be followed both backward toward production and components and forward toward finished products and shipments.