General

Entries follow the pattern:

=Function description (parameter 1; parameter 2;...)

Parameter

  • Meter point ID (MP ID)
    The meter point ID is a unique character sequence representing the meter point. The ID has to be put in quotation marks, e.g.: "4104abdd-4d0b-48af-8ab0-9c860122ada0". It is either automatically added when using the project explorer or can be manually copied from the automatically generated list.
  • OBIS code (OBIS)
    The OBIS code is a standardized character sequence, e.g.:"1-1:1.29.0*255" and uniquely identifies the desired medium, measured value and tariff. The code is either automatically added when using the project explorer or can be manually copied from the automatically generated list.
  • Starting point (FirstDate)
    Defines the starting date and time of the evaluation.When pasting from the project explorer, you can either select 'Absolute start' or a cell reference.
  • Ending point (EndDate)
    Defines the end date and time of the evaluation.When pasting from the project explorer, you can either select 'Absolute end' or a cell reference.
  • Interval
    Expressed in hours (1-24)
  • Base function (BaseFunction)
    0 = total, 1 = maximum, 2 = minimum, 3 = average
  • Interval function (IntervalFunction)
    0 = total, 1 = maximum, 2 = minimum, 3 = average

Period

  • To retrieve data, the time ranges have to be defined exactly with the date and time. If you only enter the date, Excel sets the time to 00:00.
  • The first period of a day has the timestamp 00:15 for a period duration of 15 minutes or 01:00 for a period duration of 1 hour.
  • The last period of the day has the timestamp 00:00.
  • Up to version 4.6.3: When you retrieve the data from 01.01.2012 00:00 to 02.01.2012 00:00, the last period of the previous day (31.12.2011) is included! For a period duration of 15 minutes, the correct request is: 01.01.2012 00:15 to 02.01.2012 00:00
  • Version 4.6.4 and higher: In the settings, you can select an option to define how the data is retrieved:
    • The starting time is the timestamp of the first period: As the saved timestamp is always the end of the period, a value > 00:00 such as 00:15 has to be entered as the starting time to retrieve the first period of the day.
    • The starting time is the beginning of the first period: To retrieve the first period of the day, you have to enter 00:00 (beginning of the day).

List of functions

Function description Parameter Description abailable as of version
VE4_CalcMPointSum ZP-ID, OBIS, FirstDate, EndDate Displays the total of the entered OBIS value of a meter point. 4.2
VE4_CalcMPointMax ZP-ID, OBIS, FirstDate, EndDate Displays the maximum period value for the time frame specified. 4.2
VE4_CalcMPointMin ZP-ID, OBIS, FirstDate, EndDate Displays the minimum period value for the time frame specified 4.2
VE4_CalcMPointAverage ZP-ID, OBIS, FirstDate, EndDate Displays the average period value for the time frame specified. 4.2
VE4_CalcMPointStatus ZP-ID, OBIS, FirstDate, EndDate Displays the worst status for the time frame specified. 4.2
VE4_CalcMPointPerCount ZP-ID, OBIS, FirstDate, EndDate Displays the number of measuring periods for the time frame specified. 4.2
VE4_CalcCCenterMPointSum KS-ID, ZP-ID, OBIS, FirstDate, EndDate Displays the total of the cost center proportions for the entered OBIS value of a meter point. 4.4
VE4_CalcEBenefitMPointSum EB-ID, ZP-ID, OBIS, FirstDate, EndDate Displays the total of the energy benefit proportions for the entered OBIS value of a meter point. 4.6.4
VE4_CalcCCenterSum KS-ID, OBIS, FirstDate, EndDate Displays the total consumption values of a cost center. 4.4
VE4_CalcMPointBaseValue ZP-ID, OBIS, Day, Interval, BaseFunction, IntervalFunction Displays the function value (0 total, 1 min, 2 max, 3 avg) for an interval
Example:
The maximum value of a specified day should be determined using 2h average values: =VE4_CalcMPointBaseValue("DE123...";"0-0:128.128.0*255";Date(2013;01;25);2;3;2)
4.4
VE4_CalcMPointTariffValue ZP-ID, OBIS, FirstDate, LastDate, StartTime, Duration, BaseFunction, IntervalFunction Displays the function value (0 total, 1 min, 2 max, 3 avg) for a tariff consumption.
Example:
To determine the electrical active consumption from 06:00 to 18:00 for 2012: 
=VE4_CalcMPointTariffValue("DE99999979256KBRBB000000000000001";"001-001:001.029.000*255";"01.01.2012 00:00:01";"01.01.2013 00:00";"06:00";12;0;0)
To retrieve the electrical power peak for the same timeframe of 06:00 to 18:00:
=VE4_CalcMPointTariffValue("DE99999979256KBRBB000000000000001";"001-001:001.012.000*255";"01.01.2012 00:00:01";"01.01.2013 00:00";"06:00";12;2;2)
4.4
VE4_GetMPointMaxDate ZP-ID, OBIS, FirstDate, LastDate Displays the time of the maximum period value within the specified time range 4.4
VE4_GetMPointMinDate ZP-ID, OBIS, FirstDate, LastDate Displays the time of the minimum period value within the specified time range 4.4
VE4_GetMPointName ZP-ID Displays the meter point name matching an ID 4.4
VE4_GetMPointPerCount ZP-ID, OBIS, FirstDate, LastDate Displays the number of periods for the specified time range 4.4
VE4_GetMpointPerLenght ZP-IS, OBIS GroupA Displays the period duration 4.4
VE4_GetMPointStatus ZP-ID, OBIS, FirstDate, LastDate Displays the worst status of a period for the time frame specified. 4.4
VE4_GetOBISUnit OBIS Displays the unit for an OBIS code 4.4
VE4_GetOBISShortname OBIS Displays the short description of an OBIS code 4.4 
VE4_GetTimeWhen ZP-ID, OBIS, FirstDate, LastDate, MinVal, MaxVal Displays the time in hours for a value range 4.6.5