Pull Scrap Metal Prices into Excel with Power Query and the API

Many people in the scrap metal industry live in Excel. Pricing sheets, margin calculators, inventory reports, and purchasing budgets are all built in spreadsheets. Instead of manually updating prices every day, you can connect Excel directly to the ScrapMetal API using Power Query and have fresh pricing data flow into your workbook automatically.

What You Need

You need Excel 2016 or later for Windows, or Excel for Microsoft 365, which includes Power Query (called Get and Transform Data in some versions). You also need a ScrapMetal API key, which you can get for free at scrapmetal.dev.

Power Query is a built-in Excel tool that can connect to web APIs, transform JSON responses into table format, and refresh on demand or on a schedule. No VBA or coding is required.

Connecting to the API

Open Excel and navigate to the Data tab. Click Get Data, then From Other Sources, then From Web. In the URL field, enter the ScrapMetal API endpoint for current prices. You will need to add your API key as a header parameter.

In the Advanced section of the dialog, add a header with the name X-API-Key and your API key as the value. Click OK and Power Query will fetch the data and open the Query Editor.

Transforming the Response

The API returns a JSON array of price objects. Power Query will initially show this as a list. Click Into Table to convert it, then expand the Record column to extract all fields: metal, grade, isri_code, price, unit, source, region, and timestamp.

Set the data types for each column. Price should be a decimal number. Timestamp should be a date/time. The rest are text fields. Rename columns if you prefer different headers for your spreadsheet.

Building a Pricing Sheet

Once the query loads into a worksheet, you have a live data table that you can reference from other cells and sheets. Build your margin calculator by referencing the price column. For example, if your copper buy price is the market price minus $0.15 per pound, create a formula that subtracts 0.15 from the API price for each copper grade.

Create a summary dashboard on a separate sheet that pulls in prices for your most-traded grades. Add conditional formatting to highlight prices that have moved more than 5 percent from a baseline.

Refreshing Data

Right-click the query table and select Refresh to pull the latest prices. For automatic updates, go to the query properties and set a refresh interval. The minimum interval in Excel is one minute, but for scrap metal pricing, refreshing once per hour or once per day is more appropriate.

If you save the workbook to OneDrive or SharePoint, you can configure background refresh so the data updates even when the workbook is not open.

Sharing with Your Team

When you share the workbook, other users will be prompted to authorize the API connection. Each user will need their own API key. Alternatively, publish the workbook to a shared location and let it refresh with a single API key, which is simpler for small teams.