Users Guide Energy Charting and Metrics Tool (ECAM)
Version 1.0
PECI
Portland Energy Conservation Inc. 1400 SW 5th Avenue, Suite 700 Portland, Oregon 97201 Voice: 503-595-4441 Fax: 503-295-0820 Web: www.peci.org
Disclaimer
This information and the associated software is provided as is without warranty of any kind. PECI disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. In no event shall PECI or its employees, officers, or directors be liable for any damages whatsoever including direct, indirect, incidental, consequential, loss of business profits or special damages, even if PECI or its employees, officers, or directors have been advised of the possibility of such damages. Some states do not allow the exclusion or limitation of liability for consequential or incidental damages so the foregoing limitation may not apply. PECI does not warrant that the software will meet your requirements, or that the operation of the software will be uninterrupted or error-free, or that any defects can be corrected. PECI does not warrant or make any representations regarding the use or the results of the use of the software or related documentation in terms of their correctness, accuracy, reliability, or otherwise. No oral or written information or advice given by PECI or its authorized representatives shall create a warranty or in any way increase the scope of this warranty. Should the software prove defective, you, and you alone, shall assume the entire cost associated with all necessary servicing, repair or correction.
Users 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 metrics for the points selected by the user.
The users 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 tools 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 www.utonline.org. 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 common location, such as Documents and Settings. It can, 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 called ECAM in the Excel Toolbar.
2/12/2008
Energy Charting and Metrics Tool Users Guide
Page 1 of 17
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)
2/12/2008
Energy Charting and Metrics Tool Users Guide
Page 2 of 17
The first 4 items in the menu should be used in order. Here is a brief description of their function: Select DataSelect the data range (range of cells) to be included in the analysis, using either the mouse or the keyboard. Definition of PointsMap (correlate) the point names in the users data to standard point names recognized by the tool. Create SchedulesEnter the operating schedule applicable to the building or equipment, using the input forms included. Input Dates for Comparison of Pre and PostIf 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 58 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 tools 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. Dont 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).
2/12/2008
Energy Charting and Metrics Tool Users Guide
Page 3 of 17
Step 1: Choose "Select Data" from the ECAM menu
Step 2: Select the correct time stamp format
2/12/2008
Energy Charting and Metrics Tool Users Guide
Page 4 of 17
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.
2/12/2008
Energy Charting and Metrics Tool Users Guide
Page 5 of 17
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 users 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.
2/12/2008
Energy Charting and Metrics Tool Users Guide
Page 6 of 17
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.
2/12/2008
Energy Charting and Metrics Tool Users Guide
Page 7 of 17
Step 3: Define map points
The form above consists of a list of the users 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 is not 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 .
2/12/2008
Energy Charting and Metrics Tool Users Guide
Page 8 of 17
Table 1: Automatically created points
Generated Point Point type Watts per square foot (W_perSF) Equipment status normalized status
Mapped Points Required Any kW Any kW
Parameters
Only if a status point is not available >3% of max= On <=3% of max= Off Only if a status point and kW are not available >3% of max= On <=3% of max= Off
Equipment status
status
Any Amps
CFM per square normalized foot (cfm_perSF) Chiller Cooling capacity in tons (ChillerTons) Chilled Water Cooling capacity in tons (SecChWTons) kW_perTon calculated
Any CFM Pmp_ChW_Evap_gpm, Chlr_ChW_TempIn, Chlr_ChW_TempOut Pmp-ChW_Sec_gpm, ChW_TempIn, ChW_TempOut Any_kW, PmpChW_Sec_gpm, ChW_TempIn, ChW_TempOut Pmp_ChW_Sec_gpm, ChW_TempIn, ChW_TempOut uses SecChWTons
calculated
normalized
gpm_perTon
normalized
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 24-hour operation schedule, select 12 AM as the start time, and 11:59 PM 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.
2/12/2008
Energy Charting and Metrics Tool Users Guide
Page 9 of 17
Step 1: Choose "Create Schedules" from the ECAM menu
Step 2: Input Schedule information
Tab 1 of 3: Day Schedules
2/12/2008
Energy Charting and Metrics Tool Users Guide
Page 10 of 17
Tab 2 of 3: Week Schedules
Tab 3 of 3: Annual Schedule
2/12/2008
Energy Charting and Metrics Tool Users Guide
Page 11 of 17
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 be used 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/12/2008
Energy Charting and Metrics Tool Users Guide
Page 12 of 17
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 update as 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).
2/12/2008 Energy Charting and Metrics Tool Users Guide Page 13 of 17
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 become confusing, 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 linesthe 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 Date Range (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.
2/12/2008
Energy Charting and Metrics Tool Users Guide
Page 14 of 17
3.3.6. Create 3d Load Profile
Load Profile by Day must be completed first. This item will convert a months 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 months 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 months 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 Profile Calendar 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. Dont 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.
2/12/2008
Energy Charting and Metrics Tool Users Guide
Page 15 of 17
3.4.2. Scatter Chart by Date Range (Pre/Post)
This creates a scatter chart with three series: one series with data before the energy project started, one series with the data after the project was completed, and one series representing operation during the project.
4.
Known Issues and Reminders
4.1. Issues In defining the points, when first opening the form, the user must sometimes deselect and then re-select a choice to get the lists to filter. Input schedules are remembered by the application. The schedules, however, dont show up in the Input Schedule Information form if the workbook is saved and re-opened. The toolbar menu disappears if a chart is selected. Click on the worksheet, and the menu will reappear. Some of the filter drop-downs for metrics pages and PivotTables dont make complete sense for certain procedures. For example, a typical metrics table shows the average value of the selected parameter(s) for the following categories: All Periods, Weekdays, Saturdays, Sundays, Occupied, and Unoccupied. The filter selections, however, include Daytype, Weekday, and Occupancy. Use of these selections will just result in an error value for the categories that filter to an empty set when using the drop-down (e.g., selecting a Weekday of Wednesday will result in N.A. values for Saturday and Sunday). For most metrics and charts, number of digits displayed is not adjusted for the number of significant digits. Reminders The following list summarizes known issues of this tool:
4.2.
One important note regarding the automated charting is that the user should not put anything in cells directly below the PivotTables. The name should be changed for any tool-created worksheets that you wish to save, prior to re-creating the same metric or chart. Otherwise work may be lost, since a new worksheet will overwrite any prior worksheet with the same name.
2/12/2008
Energy Charting and Metrics Tool Users Guide
Page 16 of 17
Acknowledgements
Funders Northwest Energy Efficiency Alliance California Energy Commission, Public Interest Energy Research (PIER) Buildings Program New Buildings Institute Project Team Lead Developer: Bill Koran Project Support: Hannah Friedman, Marci Shuman, Kathy Hile, Ryan Fedie Fall 2007 Pilot Participants Paul Gregory Bill Michael Matt David Steven Brian Sachin Ham Cmar Gnerre Kuk Denny Jump Long Smith Jamadagni Target Interval Data Systems Interval Data Systems Sieben Energy Associates QuEST QuEST Southern California Edison TRC Strategic Building Solutions
2/12/2008
Energy Charting and Metrics Tool Users Guide
Page 17 of 17
