Formula Time Series

The TSM supports formula based time series. All Microsoft .Net Framework functions are supported in VB.Net notation. Additionally, user-defined functions can be created and used in VB.Net.

Editing and calling a formula is carried out in the Edit tab for time series. It can be reached via Search -> Edit.

When the cursor is located in the Formula text field, you can have a list of all available functions by pressing the space bar.

Overview formula time series

Formula time series are defined by the underlying formula. This formula contains the calculation rule. The time series definition of a calculated time series is saved in the data base like all other time series. Additionally, the definition includes a formula. If the time series definition is changed, an archive entry with corresponding time mark is created in the data base to secure auditing acceptability. Thus, also historical outcomes can be accessed and displayed. When time series are read-out, they are calculated according to the formula and the outcome is provided.

Calculation rules based on other time series can be displayed with formulas. Time series or numbers can be calculated using basic arithmetical operations and functions (+, -, *, /). Moreover, customer specified functions can be implemented.

Syntax of formulas

Formulas are administered in simple text. When a time series is read-out, the formula is compiled and calculated by the HAKOM Framework. The syntax is identical to the one used in VB.Net. Moreover, all functions from Math Namespace can be used:

(https://docs.microsoft.com/en-us/dotnet/api/system.math?redirectedfrom=MSDN&view=netframework-4.7.2).

Operators

Formulas support the following operators:

Additionally the following relational operators are supported:

e.g.: 3 + 5 * 2 results in a time series that has the constant value of 13

Brackets are supported as well:

e.g.: (3 + 5) * 2 results in a time series that has the constant value of 16

Including other time series

Time series can be included in a formula according to the following pattern:

[Time series (name or ID), offset (optional), unit (optional), aggregation (optional)]

When using time series in formulas using square brackets, a data element is passed. In addition to the values ([8765].Value) of the time series, this contains the status ([8765].Flag) of the values, the from and to date ([8765].fromDate and [8765].toDate) and the index of the time series ([8765].Index).

For all operators mentioned above under "Operators" the following rules apply:

• Comparison/calculation of a referenced time series with another time series also returns a data element as a result.
• Comparison/calculation of a referenced time series with an integer-value returns a data element as the result.
• Comparison/calculation of a referenced time series with a double-value returns a data element as the result.
• Calculation may be performed using the value of a time series directly instead of the data elements (By using [time-series-id].Value instead of just [time-series-id]), This returns a double-value as the result.

Examples

Example 1

Only the ID of a time series is used here:

[4711]

When it is loaded, the formula time series returns the same data as the time series 4711.

Example 2

Here the ID of a time series and an offset are passed in order to use the "Lead" function:

Lead([4711, 2], 2)

The formula time series assumes the data of the time series 4711 when loading. Additionally the data 2 interval steps before the "from" date and 2 interval steps after "to" date. The "Lead" function shifts the data by two interval steps, which is made possible by the additionally loaded data outside the "from" and "to" data.

Example 3

Here the ID of a time series, an offset and a unit are passed:

[4711, 0, kW]

When the formula time series is loaded, it returns the values of time series 4711 converted to kW.

Example 4

Here the ID of a time series, an offset, a unit and an aggregation are passed:

[4711, 0, "", Sum]

When the formula time series is loaded, it returns the values of time series 4711. If data is aggregated (e.g. because the intervals of the time series are different), the sum of the data is calculated.

Example 5

Here the values of several time series are used in combination with some of the supported operators:

[4711]*3+[123]+0.22

Here, the formula time series assumes the value of time series 4711 multiplied by three and summed with the value of time series 123 and the value 0.22.

If the name of a time series is entered into the formula editor, it will be automatically changed to that time series ID. This guarantees that no unwanted side effects occur should this time series be renamed. By choosing the displaymode "Time series name" it is possible to display the name rather than the ID. In this mode it is not possible to make changes to the formula.

Specification of Formula Time Series

Interval

Formula time series are calculated based on their specified interval. All time series referenced in the formula will be loaded in this given interval before a

Example: Evaluating load profiles with the load stored in hourly intervals and price time series stored in half hourly interval.

Scenario 1 – Formula time series with interval: Hour

Calculation: Load profile (hourly resolution) * price time series (averaged to hourly resolution)

Result: A formula time series in hourly resolution 

Scenario 2 – Formula time series with interval: Day

Calculation: Load profile (averaged to daily resolution) * price time series (averaged to daily resolution)

Result: Formula time series in daily resolution

->  This consequences in a wrong result, as only daily average values are calculated.

Scenario 3 – Formula time series with interval: Hour, Read-Out with interval: Day

Calculation: Load profile (averaged to hourly resolution) * price time series (averaged to hourly resolution)

Intermediate result: Formula time series in hourly resolution

Result: Formula time series read-out in daily resolution

-> This leads to a correct result, as the daily value of the formula time series consists of accumulated hourly values.

Units

It is possible to state a unit in the formula of a time series.

e.g. [530379,-3,KW,Average] converts the data of the time series 530379 from MW to KW, defers the time series for -3 intervals and calculates the average which is generated across the resolution of the formula time series with the values of the times series 530379.

Please note that the conversion of units has to be plausible (Conversion from MW to Euro or percentage is not possible.).

Functions in EvalComponent

There are several .VB files in the BIN directory of the TSM which names start with EvalComponent. The functions which can be called in the formula editor are located in these files. They are deposited in .Net syntax.

Formulas that are public functions in an EvalComponent, will be displayed in the TSM's formula editor through the Intellisense. By pressing the space bar the Intellisense will show you a list of all public functions.

Example function in EvalComponents:

Public Function If(ByVal a As Boolean, ByVal b As Double, ByVal c As Double) As Double
   If a Then Return b Else Return c
End Function

Calling the formula in the TSM:

In this case, when calling the formula, the operators <> trigger a comparison of the values from the time series [12] are unequal 0.

The outcome is a Boolean value (True / False). By means of this Boolean, the function decides if when True the value from time series [11] is delivered, or when False the value “100”.

Standard functions

The TSM bin-folder contains files whose names start with "EvalComponentHAKOM". These files contain the standard functions delivered by Hakom.

*These functions can be used with an integer instead of a data-type parameter (Lag([123].Index instead of Lag([123])).

Examples

For the following examples the following definitions apply:

There are four different versions of "ConstantMaturity_Data": On 15.02.2019 every quarter of an hour between 06:00:00 AM and 09:00:00 AM.

The resolution of both time series is a quarter of an hour.

Example 1: The ConstantMaturity Function

ConstantMaturity([ConstantMaturity_Data],0)

How do these values come about?

The function checks, if there is a version of the given time series for the actual period. So for 06:00-06:15 it checks, if there is a version of "ConstantMaturity_Data" of 15.02.2019 at 06:00. Since this is the case, the parameter "maturity" which represents a shift, is applied to this version. In this example maturity is 0 and therefore no shifting happens and the value from 06:00-06:15 is acquired.

The next period is 06:15-06:30. Again the function checks for a version of the given time series at this period and writes it to the formula time series.

From 07:00 there is no more version of "ConstantMaturity_Data" and so the following values are 0 missing.

Example 2: The ConstantMaturity Function With Shifting

ConstantMaturity([ConstantMaturity_Data],4)

The values of the formula time series are calculated as in example 1. Only the parameter "maturity" differs. This means, that the value is still taken from the version at 06:00, which is the actual starting time of the period, but now applying 4 shifts by 15 minutes each, the value from 07:00-07:15 is taken.

After the period from 06:00-06:15 the function continues with the one from 06:15-06:30 and takes the value from the version at 06:15 again applying a shift of 4 periods and so on.

Because there is no version of "ConstantMaturity_Data" before 06:00 the values of the formula time series from 06:00-07:00 are 0 missing.

Example 3: The ConstantMaturity Function With Shifting By Interval

ConstantMaturity([ConstantMaturity_Data],2,Components2016.TimeSeries.TimeSeriesInterval.Hour)

In this example the function was given an additional parameter - an interval (Components2016.TimeSeries.TimeSeriesInterval.Hour). Now maturity has the value 2 and the interval is hour. Again the values are taken from the version that fits the actual periods starting time, but this time they are shifted by two hours.

Individual EvalComponents and Programming

It is possible to create own EvalComponents files. These files should be administered by the user or arranged with HAKOM. Otherwise, it is possible that these special files are not installed when reinstalling. These files can be saved in the folder Custom of the installation package to be picked up for the next installation.

Creation and Syntax

To create an own EvalComponent file, a .vb file has to be created in the Bin directory of the TSM installation. The file name has to start with EvalComponent.

e.g. EvalComponentMyFunctions.vb

Content: 

Imports System
Imports HAKOM.Framework.DAL
Imports HAKOM.Framework.Lib
Imports HAKOM.Framework.TimeSeries
Imports System.Collections.Generic
Imports System.Data

Partial Public Class EvalComponent

'own functions
     
End Class

Attention: Self-made EvalComponents must never be written into the ones delivered by HAKOM (EvalComponentHAKOM.vb, EvalComponentHAKOMOptional.vb, EvalComponentHAKOMReferences.vb)

Value and index functions:

There are two options to apply time series values when calling a function.

Value

The value can be applied directly like in the following example:

Add2(ByVal value Double) As Double

The call of this function looks like this: Add2([1234])

In the EvalComponent file the function would look like this:

Private Function Add2(ByVal value As Double) As Double
      Return value + 2
End Function

Index

Moreover, the index of a value can be applied. In this case, not the value is applied but the position of the value in mSourceTSData emanating from the current position. This can be required if an offset shall be used.

Add2(ByVal index As Integer) As Double

The call of this function looks like this: Add2([1234].Index)

In the EvalComponent file the function would look like this:

Private Function Add2(ByVal index As Integer) As Double
      Return mSourceTSData(index)(aktLine).Value + 2
End Function

Variables

Assembly Cache

The assembly cache can be activated to accelerate the call of functions in EvalComponents.

The functions in the EvalComponent*.vb files are translated into a temporary DLL at every function call. To save the required time for this process, the following entry can be added to HAKOM.Config.

<HAKOMFramework>
    <settings>
		<AssemblyCache>
			%ALLUSERSPROFILE%\HAKOM\TimeSeriesManager\AssemblyCache
		</AssemblyCache>
    </settings>
</HAKOMFramework>

A path where the otherwise temporary DLL would be saved can be defined in the tags <AssemblyCache>. If the EvalComponent data respectively the formula are not changed until the next call of a formula time series, this DLL is reused.