Use 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 data is listed in rows. If FALSE, the data is 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. Otherwise, the data is sorted in descending order. If no value is specified, the default value of TRUE (ascending) is used.

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, TODAY() - (0), NOW(), 1/24, "Average,Count,StartDateTime", TRUE, TRUE)

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, TODAY() - (0), 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 Count property is omitted, and the data is sorted in descending order.

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 output cannot be refreshed automatically. You must manually trigger the update by selecting the output range, and pressing Ctrl-Shift-Enter.