A Simple (and Dirty) Bill Of Materials And Stock Management Utility

As many readers may already know, when I’m not featuring your projects or working on the mooltipass I try to make simple things that may be useful to electronics enthusiasts. My latest creation is a simple bill of materials generation tool, which can also do simple stock management. Unfortunately for Linux users, this utility is made using Visual Basic functions in an Excel file.

It works fairly simply: just enter your schematics’ components references in the excel sheet, along with the corresponding Digikey webpage address. Click on the “fetch” button and the script will automatically get all your component characteristics from the internet and tell you the component costs depending on the number of prototypes you want to make. Then click the “sort BoM” button and your BoM will automatically be sorted by component type and value. Another functionality allows you to check that all the components present in your BoM are also present on the (very simple) Kicad generated one. Finally, using another Excel sheet containing your current stock, the Bill of Materials will let you know if you have enough components for the assembly stage. A video of the tool in action is embedded after the break, and you can download the BoM template here (.XLSM file) and the corresponding stock file there (.XLSM file).

http://www.youtube.com/watch?v=lPLJ8kMP0Uo

36 thoughts on “A Simple (and Dirty) Bill Of Materials And Stock Management Utility

      1. Has anyone tried using googledocs for something like this? I was having a poke around recently and noticed all the usual excel functions have been ported in addition to a fair bit of scripting capability for the whole googledocs suite. Could potentially be useful for projects like this…

  1. This looks great. The VB code should be possible to port to shell script, Python, or whatever. That script could easily input a CSV file of components and output a complete CSV file. OpenOffice uses XML, I think, so someone who knows that format could build a parser and generator for it. But CSV is easy and universal.

      1. Very keen to try this BOM method for my own work.
        I have a little trouble running it at the moment: new install of Office 2010, Windows XP virtual machine.
        I have set the folder where the BOM template file is stored as a trusted location within excel, using the “trust centre”.
        If I enable “IE.Visible = True” I can see the correct pages are being opened by the scripts…
        Could there be some settings which by default won’t allow the scripts to work fully?
        Any thoughts appreciated..

          1. Exactly. It does everything but insert the data into the cells such as ‘manufacturer’ and ‘digikey stock’ etc..
            I have tried everything I can think of. Would appreciate any suggestions as I can see this becoming part of my toolbox for making hardware.
            Also it is at all possible to do the same for Mouser? I have a few parts which Digikey simply don’t stock…

          2. Maybe it has something to do with Windows and Office versions.
            I am running win7 32bit with office 2007.
            The only thing that seems to function for me is opening the stock file.

            Does one need an account on Digikey?

          3. I haven’t changed the file name, but I have made some minor changes to the layout and added more rows etc. So I tried again with a freshly downloaded file, and no difference.
            Also the check against KiCAD generated BOM doesn’t work either.
            I am sure we can work this out, please persevere with me – I would get such great use out of this template!

  2. “Unfortunately for Linux users, this utility is made using Visual Basic functions in an Excel file.”

    Unfortunately for everyone, really. Open source is not just about open source, it’s about open data formats and avoiding vendor lock-in.

  3. Here’s what we do at work:

    Each component placed on a schematic has the following fields (attributes) added to it:

    PCB Footprint (we use the IPC naming convention and PCB Libraries’ PCB Library Expert to generate them)
    Manufacturer
    Manufacturer Part
    Distributor
    Distributor Part

    It’s not as bad as it seems, because once you create a schematic part, you have all that information for the next time you use it. We also have some parts with PCB Footprint already filled in (R’s and C’s for example)

    Draw your schematic (we use OrCAD), and as you draw, enter the information into the above fields for each part you use.

    When your schematic is complete, run the net lister (uses the PCB Footprint field) and the BOM generator (output is an Excel sheet with the usual information, extracted from the fields you added to the parts). I know some hobby schematic capture systems don’t generate a netlist, and use a single data structure for both Schematic and PCB symbols, but every commercial schematic capture system I know of allows the generation of a netlist and BOM from information stored with the schematic symbols.

    It’s really a lot more work to generate a BOM after the fact, and if the part information isn’t integral to the schematic symbols, you run the risk of having disagreements between your BOM and your schematic. And, as I pointed out earlier, once you create a schematic symbol with all the field data, you can save it and never have to enter the data again.

    1. Boy are you optimistic, and lucky.

      I was working as local outsourcing for UTC/Pratt & Whitney. They hired the company I worked for to do the “grunt” work so the engineers could be paid to do real work.

      Anyways, I forget the software they were using, but it was always just the component. Because the purchasing was always trying to find the cheapest parts, or faster supplier, or whatever was the driving factor that week. So us grunts were always going back and rebuilding the BOMs over and over and over again. Mind numbing I tell ya.

        1. I couldn’t comment on mission critical or not, my experience isn’t in Aerospace directly. I wouldn’t know a sprocket from a widget. My comment was more about the workflow.

          For smaller companies, it might not matter as much. But when its an “hydra” of many heads of driving factors things might not be as simple.

          And realize, this isn’t just happening in aerospace. Local outsourcing is hitting alot of the larger companies these days. Automotive, power, aerospace, finance and more.

  4. Hi all,
    I just saw this application and it was an “Aha moment!” of the kind that makes you say I hope I am not too late to the party!
    II downloaded this Bill of Materials project and thought maybe it will work for me, it does to open a browser and go to the specific part or component – the first one at least- then it just goes to the error line and stops there.
    But it fails at this line:
    Set objCollection = IE.document.getElementsByTagName(“table”)
    and goes to this line:
    handleCancel:
    If Err = 18 Then
    I am using Office 365, I tried to make it work but I am new to vba and programming in general. I am learning though and I am trying to learn programming, specially VBA, as much as possible on my own.

    Any help is appreciated.

    Thanks

Leave a Reply to Mathieu StephanCancel reply

Please be kind and respectful to help make the comments section excellent. (Comment Policy)

This site uses Akismet to reduce spam. Learn how your comment data is processed.