Back to the overview
General
With the Excel Add In and integrated functions, measured and consumption data can be requested from the visual energy database. Each retrieval happens with the permissions of the registered user. The function parameters were set typical by Meteringpoints, OBIS measured values and time range.
The input is made according to the following scheme:=function name(paramter1; parameter2; …) Excel function can paste over the project explorer too.
Paramter
- Meteringpoint-ID (MP-ID)
The Meteringpoint-ID is a clear string, which represent the Meteringpoint. The ID has to be in quotes, e.g.: „4104abdd-4d0b-48af-8ab0-9c860122ada0“. It either will be pasted automatically or can be copied manual from the automatic generated list. - OBIS codes (OBIS)
The OBIS codes is a normalized string e.g.: „1-1:1.29.0*255“ and identify the desire medium, measured value and the tariff. The code will be copy automatically by using the Project explorer or can be copied manual from the automatic generated list. - Start point (FirstDate)
Set the date and the time for beginning of the Evaluation. When inserting from the Project explorer, either “Start Absolut” or a cell reference can be chosen. - End point (EndDate)
Set the date and the time for end of the Evaluation. When inserting from the Project explorer, either “End Absolut” or a cell reference can be chosen. - Interval (Interval)
Input in hours (1-24) - Basic function (BaseFunction)
0 = Sum, 1 = Maximum, 2 = Minimum, 3 = average - Interval function (IntervalFunction)
- 0 = Sum, 1 = Maximum, 2 = Minimum, 3 = average
Time range
- For retrieving of data, the time ranges has to be defined exactly with dates and times. When you indicate only the date, Excel assume from the time 00:00.
- The first period of a day has the timestamp 00:15 o’clock with 15 min. period length, or 01:00 o’clock with 1h period length.
- The last period of a day has the timestamp 00:00 o’clock.
- Up to the version 4.6.3: A retrieving of the data 01.01.2012 00:00 to 02.01.2012 00:00 contains the last period of the previous day (31.12.2011)! With 15 min. period length the query is rightly 01.01.2012 00:15 to 02.01.2012 00:00
- From version 4.6.4: One option in the settings sets how the data will be retrieved:
- Start time is timestamp of the first period: To retrieve the first period of a day, the start time has to value > 00:00 z.B. 00.15, because the saved timestamp present the the end of a period.
- Start time is beginning of the first period: To retrieve the first period of a day, 00:00 (beginning of the day) has to be declared.
List of functions
| Name of function | Parameter | Description | available from version |
|---|---|---|---|
| VE4_CalcMPointSum | MP-ID, OBIS, FirstDate, EndDate | Retrieve the Sum for the declared OBIS measured value for a Meteringpoint. | 4.2 |
| VE4_CalcMPointMax | MP-ID, OBIS, FirstDate, EndDate | Retrieve the biggest period value for the declared time range. | 4.2 |
| VE4_CalcMPointMin | MP-ID, OBIS, FirstDate, EndDate | Retrieve the smallest period value for the declared time range. | 4.2 |
| VE4_CalcMPointAverage | MP-ID, OBIS, FirstDate, EndDate | Retrieve the average period value for the declared time range. | 4.2 |
| VE4_CalcMPointStatus | MP-ID, OBIS, FirstDate, EndDate | Retrieve the worst status for the declared time range. | 4.2 |
| VE4_CalcMPointPerCount | MP-ID, OBIS, FirstDate, EndDate | Retrieve the number of measuring period for the declared time range. | 4.2 |
| VE4_CalcCCenterMPointSum | KS-ID, MP-ID, OBIS, FirstDate, EndDate | Retrieve the sum of Cost center parts for the declared OBIS measured value of a Meteringpoint. | 4.4 |
| VE4_CalcEBenefitMPointSum | EB-ID, MP-ID, OBIS, FirstDate, EndDate | Retrieve the sum of Energy benefit parts for the declared OBIS measured value of a Meteringpont. | 4.6.4 |
| VE4_CalcCCenterSum | KS-ID, OBIS, FirstDate, EndDate | Retrieve the sum of the consumer of a Cost center. | 4.4 |
| VE4_CalcMPointBaseValue | MP-ID, OBIS, Day, Interval, BaseFunction, IntervalFunction | Retrieve the function value (0-Sum, 1-Min, 2-Max, 3-Avg) for a intervall Example: For a declared day, the maximum from 2h-average values should be determined: VE4_CalcMPointBaseValue(“DE123…”;”0-0:128.128.0*255″;Date(2013;01;25);2;3;2) |
4.4 |
| VE4_CalcMPointTariffValue | MP-ID, OBIS, FirstDate, LastDate, StartTime, Duration, BaseFunction, IntervalFunction | Retrieve the function value (0-Sum, 1-Min, 2-Max, 3-Avg) for a tariff consumption Example: For the year 2012, the electric more consumption from 06:00 to 18:00 should be determined: =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 determinate the peak power of the same time range 06-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 | MP-ID, OBIS, FirstDate, LastDate | Retrieve the point for the maximum period value in the determined time range. | 4.4 |
| VE4_GetMPointMinDate | MP-ID, OBIS, FirstDate, LastDate | Retrieve the point for the minimum period value in the determined time range. | 4.4 |
| VE4_GetMPointName | MP-ID | Retrieve the Meteringpoint name from a ID. | 4.4 |
| VE4_GetMPointPerCount | MP-ID, OBIS, FirstDate, LastDate | Retrieve a number of the periods in the determined time range. | 4.4 |
| VE4_GetMpointPerLenght | ZP-IS, OBIS GroupA | Retrieve the period length. | 4.4 |
| VE4_GetMPointStatus | MP-ID, OBIS, FirstDate, LastDate | Retrieve the worst status of a period from the determined time range. | 4.4 |
| VE4_GetOBISUnit | OBIS | Retrieve the unit to a OBIS code. | 4.4 |
| VE4_GetOBISShortname | OBIS | Retrieve the shortname of a OBIS code. | 4.4 |
| VE4_GetTimeWhen | MP-ID, OBIS, FirstDate, LastDate, MinVal, MaxVal | Retrieve the time in hours for a Value range. | 4.6.5 |