How to Track Material Price History From Your Invoices

To track material price history as a contractor, you build one row per invoice line for your top 30 to 50 part numbers across twelve months of supply-house invoices, keyed on part number plus unit of measure plus branch, then sort by part number and date. That sorted list is a price trail. Everything else in this post is the detail that keeps the trail honest.

You are not building an analytics system. You are building the one artifact you have never had: proof of what you paid for the same part, on named dates, from the same supplier. Without it, every pricing conversation is a memory contest, and you lose those. With it, your rep is looking at their own invoice numbers.

Fair warning up front. The first build is real work: pulling twelve months of paper, normalizing part numbers, fixing units of measure. Most shops that start this abandon it in month three, not because it is hard but because it is tedious in exactly the way a Thursday night is already tedious. Read the last section before you start, so you know what you are signing up for.

What a price trail actually is

A price trail is one part number, one unit of measure, and every unit price you paid for it in date order. Nothing more.

3/4" copper 90 ell, bag of 10, Branch 04
Mar 11  $14.20
Apr 02  $14.20
Apr 29  $16.10
May 20  $16.10
Jun 09  $17.80

That five-line block is the entire product. It settles arguments, it supports credit requests, and it is the only thing that turns a negotiation into a number. The reason nobody has one is that the data is spread across sixty invoices in a truck, a filing cabinet and an email folder, and nobody has ever put it in one column.

If you have not read the background on why this pattern goes unnoticed in the first place, start with what material price creep is and how it hides. This post is the mechanical build.

The eight columns that matter

Resist the urge to capture everything. Extra columns are the reason these files die. Build these eight and stop.

Two optional columns earn their keep once the file is running: job number, if you want to tie variance back to a job, and ticket type (will-call, delivered, counter), which is how you find out that your will-call pickups price differently from your delivered orders. That pattern shows up more often than people expect, and the reason is explained in how contract price and counter price diverge.

One row per invoice line. Not one row per invoice. If an invoice has fourteen lines and three of them are parts you track, you enter three rows.

Pick the parts worth tracking, and ignore the rest

You buy thousands of distinct SKUs a year. You will not track thousands. You do not need to.

Distributors themselves classify inventory into velocity buckets, the A/B/C classes that Electrical Trends describes as standard practice on the distributor side. Do the same thing to your own purchasing. Sort last year’s spend by part number, descending, and look at where the curve flattens. For most shops the top 30 to 50 part numbers carry the large majority of repeat material spend.

Then apply three filters:

• Repeat purchases only. A part you bought once has no trail. Two purchases is a data point. Five or more in twelve months is a trail worth watching.
• Commodity-linked first. Copper fittings, wire, PVC, sheet metal, strut, fasteners. These move with real markets, which means both that they change often and that you can benchmark them, and they overlap heavily with the line items where an increase usually hides.
• Anything a rep quoted you a price on. If it was worth negotiating, it is worth verifying.

Skip the one-off gear, the special orders and the non-stock items. Their prices are job-specific and a trail across them tells you nothing.

Getting twelve months of invoices without retyping them

Before you type a single row, ask your supplier for a purchase history export. Distributor ERPs hold this. Epicor’s electrical distribution platform, as one published example, ships contract pricing and job management modules with bid schedules and lot costs alongside its counter sales module, which means the transaction history exists in structured form on their side. Your rep can usually produce a CSV or a spreadsheet of your purchases by date, part number, quantity and unit price. Ask for twelve months, by line, including unit of measure and branch.

Ask plainly: “Can you send me my purchase history for the last twelve months at the line level, with part number, UOM, branch, quantity and unit price?” This is a normal request. It is not an accusation and no reasonable rep treats it as one.

If the export does not come, or comes as a PDF summary with no unit prices, you are typing. Two things make that survivable:

1. Type only your tracked part numbers. Ignore every other line on the invoice. This cuts the work by roughly the ratio of tracked SKUs to total lines, which is usually enormous.
2. Work backward from today. The most recent three months are the ones you can still act on. Build those first. If you never get to months seven through twelve, you still have something useful.

For the paper-in-the-truck version of this problem, the capture routine is its own subject: see how to scan paper supply-house invoices without losing the line detail.

Three normalizations that make or break the file

This is where price history files go wrong, and all three failures produce the same symptom: a price that looks like it moved when it did not.

Unit of measure

The same copper elbow can be billed per each, per bag of 10, or per carton of 100. A file that mixes them shows a 10x price jump that is not real, and worse, it hides a real 8% increase inside the noise. Pick one canonical UOM per part number and convert everything to it as you enter. Put the conversion in a separate column so you can see what you did.

Part number substitution

Your counter is out of the exact SKU and hands you the equivalent from another manufacturer. The description is close enough that you will not notice, but the part number is different, and negotiated or special pricing is attached to specific product records rather than to categories. Two consequences: the substitute may genuinely cost more, and the trail for your original part now has a hole in it. When you spot a substitution, add a note column entry rather than silently folding it into the original part’s trail.

Branch

Branches carry real autonomy over stocking and pricing. If your file mixes two branches and one of them never got your agreement loaded, the trail zigzags for a reason that has nothing to do with the market, which is how one company ends up quoting two prices for the same SKU. Keep branch as a column and, when you first analyze, look at each branch separately before you combine them.

What to compute once the data is in

Sort by part number, then by date. That single sort is worth more than any chart. Then add four calculated columns.

That last column is the one that decides where your time goes. A 25% jump on a part you buy twice a year is a curiosity. A 6% jump on a part you buy every week is a real number, and what 6% drift costs a shop over a year is arithmetic worth running once.

