# Ultimate Battery Tracking Spreadsheet



## bbb74 (Apr 22, 2011)

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:






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





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




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:





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.





There is extensive help, an excerpt of which is here:






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 oo:


----------



## angelofwar (Apr 22, 2011)

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 (Apr 22, 2011)

angelofwar said:


> Neat idea...and, no, you're not the only one...maybe just the worst??? LOL!



I always have to be the worst...


----------



## bbb74 (Oct 23, 2011)

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 (Oct 24, 2011)

I just wanted to say thanks for the spreadsheet. It sure beats my paper notepad with all my chicken scratch on it..... Thanks


----------



## wheniwake (Dec 10, 2011)

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


----------



## bbb74 (Dec 10, 2011)

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.


----------



## tobrien (Dec 10, 2011)

holy cow! this is so cool! thank you!


----------



## wheniwake (Dec 10, 2011)

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 (Mar 7, 2012)

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 (Mar 12, 2012)

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 (Mar 12, 2012)

Wreck3r said:


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



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 (Mar 13, 2012)

Wreck3r said:


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



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 (Mar 13, 2012)

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:







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

Cheers


----------



## bbb74 (Mar 13, 2012)

Wreck3r said:


> 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 (Mar 14, 2012)

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 (Mar 17, 2012)

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 (Mar 18, 2012)

tobrien said:


> 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 (Mar 20, 2012)

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 (Apr 13, 2012)

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.


----------



## bbb74 (Apr 13, 2012)

Gah, double post


----------



## bbb74 (Apr 23, 2012)

New in version 15, still available from the above link:




Created new Summary sheet, renamed old Summary sheet to "Stats", way better for large number of batts. See screenshot below.

Added "Retired" column to inventory, to retire cells and exclude them from display and stats

More performance tuning - chopped 50ms off the Summarise button, so its as close to instant now as I can get it

Changed mind, renamed 2 actions: "Dischg remainder"->"Discharge", and "Dischg capacity"->"Discharge cap"

Any change to Records, Setup, or Inventory sheet now cause the Reset Cache flag to be set to true

Added conditional formatting of %orig capacity and Avg Soc fields in Stats sheet

Added titles to the Device Grid (start the entry with a * and it will be a title rather than a device)

Big cleanup and improvement in the Help sheet

Got bored of Arial font and changed everything to Tahoma, made sheet tab colours neater…

Will import v14 spreadsheets, plus older spreadsheets that v14 could import.


New summary screen:






You can upgrade from previous versions simply by using the Import Data button on the help page. This will slurp your data into the new version from your old version.


----------



## bbb74 (Aug 31, 2012)

Ok new version available.

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

*Changes:*

v18Visual revamp, especially of Stats sheet but others tooAdded "Age (years), "~Used Ah" overall statistic and %Maint column to Stats sheetPuts thicker border between groups on the Stats sheet where the first word of the Cell name (before the first space) is different to the first word of the previous Cell name. Eg. Using prefixes of "aa " and "aaa " will cause a thicker border to be drawn between your AA and AAA Cells.Rewrote the highlighting section so there's heaps of intelligence in it and no niggling issues left. Added help section about all the extra highlighting features giving all the details. See that part of help above, for an idea of the changes.Now copies a list of maintenance actions required to the clipboard.F11 button now flips to and from Full Screen modeThe print area is automatically set for the Stats sheet so its easy to just hit "Print" now"Real Capacity" is now sum of the latest Breakin or R&A capacity for each Cell, rather than refresh&analyse onlyv17Added default shelfqueen location option to setup sheetAllow Cells in Shelfqueen status to be Autofilled to Use status

*Couple of screenshots:*


----------



## tobrien (Aug 31, 2012)

thanks for your excellent work!


----------



## ehmidat (Dec 16, 2015)

hei, 

I wonder if some one have excel sheet example of how to use the load profile with the battery capacity in optimum way.
I have fixed power from the grid, the house consumption, and the battery that connected to inverter. some hours the consumption is greter than the power from the grid and the battery is embty. then i need algorithm to better use the battery power such that the the consumption dosnt exceed the power of the grid.
Any one have an algorithm ore an example ?

regards


----------



## TermiNado (Aug 30, 2019)

Can someone send me this please? I need it to aid me in my power wall build


----------



## kojack6319 (Sep 3, 2019)

The link in #23 still works.


----------



## RobSpook (Jan 18, 2020)

This spreadsheet is fantastic! I've just discovered it, transferred all the data from my own spreadsheet and the info is just great! Well done. Just wondering if the original poster has made any additional mods to this since version "20"?


----------



## FlashlightJunkie757 (Jan 5, 2022)

@bbb74 could I please get access to the file? Or can anyone else who has it re-upload it? It looks like bbb74 might be MIA since early 2016


----------

