This website uses cookies to improve user experience. We assume that you agree. You can also object to the use. All relevant information and settings can be found here

Agree and continue
Suche:

Excel functions

Add In

Zurück zur Übersicht

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

 

Diesen Beitrag teilen