Simple Farnell / Element14 Live Price & Stock Update in Excel BOM

When compiling a Bill Of Materials (BOM) for a project, you inevitably end up with a spreadsheet listing the parts and suppliers. Most PCB CAD packages can even output parts directly in CSV format which can be directly imported into a spreadsheet package such as Excel. Recently, I was playing around with a circuit in Eagle and thought I’d give the DesignLink feature a go. This links directly into the Farnell / Element 14 parts databases and allows you to compile a BOM with live pricing and stock and then allows you to export it as a CSV file. This is all well and good, but as soon as you export the data, it becomes “stale” and no longer updates. Could you set-up your exported BOM with live pricing? This post describes a simple way to do this in Excel under Windows using the Web Query feature. I’ve yet to find a way to do this in Excel on the Mac, so this is Windows only for the moment unfortunately.

Element14 Product Search API

Element14 has a natty little Web API that allows you to send a query direct to it and it returns the results in either XML or as JSON format. To see this is action, they have an interactive demo page which allows you to put a stock number in and it shows you what you get back. They encourage you to register and get an API Key that identifies you and will give you special pricing, if you have a special discount structure in place with them. If you don’t want to register, I’ve fiound that the API key shown in their examples seems to work just fine.

It generates a query such as:

https://api.element14.com/catalog/products?
term=id%3A1278613
&storeInfo.id=uk.farnell.com
&resultsSettings.offset=0
&resultsSettings.numberOfResults=1
&resultsSettings.refinements.filters=rohsCompliant%2CinStock
&resultsSettings.responseGroup=large
&callInfo.omitXmlSchema=false&callInfo.responseDataFormat=xml
&callinfo.apiKey=gd8n8b2kxqw6jq5mutsbrvur

which you can just copy and paste into a Browser address field and it’ll return the data into the Browser window.

Web Queries in an Excel BOM

One way to incorporate this functionality into Excel is via a Web Query stored in a file and imported into a table in Excel. Simply create a plain text file in NotePad or equivalent with something like the following text in it:

WEB
1
https://api.element14.com/catalog/products?term=id%3A["OC_FARNELL","Enter Farnell Order Code"]&storeInfo.id=uk.farnell.com&resultsSettings.responseGroup=medium&callInfo.responseDataFormat=xml&callinfo.apiKey=gd8n8b2kxqw6jq5mutsbrvur

Selection=EntirePage
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

Save it with a .iqy extension, which identifies it as a Web Query. Follow the link below to download my version: Farnell.iqy. Note I have used the example generic API Key – you can replace this with your own, and I have used a “Parameter” for the search term, which will allow you enter the Order code dynamically. You can of course edit the message “Enter Farnell Order Code” to suit – this API works equally well with Element14 and Newark websites.

[wpdm_file id=6]

Setting up your Excel spreadsheet

Setup a your BOM tab in the format you like with the Order Codes included in a column.

On you BOM spreadsheet, when you run your Web Query it tends to dump a whole table of data into your spreadsheet and so I tend to use a separate Tab for this “Live” information. Select a suitable cell for the start of this table and navigate to the “Data” Tab or Menu (depending on your version) and Select “Existing Connections”.

This will open a dialogue box, for you to navigate and select your .iqy file.

You will then be presented with a dialogue box that will prompt you for the Order Code. Select an appropriate Order Code from your BOM tab and this will link the Web Query with that cell. Excel should now run the Web Query and create a Table with the live imported data.

Query Settings

If you right-click on the start of the data and select the “Data Range Properties” you can fine tune the query options, such as refresh rate. It can be a good idea to select “Overwrite existing cells with new data, clear unused calls” as the default “Insert cells…” can sometimes insert extra lines of data on refesh and subsequent data links can get broken.

Link the Live Data to Your BOM

If you have got the live data imported into a separate Tab, you need to now link the cells in your BOM to the live data. Simply link the cell by clicking in the cell and then typing “=” and navigate to the relevant cell in your live table. This will then update your main BOM every time the live values changes.

Example Files

I have created an example spreadsheet using some Raspberry Pi parts that have a high stock turnover to demonstrate the live updates in action. You can download it from the link below:

Taking it Further

Once you start looking into this, you find there are many ways to do live BOMs. These range from online services such as PartsList that allow you to store live BOMS in the cloud to hooking into the data produced by services such as Octopart.com (which PartsList does for you). Octopart have a similar API and it outputs data in JSON format (however it will start to charge for using it’s service soon).

If you are a programmer with expertise in things like Java or Python you can roll applications to format and interpret this data. For companies that don’t have dedicated API’s you can even do a thing called “Web Scaping” which involves extracting the data you are interested in from the normal html page returned in your Browser.

Some companies like Mouser have good BOM hosting facilities, but I always find that you can never source all your parts from one supplier, which is one reason why I tend to keep them local. Of course there are other considerations too, for example, if you are working on a classified project, you won’t be able to keep BOM anywhere non-secure.

For the parts that can’t be linked to live data, I find a simple hyperlink in the spreadsheet to the supplier’s page is a reasonable compromise.

I hope this post has introduced you to what is now possible and provided a few signpost onwards.