Excel Add-in

The Factry Historian Excel Add-in enables you to retrieve time-series data from Factry Historian (from Measurements and Calculations) into Microsoft Excel directly.

Before you can use the Add-in, you will need to install and set up a connection to a Factry Historian instance.

Once installed and configured, the following functions become available. These functions are prefixed with FACTRY, e.g.:

=FACTRY.GET_VAL(arguments...)
=FACTRY.GET_SAMPLED_DATA_MEAN(arguments...)

Single value functions

These functions return a single value to the cell where the formula is entered.

GET_VAL

Description: Returns the value of the most recent datapoint before a given timestamp.

Arguments:

Name: Connection Description: The connection name configured in the Excel Add-in taskpane. Datatype: Text Example: Factry
Name: Database Description: The database name. Datatype: Text Example: historian
Name: Measurement Description: The measurement to query. Datatype: Text Example: JS-OPC-UA
Name: Timestamp Description: The latest timestamp allowed for the datapoint (exclusive). Datatype: Excel datetime Example: 29/06/2022 16:20:00
Name: [MaxAge] Description: The maximum age of the datapoint relative to the timestamp. Accepts hours, minutes, and seconds. Datatype: Text Example: 48h | 1h30m | 1m30s

GET_CALC_VAL_COUNT

Description: Returns the number of datapoints between a start and end time.

Arguments:

Name: Connection Description: The connection name configured in the Excel Add-in taskpane. Datatype: Text Example: Factry
Name: Database Description: The database name. Datatype: Text Example: historian
Name: Measurement Description: The measurement to query. Datatype: Text Example: JS-OPC-UA
Name: Start Description: The start time of the query (inclusive). Datatype: Excel datetime Example: 28/06/2022 16:20:00
Name: End Description: The end time of the query (exclusive). Datatype: Excel datetime Example: 29/06/2022 16:20:00

GET_CALC_VAL_INTEGRAL

Description: Returns the integral of the values between the start and end time in steps of a base period.

Arguments:

Name: Connection Description: The connection name configured in the Excel Add-in taskpane. Datatype: Text Example: Factry
Name: Database Description: The database name. Datatype: Text Example: historian
Name: Measurement Description: The measurement to query. Datatype: Text Example: JS-OPC-UA
Name: Start Description: The start time of the query (inclusive). Datatype: Excel datetime Example: 28/06/2022 16:20:00
Name: End Description: The end time of the query (exclusive). Datatype: Excel datetime Example: 29/06/2022 16:20:00
Name: BasePeriod Description: The unit of time to integrate over. Accepts hours, minutes, and seconds. Datatype: Text Example: 1h30m

GET_CALC_VAL_MEAN

Description: Returns the mean value between the start and end time.

Arguments:

Name: Connection Description: The connection name configured in the Excel Add-in taskpane. Datatype: Text Example: Factry
Name: Database Description: The database name. Datatype: Text Example: historian
Name: Measurement Description: The measurement to query. Datatype: Text Example: JS-OPC-UA
Name: Start Description: The start time of the query (inclusive). Datatype: Excel datetime Example: 28/06/2022 16:20:00
Name: End Description: The end time of the query (exclusive). Datatype: Excel datetime Example: 29/06/2022 16:20:00

GET_CALC_VAL_MEDIAN

Description: Returns the median value between the start and end time.

Arguments:

Name: Connection Description: The connection name configured in the Excel Add-in taskpane. Datatype: Text Example: Factry
Name: Database Description: The database name. Datatype: Text Example: historian
Name: Measurement Description: The measurement to query. Datatype: Text Example: JS-OPC-UA
Name: Start Description: The start time of the query (inclusive). Datatype: Excel datetime Example: 28/06/2022 16:20:00
Name: End Description: The end time of the query (exclusive). Datatype: Excel datetime Example: 29/06/2022 16:20:00

GET_CALC_VAL_MODE

Description: Returns the mode of the values between the start and end time.

Arguments:

Name: Connection Description: The connection name configured in the Excel Add-in taskpane. Datatype: Text Example: Factry
Name: Database Description: The database name. Datatype: Text Example: historian
Name: Measurement Description: The measurement to query. Datatype: Text Example: JS-OPC-UA
Name: Start Description: The start time of the query (inclusive). Datatype: Excel datetime Example: 28/06/2022 16:20:00
Name: End Description: The end time of the query (exclusive). Datatype: Excel datetime Example: 29/06/2022 16:20:00

GET_CALC_VAL_SPREAD

