Bill of Materials purchasing optimization (using Octopart APIs)



Like many people, I periodically need to purchase components for a somewhat lengthy bill of materials for board production. Figuring out where to buy what without mistakes can be a somewhat stressful task. Octopart is a very useful search engine and data source for electronic components purchasing. They provide APIs which can call vendor and quantity specific price, inventory, and minimum order quantity. Almost always more than one vendor is needed to overcome shortages in inventory and fulfill the entire order. The more vendors you use, the lower the potential total purchasing cost for the project. However there are diminishing returns as the number of vendors increases. At some point its not worth dealing with the extra paperwork so identifying the ideal combination and number of vendors is of great interest. I've previously written a spreadsheet to manually select vendors based on on case by case decision making given stock levels and prices. The problem with that approach is that restricting the number of vendors as you go is problematic.

Based on this need I'd like an excel spreadsheet that calls Octopart APIs to optimally populate vendor choice for each part based on availability and lowest cost for a given number of vendors. This will allow me to increment the vendor quantity to get a feel for the most desirable course of action.

For example:

input: max 3 vendors allowed (of variable ~10+ options)


1: vendor list for minimum cost

2: vendor assignment for each part

The challenge is that the minimum cost combination of those 3 vendors must be found. In the example above there are 120 combinations of 3 vendors given a choice of ten. (combinations) Each part has a different number of vendors that stock it and vendors are often out of stock.


Develop an algorithm that handles the intent of these requirement including relevant exceptions, error handling and logic. Programming approaches other than excel are welcome if you believe they are more suited or you are more comfortable with them. The results should be formatted for easy output review and exportable as a CSV for purchasing. Please mention alternate approach if known ahead of time in your quote. Looking forward to working with you. Thanks!

Best regards,


Skills: Algorithm, Coding, Excel, Mathematics, Programming

See more: software write mq4, software write chip epson, useful software write book, software write web specs, full duplex audio player using waveinwaveout apis, twitter apis using aspnet, free software write user guide, software write edid, free software write company profile, access production plans bill materials, software write websites idea, structural steel bill materials, developed bill issuance system using visual basic language, software write book images, using excel sort phone bill, software write books, program using apis, software write protection, free software write book, software write book, bill quantity electrical design using autocad, bill materials revit, optimization machining parameter using finite element, structural bill materials spreadsheet, monthly bill payment software using vbnet

Project ID: #11721664

Awarded to:


Solid software engineer with 17+ experience, I write clean, fast and beautiful code. Quick delivery guaranteed. I work in consumer product manufacturing and used to run a small electronics business, so I am well acquai More

$222 USD in 3 days
(0 Reviews)