Spreadsheet data manipulation - Here's a macro for parsing LIVE output files

[5 Minutes 2 Midnite]

I created a macro to run against the the LIVE output data file. Posting it here in case anyone would like such a tool. The macro allows you to parse the data from the 2 second polling frequency of the controller dump file down to a time frequency more manageable for analysis and charting. You can choose a value of 1 minute or greater frequency of data rows to extract.

To use the macro just download the attached Excel file. It's just a matter of exporting the data from the file the controller output (Data / Live), then open the macro sheet and paste the values in. Detailed instructions are in the file.

Raw export file with cells selected. Notice that column A is not selected:



Macro sheet (template). Note that a 15 minute time frame is entered by default:



Macro sheet with data pasted:



After running macro:



The macro also freezes the header row for easy scrolling and calculates max values on the bottom row of data



NOTES:
a) Be sure to save the macro enabled file under a different file name. Best to mark the file read only to avoid overwriting it.
b) You can also set your UTC time zone (I'm in the Pacific time zone) and also set the default time frame and save the empty template with those changes.
c) The easiest way to select the correct data in the raw output file is to place the cell pointer in B4, then while holding down SHIFT + CTRL keys tap right arrow then tap down arrow (and let up on keys). Then Ctl+C to copy.

[Vic]

Hi Five Minutes ..,

Thank you very much for Posting this,  with the annotated examples.   Am sure that this will be very useful to many of us.

Regarding the definition of the "Charge Stage" question that you posted in the Classic Topic,   it would seem  that MN Tech folks would know    ...   and Atop Andrew probably knows as well,   but all of these folks are probably quite bust with all of the new MN  products in late stages of development.

Will study your data more carefully,  in a bit.   Thanks again,  great work!   Vic

[5 Minutes 2 Midnite]

Thank you.

I hadn't programmed an Excel macro in 20 years so rather than go though the pain of learning Visual Basic for Applications (VBA) I just hired someone on upwork.com to do it. Got a bid from a gentleman in Pakistan to create the macro in 1 hour. His hourly rate: $12.50. I use UpWork for all computer related jobs, from needing a graphic artist to design a book cover to, well, Excel macros. ;>

I have updated the stage of charge thread with a best guess of the stages as I've been able to discern them.

[ClassicCrazy]

Thank you.

I hadn't programmed an Excel macro in 20 years so rather than go though the pain of learning Visual Basic for Applications (VBA) I just hired someone on upwork.com to do it. Got a bid from a gentleman in Pakistan to create the macro in 1 hour. His hourly rate: $12.50. I use UpWork for all computer related jobs, from needing a graphic artist to design a book cover to, well, Excel macros. ;>

I have updated the stage of charge thread with a best guess of the stages as I've been able to discern them.

That is good to know - I will have to check that place out. Sounds like a good resource.

Larry

[ClassicCrazy]

Be nice to make a graph of the system amps along with the SOC and charge state to see where the ending amps point is at.
I do that on My Midnite but it doesn't have the resolution you get with this.


Larry

[Vic]

Thank you.

I hadn't programmed an Excel macro in 20 years so rather than go though the pain of learning Visual Basic for Applications (VBA) I just hired someone on upwork.com to do it. Got a bid from a gentleman in Pakistan to create the macro in 1 hour. His hourly rate: $12.50. I use UpWork for all computer related jobs, from needing a graphic artist to design a book cover to, well, Excel macros. ;>

I have updated the stage of charge thread with a best guess of the stages as I've been able to discern them.

Thanks,  too,  for the tip on UpWork.      So much to learn,  here.   Vic

[5 Minutes 2 Midnite]

Yeah, I think the next thing to do is to add some charts to the sheet. The macro could be enhanced to automatically create a few popular charts based on feedback posted to this thread. The macro would build the charts based upon the data remaining after the macro runs. If you build the chart based upon the 2-second interval data it becomes a mess. That is the primary reason I thought about parsing the data in the first place, and to do so manually is a nuisance.

Let me pass on a few things I've learned about UpWork, since there is interest. I've found that chances of a successful outcome increase with these tips...

• When you create the job on upwork be very specific and detailed regarding what you want
• It is not unusually to get a LOT of responses back (upwork calls them proposals). Professionals from all over the world are on upwork and it's a buyers market. In my view, taking note of the content of the responses is the key to selecting the right candidate
• One clue in that regard is to pick someone who demonstrates they have actually read your job posting. Meaning, they refer to the specifics of the job requirements in their reply. Only a few candidates usually go to this trouble.
• The response will also inform you if they speak your language adequately to communicate well enough to get the job completed.
• Once you have a short list of candidates you can review the jobs they have already completed on upwork to see how close the match is. I find that is rarely necessary though. It is their first response to the posted job that really tells the story.