Description: Returns the spread (difference between max and min) of the values between the start and end time.

Arguments:

Name: Connection Description: The connection name configured in the Excel Add-in taskpane. Datatype: Text Example: Factry
Name: Database Description: The database name. Datatype: Text Example: historian
Name: Measurement Description: The measurement to query. Datatype: Text Example: JS-OPC-UA
Name: Start Description: The start time of the query (inclusive). Datatype: Excel datetime Example: 28/06/2022 16:20:00
Name: End Description: The end time of the query (exclusive). Datatype: Excel datetime Example: 29/06/2022 16:20:00

GET_CALC_VAL_STDDEV

Description: Returns the standard deviation of the values between the start and end time.

Arguments:

Name: Connection Description: The connection name configured in the Excel Add-in taskpane. Datatype: Text Example: Factry
Name: Database Description: The database name. Datatype: Text Example: historian
Name: Measurement Description: The measurement to query. Datatype: Text Example: JS-OPC-UA
Name: Start Description: The start time of the query (inclusive). Datatype: Excel datetime Example: 28/06/2022 16:20:00
Name: End Description: The end time of the query (exclusive). Datatype: Excel datetime Example: 29/06/2022 16:20:00

GET_CALC_VAL_SUM

Description: Returns the sum of the values between the start and end time.

Arguments:

Name: Connection Description: The connection name configured in the Excel Add-in taskpane. Datatype: Text Example: Factry
Name: Database Description: The database name. Datatype: Text Example: historian
Name: Measurement Description: The measurement to query. Datatype: Text Example: JS-OPC-UA
Name: Start Description: The start time of the query (inclusive). Datatype: Excel datetime Example: 28/06/2022 16:20:00
Name: End Description: The end time of the query (exclusive). Datatype: Excel datetime Example: 29/06/2022 16:20:00

GET_CALC_VAL_FIRST

Description: Returns the first value between the start and end time.

Arguments:

Name: Connection Description: The connection name configured in the Excel Add-in taskpane. Datatype: Text Example: Factry
Name: Database Description: The database name. Datatype: Text Example: historian
Name: Measurement Description: The measurement to query. Datatype: Text Example: JS-OPC-UA
Name: Start Description: The start time of the query (inclusive). Datatype: Excel datetime Example: 28/06/2022 16:20:00
Name: End Description: The end time of the query (exclusive). Datatype: Excel datetime Example: 29/06/2022 16:20:00

GET_CALC_VAL_LAST

Description: Returns the last value between the start and end time.

Arguments:

Name: Connection Description: The connection name configured in the Excel Add-in taskpane. Datatype: Text Example: Factry
Name: Database Description: The database name. Datatype: Text Example: historian
Name: Measurement Description: The measurement to query. Datatype: Text Example: JS-OPC-UA
Name: Start Description: The start time of the query (inclusive). Datatype: Excel datetime Example: 28/06/2022 16:20:00
Name: End Description: The end time of the query (exclusive). Datatype: Excel datetime Example: 29/06/2022 16:20:00

GET_CALC_VAL_MAX

Description: Returns the maximum value between the start and end time.

Arguments:

Name: Connection Description: The connection name configured in the Excel Add-in taskpane. Datatype: Text Example: Factry
Name: Database Description: The database name. Datatype: Text Example: historian
Name: Measurement Description: The measurement to query. Datatype: Text Example: JS-OPC-UA
Name: Start Description: The start time of the query (inclusive). Datatype: Excel datetime Example: 28/06/2022 16:20:00
Name: End Description: The end time of the query (exclusive). Datatype: Excel datetime Example: 29/06/2022 16:20:00

GET_CALC_VAL_MIN

Description: Returns the minimum value between the start and end time.

Arguments:

Name: Connection Description: The connection name configured in the Excel Add-in taskpane. Datatype: Text Example: Factry
Name: Database Description: The database name. Datatype: Text Example: historian
Name: Measurement Description: The measurement to query. Datatype: Text Example: JS-OPC-UA
Name: Start Description: The start time of the query (inclusive). Datatype: Excel datetime Example: 28/06/2022 16:20:00
Name: End Description: The end time of the query (exclusive). Datatype: Excel datetime Example: 29/06/2022 16:20:00

Multiple value functions

These functions return multiple values in a range of cells. The first column contains timestamps, formatted by default as dd/mm/yy hh:mm:ss. To change the format, apply a custom Excel cell format.

GET_RAW_DATA