If you want the accounting-standard framing, materials price variance is (actual price minus standard price) times actual quantity, with named causes including rush delivery charges, commodity swings and volume differing from the estimate. Your “prior purchase price” is functioning as the standard price. The formula is the same one your job costing uses; you are just applying it at the part level instead of the job level.

Benchmark the trail against a published index

A price increase is not automatically an error. Some of it is the market, and you need to know which is which before you pick up the phone. The Bureau of Labor Statistics publishes producer price index series that map reasonably well onto contractor material categories, and they are free.

Wire is the one people most often get wrong. There is a real commodity series for it, WPU10260314, running monthly since December 1986, and it read 530.917 in January 2026 and 571.299 (P) in June 2026. Use it rather than an industry manufacturing proxy. It covers wire and cable as a category and does not separate THHN from MC from NM-B, so a single tracked SKU can still move differently.

HVAC is the other one to get right, and the fix is to split it rather than blend it. WPU114802 covers unitary air-conditioners except air source heat pumps and read 280.520 in January 2026 and 297.025 (P) in May 2026. WPU11480734 covers heat pumps and read 146.923 in January 2026 and 141.564 (P) in June 2026. One is up about 6%, the other is down about 4%, so pick the one that matches what you install and never average the two. Note also that WPU114802 publishes about a month behind the rest of these series, ending May 2026 while the others end June, so line the months up before you compare. The industry index PCU333415333415 is worth a look only as a secondary cross-check when your scope genuinely spans both. Plastic pipe splits the same way: WPU07210603 for pipe, 171.280 in January 2026 to 182.609 (P) in June, and WPU07210604 for fittings and unions, 334.420 to 354.724 (P) over the same months. Neither one separates PVC from CPVC from PEX.

Recent months in these series carry a preliminary (P) flag from BLS and get revised, so pull the current value yourself rather than trusting a number you read somewhere. Add one column to your file: the index level for the month of each purchase. Now you can ask the only question that matters, which is whether your price moved more than the index did. Sorting that out properly is its own post: telling price creep apart from a real market increase.

How long this takes, and when to stop

Honest numbers, from the shape of the work rather than from any survey.

First build, with a supplier export: an afternoon. Most of it is normalizing UOM and deciding which parts to track.

First build, typing from paper: considerably longer, and it scales with how many tracked parts appear per invoice. Budget several evenings and do the most recent three months first so you get something usable early.

Monthly upkeep: short, once the columns exist. Adding a month of lines for 40 tracked parts is a routine data entry task, not an analysis task.

The failure mode shows up at month three, when nothing dramatic has turned up and the file quietly stops getting updated. Two things prevent that:

• Put it on the calendar with a name. Not “do invoices.” A recurring 30-minute block called “price trail” on the same day each month.
• Report a dollar figure to yourself. Every month, the sum of the extended-impact column. Even when it is small, the number keeps the file alive. When it is not small, you already have the evidence.

And be clear-eyed about the threshold. Below a certain material spend, a manual price history file is a hobby. Above it, the file pays for the time it takes several times over. You know which side of that line your shop is on better than anyone.

The payoff: what you do with it

The file is not the point. Three things you can only do once you have it:

1. Send a credit request with evidence. “Invoice #388102, line 3, part CU90-075-B10, billed at $17.80. My last four purchases of the same part at the same branch were $14.20, $14.20, $16.10 and $16.10.” That email is very hard to argue with, and fresh lines get corrected far more readily than old ones.
2. Negotiate from your own numbers. Walking into a pricing conversation with twelve months of your own unit prices changes the entire conversation. That is the whole subject of how to negotiate with your supply house.
3. Estimate from actuals. Your last-paid price beats your catalog price on every bid, and the trailing twelve-month slope tells you how much escalation to carry.

The build checklist

• Pull last year’s spend and pick the top 30 to 50 repeat part numbers.
• Ask your rep for a twelve-month line-level purchase history export before typing anything.
• Build eight columns: date, invoice number, branch, part number, description, UOM, quantity, unit price.
• One row per invoice line, not per invoice.
• Normalize UOM to one canonical unit per part, in a visible conversion column.
• Flag substituted part numbers instead of merging them into the original trail.
• Keep branches separate until you have looked at each one.
• Add change vs prior, change vs 12-month low, price-point count, and extended impact.
• Add the relevant BLS index level per month.
• Sort by part number and date. Read the trails.
• Book a recurring 30-minute monthly block and report yourself a dollar figure.

Sources

• Copper and brass mill shapes, PPI series WPU102502, BLS
• Copper wire and cable, PPI series WPU10260314, BLS
• Plastics pipe, PPI series WPU07210603, BLS
• Plastics pipe fittings and unions, PPI series WPU07210604, BLS
• Plastics pipe and pipe fitting manufacturing, PPI series PCU326122326122, BLS
• Plumbing fixture fittings and trim, PPI series WPU1054, BLS
• Steel mill products, PPI series WPU1017, BLS
• Sheet metal products, PPI series WPU1073, BLS
• Unitary air-conditioners, except air source heat pumps, PPI series WPU114802, BLS
• Heat pumps, PPI series WPU11480734, BLS
• Air-conditioning, refrigeration and forced air heating equipment manufacturing, PPI series PCU333415333415, BLS
• Materials price variance, AccountingTools
• Pricing Strategy Starts With Experience, Electrical Trends
• Introduction to Special Pricing Agreements, Enable
• Electrical Distribution Software, Epicor