Trading Card Acquisition, Pricing & Inventory Optimization

Project Overview

Designed and operated an Excel-based decision-support system that combined TCGplayer inventory data with external Trader Tools market pricing to support collection acquisition, inventory reconciliation, retail repricing, trade valuation, and inventory management.

The workflow supported recurring trading-card acquisition campaigns and post-event inventory processing at a scale of thousands of SKUs and thousands of cards evaluated during individual event weekends.

Business Problem

Trading-card acquisition required decisions across multiple variables including market value, rarity, condition, language, inventory depth, customer demand, acquisition cost, and expected resale value.

A repeatable process was needed to convert those inputs into practical buying, trading, pricing, and inventory decisions.

Original Solution

The original workbook:

• imported fresh TCGplayer inventory data;
• incorporated Trader Tools pricing exports;
• matched inventory records against external market pricing;
• applied different business rules to bulk and higher-value inventory;
• adjusted pricing based on condition and language;
• controlled the number of copies exposed for sale;
• calculated cash acquisition values;
• calculated trade-in values;
• generated CSV output for updating TCGplayer inventory and pricing;
• routed unmatched records for manual verification.

Operating Scale

The workflow supported an inventory of more than 4,000 SKUs and was used around recurring acquisition events occurring approximately 20 times per year.

During individual event weekends, approximately 3,000 to 12,000 cards could be evaluated, with 4,000 to 5,000 or more cards retained during larger acquisition campaigns.

When operating as a buyer for dealer booths, acquisition budgets could reach approximately $60,000 per event.

Representative Business Rules

• Limit live inventory exposure to a maximum of eight copies per card.
• Treat cards around or below the $1 threshold according to bulk-card economics.
• Use market-driven pricing for higher-value inventory.
• Apply condition- and language-based pricing adjustments.
• Use value- and rarity-based acquisition rules.
• Calculate trade-in value at 125% of cash buylist value.
• Route unmatched or questionable records for manual review.

Modernization

The original workbook successfully encoded substantial business and operating logic, but its reliance on manual data movement and spreadsheet matching created maintainability and data-integrity risks as the system grew.

The modernization project reconstructed the historical workflow in Python using pandas while preserving the proven operating rules.

The redesigned workflow now:

• loads TCGplayer inventory and Trader Tools pricing data;

• creates explicit normalized Name + Edition matching keys;

• validates the market-data relationship as many-to-one;

• applies the reconstructed Tradelist Count = MIN(Count, 8) rule;

• assigns Trader Tools sellPrice to deterministically matched records;

• preserves original inventory data for traceability;

• identifies unmatched records rather than silently force-matching them;

• classifies reconciliation exceptions by cause; and

• produces reproducible reconciliation and exception outputs.

Validation Results

The reconstructed workflow was validated against 4,368 historical inventory records.

• 4,368 source records preserved

• 4,257 records successfully reconciled to Trader Tools pricing

• 111 unresolved records retained as explicit exceptions

• Name, Edition, and Count preserved across all 4,368 records

• Tradelist Count = MIN(Count, 8) validated across all 4,368 records

• Reconstructed pricing validated against Trader Tools sellPrice for all 4,257 matched records

The completed standalone Python script independently reproduced the same results established during notebook-based reconstruction and validation.

Historical Findings

The reconstruction identified several behaviors that were difficult to see in the original spreadsheet workflow.

A controlled Ae / Æ normalization rule recovered three inventory records that had previously failed to reconcile because TCGplayer and Trader Tools represented the same card names differently.

The analysis also reconstructed a historical Tarmogoyf pricing defect. Trader Tools contained sell prices of $139.97 for Modern Masters and $134.97 for Modern Masters 2015 Edition. The historical workbook assigned $274.94 to the Modern Masters record — exactly the sum of those two values.

This demonstrated that both Masters-edition price records contributed to the historical result. The modernized workflow prevents this condition by requiring an explicit normalized Name + Edition relationship.

The precise historical spreadsheet formula responsible was not inferred without direct formula evidence.

Project Status

Python modernization and reconciliation reconstruction complete.

The historical Excel workbook remains part of the project as source evidence and a record of the original operating system. The validated Python implementation now provides the reproducible reconciliation workflow.

The project repository includes:

• historical and reconstruction input data;

• analytical reconstruction and validation notebook;

• reusable Python reconciliation script;

• modernized reconciliation output;

• classified exception output; and

• documented validation results.