Custom functions
The Factry Historian excel add-in will help you to export data from any Factry Historian database right into this excel file. Before you can use any of the many custom functions, you will need to setup at least one connection to a Factry Historian API. This can be done by clicking on ‘Add a connection’ in the ‘Configurations’ tab. After setting up a connection, it should show this new connection below ‘Configured Historian API’s’.
When a connection has been made, it should be possible to use one of the custom functions by typing ‘=FACTRY.function_name(arguments)’ in any cell. Here ‘function_name’ is the name of the custom function you want to use and ‘arguments’ the corresponding needed arguments. Below you can find an overview of all the available functions and their corresponding arguments.
Single value functions
These functions will return a single value where the function is called.
GET_VAL
This function returns the value of the most recent datapoint before timestamp
Arguments
Connection - Text: Eg. factry
The connection name that has been set up in the configurations tab of the taskpane.
Database - Text: Eg. historian
The name of the database.
Measurement - Text: Eg. JS-OPC-UA
The measurement that needs to be fetched from the database.
Timestamp - Excel datetime: Eg. 29/06/2022 16:20:00
The newest timestamp that a datapoint can have (not inclusive).
[MaxAge] - Text: Eg. 48h | 1h30m | 1m30s
The maximum age of the datapoint at timestamp (only accepts h(ours), m(inutes) and s(econds)). No maximum if not given.
GET_CALC_VAL_COUNT
This function returns the number of datapoints between the start and end time
Arguments
Connection - Text: Eg. factry
The connection name that has been set up in the configurations tab of the taskpane.
Database - Text: Eg. historian
The name of the database.
Measurement - Text: Eg. JS-OPC-UA
The measurement that needs to be fetched from the database.
Start - Excel datetime: Eg. 28/06/2022 16:20:00
The start time of the query (inclusive)
End - Excel datetime: Eg. 29/06/2022 16:20:00
The end time of the query (not inclusive)
GET_CALC_VAL_INTEGRAL
This function returns the integral over values between the start and end time in steps of base period
Arguments
Connection - Text: Eg. factry
The connection name that has been set up in the configurations tab of the taskpane.
Database - Text: Eg. historian
The name of the database.
Measurement - Text: Eg. JS-OPC-UA
The measurement that needs to be fetched from the database.
Start - Excel datetime: Eg. 28/06/2022 16:20:00
The start time of the query (inclusive)
End - Excel datetime: Eg. 29/06/2022 16:20:00
The end time of the query (not inclusive)
BasePeriod - Text: Eg. 48h | 1h30m | 1m30s
The unit of time to integrate over (only accepts h(ours), m(inutes) and s(econds))
GET_CALC_VAL_MEAN
This function returns the mean value between the start and end time
Arguments
Connection - Text: Eg. factry
The connection name that has been set up in the configurations tab of the taskpane.
Database - Text: Eg. historian
The name of the database.
Measurement - Text: Eg. JS-OPC-UA
The measurement that needs to be fetched from the database.
Start - Excel datetime: Eg. 28/06/2022 16:20:00
The start time of the query (inclusive)
End - Excel datetime: Eg. 29/06/2022 16:20:00
The end time of the query (not inclusive)
GET_CALC_VAL_MEDIAN
This function returns the median value between the start and end time
Arguments
Connection - Text: Eg. factry
The connection name that has been set up in the configurations tab of the taskpane.
Database - Text: Eg. historian
The name of the database.
Measurement - Text: Eg. JS-OPC-UA
The measurement that needs to be fetched from the database.
Start - Excel datetime: Eg. 28/06/2022 16:20:00
The start time of the query (inclusive)
End - Excel datetime: Eg. 29/06/2022 16:20:00
The end time of the query (not inclusive)
GET_CALC_VAL_MODE
This function returns the mode between the start and end time
Arguments
Connection - Text: Eg. factry
The connection name that has been set up in the configurations tab of the taskpane.
Database - Text: Eg. historian
The name of the database.
Measurement - Text: Eg. JS-OPC-UA
The measurement that needs to be fetched from the database.
Start - Excel datetime: Eg. 28/06/2022 16:20:00
The start time of the query (inclusive)
End - Excel datetime: Eg. 29/06/2022 16:20:00
The end time of the query (not inclusive)
GET_CALC_VAL_SPREAD
This function returns the spread of the values between the start and end time
Arguments
Connection - Text: Eg. factry
The connection name that has been set up in the configurations tab of the taskpane.
Database - Text: Eg. historian
The name of the database.
Measurement - Text: Eg. JS-OPC-UA
The measurement that needs to be fetched from the database.
Start - Excel datetime: Eg. 28/06/2022 16:20:00
The start time of the query (inclusive)
End - Excel datetime: Eg. 29/06/2022 16:20:00
The end time of the query (not inclusive)
GET_CALC_VAL_STDDEV
This function returns the standard deviation of the values between the start and end time
Arguments
Connection - Text: Eg. factry
The connection name that has been set up in the configurations tab of the taskpane.
Database - Text: Eg. historian
The name of the database.
Measurement - Text: Eg. JS-OPC-UA
The measurement that needs to be fetched from the database.
Start - Excel datetime: Eg. 28/06/2022 16:20:00
The start time of the query (inclusive)
End - Excel datetime: Eg. 29/06/2022 16:20:00
The end time of the query (not inclusive)
GET_CALC_VAL_SUM
This function returns the sum of the values between the start and end time
Arguments
Connection - Text: Eg. factry
The connection name that has been set up in the configurations tab of the taskpane.
Database - Text: Eg. historian
The name of the database.
Measurement - Text: Eg. JS-OPC-UA
The measurement that needs to be fetched from the database.
Start - Excel datetime: Eg. 28/06/2022 16:20:00
The start time of the query (inclusive)
End - Excel datetime: Eg. 29/06/2022 16:20:00
The end time of the query (not inclusive)
GET_CALC_VAL_FIRST
This function returns the first value between the start and end time
Arguments
Connection - Text: Eg. factry
The connection name that has been set up in the configurations tab of the taskpane.
Database - Text: Eg. historian
The name of the database.
Measurement - Text: Eg. JS-OPC-UA
The measurement that needs to be fetched from the database.
Start - Excel datetime: Eg. 28/06/2022 16:20:00
The start time of the query (inclusive)
End - Excel datetime: Eg. 29/06/2022 16:20:00
The end time of the query (not inclusive)
GET_CALC_VAL_LAST
This function returns the last value between the start and end time
Arguments
Connection - Text: Eg. factry
The connection name that has been set up in the configurations tab of the taskpane.
Database - Text: Eg. historian
The name of the database.
Measurement - Text: Eg. JS-OPC-UA
The measurement that needs to be fetched from the database.
Start - Excel datetime: Eg. 28/06/2022 16:20:00
The start time of the query (inclusive)
End - Excel datetime: Eg. 29/06/2022 16:20:00
The end time of the query (not inclusive)
GET_CALC_VAL_MAX
This function returns the maximum value between the start and end time
Arguments
Connection - Text: Eg. factry
The connection name that has been set up in the configurations tab of the taskpane.
Database - Text: Eg. historian
The name of the database.
Measurement - Text: Eg. JS-OPC-UA
The measurement that needs to be fetched from the database.
Start - Excel datetime: Eg. 28/06/2022 16:20:00
The start time of the query (inclusive)
End - Excel datetime: Eg. 29/06/2022 16:20:00
The end time of the query (not inclusive)
GET_CALC_VAL_MIN
This function returns the minimum value between the start and end time
Arguments
Connection - Text: Eg. factry
The connection name that has been set up in the configurations tab of the taskpane.
Database - Text: Eg. historian
The name of the database.
Measurement - Text: Eg. JS-OPC-UA
The measurement that needs to be fetched from the database.
Start - Excel datetime: Eg. 28/06/2022 16:20:00
The start time of the query (inclusive)
End - Excel datetime: Eg. 29/06/2022 16:20:00
The end time of the query (not inclusive)
Multiple value functions
These functions will return a range of values starting from where the function is called. The first column has the timestamps and the next colum(s) will have the values of each measurement. The timestamp has a default format ‘dd/mm/yy hh:mm:ss’. To override this format simply set a custom format in that cell.
GET_RAW_DATA
This function returns the timestamp and value pairs for all measurements between the start and end time
Arguments
Connection - Text: Eg. factry
The connection name that has been set up in the configurations tab of the taskpane.
Database - Text: Eg. historian
The name of the database.
Measurements - Excel range | Text: Eg. A3:B4 | JS-OPC-UA
The measurements that needs to be fetched from the database.
Start - Excel datetime: Eg. 28/06/2022 16:20:00
The start time of the query (inclusive)
End - Excel datetime: Eg. 29/06/2022 16:20:00
The end time of the query (not inclusive)
Limit - Number: Eg. 10
The maximum number of datapoints for each measurement. If no limit is needed use 0.
GET_SAMPLED_DATA_COUNT
This function returns the timestamp and number of datapoint for all measurements in each sampling interval between the start and end time
Arguments
Connection - Text: Eg. factry
The connection name that has been set up in the configurations tab of the taskpane.
Database - Text: Eg. historian
The name of the database.
Measurements - Excel range | Text: Eg. A3:B4 | JS-OPC-UA
The measurements that needs to be fetched from the database.
Start - Excel datetime: Eg. 28/06/2022 16:20:00
The start time of the query (inclusive)
End - Excel datetime: Eg. 29/06/2022 16:20:00
The end time of the query (not inclusive)
SamplingInterval - Text: Eg. 48h | 1h30m | 1m30s
The time interval for each sample (only accepts h(ours), m(inutes) and s(econds)).
Limit - Number: Eg. 10
The maximum number of datapoints for each measurement. If no limit is needed use 0.
[Fill] - Text: Eg. linear
The type of fill used (none, null, previous, linear, 0). If no fill is given ’null’ will be used.
GET_SAMPLED_DATA_INTEGRAL
This function returns the timestamp and integral over values in steps of base period for all measurements in each sampling interval between the start and end time
Arguments
Connection - Text: Eg. factry
The connection name that has been set up in the configurations tab of the taskpane.
Database - Text: Eg. historian
The name of the database.
Measurements - Excel range | Text: Eg. A3:B4 | JS-OPC-UA
The measurements that needs to be fetched from the database.
Start - Excel datetime: Eg. 28/06/2022 16:20:00
The start time of the query (inclusive)
End - Excel datetime: Eg. 29/06/2022 16:20:00
The end time of the query (not inclusive)
SamplingInterval - Text: Eg. 48h | 1h30m | 1m30s
The time interval for each sample (only accepts h(ours), m(inutes) and s(econds)).
Limit - Number: Eg. 10
The maximum number of datapoints for each measurement. If no limit is needed use 0.
BasePeriod - Text: Eg. 48h | 1h30m | 1m30s
The unit of time to integrate over (only accepts h(ours), m(inutes) and s(econds))
[Fill] - Text: Eg. linear
The type of fill used (none, null, previous, linear, 0). If no fill is given ’null’ will be used.
GET_SAMPLED_DATA_MEAN
This function returns the timestamp and mean value for all measurements in each sampling interval between the start and end time
Arguments
Connection - Text: Eg. factry
The connection name that has been set up in the configurations tab of the taskpane.
Database - Text: Eg. historian
The name of the database.
Measurements - Excel range | Text: Eg. A3:B4 | JS-OPC-UA
The measurements that needs to be fetched from the database.
Start - Excel datetime: Eg. 28/06/2022 16:20:00
The start time of the query (inclusive)
End - Excel datetime: Eg. 29/06/2022 16:20:00
The end time of the query (not inclusive)
SamplingInterval - Text: Eg. 48h | 1h30m | 1m30s
The time interval for each sample (only accepts h(ours), m(inutes) and s(econds)).
Limit - Number: Eg. 10
The maximum number of datapoints for each measurement. If no limit is needed use 0.
[Fill] - Text: Eg. linear
The type of fill used (none, null, previous, linear, 0). If no fill is given ’null’ will be used.
GET_SAMPLED_DATA_MEDIAN
This function returns the timestamp and median value for all measurements in each sampling interval between the start and end time
Arguments
Connection - Text: Eg. factry
The connection name that has been set up in the configurations tab of the taskpane.
Database - Text: Eg. historian
The name of the database.
Measurements - Excel range | Text: Eg. A3:B4 | JS-OPC-UA
The measurements that needs to be fetched from the database.
Start - Excel datetime: Eg. 28/06/2022 16:20:00
The start time of the query (inclusive)
End - Excel datetime: Eg. 29/06/2022 16:20:00
The end time of the query (not inclusive)
SamplingInterval - Text: Eg. 48h | 1h30m | 1m30s
The time interval for each sample (only accepts h(ours), m(inutes) and s(econds)).
Limit - Number: Eg. 10
The maximum number of datapoints for each measurement. If no limit is needed use 0.
[Fill] - Text: Eg. linear
The type of fill used (none, null, previous, linear, 0). If no fill is given ’null’ will be used.
GET_SAMPLED_DATA_MODE
This function returns the timestamp and mode for all measurements in each sampling interval between the start and end time
Arguments
Connection - Text: Eg. factry
The connection name that has been set up in the configurations tab of the taskpane.
Database - Text: Eg. historian
The name of the database.
Measurements - Excel range | Text: Eg. A3:B4 | JS-OPC-UA
The measurements that needs to be fetched from the database.
Start - Excel datetime: Eg. 28/06/2022 16:20:00
The start time of the query (inclusive)
End - Excel datetime: Eg. 29/06/2022 16:20:00
The end time of the query (not inclusive)
SamplingInterval - Text: Eg. 48h | 1h30m | 1m30s
The time interval for each sample (only accepts h(ours), m(inutes) and s(econds)).
Limit - Number: Eg. 10
The maximum number of datapoints for each measurement. If no limit is needed use 0.
[Fill] - Text: Eg. linear
The type of fill used (none, null, previous, linear, 0). If no fill is given ’null’ will be used.
GET_SAMPLED_DATA_SPREAD
This function returns the timestamp and spread of the values for all measurements in each sampling interval between the start and end time
Arguments
Connection - Text: Eg. factry
The connection name that has been set up in the configurations tab of the taskpane.
Database - Text: Eg. historian
The name of the database.
Measurements - Excel range | Text: Eg. A3:B4 | JS-OPC-UA
The measurements that needs to be fetched from the database.
Start - Excel datetime: Eg. 28/06/2022 16:20:00
The start time of the query (inclusive)
End - Excel datetime: Eg. 29/06/2022 16:20:00
The end time of the query (not inclusive)
SamplingInterval - Text: Eg. 48h | 1h30m | 1m30s
The time interval for each sample (only accepts h(ours), m(inutes) and s(econds)).
Limit - Number: Eg. 10
The maximum number of datapoints for each measurement. If no limit is needed use 0.
[Fill] - Text: Eg. linear
The type of fill used (none, null, previous, linear, 0). If no fill is given ’null’ will be used.
GET_SAMPLED_DATA_STDDEV
This function returns the timestamp and standard deviation of the values for all measurements in each sampling interval between the start and end time
Arguments
Connection - Text: Eg. factry
The connection name that has been set up in the configurations tab of the taskpane.
Database - Text: Eg. historian
The name of the database.
Measurements - Excel range | Text: Eg. A3:B4 | JS-OPC-UA
The measurements that needs to be fetched from the database.
Start - Excel datetime: Eg. 28/06/2022 16:20:00
The start time of the query (inclusive)
End - Excel datetime: Eg. 29/06/2022 16:20:00
The end time of the query (not inclusive)
SamplingInterval - Text: Eg. 48h | 1h30m | 1m30s
The time interval for each sample (only accepts h(ours), m(inutes) and s(econds)).
Limit - Number: Eg. 10
The maximum number of datapoints for each measurement. If no limit is needed use 0.
[Fill] - Text: Eg. linear
The type of fill used (none, null, previous, linear, 0). If no fill is given ’null’ will be used.
GET_SAMPLED_DATA_SUM
This function returns the timestamp and sum of the values for all measurements in each sampling interval between the start and end time
Arguments
Connection - Text: Eg. factry
The connection name that has been set up in the configurations tab of the taskpane.
Database - Text: Eg. historian
The name of the database.
Measurements - Excel range | Text: Eg. A3:B4 | JS-OPC-UA
The measurements that needs to be fetched from the database.
Start - Excel datetime: Eg. 28/06/2022 16:20:00
The start time of the query (inclusive)
End - Excel datetime: Eg. 29/06/2022 16:20:00
The end time of the query (not inclusive)
SamplingInterval - Text: Eg. 48h | 1h30m | 1m30s
The time interval for each sample (only accepts h(ours), m(inutes) and s(econds)).
Limit - Number: Eg. 10
The maximum number of datapoints for each measurement. If no limit is needed use 0.
[Fill] - Text: Eg. linear
The type of fill used (none, null, previous, linear, 0). If no fill is given ’null’ will be used.
GET_SAMPLED_DATA_FIRST
This function returns the timestamp and first value for all measurements in each sampling interval between the start and end time
Arguments
Connection - Text: Eg. factry
The connection name that has been set up in the configurations tab of the taskpane.
Database - Text: Eg. historian
The name of the database.
Measurements - Excel range | Text: Eg. A3:B4 | JS-OPC-UA
The measurements that needs to be fetched from the database.
Start - Excel datetime: Eg. 28/06/2022 16:20:00
The start time of the query (inclusive)
End - Excel datetime: Eg. 29/06/2022 16:20:00
The end time of the query (not inclusive)
SamplingInterval - Text: Eg. 48h | 1h30m | 1m30s
The time interval for each sample (only accepts h(ours), m(inutes) and s(econds)).
Limit - Number: Eg. 10
The maximum number of datapoints for each measurement. If no limit is needed use 0.
[Fill] - Text: Eg. linear
The type of fill used (none, null, previous, linear, 0). If no fill is given ’null’ will be used.
GET_SAMPLED_DATA_LAST
This function returns the timestamp and last value for all measurements in each sampling interval between the start and end time
Arguments
Connection - Text: Eg. factry
The connection name that has been set up in the configurations tab of the taskpane.
Database - Text: Eg. historian
The name of the database.
Measurements - Excel range | Text: Eg. A3:B4 | JS-OPC-UA
The measurements that needs to be fetched from the database.
Start - Excel datetime: Eg. 28/06/2022 16:20:00
The start time of the query (inclusive)
End - Excel datetime: Eg. 29/06/2022 16:20:00
The end time of the query (not inclusive)
SamplingInterval - Text: Eg. 48h | 1h30m | 1m30s
The time interval for each sample (only accepts h(ours), m(inutes) and s(econds)).
Limit - Number: Eg. 10
The maximum number of datapoints for each measurement. If no limit is needed use 0.
[Fill] - Text: Eg. linear
The type of fill used (none, null, previous, linear, 0). If no fill is given ’null’ will be used.
GET_SAMPLED_DATA_MAX
This function returns the timestamp and maximum value for all measurements in each sampling interval between the start and end time
Arguments
Connection - Text: Eg. factry
The connection name that has been set up in the configurations tab of the taskpane.
Database - Text: Eg. historian
The name of the database.
Measurements - Excel range | Text: Eg. A3:B4 | JS-OPC-UA
The measurements that needs to be fetched from the database.
Start - Excel datetime: Eg. 28/06/2022 16:20:00
The start time of the query (inclusive)
End - Excel datetime: Eg. 29/06/2022 16:20:00
The end time of the query (not inclusive)
SamplingInterval - Text: Eg. 48h | 1h30m | 1m30s
The time interval for each sample (only accepts h(ours), m(inutes) and s(econds)).
Limit - Number: Eg. 10
The maximum number of datapoints for each measurement. If no limit is needed use 0.
[Fill] - Text: Eg. linear
The type of fill used (none, null, previous, linear, 0). If no fill is given ’null’ will be used.
GET_SAMPLED_DATA_MIN
This function returns the timestamp and minimum value for all measurements in each sampling interval between the start and end time
Arguments
Connection - Text: Eg. factry
The connection name that has been set up in the configurations tab of the taskpane.
Database - Text: Eg. historian
The name of the database.
Measurements - Excel range | Text: Eg. A3:B4 | JS-OPC-UA
The measurements that needs to be fetched from the database.
Start - Excel datetime: Eg. 28/06/2022 16:20:00
The start time of the query (inclusive)
End - Excel datetime: Eg. 29/06/2022 16:20:00
The end time of the query (not inclusive)
SamplingInterval - Text: Eg. 48h | 1h30m | 1m30s
The time interval for each sample (only accepts h(ours), m(inutes) and s(econds)).
Limit - Number: Eg. 10
The maximum number of datapoints for each measurement. If no limit is needed use 0.
[Fill] - Text: Eg. linear
The type of fill used (none, null, previous, linear, 0). If no fill is given ’null’ will be used.