History

The History formula in its simplest form is this =<instance>.READTAGHISTORY("<source::tag>") and it will return RAW data points within the last two hours.

Parameters

The History formula contains in total 21 parameters, where only the first one is required and all the others are optional and when omitted it will set the default values to some of them when the formula is inserted to an Excel cell.

The complete formula looks as follows:

=<instance>.READTAGHISTORY(
    Tags,
    StartTime,
    EndTime,
    NumValues,
    Resolution,
    InterpolationMethod,
    AggregateType
    StatusFilter,
    Bounding,
    Alignment,
    Edges,
    Elements,
    CrossTabular,
    ShowHeader,
    ShowTagName,
    ShowStatus,
    StatusAsString,
    ShowTimeStamp,
    NewestFirst,
    Orientation,
    EmptyValue
)

All parameters can be bounded to cell values.

Tags <List> Required

Tags contains a comma separated list of tags encapsulated with double quotes.

"<source::tag1>,<source::tag2>,<source::tag3,...>"

StartTime <Time>

Can be set by either using OPC-format or date-format.
Default: NOW-2H

EndTime <Time>

Can be set by either using OPC-format or date-format.
Default: NOW

NumValues <Number>

Will limit the amount of values to be fetched.
Default: <empty>

Resolution <String>

Determines the resolution of the data set.
Default: RAW

InterpolationMethod <String>

How Interpolation of data is done. May be Weighted, Extright, Extleft, Extcenter, Interpolated, Exacttime or Aggregate. If missing, Weighted is used. If Resolution = RAW, it is ignored. If Aggregate, see Aggregate Type parameter below. Exacttime only return data if RAW point exists at exact interval time.
Default: <empty>

AggregateType <List>

Aggregate Type contains a comma separated list that should only be set if the Interpolation method (see Interpolation method parameter above) is set to Aggregate. Some Aggregate Types are mutually exclusive (meaning that not all Aggregate Types can be combined).

The following example will return the Maximum, Minimum, Average and Count values for a given period:

"Maximum,Minimum,Average,Count"

Default: <empty>

All Aggregate Types:

Actualtime, Average, Count, Delta, End, First, Interpolative, Interval, Last, Maximum, Maximumactualtime, Minimum, Minimumactualtime, Other, Pointaverage, Pointmaximum, Pointminimum, Pointtotal, Pseudoaggregate, Range, Regconst, Regdev, Regslope, Start, Stdev, Step, Steptimeaverage, Steptimetotal, Time, Timeaverage, Timetotal, Total, Totalx2, Variance

StatusFilter <List>

Status Filter contains a comma separated list of statuses, encapsulated with double quotes, to filter out values that DO NOT have the specified statuses.

The following example will return all values the DO NOT contain the statuses LOA, HIA, LLA and HHA:

"LOA,HIA,LLA,HHA"

Default: <empty>

All statuses:

OK, ROC, LOA, HIA, LLA, HHA, LLL, MNL, OFS, INV, BAD, IOF, INF, LOR, AVG, SUS, MXT, RND, INC

Bounding <Boolean>

If set to true, fetch one extra value beyond StartTime and EndTime if they exist.
Default: TRUE

Alignment <“RIGHT / “LEFT” / “CENTER”>

Timestamp alignment for returned intervals when Resolution not set to RAW. May be RIGHT (newest), LEFT (oldest) or CENTER.
Default: RIGHT

Edges <“RIGHT / “LEFT” / “BOTH”>

Specifies which values to include if they lie on the sample period edge exactly. May be RIGHT (newest), LEFT (oldest) or BOTH.
Default: RIGHT

CrossTabular <Boolean>

Cross Tabular will pivot the data to show tags in columns and the values in rows accordingly to the timestamps.
Default: FALSE

ShowHeader <Boolean>

Determines if the headers should be shown or not in the spreadsheet.
Default: TRUE

ShowTagName <Boolean>

Determines whether the tag name should be shown or not.
Default: TRUE

ShowStatus <Boolean>

Determines whether the status should be shown or not.
Default: TRUE

StatusAsString <Boolean>

Determines whether the status should be shown as a string or as a bit value.
Default: TRUE

ShowTimeStamp <Boolean>

Determines whether the timestamp should be shown or not.
Default: TRUE

NewestFirst <Boolean>

Determines the sort order of the data set by date when multiple tags are included.
Default: TRUE

Orientation <“VERTICAL” / “HORIZONTAL”>

Determines how the data set should be presented in the spreadsheet
Default: HORIZONTAL

EmptyValue <“N/A” / “EMPTY”>

Determines if an empty or missing value should be shown as “N/A” or as an empty cell.
Default: N/A