h1

OOcalc tricks for BOM management

2011/02/12

For the last several revisions of my current project I’ve constructed an insanely complex spreadsheet to keep track of all the parts each separate PCB needs, their part numbers, personal stock, quantity prices and other things.

As you can see, things get unmanageable very quickly.  In particular, having to look up and enter all the quantity pricing for Digikey and others is a major source of frustration and error, and waste of time.  Even worse, the spreadsheet only has room for minimum and “best guess” quantities, which means I’m unable to increase the number of units beyond my pre-set best guess of quantity and actually get realistic pricing.

I’ve made several attempts at solving this, and it finally started working.  The trick is to load the prices from Digikey directly into the spreadsheet, and have some clever processing that finds the right quantity price breaks for the required number of parts.

Unfortunately, I have yet to figure out how to get OOcalc to actually load a URL directly.  As a result, I’ve written a shell script that does the work of grabbing the HTML from DigiKey and parsing it into a basic list of quantity=price;quantity=price;… that gets written to a temporary file on disk.  Then a Basic (ewww!) macro triggers this shell script and reads the file from disk (since I can’t capture the script’s output…) to insert into a hidden cell in the spreadsheet.

The next step is more macros that parse through the quantity=price list and find me the nearest quantities above and below, as well as looking up the selected price.  From there I can calculate whether it’s cost effective to purchase a larger quantity or just go with the exact number I need.  For example, a particular chip I use is $54.63 in quantity q, but $47.73 in quantity 25.  Since I theoretically need 24 of them, it ends up being $1193 for 25 or $1311 for 24…  The spreadsheet calls that “high q. free”, and in that case comes to ~2.16 “free” parts.

The beast isn’t ready for release yet, but I hope to do so shortly, once I get a better handle on some more of the macros and formulas to give the best possible hints on quantity.  In the meantime if somebody can figure out how to (portably!) load the DigiKey pages from inside OOcalc without an external program, I’d love to integrate that and make this run entirely in OOcalc.

Advertisements

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

%d bloggers like this: