5504CMU History output with Wiser or CTC

Hi All,

This is a bit of a beg to save some time, so if you have something you'll score a beer or two next time I see you.

Has anyone yet written a logic project to export the 5504CMU Current Measurement Unit's power usage / history database to something usable in the 'outside world'? I am collecting history in both a Wiser and a CTC, so logic for easiest target is good.

My client wants to use the data collected to compare with his electricity bills etc which I am sure is a fairly standard request.

Can it output in a spreadsheet, CSV, text file or whatever?

Thanks in advance if you have something,

Brad

 

You should be able to just open the Power Failure Recovery file (*_PFR.CSV) in Excel. It is tab separated data, so you may need to rename the file extension from csv to txt otherwise Excel will be looking for commas as separators.

If you have a graph of the power data in your CTC project, you can use the feature in PICED to export the graph to Excel.

I would actually discourage this, as it is unlikely to be useful. The current measurement unit is not as accurate as the meters used for billing. It is really intended to provide real time monitoring and to provide trends of energy usage.

If you can get the period of the data from the current measurement unit to line up with the billing period and you have the right tariff (which changes) and you have the mains voltage right (which varies) and you have the power factor right (which is usually unknown, and varies anyway), then they should be reasonably close.

The PICED help file topic "Power Meters" covers this.

 

Hi Darren,

Thanks for your help. As always it is appreciated, but the help file isn't really that comprehensive in this area. I had looked at the PFR file but it seemed pretty useless looking being like (just one line):

MDE 254 200 3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 9465 9465 9465 9465 9465 9465 9465 9465 9465 9465 0 0 0 0 0 0 0 996 13866 16048 11585 11962 13088 20961 23361 19854 39464 13690 11818 15155 16910 28740 35918 0 1092 2

without any time corelation info that I could see (other than assume each entry is one hour). Also, when trying to import it into Excel using data import it is too long for one sheet (seems to be after no time at all). So you get the 'The text file contains more data than will fit on a single worksheet, To continue and import as much as will fit Click OK. You can then import the rest of the data by repeating the import operation on another worksheet and using the Text Import Wizard to exclude data already imported' error.

Sorry, I know you were trying to be helpful but.........this file isn't very useful IMHO.(Is there another file I haven't found??)

Also, when you transfer the project from the unit, do some work and then back, any correlation seems to disappear as it appends the next hour onto the same line. When you modify the project file, it changes that day's figures, that affects that week's figure and also that month making the graphs nice but inaccurate. I think the update / write back routine should re-copy the most up to date history data just before it uploads the replacement project.

What I was actually after was something that one of the enthusiasts may have written in logic, that every hour exports to a text file / web page etc:
1. A timestamp datestamp
2. The kWhrs used in that hour per monitored CMU channel
3. The total kWhrs used in that hour
4. The tariff in place at that time
5. Cost per monitored CMU (nice to have)
6. Total Cost (nice to have)

Of course, any solar feed in tariff could be offset in the logic etc based on the site circumstances.

It really shouldn't be more than a few hours writing it and a few hours to debug etc. I just thought someone might have got around to it already before me and be willing to share their work to save others time. Sharing means better projects for everyone and that promotes the Clipsal C-Bus automation more above your competition. Being closed handed installers does not help promote the products, as everyone loses time for something requiring a steep learning curve, when some competitor's products do this generically from box open.

The logic examples forum is amazingly bare!!!! Let's get some brilliant examples there to help everyone.

Soapbox deleted................. Does anyone have a logic file they are using?

Many Thanks and potential beers,

Brad

 

Brad,
I'm with you onthis, but I've never really programmed clipsal. If you can make an overall idea and architecture how it would need to be programmed, I can donate some time to learning/doing it.

 

Hi Poldim,

A very nice offer and if you want to do it to get better acquainted with the C-Bus logic, go for it. I would suggest that it may be a steep learning curve for you though if you haven't done any of it yet (or pascal programming) so I wouldn't suggest it unless you are really interested to.

