Innovative and high-quality Rofis flashlight
Results 1 to 24 of 24

Thread: Ultimate Battery Tracking Spreadsheet

  1. #1
    Flashaholic
    Join Date
    Jul 2010
    Location
    Australia
    Posts
    342

    Arrow Ultimate Battery Tracking Spreadsheet

    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

  2. #2
    Flashaholic* angelofwar's Avatar
    Join Date
    Nov 2007
    Location
    South Carolina
    Posts
    3,339

    Default Re: Ultimate Battery Tracking Spreadsheet

    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!

  3. #3
    Flashaholic
    Join Date
    Jul 2010
    Location
    Australia
    Posts
    342

    Default Re: Ultimate Battery Tracking Spreadsheet

    Quote Originally Posted by angelofwar View Post
    Neat idea...and, no, you're not the only one...maybe just the worst??? LOL!
    I always have to be the worst...

  4. #4
    Flashaholic
    Join Date
    Jul 2010
    Location
    Australia
    Posts
    342

    Default Re: Ultimate Battery Tracking Spreadsheet

    Ok updated version available.

    Click here:
    1. https://docs.google.com/leaf?id=0B3F...wMTY4&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.

  5. #5

    Default Re: Ultimate Battery Tracking Spreadsheet

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

  6. #6
    Unenlightened wheniwake's Avatar
    Join Date
    Jul 2010
    Location
    Scotland, UK
    Posts
    15

    Default Re: Ultimate Battery Tracking Spreadsheet

    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 by wheniwake; 12-10-2011 at 05:52 AM. Reason: Added error message

  7. #7
    Flashaholic
    Join Date
    Jul 2010
    Location
    Australia
    Posts
    342

    Default Re: Ultimate Battery Tracking Spreadsheet

    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.

  8. #8
    Flashaholic* tobrien's Avatar
    Join Date
    Dec 2005
    Location
    Georgia Highway 441
    Posts
    4,303

    Default Re: Ultimate Battery Tracking Spreadsheet

    holy cow! this is so cool! thank you!
    aka Edgar Allan Bro, Brosama Bin Liftin, Walter Crunkite, Bro Namath, Teddy Brosevelt, and the Tomahawk Crunkmissile.
    my lights - review of PrecisionWorks - that's Gucci Mane in my avatar

  9. #9
    Unenlightened wheniwake's Avatar
    Join Date
    Jul 2010
    Location
    Scotland, UK
    Posts
    15

    Default Re: Ultimate Battery Tracking Spreadsheet

    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!

  10. #10
    Flashaholic
    Join Date
    Jul 2010
    Location
    Australia
    Posts
    342

    Default Re: Ultimate Battery Tracking Spreadsheet

    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=0B3F...cyOTlhMzcwMTY4

    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.

  11. #11

    Default Re: Ultimate Battery Tracking Spreadsheet

    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

  12. #12
    Flashaholic
    Join Date
    Jul 2010
    Location
    Australia
    Posts
    342

    Default Re: Ultimate Battery Tracking Spreadsheet

    Quote Originally Posted by Wreck3r View Post
    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!

  13. #13
    Flashaholic
    Join Date
    Jul 2010
    Location
    Australia
    Posts
    342

    Default Re: Ultimate Battery Tracking Spreadsheet

    Quote Originally Posted by Wreck3r View Post
    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.

  14. #14

    Default Re: Ultimate Battery Tracking Spreadsheet

    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

  15. #15
    Flashaholic
    Join Date
    Jul 2010
    Location
    Australia
    Posts
    342

    Default Re: Ultimate Battery Tracking Spreadsheet

    Quote Originally Posted by Wreck3r View Post
    I'm starting to think that on this PC I'm missing a component.
    Kind of - but its easily fixable 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.

  16. #16

    Default Re: Ultimate Battery Tracking Spreadsheet

    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!

  17. #17
    Flashaholic* tobrien's Avatar
    Join Date
    Dec 2005
    Location
    Georgia Highway 441
    Posts
    4,303

    Default Re: Ultimate Battery Tracking Spreadsheet

    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!
    aka Edgar Allan Bro, Brosama Bin Liftin, Walter Crunkite, Bro Namath, Teddy Brosevelt, and the Tomahawk Crunkmissile.
    my lights - review of PrecisionWorks - that's Gucci Mane in my avatar

  18. #18
    Flashaholic
    Join Date
    Jul 2010
    Location
    Australia
    Posts
    342

    Default Re: Ultimate Battery Tracking Spreadsheet

    Quote Originally Posted by tobrien View Post
    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.

  19. #19
    Flashaholic
    Join Date
    Jul 2010
    Location
    Australia
    Posts
    342

    Default Re: Ultimate Battery Tracking Spreadsheet

    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.

  20. #20
    Flashaholic
    Join Date
    Jul 2010
    Location
    Australia
    Posts
    342

    Party Re: Ultimate Battery Tracking Spreadsheet

    Ok a new version (v14) full of awesomeness is now available, still via https://docs.google.com/open?id=0B3F1KhnoruHSZDZlM2RkNzMtNjVhZC00YjNjLTkwO DUtZjcyOTlhMzcwMTY4


    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.

  21. #21
    Flashaholic
    Join Date
    Jul 2010
    Location
    Australia
    Posts
    342

    Party Re: Ultimate Battery Tracking Spreadsheet

    Gah, double post
    Last edited by bbb74; 04-13-2012 at 08:25 AM. Reason: Gah, double post

  22. #22
    Flashaholic
    Join Date
    Jul 2010
    Location
    Australia
    Posts
    342

    Default Re: Ultimate Battery Tracking Spreadsheet

    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.

  23. #23
    Flashaholic
    Join Date
    Jul 2010
    Location
    Australia
    Posts
    342

    Default Re: Ultimate Battery Tracking Spreadsheet

    Ok new version available.

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

    Changes:
    v18 Visual revamp, especially of Stats sheet but others too
    Added "Age (years), "~Used Ah" overall statistic and %Maint column to Stats sheet
    Puts 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 mode
    The 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 only
    v17 Added default shelfqueen location option to setup sheet
    Allow Cells in Shelfqueen status to be Autofilled to Use status

    Couple of screenshots:











  24. #24
    Flashaholic* tobrien's Avatar
    Join Date
    Dec 2005
    Location
    Georgia Highway 441
    Posts
    4,303

    Default Re: Ultimate Battery Tracking Spreadsheet

    thanks for your excellent work!
    aka Edgar Allan Bro, Brosama Bin Liftin, Walter Crunkite, Bro Namath, Teddy Brosevelt, and the Tomahawk Crunkmissile.
    my lights - review of PrecisionWorks - that's Gucci Mane in my avatar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •