COMTRADE parsing VBA (Excel) code

[Dave Loucks]

New Features (V0.16h alpha) (31-OCT-17 (latest version)

• various bug clean up and stability improvements
  

Download this latest version at: https://pps2.com/cei/cei_16h.xlsb

Note: This Excel file contains macros and Windows will block execution of those macros by default.  View this Microsoft article on how to unblock this feature so you can run the spreadsheet.

You will also be prompted to "update links" when first opening.  Click Don't Update
Next click on the Graphing tab to view an example COMTRADE file that was imported.  Click the Zoom+ or Zoom- to zoom the waveform.  Press < Shift or Shift > to pan through the waveform.



New Features (V0.16g alpha) (22-MAY-17 (latest version)

• various bug clean up and stability improvements
  

Download this latest version at: http://loucks.ws/v/ct/cei_16g.xlsb

Or as always you can check and download the latest from within the program:




New Features (V0.16d alpha) (22-FEB-17)

• ability to perform math on data
  You can select up to 4 "math  pens" that are computed values based on +, -, *, / between any other data (including previously calculated values, so the equations can chain to build more complex y=(mx+b)/c kind of equations
  

Known bugs:

• Performing math on large data blocks
  When doing math on multiple chained files, the calculated values are only visible when zoomed in to the high resolution data.  When zoomed out, no math trends are shown for long multiple-file captures.
  

Here's a video tutorial on how to use this math functionality.


Download this latest version at: http://loucks.ws/v/ct/cei_16d.xlsb

Or as always you can check and download the latest from within the program:



New Features (V0.15d alpha) (3-FEB-17)

• rms calculation
  You can now select from 1 to 5 trends from the COMTRADE file over which you can compute an rms calculation and plot as an overlay to the regular waveform data
  
  
• Ability to display Fourier plots
  Using the 'aux' data tab, you can now drop in data where rather than having the x-axis be a time value, the x-axis can be frequency
  
  
• Logarithmic x-axis
  Added to support the ability to display Fourier series data

Here's a video tutorial on how to use the rms functionality.


Download this latest version at: http://loucks.ws/v/ct/cei_15d.xlsb

New Features (V0.15 alpha)

• Proxy server support
  Now includes ability to connect to meter via FTP routed through a proxy server.  Just check the checkbox and enter your proxy server address, colon, then port
  
• FTP download bug fixes
• FFT graphing bug fixes

Download this latest version at: http://loucks.ws/v/ct/cei_15.xlsb

New Features (V0.14n alpha)

• New FFT features

Download this latest version at: http://loucks.ws/v/ct/cei_14n.xlsb

Watch a video screencast video on how to use these FFT features:
https://pps2.com/v/s/1/ceifft.php

Did you have trouble getting the FFT function to run inside Excel?  You might have either not installed the Analysis Toolpak, or you might not have enabled the functionality within Excel.  Check out this video for an explanation:
https://pps2.com/v/s/1/atp.php

New Features (V0.14g alpha)

• Fixed compatibility issues

Download this latest version at: http://loucks.ws/v/ct/cei14g.xlsb

New Features (V0.13n alpha)

• Pan and Zoom operate much faster (~10x)
• FFT features re-enabled (when I began the process of speeding up the code, I broke the FFT functionality.  Now fixed.)
• Lissajous pattern plotting re-enabled (it too was broken when I wrote the faster code.  Now fixed.)

The new PX meter firmware captures 18 seconds (2.5 seconds pre-trigger and 15.5 post-trigger).

Download this latest version at: http://loucks.ws/v/ct/cei13n.xlsb

Here's the screencast explaining how to use the new features:
http://pps2.com/v/1/v013n.php

Features (V0.13k alpha)

• COMTRADE import works 15x faster

Download this latest version at: http://loucks.ws/v/ct/cei13k.xlsb

Previous Version (V0.13a alpha)
(bug fixed that caused "Single" button download to fail)

• Able to import and append multiple sequential COMTRADE files

The latest Power Xpert meters allow sequential file captures to retrieve longer high speed captures.  This tool allows you to select those files and have them appended one to another.  Excel 2010 (tested on) limits spreadsheets to no more than 1 million rows, so the program will crash if attempting to import more than that.  Practically speaking, Excel starts to bog down, so you may find it more useful to only import the portions of the waveform files that contain information you find useful.  That might mean importing them all, noting which file or files (based on time stamp) contains the information of interest and repeat the import with just those files.

Also, since this program was written without requiring add-ins (which require admin rights to install), I can only use VBA to parse the files.  Large files can require minutes to process.  Likely a future version will include an add-in (compiled parsing code) to speed through the import for those users who have admin rights on their computer.

Here's the screencast that explains how to use these latest features:
http://pps2.com/v/1/v13.php

Download this latest version at: http://loucks.ws/v/ct/cei13a.xlsb

Previous Version (V0.12) Features

• Ability to plot pens to a third scaling axis
• Ability to plot only a portion of the COMTRADE sequence (useful when viewing Lissajous patterns)
• Ability to import any time sequence data and plot it (no longer must be a COMTRADE file)
• Ability to plot mathematical relationships (e.g. can plot Z = V/I, or anything else using Excel's built-in math functionality)

Here's the screencast that explains how to use these latest features:
http://pps2.com/v/1/v12.php

Download this latest version at: http://loucks.ws/v/ct/cei12a.xlsb
(Note: fixed bug in the 0.12a version that caused very slow operation in previous versions.)

Previous Version (V0.11) Features

• Ability to plot to secondary axis
• Ability to measure phase shift between plotted values

Here's the screencast that explains how to use these latest features:
http://pps2.com/v/1/cei011.php

Download previous version V0.11 at: http://loucks.ws/v/ct/cei11.xlsb

Previous Version (V0.1) Features

• Graphing feature added

Watch the screencast that explains the features added for this version: http://pps2.com/v/1/cei01_00.php

Download the previous version V0.1 at: http://loucks.ws/v/ct/cei10.xlsm



If you are interested in the VBA code that performs these functions I demonstrate how to use VBA to:

• Zoom and pan around an Excel plot
• Select points from the plot by clicking on them, then perform math on the selected points
• Select and deselect multiple y-axis values (any of the trended values in the COMTRADE file) and deal with the legend properly
• Allow selecting any of the trended values in the COMTRADE file as the x-axis.  This allows plotting Lissajous x-y plots as well as parameter-vs-time plots

V 0.09 version is now available:
loucks.ws/v/ct/cei9_04.xlsm... or just click the check version link inside the earlier version to download the new version for free.

Flash version of screencast tutorial on how to use (I'll re-record this version's screencast in friendlier media format eventually!): http://loucks.ws/v/ct/update.html

Sample COMTRADE files to test out system:
https://app.box.com/s/yyi3ws6pk16ryhrhziw5uyny2d9y66af



Changes:

• Parsing of COMTRADE file >3x faster
• Supports opening COMTRADE files from either local or FTP sites
• Includes FFT analysis (requires free Microsoft Analysis Toolkit to be installed)
  Example included on how to calculate FFT of ground current (IG).  Note: Microsoft's tool kit limits FFT analysis to only 4096 points.  Over a first generation PX 4/6/8K (15360 samples/sec) this works out to 3.75 Hz resolution.  On a second generation PXM (30720 samples/sec) the same 4096 points halves the resolution to 7.5 Hz.
  
  The program scans the available data and provides a convenient pull-down menu to select which value to perform the FFT analysis over.  A second pull-down menu allows selecting which group of 4096 points to scan.  Press the Solve button to compute Fourier coefficients.
  
  
  FFT data will be shown in the table:
  
  
  Alternatively, FFT data can be viewed graphically by navigating to the FFT Graph tab:
  
  The blue dots represent the calculated FFT coefficients over the first 4096 samples, the red line, the second 4096 samples and the green dots the third.
  

Waveform capture files from power meters, circuit breakers, reclosers and other IEDs (Intelligent Electronic Devices, in the utility vernacular) can provide valuable diagnostic information.  Use this application to open a waveform file stored in the IEEE COMTRADE format and load the data found into an Excel spreadsheet.  From Excel it is then a simple matter to create graphs, charts and other reports of the COMTRADE data.

I created a screencast that shows how to use it: loucks.ws/v/ct

You can download the Excel spreadsheet here: loucks.ws/v/ct/cei7b.xlsm

The VBA code is opened by clicking on Developer, then Visual Basic:


Once open, navigate through the VBA IDE to review my code.