Using the HISTORIAN​.PERIODIC Function

The HISTORIAN.PERIODIC function retrieves a periodic numeric summary for a specified list of tags.

Syntax

HISTORIAN.PERIODIC(TagList, StartTime, EndTime, Interval, Details, Direction, SortOrder)

• TagList - Required. The range of cells containing the list of tag names you want to retrieve information about.

• StartTime - Required. The date and time to start the data summary, expressed as a serial date number.

• EndTime - Required. The date and time to end the data summary, expressed as a serial date number.

• Interval - Required. The time interval over which values will be retrieved, expressed as a number of days. For example, 1/24 represents an interval of one hour, while 1/4 represents an interval of 6 hours.

• Details - Required. A comma-separated list of additional properties to retrieve for the selected tags. The following properties are supported: StartDateTime, EndDateTime, OpcQuality, and PercentGood.

• Direction - Optional. If TRUE, the tags are listed in rows. If FALSE, the tags are listed in columns. If no value is specified, the default value of TRUE (rows) is used.

• SortOrder - Optional. If TRUE, data is sorted in ascending order by time. Otherwise, the data is sorted in descending order by time. If no value is specified, the default value of TRUE (ascending) is used.

• UOMAddress - Optional. A cell reference containing the name of an engineering unit. When tag data is retrieved, it is converted to display in this unit if an appropriate conversion is defined in the Engineering Unit Catalog. Otherwise, it displays in the tag's default engineering unit.

Example

Recreating the simple report from the previous example, we select the Function option instead of the Static option. This results in a formula being inserted into the spreadsheet, instead of static data. There is a brief pause while communicating with the server, then the retrieved data is displayed.

The formula generated using this example is:

=HISTORIAN.PERIODIC(Sheet1!A2:A4, NOW() - (1), NOW(), 1/24, "Average,StartDateTime", TRUE, TRUE, Sheet1!A6)

You can modify the formula, and the results refresh automatically to reflect your changes. You can also use the function independently from the add-in. For example, the following formula is entered into cell B20:

=HISTORIAN.PERIODIC(Sheet1!A2:A3, NOW() - (1), NOW(), 1/8, "Average,StartDateTime", TRUE, FALSE)

The resulting report is similar to the first, but one of the tags is omitted because of the modified range, the time interval changes from hourly (1/24) to every 3 hours (1/8), the data is sorted in descending order, and, because there is no conversion unit specified, the data displays in the default unit for each tag.

If your version of Excel does not support dynamic arrays, a message similar to the following displays:

In this case, although you can still use the custom function, the size of the output range cannot be refreshed automatically. For example, if you run the function with a date range of 1 day (returning 24 rows or columns) and want to change the date range to 2 days (returning 48 rows or columns), the output range cannot be automatically resized to accommodate the additional results. You must manually selecting the new output range, edit the function parameters, and then press Ctrl-Shift-Enter.