Tuesday, April 16, 2013

Excel Column Name as a 0-based Index Number

This function is handy if you want to use packages like NPOI or EPPlus or some other Excel manipulation package.

Basically find the numeric value of the letter, and multiply it by the appropriate power of 26.  Think of it like Base-26 numerals converting to decimal (or Base-10).  The process is basically the same.  We have to subtract 1 at the end because A is used like a 1 rather than a 0, and in Excel, the columns start from 1, not 0. So A=0, B=1,C=2, ... AA=26, AB=27, AC=28, ... ZA=676, ... AAA=702, etc.  Although there is a limit to the number of columns in Excel 2007/2010 (I just can't remember it off the top of my head, and it's also dependent on if you are using 32bit Office or 64bit).

/// <summary>
/// Returns 0-based column number from Excel column name 

///   string, like "G" or "ZD" or "DAJ".
/// </summary>
/// <param name="column"></param>
/// <returns></returns>
private static int GetIntFromColumnName(string column)
{

    column = column.ToUpperInvariant();
    const string alpha = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
    int multiplier = (int)Math.Pow(26, column.Length - 1) ;
    int result = 0;
    char[] stuff = column.ToCharArray();
    for (int scan = 0; scan < column.Length; scan++)
    {
        if (!alpha.Contains(stuff[scan]))
            throw new ArgumentOutOfRangeException();
        result += (alpha.IndexOf(stuff[scan]) + 1) * multiplier;
        multiplier = (int)(multiplier / 26);
    }
    return result - 1;
}

Thursday, April 4, 2013

555 Based Constant Current Charge Circuit (UPDATED)

Ok, so first things first, I didn't come up with this.

Secondly, I'll probably be posting a followup and/or gutting this post to update any major changes, which, already I found one doing some troubleshooting.  Each update will be in red, and outright fallacies will be struck out or removed. 

UPDATE May1st, 2013:  Thirdly, the fab house where I bought my boards were themselves bought by another company (see below).

I'm borrowing heavily from the work of Kenneth Finnegan and Mike Davis.   Mike came up with the idea of using the comparators and the latch inside of a 555 timer chip to do a charge/dump cycle for a battery charger powered from a solar panel.  And Kenneth extended it a bit so that it's now a constant current charger.

The idea is that when a lead acid battery falls to 11.5V, it's considered discharged (at least if it's being stored, as lead acid batteries don't like being discharged below like 9V unless you're talking about a marine battery or an AGM battery).  So the Trigger comparator (pin 2) looks for a low battery voltage set there (through a voltage divider), and then kicks the 555 on, which switches on an LM317 constant current circuit.  Then the Threshold comparator (pin 6) will kick the 555 off again when the battery rises to about 14.2V, which is the point where most lead acid batteries should stop charging, although your mileage will vary (it should be written on the battery).

This does not go into the constant voltage stage of a charge cycle after the 555 kicks off, and there is resistance between positive and ground of the battery through the voltage dividers, so it WILL eventually drain the battery, BUT it should kick back in to charge it back up.  So as long as there is power: No problem!

Go here for a great talk on lead acid batteries from Ken for more details on the whole process and the chemistry of these batteries.  And also this great primer from Bill Hammack the Engineer Guy.