A start would be reading the Logic help on Piced and looking at the examples in the Logic forum and on other posts for the power measurement module. I posted some logic in there in one recent post. It is then a matter of reading the appropriate logic variables once an hour, parsing them into an output line (eg 12:00PM 11/11/11 P1 1650WH P2 2100WH P3 4400WH S1 -2200WH TOT 5950WH TARIFF 2 34.5c TC $2.05) and using whichever exterior file writing means that is best to append to a text file. I was going to write it using the AssignFile, Server Write and AppendFile methods to create an external file from one of the units (probably the CTC).

I currently do all this logic but just display it on the Wiser GUI and CTC screen using SetStringIBSystem commands. I will happily forward that logic for you to use as a base if that helps.

Though I appreciate your offer and would happily receive your logic for the stated payment, I would have thought someone else would have had something similar completed already and so could post it right away for the benefit of you and I.

Good luck in your programming effort if you decide to try it out and will still honour the beers, even if I need to send them to SF.

All the best,

Brad

 

There was never any intention that anyone would be needing to use the PFR data, so there is no documentation for it.

To reiterate my previous point, it is probably going to be counter productive to provide this data to a customer for comparing with their bill.

If anyone really wants to parse the PFR file, the format is described below, but it is subject to change without notice.

The first line of the file is the ISO 8601 Date & Time. Following this, the relevant lines of data are:

PHE <tab> <pulse power meter number> <tab> <Pulse power meter Hourly Energy values>
PHP <tab> <pulse power meter number> <tab> <Pulse power meter Hourly Peak power values>
PHC <tab> <pulse power meter number> <tab> <Pulse power meter Hourly Cost values>
PDE <tab> <pulse power meter number> <tab> <Pulse power meter Daily Energy values>
PDP <tab> <pulse power meter number> <tab> <Pulse power meter Daily Peak power values>
PDC <tab> <pulse power meter number> <tab> <Pulse power meter Daily Cost values>
MHE <tab> <meas app network> <tab> <meas app Unit Id> <tab> <meas app Channel> <tab> <Meas app Hourly Energy values>
MHP <tab> <meas app network> <tab> <meas app Unit Id> <tab> <meas app Channel> <tab> <Meas app Hourly Peak power values>
MHC <tab> <meas app network> <tab> <meas app Unit Id> <tab> <meas app Channel> <tab> <Meas app Hourly Cost values>
MDE <tab> <meas app network> <tab> <meas app Unit Id> <tab> <meas app Channel> <tab> <Meas app Daily Energy values>
MDP <tab> <meas app network> <tab> <meas app Unit Id> <tab> <meas app Channel> <tab> <Meas app Daily Peak power values>
MDC <tab> <meas app network> <tab> <meas app Unit Id> <tab> <meas app Channel> <tab> <Meas app Daily Cost values>

It probably depends on the version of Excel you are using. Mine worked fine.

No

This is issue number 20703

As a starting point, here is one way of doing this:
1. Create a file called "Power Data.csv"
2. Enter a single line of text (with a line feed):

3. Use the following logic code to write the data just before the end of the hour:

Code:

{ Global variables }
Energy, Cost : real;
DataString, s : string;

{Initialisation }
AssignFile(file1, 'Power Data.csv');
AppendFile(file1);

{ Modules }
once (Minute = 59) and (Second = 55) then
begin
  DateToString(Date, DataString);
  TimeToString(Time, s);
  Append(DataString, ',');
  Append(DataString, s);
  Energy := GetRealIBSystemIO("Measurement App Energy Value", "Local", 1, 1, 0, 1, 0, 0, 0);
  Format(s, ',', Energy:0:0);
  Append(DataString, s);
  Energy := GetRealIBSystemIO("Measurement App Energy Value", "Local", 1, 2, 0, 1, 0, 0, 0);
  Format(s, ',', Energy:0:0);
  Append(DataString, s);
  Energy := GetRealIBSystemIO("Power Meter Total Energy", 0, 1, 0, 0, 0, 0);
  Format(s, ',', Energy:0:0);
  Append(DataString, s);
  Cost := GetRealIBSystemIO("Energy Tariff", "General");
  Format(s, ',', Cost:0:2);
  Append(DataString, s);
  Cost := GetRealIBSystemIO("Power Meter Total Energy", 0, 1, 0, 2, 0, 0);
  Format(s, ',', Cost:0:2);
  Append(DataString, s);
  WriteLn(DataString);
  WriteLn(File1, DataString);
