User’s Guide
Energy Charting and Metrics Tool (ECAM)

Version 1.0

P│E│C│I

Portland Energy Conservation Inc.

1400 SW 5th Avenue, Suite 700

Portland, Oregon97201

Voice: 503-595-4441

Fax: 503-295-0820

Web:

2/12/2008Energy Charting and Metrics Tool User’s Guide Page 1 of 17

User’s Guide
Energy Charting and Metrics Tool (ECAM)

Version 1.0

Introduction

This tool for energy charting and metrics is intended to facilitate the examiniation of energy information from buildings, reducing the time spent analyzing trend and utility meter data.. In addition to being easy-to-use, this tool is also flexible. Key features include the following:

  • Data processing to attach schedule and day-type information to time-series data;
  • Filtering by day-type, occupancy schedule, binned weather data, month/year, pre/post, etc;
  • Normalization of data based on user-entered information;
  • Creation of standard charts for the points selected by the user; and
  • Calculation of normalized metricsfor thepoints selected by the user.

The user’s original data is not modified in the process of using the tool, but is copied into a new workbook automatically. The tool makes extensive use of Excel PivotTables to facilitate summarization and filtering of the data. It goes beyond normal PivotTables and PivotCharts, however, by automating the creation of scatter charts based on PivotTable data.

This document describes the tool’s general functions and features.

1.Quick Start

This tool was developed using Excel 2003 and has not been tested on earlier or later versions. It should work on versions as early as Excel 2000.

The system requires continuous, uniform interval data. Change-of-value data, or data with different parameters stored at different time intervals, must be pre-processed. One tool designed to assist and automate such pre-processing is the Universal Translator, available at

1.1.Installation

The energy charting and metrics tool is installed as an Excel Add-In. Save the tool file in your chosen location. Please note that Microsoft Add-Ins are installed, by default, in a commonlocation, such as Documents and Settings. Itcan, however, be saved to any location.

To install the application, open Excel and go to Tools, Add-Ins, and Browse to the location where the file was saved. Select the filename, and click OK. “PECI Metrics” will be in the list of Add-Ins. There will also be a Menu calledECAM in the Excel Toolbar.

1.2.Using the Tool to Create Metrics and Charts

Here are the top-level items in the tool menu:

Steps:

  1. Select data from existing spreadsheet (required)
  2. Define points (optional)
  3. Create schedules (optional)
  4. Input dates for comparison of pre and post (optional)

The first 4 items in the menu should be used in order. Here is a brief description of their function:

Select Data—Select the data range (range of cells) to be included in the analysis, using either the mouse or the keyboard.

Definition of Points—Map (correlate) the point names in the user’s data to standard point names recognized by the tool.

Create Schedules—Enter the operating schedule applicable to the building or equipment, using the input forms included.

Input Dates for Comparison of Pre and Post—If there is an energy project to be evaluated, input the date when the energy project started and the date it was completed.

Everything after “Select Data” is optional, but issues may arise depending upon what subsequent menu item(s) are used. For example, if the user does not enter a schedule, but does create metrics, then the fields for metrics that are dependent upon occupancy will show “NA”. Similarly, if data for comparison of pre and post is not input before trying to create a load profile by date range the chart will only show a single line with a series name of “(blank).”

When using the application to create metrics and charts, the workbook created by the tool must be the active (visible) workbook. Using the metrics and charts menu items (steps 5-8 above) will add new worksheets to the active workbook. Repeated use of the same menu items will overwrite prior work, since the worksheet names are not changed. to avoid losing work, the user should change the names of any tool-created worksheets that you wish to save, prior to recreating a metric or chart.This is especially important if any new formulas or customization has been added.

1.3.Tool Notes

Only the tool’s VBA code is protected.Therefore, all Excelformatting and other customization options should be available.

Scatter charts require that the point name to be used for the independent value (to be placed on the X-axis) be selected first. Don’t drag the mouse or use the Shift key to select subsequent point names. Use the Ctrl key to select the second and subsequent point names for the dependent values.

Important: Do not enter any data or information in the cells directly below the PivotTables.

2.Menu Items for Preprocessing of Data

2.1.Select Data

This menu item asks the user to select the data to be processed. The data must be continuous (i.e., there should not be any completely blank rows or columns).

Step 1: Choose "Select Data" from the ECAM menu

Step 2: Select the correct time stamp format

Step 3: Select the range of cells that contain the data

The data must be continuous (i.e., there should not be any completely blank rows or columns). To select data:

  • When prompted, select the first (upper-left) cell in the data.
  • Hold down the Ctrl and Shift keys, and hit the right arrow and then the left arrow. This should select all continuous data.
  • Click OK.

If there are timestamps or data that need to excluded, then it may be easier to select a subset of data by using the mouse. Select the first cell of desired data, scroll until the last cell of desired data is visible, hold down the Shift key and select the last cell, and click OK. Of course, it is also possible to manually type in the data range.