So, here is the circuit (see Kenneth's blog and Mike's post for the originals).  (This is the prototype I had made, not the current circuit I'm working on, although if you can get this to work for you, please drop me a line with anything you learned along the way, as I seem to be getting different behavior from Ken)

I'm a bit of a n00b at Eagle, so there's extra pins for the pads where you connect the supply voltage and the battery connections instead of nice connectors or screw terminals.  *shrug*  It works, which is a win in my book, so... It is what it is.

The 7805 acts as a stable voltage reference against which the 555 checks the battery at VOUT.  The trim pots need to be adjusted so that, when the VOUT pin is at 11.5 volts (the trigger or ON point for the battery to start charging), the voltage being fed into Pin 2 is about 1.667 volts (test point 1).  Then adjust the other trim pot with VOUT at 14.2V so that the voltage at Pin 6 is at about 3.333 volts (test point 2).  This should calibrate the charger for most lead acid batteries.

According to Ken, when the 555 output goes low (OFF), the LM317 constant current circuit shuts down, and the 1N5818 diode prevents damage to the rest of the circuit.  Basically, with the Adjust pin pulled down through the transistor (which is there to prevent over-voltage to the 555), the voltage regulator output goes way down, to the point where the diode is reverse-biased.   When the 555 goes high, the transistor stops conducting and the Adjust pin floats relative to the battery, and the current sensing resistor (R1).  The current sensing resistor keeps the adjust pin just at the right point to bring the voltage high enough to conduct to the battery, but not so high as to draw too much current.  Ken indicated on his blog that he has a switch to kick over to a 3ohm resistor instead of a 1.5ohm to allow for slower charging (half of the current).  He doesn't have it listed in his circuit, and I opted to not use it either.

UPDATE:  I've since come to realize that the 555 might be asked to do too much work with driving the LEDs and sinking the adjust pin of the LM317.  I've replaced the NPN transistor with a PNP transistor that pulls the LM317 directly to ground, rather than through the 555.  The 555 now drives the base of the transistor through the 4.7k resistor that was there before, rather than providing the ground path through itself.  I noticed that if the charging LED was pulling too much current from pin 3, it was enough to start "dragging down" the LM317 through a partially conductive transistor and it would fall well below the battery preventing it from actually charging.

Here is my prototype which I ordered from BatchPCB.  I had to send them the gerber files from my Eagle board file and wait about 2 weeks (note this board is the one currently uploaded on BatchPCB, not the completed prototype pictured below, which was version 1.0, this is 1.1). I'm already up to 1.2, which isn't on BatchPCB yet with the transistor change mentioned above.   

UPDATE May1st, 2013:  Also, see this note.  BatchPCB has been bought out by OSH Park. Looks like I'll either need to follow their guide on migrating accounts, or just wait, and submit a board after I've done all my major edits and other proofs.  This thing is difficult to troubleshoot, and my bench equipment sucks...


BatchPCB is used to be run by the people at Sparkfun Electronics. They have had a great service, but you should ONLY use it for prototypingThere's a $10 setup fee, and the individual board prices are... a bit high.  But it's it was MEANT to be a prototyping house so that you don't have to buy 20 boards only to find out there are huge mistakes (like the one's I've listed or worse).  They scooped up a number of orders and "panelize" them so they all get made at once, hence the wait.  (The new service seems to offer somewhat quicker turn around but still using the same "bundle peoples' orders to save costs" scheme, and they also sell boards in "sets" so you get - at the time of writing this - 3 boards in a single order, which is actually cool) I got two in case I had to do green-wire surgery (turns out I do).  Once you've proven out your board, go to a full-blown fab house to bring the cost way down.

Honestly, though, I'm really happy with the results. (OSH Park uses gold on purple, and they have tighter tolerances, so... we shall see... I might make this an SMD board after all. Then again I might just approach SparkFun and have them make/sell them)


This isn't the end of this board, I'm still making tweaks (as indicated above).  Such as adding a filter cap on the output to stomp noise from reaching the 555.  You can already see some changes from the silk screen (yellow) from the photos.  I had to switch the LEDs, as they were labeled backwards, so you'll see lines drawn on the photos.  I also had to move the mounting holes.  Turns out there's this little thing called "clearance" you have to watch out for [/sarcasm].  But that's what a prototype is meant to weed out.



It needs an enclosure, which might also help with the noise-susceptibility problem, and ideally a fuse or two. And, I need like, 8 more of them... and the same number of power supplies (*yikes*).  I have almost a dozen of those SLA's from old UPS equipment I've repaired for friends.  I use them for portable power and to bilge out my sumps if I lose power (thank you marine supply store!).  The hard part is keeping them charged for long stretches, and these boards are just what I needed... assuming I get more...