To give an example, here is the job posting for this Excel macro. (The reason for the note about the $5 amount the job pays is due to the way upwork works. I never want to tell the candidates what the job will pay. I always want the candidates to tell me the cost [Ferengi Rule of Acquisition #286]. You get some nice surprises. Upwork does not make that easy so I just set the minimum and insert that note. Always works).

[NOTE: Job is set at $5 only because a number is required. Please let me know what you charge]

See attached Excel spreadsheet. It is the log created by my solar system. It shows the state of charge of the batteries and supporting information.

The problem is the log file is too granular. The controller dumps the data in 2 second increments. I would like to be able to run a macro that would parse the data (delete rows) based upon a value (in minutes) that I would supply when prompted.

So for example if I wanted to see the log data parsed into 15 minute increments, I would be able to type in "15" somewhere. This could be a predefined cell location, or a prompt by the macro.

In the "15 minute" example when the macro completed all rows would be deleted except for log entries for every 15 minutes.

Note:
The time period is 24 hours. But the length of the log file (number of rows populated) wold vary.
Sometimes the time period (column B) is an odd number of seconds, sometimes even.
The sample file is truncated to make it easier to upload. Some logs can contain a full 24 hour period, in 2 second increments.

Is this enough information to describe the macro?


Now here is an example of a proposal I always reject, because it is a generic response:

Greetings,

Thank you for your time and consideration!

My name is Igor xxxxxx, and I am the senior developer. I have been awarded as top 1% coder as the best coder in Mobile, Web, and Desktop at UpWork Global Inc. and top 1% developer at xxxx Corp. I am keen to discuss further with you about this project, and I am available to start work immediately.



And here is the chosen proposal. The candidate understands the job requirements, speaks my language well, and is professional.

Hi Steve, I've read your job post and I think I can do this job in an hour. Thanks for the nice explanation. I think you need to decrease the frequency of data as per your requirement. I can start immediately.

I am a Top Rated Excel VBA expert on Upwork with 100% job success rate. In my Upwork profile, you can see all the necessary details about my experience in Excel.

Thank you for your time and consideration.

With Regards,

Khurram xxxxx

[Vic]

WOW,   Steve !!

Thank you very much for the treatise on using UpWork to screen and hire talent.

Really appreciate that you took the time to detail the process,   with many tips.

It is amazin'  that hour programmer could do that job in one hour,   and that his pay rate was SO reasonable.

Thanks yet again,  for everything!    Vic

[5 Minutes 2 Midnite]

Here is an observation of the parsed data for those using an LFP battery pack. It seems there is always the question of what charging parameters to use with this chemistry (bulk voltage, absorb voltage, absorb time, WbJr end amps).

First, I have the charging parameters for my 48v 400AH LFP pack set as follows in the controller:




I realize that bulk charging to 58V (3.625) is a little high, but I'm running shakedown tests with newly added panels to learn the max power the system can create each day (still not enough!). For me the question has always been; How much longer do you charge after hitting the target voltage? One of the deep mysteries of the universe, it seems. But the below output file from a one-day cycle seems to contain a clue.

Don't let your eyes glaze over. It all boils down to the action on two highlighted rows.

Parsed output file (1 minute granularity)


Notice that at 11:52AM (row 716) the controller transitions from Bulk MPPT (stage 4) to Absorb (stage 3). Pack voltage is 57.9V, which for the purposes of this discussion we'll note as virtually 58v (it may well have hit 58v in the more granular data). Notice Input current is 9.6A. So the batteries are clearly still soaking in amps at that voltage.

Now notice that 10 minutes later at 12:02 (row 726) the controller has transitioned to Float (stage 5). That is only 10 minutes, even though the Absorb time is set to 1hr. So clearly that timer did not trigger the state change. It must be amps. In that 10 minute period the amps dropped from 9.6A to <1.4A. Again, I'm assuming that the granular data shows the current dropping below 1A. The WbJr End amps is set at 1A.

Tentative conclusions (at least with this battery pack and the charge curve on this day):
1) The LFP cells continued to draw current after being fully charged. But not for that long.
2) Current did in fact decrease, finally dropping to zero or near zero. Certainly less than 1A. This argues that WbJr End amps may be a preferred way to terminate charging as opposed to using an arbitrary number of minutes as the determinater to end charging.

