Archive for February, 2011


OOcalc tricks for BOM management


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.


Graphic demonstration of remote clock sync


So I’ve been perfecting the method I use in my current project to sync up the two clocks.  In a previous post I explained the generation method, which consists of using a master-generated reference pulse on the data bus to drive a PID that pushes the slave’s crystal frequency around to get them to line up.

Originally I’d maintained a local difference between the master and slave clocks, and any request for the clock would be adjusted by that.  However, I realized it would be very useful for the actual hardware timers to line up with the same values, and it turned out to be almost a trivial change.  Instead of just taking the offset and storing it, I pause the timers and reset them with a value that’s adjusted by the offset (plus a fudge factor for the time it takes to actually change the timers).  Any slight deviation left over can be handled by the I and D of the PID controller.

The main cycle counter is actually two daisy-chained 16-bit timers, and the daisy-chain mechanism is what on Xmega is called an “event”.  If I select the right event channel (0 out of 0..7) and flip a few config bits, I can output the 16th bit of the cycle counter to a pin, which I can then connect my scope to.  I hooked up my scope to this output on both the master and slave event outputs, and set it to trigger off the master’s pulse.

Initially there is only a master trace (channel 1) visible, with the slave (on channel 3) somewhere entirely else along the 488.3KHz cycle period (32MHz / 16 bits).  Halfway through the video I hit the key that triggers the sync protocol, and you can immediately see the slave’s clock trace show up and home in on a lock.

A quick eye-ball measurement of the jitter and delay gives me about a 1 cycle average delay (32MHz cycle) or about 31nS, and a worst-case jitter of about +-2 cycles or about 62nS. I’m betting some of this has to do with the tuning of my PID, as I can clearly see some periodic overshoot in the crystal adjustment parameter. My goal was supposed to be around 25,000nS if I remember right, so I think I’ve managed fairly well ;-)

Next step is to trigger a single pulse at another time and use that to start the ADC’s conversion clock…