Stock Data Extraction and Standardization

Project Brief: Automated Stock Extraction & Excel Update System

1. Objective

Build a software tool that:
1. Extracts stock data from multiple differently formatted PDF files received from our principals.
2. Standardizes and stores that data in a master Excel file.
3. Maintains an always-updated stock summary Excel file for sharing with customers (WhatsApp, email).
4. Automatically reduces stock from the master when items are allocated/sold, and regenerates the updated summary.

This is an ongoing daily process.

⸻

2. Data Flow

Incoming Stock (from Principals)
• Input: PDF files from different principals with different layouts and styles.
• Requirement: Ability to define mappings/templates for each PDF format and extract only required fields.

Core fields (to be finalized):
• Batch / Lot No.
• Item Code / SKU
• Item Description
• Quantity
• Unit (kg, pcs, etc.)
• Rate / Price (if available)
• Principal Name
• Date
• Any other agreed fields

Extracted data should be written into a Master Stock Excel in a standardized format.

Master Stock Register (Excel)
• Central file that holds current available stock at batch/item level.
• Every new PDF import adds to or updates this master.

Customer Summary (Excel)
• Separate summary Excel derived from the master.
• Shows brief, customer-friendly view, e.g.:
• Item / Grade
• Batch / Lot
• Available Quantity
• Any other relevant info
• Used for circulation on WhatsApp and email.

⸻

3. Outgoing Stock / Allocation

Stock is moved out multiple times a day.

Allocation Input
• The factory sends a file (Excel/CSV) listing items allocated to a customer for invoicing.
• Fields (approximate):
• Customer Name
• Item Code / Description
• Batch / Lot No.
• Quantity Allocated
• Date

Required Behaviour
1. We place this allocation file into a designated folder.
2. The system should:
• Match each allocation line with the corresponding batch/item in the Master Stock Excel.
• Subtract the allocated quantity from the master.
• Handle partial batch allocations.
• (Optional) Flag if requested quantity exceeds available stock.
3. After updating the master:
• Regenerate/update the Summary Stock Excel so that it always reflects the latest available stock for circulation.

This update may happen several times per day.

⸻

4. Functional Requirements
1. PDF Extraction
• Support for multiple PDF formats from different principals.
• For each format, ability to configure:
• Where the stock table is located
• How each column maps to standard fields
• Handle both item-wise and batch-wise PDFs.
2. Standardization & Master Table
• Convert all extracted data into a single standardized schema (fixed column set).
• Append or update entries in the Master Stock Excel.
• Logic to avoid duplicate entries for the same batch can be discussed.
3. Updates (Stock In & Stock Out)
• Ability to run:
• Stock import (from PDFs) at least once daily.
• Allocation processing multiple times a day.
4. Allocation Processing
• Read allocation files (Excel/CSV) from a specific folder.
• Update quantities in Master Stock based on allocation.
• Batches with zero quantity should not appear as available in the summary.
5. Summary Generation
• Auto-generate/update a clean summary Excel after:
• New stock is imported, and/or
• Allocations are processed.
• Summary should be compact and easy to share.
6. Error Handling & Logs
• Maintain logs for:
• PDF parsing failures
• Missing mappings/templates
• Allocation lines where stock is missing or insufficient
• Simple text/Excel log is sufficient.

⸻

5. Technical / Non-Functional Expectations
• Target environment: Windows (desktop or server).
• Tech stack is flexible (Python, .NET, etc.); developer to propose.
• Interface can be simple:
• Script / small app that staff can run easily
• Or scheduled task (e.g., runs automatically at fixed times).
• Must be stable and easy enough for non-technical users to operate.

⸻

6. Suggested Folder Structure
• \PDF_Incoming\
• New stock PDFs from principals
• \Allocations_Incoming\
• Allocation/sales files from factory
• \Output\Master\
• Master_Stock.xlsx (always current)
• \Output\Summary\
• Stock_Summary_Current.xlsx (for WhatsApp/email)
• \Logs\
• Error and processing logs

⸻

7. Deliverables
1. Working software that:
• Extracts stock from mapped PDFs into Master Excel
• Processes allocation files and updates Master
• Regenerates updated Summary Excel
2. Documentation:
• How to add/modify PDF format mappings for new principals
• How to run the process (or configure scheduled runs)
• Description of Master and Summary file column structures
3. Optional:
• Validation/alerts for over-allocation or missing batches.