Step 4: Select whether ambient temperature data is included.

Note any cell within the column containing the ambient temperature data can be selected.

Step 5: Processed data created in new workbook

2.2.Definition of Points

This menu provides forms to input the building area and to “map” the user’s point names to the point names recognized by the program.This mapping function enables normalizations, calculated points, and certain metrics and charts to be created automatically.

Step 1: Choose "Definition of Points" from the ECAM menu

Step 2: Input the building area and the voltage for 3-phase equipment

Note that the building area is used to normalize the data and metrics by square foot.

Step 3: Define “map” points

The form above consists of a list of the user’s point names; an adjacent area where the mapped tool point names are listed; two lists (“Subsystems” and “Subsystem Components”) that are used to filter the possible “Component Measurement” choices; and the list of “Component Measurements.”

Sometimes, when first opening the form, the user must un-select and then re-select a choice to get the lists to filter.

The tool point names are based on the naming convention in A Specifications Guide for Performance Monitoring Systems.

Below is a more compete list of mapped points. Note that the outside air temperature point is mapped via the AHU subsystem as shown below. It isnot crucial to map the outside air temperature point as no automatically-generated points currently require the outside air temperature point.

After the points are defined, some points will be created and added to the points list automatically and their values stored on the data sheet. The automatically created points allow for certain metrics and charts to be created. For example, the Watts per square foot (W_perSF) point allows for Multiple Points Watts per SF Metrics to be created.The required mapped points needed to create the automatically created points are defined in Table 1 .

Table 1: Automatically created points

Generated Point / Point type / Mapped Points Required / Parameters
Watts per square foot (W_perSF) / normalized / Any kW
Equipment status / status / Any kW / Only if a status point is not available
>3% of max= “On”
<=3% of max= “Off”
Equipment status / status / Any Amps / Only if a status point and kW are not available
>3% of max= “On”
<=3% of max= “Off”
CFM per square foot (cfm_perSF) / normalized / Any CFM
Chiller Cooling capacity in tons(ChillerTons) / calculated / Pmp_ChW_Evap_gpm, Chlr_ChW_TempIn, Chlr_ChW_TempOut
Chilled Water Cooling capacity in tons (SecChWTons) / calculated / Pmp-ChW_Sec_gpm, ChW_TempIn, ChW_TempOut
kW_perTon / normalized / Any_kW, Pmp-ChW_Sec_gpm, ChW_TempIn, ChW_TempOut / uses SecChWTons
gpm_perTon / normalized / Pmp_ChW_Sec_gpm, ChW_TempIn, ChW_TempOut / uses SecChWTons

2.3.Create Schedules

Enter the operating schedule applicable to the building or equipment, using the input forms included. If a schedule is not input, then that daytype will be assumed to be occupied “Occ.”To create a 24hour operation schedule, select 12AM as the start time, and 11:59PM as the stop time.

Only one schedule type is available.The user must choose whether it represents occupancy or equipment operation based on his or her analytical needs. Tool designers did, however, consider offering two separate schedule types so that the user could create a schedule representing occupancy and another representing equipment operation.

Step 1: Choose "Create Schedules" from the ECAM menu

Step 2: Input Schedule information

Tab 1 of 3: Day Schedules

Tab 2 of 3: Week Schedules

Tab 3 of 3: Annual Schedule

Completing tabs one through three will polulate the data for the “DaySchedule” and “Occupancy” columns

2.4.Input Dates for Comparison of Pre and Post

This menu item will prompt you for the input of two dates: the date when an energy project was started, and the date when it was completed.An additional data field will be created that can beused for categorization and filtering of the data.

Step 1: Choose "Input Dates for Comparison of Pre and Post" from the ECAM menu

Step 2: Enter the date when an energy project was started

Step 3: Enter the date when an energy project was completed

2.5.Utilities Menu

2.5.1.Convert Table format to ECAM List format

This menu item is intended to be used if the original data is not in a list format. This function trasposes a dataset in matrix form, with dates in a column and times along a row, to a list format with time stamps in a column and data in the subsequent columns. If the data is in this format this is first step that needs to be done, prior even to selecting the data.

2.5.2.Create Bin Data from Temperatures

This menu item is intended to be used if temperature data was unavailable at the onset, but was then added at a later date.After the temperature data has been added, the “Define Data by System, Equipment, and Measurement”form will open so that it can be defined and properly recognized.

3.Menu Items to Create Metrics or Charts

When creating metrics or charts, select just the point name(s) to be included; do not select the data.

Each metric and chart type has a uniquely named worksheet which will be created the first time a metric or chart type is selected. The exceptions to this are the “Create 3d Load Profile” and “Create Energy Colors (surface chart)” chart types. These require that an existing “Load Profile by Day” worksheet must be the active worksheet to create these chart types.

