Ultimate Battery Tracking Spreadsheet

bbb74

Enlightened
Joined
Jul 6, 2010
Messages
364
Location
Australia
A while ago I posted about a battery tracking spreadsheet. Its been a bit enhanced since then, thought I would share it around. Sample images of it are in this post, to get a copy PM me!

You start off adding your cells to an inventory, and setting up some thresholds for them:
inventoryz.png


Then you add records as you do stuff with your batteries (all the formatting here is automatic):
recordsj.png


A Summary page is fully automatically generated and formatted with all sort of stats. You can add records by selecting one or more cells in the summary and then clicking on the buttons to add the records (to save you having to type them in):
summary1.png

The summary automatically highlights cells that are a) due for a charge or b) have been cycled too many times without a discharge, c) have been too long between deep discharges, d) have been too long between refresh&analyses or breakins. You can customise the thresholds for these, per battery and per device, in the Setup sheet. The setup sheet also allows you to vary the per-battery settings based on what state the battery is in, and set thresholds for yellow (action coming up), purple (action due), and red (action overdue). Its not as complicated as it sounds its pretty neat.

The summary also shows how many cycles, deep cycles, and Ah the cell has been through, and also shows the most recent R&A/BI capacity and how much the cell has degraded since its first R&A/BI.

The summary also shows overall statistics totally up all your cells for statistics junkies:
summary2.png


The summary also has the "device grid" which automatically shows which devices are loaded with cells at the moment (devices highlighted means they are loaded with cells). Its handy when you have some "lost" cells to see the device you loaded them into and forgot about not highlighted in the device grid. Also, selecting a device, and one or more batteries by holding down "control", and clicking on the "Use" button painlessly adds a record that you have just used that battery in that device.
summary3.png


There is extensive help, an excerpt of which is here:
helpdw.png



The wording I've used is all related to the Maha C9000 but there are equivalent cycles on many chargers. If you have a charger that doesn't have an LCD display for capacities etc, you can still use the spreadsheet, it will track cycles and dates and where cells are etc, it just won't do the capacity side of things - you could always hide those columns.

It currently supports up to 500 cells, and over a million historical records (although with that many historical records, analysing the stats might take a while. Luckily the spreadsheet uses a cache so it doesn't need to analyse all the stats every time you update the Summary, only new stuff you've added).

You can name cells whatever you like as long as the names are
-2 or more characters in length; and
-the last character (rightmost) is a digit 0-9

The characters to the left of the last digit form a prefix. All cells with the same prefix are displayed together as a group, and you can add records for them together.

Disclaimer: You need a form of OCD (at least a mild case) to want to track your cells over time :) I know some people (me included) do it. This spreadsheet is designed to make that as easy/painless as possible. And be pretty :eek:oo:
 

angelofwar

Flashlight Enthusiast
Joined
Nov 17, 2007
Messages
3,336
Location
South Carolina
Guilty! I use mine primarily too track to track my primaries, since during the beginning stages of my flashaholism I was swapping batteries out, and got tired of not knowing which cells were good, etc. Mine's color coded mainly on the voltage (2.95-3.25=green, 2.75-2.85 =yellow, etc. Neat idea...and, no, you're not the only one...maybe just the worst??? LOL!
 

bbb74

Enlightened
Joined
Jul 6, 2010
Messages
364
Location
Australia
Ok updated version available.

Click here:
1. https://docs.google.com/leaf?id=0B3...NjVhZC00YjNjLTkwODUtZjcyOTlhMzcwMTY4&hl=en_GB
2. Don't try to use it in google docs it won't work.
3. Click the "Download" button in google docs to download the excel sheet, and then use it on your own computer.

Changes:
Can't remember all of them they're mostly minor, so your records can be copied & pasted from old versions you might have. However one major change is it has an auto-fill feature. If you select one or more batteries, and hit Control-T, the batteries selected will be "auto-filled" by:

a) if the battery is in a ready state it will change to "Use", and the device it is used in will be automatically set to the last device the battery was used in
b) if the battery is in use in a device, or discharged etc, it will change to "Charge in" ready for you to enter the amount of charge.

eg. I have 12 batteries I cycle through each 2 days in my bike lights. By dragging and selecting all 12 and hitting Control-T, the in use batteries will change to "Charge in" ready for me to enter the mAh values, and the batteries that were already charged will be updated to be in use in the bike light that battery normally goes into.
 

Onebadengine

Newly Enlightened
Joined
Sep 11, 2010
Messages
14
I just wanted to say thanks for the spreadsheet. It sure beats my paper notepad with all my chicken scratch on it..... Thanks
 

wheniwake

Newly Enlightened
Joined
Jul 12, 2010
Messages
16
Location
Scotland, UK
Sorry to dig up an older thread, the spreadsheet looks great. However Im running a 64bit System. Anyone know how to get this to work on a 64bit System?
W7 Ultimate 64bit w Excell 2010

Error message

"Compile error:
The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute."
 
Last edited:

bbb74

Enlightened
Joined
Jul 6, 2010
Messages
364
Location
Australia
I think its just due to the performance measuring module I have in there that I used when writing it. It can be deleted, this may fix it.