Description: Returns timestamp–value pairs for all specified measurements between a start and end time.

Arguments:

Name: Connection Description: The connection name configured in the Excel Add-in taskpane. Datatype: Text Example: Factry
Name: Database Description: The database name. Datatype: Text Example: historian
Name: Measurements Description: The measurements to query. Datatype: Excel range | Text Example: A3:B4 | JS-OPC-UA
Name: Start Description: The start time of the query (inclusive). Datatype: Excel datetime Example: 28/06/2022 16:20:00
Name: End Description: The end time of the query (exclusive). Datatype: Excel datetime Example: 29/06/2022 16:20:00
Name: Limit Description: The maximum number of datapoints for each measurement. Use 0 for no limit. Datatype: Number Example: 10

GET_SAMPLED_DATA_COUNT

Description: Returns the number of datapoints for each measurement in every sampling interval between start and end time.

Arguments:

Name: Connection Description: The connection name configured in the Excel Add-in taskpane. Datatype: Text Example: Factry
Name: Database Description: The database name. Datatype: Text Example: historian
Name: Measurements Description: The measurements to query. Datatype: Excel range | Text Example: A3:B4 | JS-OPC-UA
Name: Start Description: The start time of the query (inclusive). Datatype: Excel datetime Example: 28/06/2022 16:20:00
Name: End Description: The end time of the query (exclusive). Datatype: Excel datetime Example: 29/06/2022 16:20:00
Name: SamplingInterval Description: The time interval for each sample. Accepts hours, minutes, and seconds. Datatype: Text Example: 1h30m
Name: Limit Description: The maximum number of datapoints for each measurement. Use 0 for no limit. Datatype: Number Example: 10
Name: [Fill] Description: The type of fill used. Options: none, null, previous, linear, 0. Default is null. Datatype: Text Example: linear

GET_SAMPLED_DATA_INTEGRAL

Description: Returns the integral over values in steps of a base period for all measurements in each sampling interval.

Arguments:

Name: Connection Description: The connection name configured in the Excel Add-in taskpane. Datatype: Text Example: Factry
Name: Database Description: The database name. Datatype: Text Example: historian
Name: Measurements Description: The measurements to query. Datatype: Excel range | Text Example: A3:B4 | JS-OPC-UA
Name: Start Description: The start time of the query (inclusive). Datatype: Excel datetime Example: 28/06/2022 16:20:00
Name: End Description: The end time of the query (exclusive). Datatype: Excel datetime Example: 29/06/2022 16:20:00
Name: SamplingInterval Description: The time interval for each sample. Accepts hours, minutes, and seconds. Datatype: Text Example: 1h30m
Name: Limit Description: The maximum number of datapoints for each measurement. Use 0 for no limit. Datatype: Number Example: 10
Name: BasePeriod Description: The unit of time to integrate over. Accepts hours, minutes, and seconds. Datatype: Text Example: 1h30m
Name: [Fill] Description: The type of fill used. Options: none, null, previous, linear, 0. Default is null. Datatype: Text Example: linear

GET_SAMPLED_DATA_MEAN

Description: Returns the mean value for all measurements in each sampling interval.

Arguments:

Name: Connection Description: The connection name configured in the Excel Add-in taskpane. Datatype: Text Example: Factry
Name: Database Description: The database name. Datatype: Text Example: historian
Name: Measurements Description: The measurements to query. Datatype: Excel range | Text Example: A3:B4 | JS-OPC-UA
Name: Start Description: The start time of the query (inclusive). Datatype: Excel datetime Example: 28/06/2022 16:20:00
Name: End Description: The end time of the query (exclusive). Datatype: Excel datetime Example: 29/06/2022 16:20:00
Name: SamplingInterval Description: The time interval for each sample. Accepts hours, minutes, and seconds. Datatype: Text Example: 1h30m
Name: Limit Description: The maximum number of datapoints for each measurement. Use 0 for no limit. Datatype: Number Example: 10
Name: [Fill] Description: The type of fill used. Options: none, null, previous, linear, 0. Default is null. Datatype: Text Example: linear

GET_SAMPLED_DATA_MEDIAN

Description: Returns the median value for all measurements in each sampling interval.

Arguments:

Name: Connection Description: The connection name configured in the Excel Add-in taskpane. Datatype: Text Example: Factry
Name: Database Description: The database name. Datatype: Text Example: historian
Name: Measurements Description: The measurements to query. Datatype: Excel range | Text Example: A3:B4 | JS-OPC-UA
Name: Start Description: The start time of the query (inclusive). Datatype: Excel datetime Example: 28/06/2022 16:20:00
Name: End Description: The end time of the query (exclusive). Datatype: Excel datetime Example: 29/06/2022 16:20:00
Name: SamplingInterval Description: The time interval for each sample. Accepts hours, minutes, and seconds. Datatype: Text Example: 1h30m
Name: Limit Description: The maximum number of datapoints for each measurement. Use 0 for no limit. Datatype: Number Example: 10
Name: [Fill] Description: The type of fill used. Options: none, null, previous, linear, 0. Default is null. Datatype: Text Example: linear

GET_SAMPLED_DATA_MODE

Description: Returns the mode for all measurements in each sampling interval.

Arguments:

Name: Connection Description: The connection name configured in the Excel Add-in taskpane. Datatype: Text Example: Factry
Name: Database Description: The database name. Datatype: Text Example: historian
Name: Measurements Description: The measurements to query. Datatype: Excel range | Text Example: A3:B4 | JS-OPC-UA
Name: Start Description: The start time of the query (inclusive). Datatype: Excel datetime Example: 28/06/2022 16:20:00
Name: End Description: The end time of the query (exclusive). Datatype: Excel datetime Example: 29/06/2022 16:20:00
Name: SamplingInterval Description: The time interval for each sample. Accepts hours, minutes, and seconds. Datatype: Text Example: 1h30m
Name: Limit Description: The maximum number of datapoints for each measurement. Use 0 for no limit. Datatype: Number Example: 10
Name: [Fill] Description: The type of fill used. Options: none, null, previous, linear, 0. Default is null. Datatype: Text Example: linear

GET_SAMPLED_DATA_SPREAD

Description: Returns the spread of values for all measurements in each sampling interval.

Arguments:

Name: Connection Description: The connection name configured in the Excel Add-in taskpane. Datatype: Text Example: Factry
Name: Database Description: The database name. Datatype: Text Example: historian
Name: Measurements Description: The measurements to query. Datatype: Excel range | Text Example: A3:B4 | JS-OPC-UA
Name: Start Description: The start time of the query (inclusive). Datatype: Excel datetime Example: 28/06/2022 16:20:00
Name: End Description: The end time of the query (exclusive). Datatype: Excel datetime Example: 29/06/2022 16:20:00
Name: SamplingInterval Description: The time interval for each sample. Accepts hours, minutes, and seconds. Datatype: Text Example: 1h30m
Name: Limit Description: The maximum number of datapoints for each measurement. Use 0 for no limit. Datatype: Number Example: 10
Name: [Fill] Description: The type of fill used. Options: none, null, previous, linear, 0. Default is null. Datatype: Text Example: linear

GET_SAMPLED_DATA_STDDEV

Description: Returns the standard deviation of values for all measurements in each sampling interval.

Arguments:

Name: Connection Description: The connection name configured in the Excel Add-in taskpane. Datatype: Text Example: Factry
Name: Database Description: The database name. Datatype: Text Example: historian
Name: Measurements Description: The measurements to query. Datatype: Excel range | Text Example: A3:B4 | JS-OPC-UA
Name: Start Description: The start time of the query (inclusive). Datatype: Excel datetime Example: 28/06/2022 16:20:00
Name: End Description: The end time of the query (exclusive). Datatype: Excel datetime Example: 29/06/2022 16:20:00
Name: SamplingInterval Description: The time interval for each sample. Accepts hours, minutes, and seconds. Datatype: Text Example: 1h30m
Name: Limit Description: The maximum number of datapoints for each measurement. Use 0 for no limit. Datatype: Number Example: 10
Name: [Fill] Description: The type of fill used. Options: none, null, previous, linear, 0. Default is null. Datatype: Text Example: linear

GET_SAMPLED_DATA_SUM

Description: Returns the sum of values for all measurements in each sampling interval.

Arguments:

Name: Connection Description: The connection name configured in the Excel Add-in taskpane. Datatype: Text Example: Factry
Name: Database Description: The database name. Datatype: Text Example: historian
Name: Measurements Description: The measurements to query. Datatype: Excel range | Text Example: A3:B4 | JS-OPC-UA
Name: Start Description: The start time of the query (inclusive). Datatype: Excel datetime Example: 28/06/2022 16:20:00
Name: End Description: The end time of the query (exclusive). Datatype: Excel datetime Example: 29/06/2022 16:20:00
Name: SamplingInterval Description: The time interval for each sample. Accepts hours, minutes, and seconds. Datatype: Text Example: 1h30m
Name: Limit Description: The maximum number of datapoints for each measurement. Use 0 for no limit. Datatype: Number Example: 10
Name: [Fill] Description: The type of fill used. Options: none, null, previous, linear, 0. Default is null. Datatype: Text Example: linear