Once a metric or chart worksheet has been created, reselecting the metric or chart from the menu will recreate the chart , overwriting the existing worksheet. If you wish to preserve the metrics and charts on the current worksheet, the worksheet must be renamed before reselecting the metric or chart from the menu.

All metrics and charts will automatically updateas their pivot table fields are changed except for the “Load Profile Calendar” chart which needs to be reselected from the menu in order to be updated.

The charts are created with a number of default page fields selected. You can customize the charts by modifying the selections in the dropdown of the page fields to filter the entire PivotTable and PivotChart to display data for the selected item. .

3.1.Create Per Sq. Foot Metrics

Multiple Points Watts per SF Metrics

This menu item will create metrics for all points that can be converted to a Watts per square foot (W/sf). Any points that were a measured kW will be converted to W/sf after the user enters the building size as part of the point definition process. The metrics that are created are average W/sf by Daytype (Weekday, Saturday, Sunday) and occupancy (Occ and Unocc).

3.2.Create Other Metrics

General Metrics

This menu item allows the user to pick the points they want to summarize as metrics. The selected points will be summarized as average by Daytype and Occupancy

3.3.Load Profile Charts

Most of these charts should be self-explanatory. Charts that are “Load Profile by …” will only show the “by…” portion (e.g. “Load Profile by Daytype”), if just a single point is selected.

When multiple points are selected, the chart may becomeconfusing, and the “by…” field is placed in the filter dropdown list of the source PivotTable.For example, if two points are selected for a “Load Profile by Daytype,” then the chart will not show the data by Daytype. Instead, the two points will each be a separate line on the chart, and the Daytype field will be be moved to the PivotTable filter field (PageField) area. The Daytype field will be set to “Weekday.”

3.3.1.Load Profile by Daytype

This creates a line chart with four lines—the average load profiles for each of four Daytypes: Weekday, Saturday, Sunday, and Holiday.

3.3.2.Load Profile by Month-Year

This creates a line chart showing the average load profile for each month in the data set. Months with incomplete data are included.

3.3.3.Load Profile by DateRange (Pre/Post)

This is a line chart with up to three lines, including: one line for the average load profile before the energy project started; one line with the average load profile after the project; and one line for the average load profile during the project.

3.3.4.Load Profile by Year

This is an average load profile for each year in the data set.

3.3.5.Load Profile by Day

This menu item requires a second step to get a meaningful chart. Selecting the item creates a line chart with 31 lines. Each line is the average for a day number (e.g., day number 10 is the 10th day of the month) for all the months in the data set. The next step is to select a particular Month or MonthYr using the PivotTable filter field (PageField) dropdowns.

Making this Month or MonthYr selection is a prerequisite for creating the last three types of Load Profiles.

3.3.6.Create 3d Load Profile

Load Profile by Day must be completed first. This item will convert a month’s worth of daily Load Profiles to a 3d chart, with the Weekdays and Weekends separated by color.

3.3.7.Create Energy Colors (surface chart)

As with the “Create 3d Load Profile” function, “Load Profile by Day” must be completed before using this menu item. . This function will convert a month’s worth of daily Load Profiles to a surface (contour) chart, with the values shown by color.

3.3.8.Load Profile Calendar

As with the two previous items, the “Load Profile by Day” must be completed before using this item. The “Load Profile Calendar” will convert a month’s worth of daily Load Profiles to a calendar format, with each day shown as a thumbnail chart on the calendar. To create a “Load Profile Calendar” a particular MonthYr must be selected from the PivotTable filter field (PageField) dropdown.

Note that a Load ProfileCalendar will not update to the proper day of the week if a different “MonthYr” is selected on the “Load Profile by Day” output worksheet (“ptLPchartDay”). The Calendar should be recreated if a different “MonthYr” is selected.

3.4.Scatter Charts

Scatter charts require that the point name to be used for the independent value (to be placed on the X-axis) be selected first. Don’t drag the mouse or use the Shift key to select subsequent point names. Use the Ctrl key to select the second and subsequent point names for the dependent values.

Note that the scatter charts include some aggregation (averaging) of the values for the independent variable. For example, if the independent variable is AHU2_TempOa, and there are 7 occurrences in the data set when the temperature is 77.3 ºF, only 1 instance of 77.3 ºF will show up in the chart, and the dependent variable value will be the average for the 7 occurrences.

Complete scatter charts without aggregation can be created using the raw data on Worksheet “Data.”

3.4.1.Scatter Chart by Occupancy

This creates a scatter chart with 2 series: one for the occupied times and one for the unoccupied times. Note that, depending upon how the schedule is defined, extra scatter will appear in whichever series includes the startup and shutdown time periods.

3.4.2.Scatter Chart by DateRange (Pre/Post)