Go into the vb editor for the spreadsheet, and delete Module2 (its not actually required). Then in Module1, find and delete any line containing

QueryPerformanceXXXXXXX

where XXXXX will be one of two different words.
 

wheniwake

Newly Enlightened
Joined
Jul 12, 2010
Messages
16
Location
Scotland, UK
Thankyou so much bbb74, works like a charm now! I spent about 2 hours this morning trying various ways of fixing it , trying to do what microsoft and various forums recommended a Declare PTRsafe function .. which just made it more confusing.
It works when your steps are followed. May help someone else in future, or am I the only weirdo using a 64bit OS hah.
Thanks again!
 

bbb74

Enlightened
Joined
Jul 6, 2010
Messages
364
Location
Australia
Ok new version is now available. Follow the google docs link and *download* the spreadsheet from the google menu option (don't bother looking at it or trying to use it in google docs it just won't work).

Download link is: https://docs.google.com/open?id=0B3F1KhnoruHSZDZlM2RkNzMtNjVhZC00YjNjLTkwODUtZjcyOTlhMzcwMTY4

The link above contains all my data so you can see how it works, including all my stats such as: I've charged in 4100 Amp hours, and my cells have overall lost 2.7% of their capacity due to age&use.

Changes:
- Fixed a bug affecting the total amount you've charged into all your cells stat (it would be slightly wrong in a certain situation)
- Fixed a bug affecting the total capacity loss for all your cells stat (it would be a bit out in certain circumstances)
- Added an extra column to summary sheet with interesting info regarding the cell's deepest discharge since it was last emptied
- Added a "Find" button to the device grid. If you pick one or more devices in the device grid and click find, the batteries in use in that device or devices will be selected for you, ready for you to hit the "Charge" or whatever button, or use the Control-T autofill feature (which is still really cool) to flip them into a different state.
- Will work on 64bit without change as I removed the performance debugging stuff

Example usage case: I've got 12 batteries I have used up in 3 different bike lights. I select all 12 batteries in the summary with a single drag of the mouse (or select the 3 bike lights in the device grid and hit the "Find" button), then hit Control-T and the batteries switch to a Charge state, and I update the ??? value for each cell to the amount charged in, just using enter after entering each value to move on to the next cell. Then I can hit Control-T again and the batteries will flip back to being "In use" in the same bike lights they were before.
 

Wreck3r

Newly Enlightened
Joined
Feb 22, 2012
Messages
34
Dear bbb74,

Thank you very much for your work. This is a very useful application but I am having some problems running it.

On a 64bit Office 2010 I cannot run it because every time I get the following error: Compile error: Can't find project or library. (an example of stop location was: Dim d As Dictionary)

On a 32bit Office 2010 whenever I hit "clear all data" I get the following error: "Runtime error 6: Overflow"

I cannot ask you to fix this, you have done so much already, but if you ever have the time please let me know what I can do to fix this.

Thanks
 

bbb74

Enlightened
Joined
Jul 6, 2010
Messages
364
Location
Australia
Dear bbb74,

Thank you very much for your work. This is a very useful application but I am having some problems running it.

On a 64bit Office 2010 I cannot run it because every time I get the following error: Compile error: Can't find project or library. (an example of stop location was: Dim d As Dictionary)

On a 32bit Office 2010 whenever I hit "clear all data" I get the following error: "Runtime error 6: Overflow"

I cannot ask you to fix this, you have done so much already, but if you ever have the time please let me know what I can do to fix this.

Thanks

Its no problem to look at issues like this, I made it to be used!

Ok your first problem is one I thought was specific to Macs, but its really for Office 2010. I already have an easy fix for this to avoid using the "Dictionary" library, I just don't have it on me at the moment. I will post an updated version tomorrow that includes it.

Your second problem unfortunately was only just introduced by a change I made, whoops sorry about that. Its a stupid divide-by-0 error I wasn't careful enough with (and actually I have to admit I didn't test the Clear All Data function in the latest version - so its def my fault for not testing that).

So in short, I'll post an updated version tomorrow. Very very sorry about that!
 

bbb74

Enlightened
Joined
Jul 6, 2010
Messages
364
Location
Australia
Dear bbb74,

Thank you very much for your work. This is a very useful application but I am having some problems running it.

On a 64bit Office 2010 I cannot run it because every time I get the following error: Compile error: Can't find project or library. (an example of stop location was: Dim d As Dictionary)

On a 32bit Office 2010 whenever I hit "clear all data" I get the following error: "Runtime error 6: Overflow"

I cannot ask you to fix this, you have done so much already, but if you ever have the time please let me know what I can do to fix this.

Thanks

Ok all fixed now, try the link and download again. I've just totally removed the usage of the "dictionary" object from the spreadsheet as it has caused problems in the past if there are occasional problems if something under tools->references is wrong. It makes it a little slower to rebuild the cache but that only happens if you go and modify old records.

So in summary, the 2010 problem should have gone away, and the "clear all data" button should work.
 

Wreck3r

Newly Enlightened
Joined
Feb 22, 2012
Messages
34
Thank you for your time dedicated to these issues.

