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 |