GET_SAMPLED_DATA_FIRST

Description: Returns the first value for all measurements in each sampling interval.

Arguments:

Name: Connection Description: The connection name configured in the Excel Add-in taskpane. Datatype: Text Example: Factry
Name: Database Description: The database name. Datatype: Text Example: historian
Name: Measurements Description: The measurements to query. Datatype: Excel range | Text Example: A3:B4 | JS-OPC-UA
Name: Start Description: The start time of the query (inclusive). Datatype: Excel datetime Example: 28/06/2022 16:20:00
Name: End Description: The end time of the query (exclusive). Datatype: Excel datetime Example: 29/06/2022 16:20:00
Name: SamplingInterval Description: The time interval for each sample. Accepts hours, minutes, and seconds. Datatype: Text Example: 1h30m
Name: Limit Description: The maximum number of datapoints for each measurement. Use 0 for no limit. Datatype: Number Example: 10
Name: [Fill] Description: The type of fill used. Options: none, null, previous, linear, 0. Default is null. Datatype: Text Example: linear

GET_SAMPLED_DATA_LAST

Description: Returns the last value for all measurements in each sampling interval.

Arguments:

Name: Connection Description: The connection name configured in the Excel Add-in taskpane. Datatype: Text Example: Factry
Name: Database Description: The database name. Datatype: Text Example: historian
Name: Measurements Description: The measurements to query. Datatype: Excel range | Text Example: A3:B4 | JS-OPC-UA
Name: Start Description: The start time of the query (inclusive). Datatype: Excel datetime Example: 28/06/2022 16:20:00
Name: End Description: The end time of the query (exclusive). Datatype: Excel datetime Example: 29/06/2022 16:20:00
Name: SamplingInterval Description: The time interval for each sample. Accepts hours, minutes, and seconds. Datatype: Text Example: 1h30m
Name: Limit Description: The maximum number of datapoints for each measurement. Use 0 for no limit. Datatype: Number Example: 10
Name: [Fill] Description: The type of fill used. Options: none, null, previous, linear, 0. Default is null. Datatype: Text Example: linear

GET_SAMPLED_DATA_MAX

Description: Returns the maximum value for all measurements in each sampling interval.

Arguments:

Name: Connection Description: The connection name configured in the Excel Add-in taskpane. Datatype: Text Example: Factry
Name: Database Description: The database name. Datatype: Text Example: historian
Name: Measurements Description: The measurements to query. Datatype: Excel range | Text Example: A3:B4 | JS-OPC-UA
Name: Start Description: The start time of the query (inclusive). Datatype: Excel datetime Example: 28/06/2022 16:20:00
Name: End Description: The end time of the query (exclusive). Datatype: Excel datetime Example: 29/06/2022 16:20:00
Name: SamplingInterval Description: The time interval for each sample. Accepts hours, minutes, and seconds. Datatype: Text Example: 1h30m
Name: Limit Description: The maximum number of datapoints for each measurement. Use 0 for no limit. Datatype: Number Example: 10
Name: [Fill] Description: The type of fill used. Options: none, null, previous, linear, 0. Default is null. Datatype: Text Example: linear

GET_SAMPLED_DATA_MIN

Description: Returns the minimum value for all measurements in each sampling interval.

Arguments:

Name: Connection Description: The connection name configured in the Excel Add-in taskpane. Datatype: Text Example: Factry
Name: Database Description: The database name. Datatype: Text Example: historian
Name: Measurements Description: The measurements to query. Datatype: Excel range | Text Example: A3:B4 | JS-OPC-UA
Name: Start Description: The start time of the query (inclusive). Datatype: Excel datetime Example: 28/06/2022 16:20:00
Name: End Description: The end time of the query (exclusive). Datatype: Excel datetime Example: 29/06/2022 16:20:00
Name: SamplingInterval Description: The time interval for each sample. Accepts hours, minutes, and seconds. Datatype: Text Example: 1h30m
Name: Limit Description: The maximum number of datapoints for each measurement. Use 0 for no limit. Datatype: Number Example: 10
Name: [Fill] Description: The type of fill used. Options: none, null, previous, linear, 0. Default is null. Datatype: Text Example: linear