Now it works fine in Office 2010 x32 but I still get the Compile Error starting with opening the file and continuing with every button press. Every time it's another stop location specific to each button.

This is the error when starting the excel:

Compileerror.jpg



I'm starting to think that on this PC I'm missing a component.

Cheers
 

bbb74

Enlightened
Joined
Jul 6, 2010
Messages
364
Location
Australia
I'm starting to think that on this PC I'm missing a component.

Kind of - but its easily fixable :thumbsup: In the visual basic editor (that's what that screenshot you posted is) go to the Tools menu up the top then to "References". In the window that comes up, you will probably have a line in there saying "Missing". Uncheck the checkbox for that line and any others saying Missing, save and close the doc, and open it again. Should be ok then.

Also, I have updated the downloadable version by doing the same thing, but removing everything that was checked that wasn't actually needed. (Excel defaults to throwing in lots of stuff even if its unneeded, and it might have included something on my computer that isn't on yours). I'd appreciate it if you try that version too.

Thanks for your patience.
 

Wreck3r

Newly Enlightened
Joined
Feb 22, 2012
Messages
34
Tried the latest version and it's working now. First time I got the "Compile Error" I checked the VB help file and tried to resolve it by accessing Reference under Tools. Unfortunately it was greyed out so I couldn't fix it.

So to conclude: it's working on Office 2010 x32 and x64. You have created a very useful spreadsheet that I will use to take control over my rechargeable inventory.

Thank you very much!
 

tobrien

Flashlight Enthusiast
Joined
Dec 16, 2005
Messages
4,861
Location
Georgia Highway 441
i need and want to use this spreadsheet. i just need time, though, lol.

this is an amazing piece of work you did and thank you for making it available to us!
 

bbb74

Enlightened
Joined
Jul 6, 2010
Messages
364
Location
Australia
i need and want to use this spreadsheet. i just need time, though, lol.

this is an amazing piece of work you did and thank you for making it available to us!

No worries. Note I've made it to keep the amount of time required to the absolute minimum cos I hate wasting time myself. That's why you can select batteries, hit Control-T, and it will automatically fill them in with a best guess for you to correct.
 

bbb74

Enlightened
Joined
Jul 6, 2010
Messages
364
Location
Australia
New version available via the link.

Changes:
-Fixed very recently added bug where it ignored the first 2 records in the records sheet (used the wrong constant name in the code)
-The colour code for Shelfqueen batteries has changed from gray to dark green. Using the "Shelfqueen" button on the summary page also lets you enter where the battery is stored, or just put a "y" or whatever if you don't need to track that.
-Device grid has been improved, unused devices are now highlighted in purple rather than left as the background gray
-New button "Find in Records" on Summary sheet. If you select a battery and click this new button, it will flip you over to the Records sheet and filter so that you can just see the records for that battery set.
-The "R" button was kind of pointless and it has been removed and replaced by the Find in Records button above.
 

bbb74

Enlightened
Joined
Jul 6, 2010
Messages
364
Location
Australia
Ok a new version (v14) full of awesomeness is now available, still via https://docs.google.com/open?id=0B3F1KhnoruHSZDZlM2RkNzMtNjVhZC00YjNjLTkwODUtZjcyOTlhMzcwMTY4


Changes:
  • New ability to import the data from your existing spreadsheet into this new version - easy upgrades from now on
  • Added AutoFill button (so you can use it or the existing Control-T shortcut for autofilling records)
  • Added 2 new cycles - 1/3rd Breakin and 2/3rd Breaking, for when you want to do a 16 hour breakin charge, or want to do the 16 hour charge followed by discharge, rather than a full 40 hour charge/discharge/charge Breakin cycle
  • Added "Lowest SoC" column to report on the lowest state of charge since the battery was last emptied
  • Added "Avg SoC" column to report on the average state of charge for each cell when it was recharged
  • Added "#chg since BI" column to report on number of charges each cell has had since its last Breakin. This field is automatically highlighted if the number of charges crosses a configurable threshold.
  • Huge cleanup of internal vb code so its a lot neater now
  • Rewrote how the vb code interacts with data in the excel sheets, leading to a huge performance increase (like 6000%) when re-building the cache sheet. Instead of accessing one cell at a time, whole blocks of sheets are copied into arrays where they are accessed.
  • Cleanup of terminology. The following cycles have been renamed: Charge in->Charge, BI capacity->Breakin, R&A capacity->Refresh&Analyse, Discharge cap->Dischg capacity, Discharge out->Disch remainder
How to upgrade:
  • Download new version from link above (don't try to use it in google docs, it won't work)
  • Open new version you just downloaded
  • In the help sheet, click the big blue "Import Data" button
  • You will then be prompted to find your old spreadsheet to open
  • The data will then automatically be copied across into the new spreadsheet
  • This time around however, the device grid won't be copied because its shape has changed.
  • I've tested the import against old versions down to version 0.4. At some point older than that, it won't be able to import, but I can help with that if you need it, by disabling parts of the import that won't be compatible with super old versions. Just let me know.
 
Top