end;

It would be nice to have more sharing of logic, templates, image resources etc.

 

Hi Darren,

Your post is great. Many Thanks, exactly what I was looking for and the data for the PFR file is also useful for less extensive monitoring I guess.

I agree with your suggestion that the accuracy value of the data for this logging sort of purpose may not be that great, but my client just wants a starting point to understand his electricity bills, as he feels his supplier is not calculating his feed in tariff correctly. Even if the numbers are within 10% of accurate it will give him the information that he needs to ensure his supplier's billing mechanism is working correctly.

Hope the logic module was either a quick knock up or a previously used one!!

As for the spreadsheet, the PFR opens if I just load it directly but it puts all the data on each line into one cell. If I load it using the 'data import' function bringing it in as delimited, which puts the columnised data into the separate cells, that is when it tries to split it.

The date and time in the PFR file seems to be updated each time the project is opened, even if not saved.

All that is irrelevant though as I now have a good module 'template' to get it working and no doubt saved several hours in the process. So the bill has come in, so what beer do you like? (I'm a Peroni myself if you want that?) And you might want to PM me your location so I know where to send it.

Thanks again,

Brad

 

It only took be 10 minutes to do - but I do have the advantage of knowing the logic rather well.

No need - it is nice to be able to help.

I tried another approach, which is probably of more general use. The following logic code writes all of the daily energy usage for three power meters, and the total to a csv file which can be opened with Excel.

You only want to run this when needed, so I have a button on a page using the Module Enable Special Function to make the module run.

Code:

{ Global variables }
i : integer;
DataString, s : string;
Energy, Cost : real;

{ Initialisation }
DisableModule("Power Export");

{ Module "Power Export" }
AssignFile(file1, 'Power Data.csv');
ReWrite(file1);

WriteLn(file1, 'date, Phase A Wh, Phase B Wh, Solar Wh, Total Wh, total cost $');

for i := 364 DownTo 0 do
begin
  DateToString(Date - i, DataString);
  Energy := GetRealIBSystemIO("Measurement App Energy Value", "Wired", 2, 1, 1, 1, i, 0, 0);
  Format(s, ',', Energy:0:0);
  Append(DataString, s);
  Energy := GetRealIBSystemIO("Measurement App Energy Value", "Wired", 2, 2, 1, 1, i, 0, 0);
  Format(s, ',', Energy:0:0);
  Append(DataString, s);
  Energy := GetRealIBSystemIO("Measurement App Energy Value", "Wired", 2, 3, 1, 1, i, 0, 0);
  Format(s, ',', Energy:0:0);
  Append(DataString, s);
  Energy := GetRealIBSystemIO("Power Meter Total Energy", 1, 1, i, 0, 0, 0);
  Format(s, ',', Energy:0:0);
  Append(DataString, s);
  Cost :=   GetRealIBSystemIO("Power Meter Total Energy", 1, 1, i, 2, 0, 0);
  Format(s, ',', Cost:0:2);
  Append(DataString, s);
  WriteLn(File1, DataString);
end;

CloseFile(file1);
DisableModule("Power Export");

This can be used as follows:
1. Refresh the project from the unit (in my case, a Colour C-Touch)
2. Click the button which makes the module run
3. Close PICED (there seems to be a problem with it releasing the file handle properly)
4. Open the Power Data.csv file in Excel

The above could also be used for hourly data (over last 48 hours) if required. The only thing it will not do is show you the tariff, as it is not recorded.

 

Hi Darren,

Will play around with both methods and modify for the solar to make it net the grid input vs solar output and that should give him what he wants.

Appreciate the code, and I am sure everyone else reading does too.

All the best,

Brad