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).

Comments

  1. SYNTRONIKS says:

    He also uses this for the Easy-Phi project. It is OK in its current state and shows off some really skilled excel scripting and stuff

  2. Vonskippy says:

    VB Functions – so no OpenOffice or LibreOffice eh?

  3. Tim says:

    Thank you Mathieu

  4. Ludzinc says:

    Frell,
    That’s awesome!

  5. Also… in case your internet connection is not good, uncomment the “‘ Application.Wait DateAdd(“s”, 2, Now)” in module1

  6. jpnorair says:

    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.

  7. truthspew says:

    I too know VBA really well across a number of Microsoft Office apps. And yeah, I can see this would be easy to webify with Python or PHP.

  8. Jeff Nichols says:

    Altium Designer has all this built into it. One of its many features that make it light years better than its “competition”.

  9. Jeff says:

    It wont work why?

    • SATovey says:

      Assuming you have a version of excel that will run xlsm macro files, you need to put the file in a folder and then go into options and declare that folder a trusted zone. At that point it should run.

      • pasdesignal says:

        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..

        • Do you mean that no data is entered in the corresponding cells?

          • pasdesignal says:

            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…

          • Are you sure you haven’t changed anything in the Template? This may happen if you changed the sheet name. Mouser support hasn’t been implemented yet.

          • SATovey says:

            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?

          • pasdesignal says:

            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!

  10. vpapanik says:
  11. Andrew says:

    “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.

  12. ka1axy says:

    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.

    • Dave says:

      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.

      • Rob says:

        seems like a big change from prior decades… were the product(s) non-mission critical, or should I start being concerned about UTC/P&W’s product line in general?

        • Dave says:

          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.

  13. Rob says:

    This is really clever. Well done, [Mathieu Stephan]!

  14. Caleb says:

    How about adding a feature to get the Digikey hyperlink based on the Part Number instead of having to add the Digikey hyperlink for each part manually?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 94,598 other followers