Is my logic sound?

[ClassicCrazy]

Your BMS may be doing some of the controlling too. On my Lifepo4 bike battery the charger would go from constant current to constant voltage ( I think I have that right ). So when the batteries hit the top voltage it dropped the current so that the BMS was able to balance the cells - in my case the BMS had resistors to bleed off the high cells so the lower cells could come up to voltage and be balanced.

I had a Calb battery pack  years ago and there was a fellow on here who had one and preached the virtues of bottom balancing.  I followed along with his recommendations but kind of wish I hadn't because I made a few mistakes along the way and with no protection it wasn't good for the batteries. At that time BMS were not readily available and very expensive so that was part of decision to avoid one.  What I do remember is that the theory was to empty all the batteries to same low voltage initially , then to charge them up but cut it off below the top filled charge point when they would be about 90% full .  I had ending amps set and what would happen is that the cells would charge , but it would go from Absorb to Float really quickly . Then would have the reason for resting 38 code because the battery voltage on the batteries would stay high for quite awhile until the loads brought them down. So basically it would cut off charging for awhile . Maybe some of that depends on what kind of loads there are on the system but even with the loads I had it would miss out on some charging until the batteries dropped down a bit in SOC .

Larry

[5 Minutes 2 Midnite]

Your BMS may be doing some of the controlling too.

Makes sense. I only recently purchased an array with a BMS, so I'm not in the habit of thinking in those terms. I guess the main thing in that case is to be sure the controller doesn't terminate the charge (like a low absorb timer) until the batteries have had their fill.

[ClassicCrazy]

They say that lithium batteries last longer if they are not filled up to the top .   The main thing is  you don't want the cells to be unbalanced.
I think that modern battery management systems somehow don't fill them up to the top but also still keep the cells balanced.
I guess you would have figure out what your bms does or if there is a way to not charge them all the way to the top - like I was doing.
here is info on this - I didn't study it but maybe you would like to
https://batteryuniversity.com/learn/article/how_to_prolong_lithium_based_batteries

Larry

[5 Minutes 2 Midnite]

They say that lithium batteries last longer if they are not filled up to the top .   The main thing is  you don't want the cells to be unbalanced.
I think that modern battery management systems somehow don't fill them up to the top but also still keep the cells balanced.
I guess you would have figure out what your bms does or if there is a way to not charge them all the way to the top - like I was doing.
here is info on this - I didn't study it but maybe you would like to
https://batteryuniversity.com/learn/article/how_to_prolong_lithium_based_batteries

Larry

Thank you Larry! All too unfortunately I happen to be an alum of battery university. The first battery pack I purchased for this system (2017) turned out be defective. It was an array of Qty 32 72AH CALB knockoffs, and the cells were of such poor quality that the manufacturer went out of business. But until it was discovered that the cells were crap I spent a year and a half researching LiFePO4 cells in an effort to understand why my pack would not stay in balance, in spite of repeated top balancing, and even purchasing cell balancers for each cell (not a full blown BMS though).

Ultimately my battery vendor graciously replaced all the cells with the CALB counterparts. For some reason I even had trouble keeping that pack in balance (after trying all the tricks) and as a final solution purchased a Chinese "PowerWall" knock off with a built-in BMS (https://www.electriccarpartscompany.com/Custom-Built-48V-EV-Lithium-Batteries). They actually call these units Powerwalls (the gall!), but they don't contain inverters. Just cells and a BMS. I bought Qty 2 of the 10kWh units, which renders 400AH. They also come in 5kWh and 7.5kWh sizes. This is a rather amazing product as it was priced identical to purchasing the equivalent AH of CALB cells.

I am charging the array to near 100% for now just to see what kind of runtime I can get, and will reduce charging to 3.45v per cell when everything is tuned (like the Classic 150 charging parameters). Interestingly, the manufacturer recommends charging to 3.65v (58.4 pack voltage). But even though the system has a 10-year warranty I will likely be more conservative than that. ;>

[ClassicCrazy]

You have done your homework !
Very clean install you have there.

Larry

[dRdoS7]

Hi,

I love Macros!

I manually export values from my Classic to OpenOffice spreadsheets.

How do you get the data automatically?

I have a Classic 200 connected to a Windows PC. I use Local App for monitoring, and export data from that on the PC. Very labour intensive.

Thanks,

dRdoS7.

EDIT: Just re-read your first post (which naturally I skimmed first time ), it seems your're doing the same as me. For some reason I though it was automatic.