Brief description of 'PEFA Builder'

Draft version as of 23 January 2014

1Objective and general architecture of the PEFA builder

Mandated by the Working Group on Environmental Accounts,Eurostat with the help of contractors is developing an IT-tool to facilitate the compilation of Physical Energy Flow Accounts (PEFA).

The basic idea is to have an IT tool (the 'PEFA-Builder') which facilitates the compilation of the PEFA tables A and B (physical supply and use tables).

The point of departure is the output of the five Annual EnergyQuestionnaires jointly operated by IEA and Eurostat(abbreviated Joint Questionnaire (JQ) in the following) as these are available in all countries.

Figure 1: General architecture of the PEFA-builder

In brief, the PEFA-builder imports data from the annual energy questionnaires (csv flat files), undertakes various manipulations (e.g. transformation to one common unit, namely Terajoules), and assigns the processed values to a pair of physical supply and use tables (presented in a multidimensional EXCEL 'pivot table'). The resulting physical supply and use table represent more or less PEFA tables A and B, but are actually more detailed in the classification of rows and columns.

Some additional (modules) have been developed for undertaking auxiliary calculations and ‘exporting’ results to the main workbook; e.g. the module on transports-resident-adjustments (TRANSPORT) and a module on industry detailing (1toN cases).

Technically, the ‘PEFA-builder’ is an EXCEL workbook which includes the mentioned modules (which are further EXCEL workbooks).

The main EXCEL workbook is composed of a number of worksheets which are described inTable 1.

Table 1: PEFA-builder - worksheets included in the PEFA Builder EXCEL workbook

The most important worksheet in the PEFA builderis DEFINITION, with more than 10000 engine-readable 'definition-rows' (records) instructing where to place single Annual Energy Questionnaire items in the physical supply and use tables. A macro reads the 'definitions' row by row and treats accordingly the source data. This 'open architecture' makes it quite easy for the user to change the definitions without touching the macros. The remaining worksheets in the main EXCEL workbook provide the necessary auxiliary data and calculations.

All the macros are controlled through a central EXCELsheet (MAIN), where brief instructions explain how the user should proceed to run each ofthem.

1.1Worksheet MAIN

As mentioned, this is the central worksheet of the tool, where the user interface is located. It includes buttons to activate all the macros, as well as a set of parameters that are used by these same macros. Additionally, brief instructions are included in order to assist the user in correctly using the tool. The worksheet is visible inFigure 2.

Figure 2: Current layout of the control worksheet (MAIN) of the PEFA builder

In worksheet MAIN the user starts various macros (buttons) which are briefly described in Table 2.

Table 2: Overview of macros included in main EXCEL workbook and started from worksheet MAIN

2Important worksheets in the main EXCEL workbook

2.1Worksheet DEFINITION

The worksheet DEFINITION includes the instructions that allow the tool to use the data that is obtained from the JQ (which is included in worksheet SOURCE) and allocate it to the correct destination cell in the final PEFA tables. Due to the complexity of the transformations involved, it includes instructions that allow for subtraction, addition and disaggregation of values, or for a conversion from one unit to another, just to give some examples. A snapshot of the worksheet is visible inFigure 3, while a more detailed description of its structure is given inTable 3.

Figure 3: Snapshot of the worksheet DEFINITION

Table 3: Description of columns (dimensions) in worksheet DEFINITION

2.2Worksheet SOURCE

In worksheet SOURCE the user has to paste the data as exported via csv flatfile from the five Annual Energy Questionnaires. Apart from the auxiliary data that are necessary for the modules TRANSPORT and 1 TO N, this is the only external data source used for all the calculations in the PEFA builder. An example of output data included in this worksheet is visible in Figure 4.

Figure 4: Worksheet SOURCE, with an example of output data from the five Joint Questionnaires

2.3Worksheets PEFACOL and PEFAROW

These two worksheets are used by the procedure CHECK in order to verify if the worksheet DEFINITION may include any code that is not part of the PEFA tables. Figure 5 and Figure 6present examples of these worksheets.

Figure 5: Worksheet PEFACOL

Figure 6: Worksheet PEFAROW

2.4Worksheet FINAL

Worksheet FINAL includes the resulting dataof manipulations done by the procedures (macros) included in the tool. It is important to note that this worksheet does not include the result of procedure “1 To N” (which is the last procedure to be used). Instead, this result is presented in worksheet FIN1TON. This allows the user to visualize and validate the data before the factors from the 1toN tables are applied(to obtain additional industry detailing).

It is a multidimensional data set with a record (row) for each final value (i.e. column VALUE2). The dimensions (columns) of worksheet FINAL are presented inTable 4.

Table 4: Dimensions (columns) of worksheet FINAL

2.5Worksheet FIN1TON

This worksheet consists of a copy of the worksheet FINAL (i.e. the result of all procedures except “1 to N”). However, three additional dimensions (rows) are included in order to incorporate the results of the procedure “1 to N”, allowing to obtain more disaggregated data (per NACE sector and/or households). As mentioned previously, the existence of two “final” worksheets (and two pivot tables linked to these, PIVOTEST and FINAL_PEFA) allows the user to visualize the results before and after applying the “1 to N” procedure.

The additional dimensions (comparing to worksheet FINAL) of worksheet FIN1TON are presented in Table 5.

Table 5: Additional dimensions (columns) of worksheet FIN1TON

3Procedure CHECK

This procedure verifies if certain cells in the worksheet DEFINITION are correctly encoded, namely:

  • If codes included in column “COL” are according to the list of columns codes provided in worksheet PEFACOL;
  • If codes included in column “ROW” are according to the list of row codes provided in worksheet PEFAROW;
  • If codes included in column “FACTOR” are according to the list of factor codesincluded in worksheet PEFAFACT.

Whenever one item in the above mentioned rows does not exist in the corresponding worksheet, the cell is highlighted in red. An example of this procedure is visible in Figure 7.

Figure 7: Example of a check of the worksheet DEFINITION, with a highlighted cell

4Procedure for conversion of units(NCV)

See also Annex in chapter 12 for a more details.

4.1Description

In some cases, conversion factors need to be applied to the values obtained from the JQ, due to two distinct reasons:

  • To convert tonnes to TJ;
  • To convert from Gross (GCV) to Net Calorific Values (NCV).

There are three different ways for a conversion factor to be applied:

a)For specific cases, a fixed conversion factor needs to be applied, so specific codes are included in sheet FACTORS and the same code is included in the same column FACTOR of sheet DEFINITION. In order to be distinguished from the codes reported in the JQ, these codes always include the prefix “F_” (e.g. F_PATFUEL).

b)If there is no pre-defined conversion factor, the conversion factor to be appliedis generally defined in column FACTOR of the sheet DEFINITION (if necessary at all, otherwise the cell is left blank). If a conversion factor needs to be applied, its code is then included in this column and the value of the conversion factor is obtained from the sheet SOURCE (since the conversion factors should come as an output of the JQ) – the key is the code, which is the same in both sheets.

c)In case a factor code is defined in sheet DEFINITION but is not available in sheet SOURCE, a standard conversion factor is applied, which is product specific (the full list is available in sheet FACTORS).

4.2Calculation

The calculation is done by pressing button “Convert” in sheet MAIN and the general sequence for the necessary conversions is:

a)A first multiplication is done, according to the value displaced in column MULT of sheet DEFINITION, to adjust the order of magnitude of the original value obtained from the JQ (e.g. to change from 1000 tonnes to tonnes). The value to be applied is defined directly in the sheet DEFINITION (case-by-case) and the same value is found in sheet FINAL (under the same column: MULT), where the entire final calculation is made using Excel formulas. In several cases, MULT is defined as 1, meaning that no conversion is necessary;

b)The second multiplication concerns one of the mentioned conversions: tonnes to TJ or GCV to NCV. For this, the macro searches in column FACTOR for a factor code:

-If the cell is blank, it does nothing;

-If there is a code, it proceeds as explained in the point below.

c)For specific cases, a fixed factor needs to be applied (e.g. for 5 solid fuels for Coal): in these cases we do not consider the factors available in the JQ, instead we use a fixed value per product. The code of the factor to be applied is included in column FACTOR of sheet DEFINITION(e.g.: F_PATFUEL, F_OVENCOKE, etc.) and the full list of fixed factors (i.e. with the code and including the respective value) is available in FACTOR sheet;

d)If the factor exists in FACTOR column and is not preceded by “F_” (meaning it is not a fixed factor), the macro searches for the corresponding value in the source data (sheet SOURCE) and stores it in sheet FINAL in the respective row and column (FACTOR);

e)If a factor code is defined in column FACTOR of sheet DEFINITION but this code is not reported in the JQ, the standard factor per product, as available in sheet FACTOR, is used.

Additionally, there are two further operations defined by columns PCT and NEPCT in sheet DEFINITION, which define an additional factor to be applied(see procedure 1toN) and the specificcalculation for Oil (see procedure NON-ENERGY USE), respectively, but these do not concern the NCV module.

5Procedure to calculate conversion factor for Crude Oil (CRUDE OIL)

See also Annex in chapter 12 for a more details.

5.1Description

The conversion from tonnes to Joules for Crude Oil requires a conversion factor, i.e. the net calorific value (NCV). This NCV for Crude oil is derived through a complex calculation. This is an exception, since for all other OIL-products Eurostat’s Energy Unit uses the average_NCV to convert from natural to energy units. For this reason, this specific calculation for the NCV factor of Crude Oil is treated separately from the remaining conversions. The calculated NCV-factor is being implemented for all “Item1” of the respective year concerning this product, without exceptions.

5.2Calculation

The calculation of the conversion NCV-factor for Crude Oil is done once pressing the button “Crude Oil factor” in sheet MAIN. This should be done before running “Convert”, since this macro will use this conversion factor calculated by the previous. The worksheets involved in this calculation are described below:

a)CRUDEOILDEF includes the definitions necessary for this calculation (columns QUEST, PRODUCT, DATATYPE, ITEM1, ITEM2 and FACTOR, as obtained from sheet DEFINITION). Column G includes the number of occurrences;

b)CRUDEOIL uses the above mentioned definitions to apply it to the SOURCE data (once again, identified under blue color, while columns in grey include the calculations);

c)PIVOTCOCTRL is used for control and validation of the calculation (easier to visualize than PIVOTCODATA, the one used by the macro). Final NCVs are included in row 43 (shaded in orange). Cells with “#DIV/0” appear due to lack of data for those years (i.e. it is not an error). If one includes data for those years the formula will be automatically applied;

d)PIVOTCODATA includes all data used for the calculation and the final factor (column B). The respective factor is then applied in column FACTOR of sheet FINAL.

6Procedure for non-energy calculation (NON-ENERGY USE)

6.1Description

This procedure is necessary to split (single out) the non-energy use of several oil productsfor several industrial sectors. The calculation of the non-energy use is made through different formulas which use data obtained from the Joint Questionnaire on Oil. The resulting values are applied according to the correspondence tables (worksheet DEFINITION).

In order to understand this calculation, we may consider the following examples:

  • Non-Energy Use - Coal Mines, is calculated as:

a)VALUE = NEENERGY * EMINES /TOTENGY

  • Non-Energy Use - Oil and Gas Extraction, is calculated as:

b)VALUE = NEENERGY * EOILGASEX / TOTENGY

These equations may be simplified by including a factor, which replaces part of the original equation.

Following the examples above, the first equation could be:

  • VALUE = EMINES *FACTOR
  • While the second would be:

  • VALUE = EOILGASEX * FACTOR,

since the factor to be applied is calculated equally in these 2 cases: NEENERGY / TOTENGY.

In fact there are only four types of factors to be applied and this simplifies the implementation of these calculations into the IT tool. The four cases are displayed in sheet DERIVEDEF of the PEFA builder, along with the list of products where this calculation is being applied:

QUEST / FACTOR / DETVAL1 / DETVAL2 / TOTVAL1 / TOTVAL2
OIL / FACTOTHER / NEOTHER / TOTOTHER
OIL / FACTTRANSF / NETRANSF / TOTTRANF
OIL / FACTIND / NEIND / NECHEM / TOTIND / CHEMICAL
OIL / FACTENERGY / NEENERGY / TOTENGY

The general equation to be applied is:

FACTOR = (DETVAL1 – DETVAL2) / (TOTVAL1 – TOTVAL2)

Therefore, for the two previously mentioned examples, the factor to be applied would be FACTENERGY (calculated as NEENERGY/TOTENGY).

In order to instruct the PEFA builder of which factor needs to be applied for each case, the code of the factor to be applied (FACTOTHER, FACTTRANSF, FACTIND or FACTENERGY) is included in column NEPCT of the sheet DEFINITION. In case the final value should be calculated as (1 – factor), by convention a negative sign is added before the code: -FACTOTHER, -FACTTTRANSF, -FACTIND or –FACTENERGY).

Concerning the source data, the tool assumes that certain rules are verified:

  • TOTIND has to be >= NEIND;
  • CHEMICAL has to be >= NECHEM.

Additionally:

  • If NEIND = 0: this would mean that also NECHEM must be 0; so there is no need for calculating a factor to split the non-energy use;
  • If NECHEM = 0: then CHEMICAL also has to be 0; the formula would then be (NEIND – 0) / (TOTIND – 0);
  • If TOTIND = 0; then also the NEIND and also NECHEM must be 0; so there is no need for calculating a factor to split;
  • If CHEMICAL = 0; then also NECHEM must be 0; one can use the formula (NEIND – 0) / (TOTIND – 0);
  • Logically, if NEIND = TOTIND (and not zero), then CHEMICAL = NECHEM.

6.2Calculation

This macro should be used before running macro “Convert”, since the latter uses the results from the first. Once pressing the button “NON-ENERGY USE” in sheet MAIN, thecalculation proceeds as follows:

a)The factors are calculated according to the definitions in sheet DERIVEDEF, where each element (i.e. the code) is defined for the necessary elements of the equation: DETVAL1, DETVAL2, TOTVAL1 and TOTVAL2;

b)The macro then searches in the source data (sheet SOURCE) for these same codes defined in sheet DERIVEDEF (NEOTHER, TOTOTHER, etc.). This is done when clicking on button “NON-ENERGY USE” in sheet MAIN;

c)For the calculation, the following generic formula is used: (DETVAL1 – DETVAL2) / (TOTVAL1 – TOTVAL2). The macro stores the values found for each element in different columns of sheet DERIVED. The calculation is then done automatically in the same sheet using an Excel formula and is displayed in column VALUE. Therefore, at this point, all the factors that may be used (or not) should be calculated;

d)The factor to be used (if necessary at all – please keep in mind that this procedure is only used for certain oil products) is defined in sheet DEFINITION. Therefore, when pressing button “Convert”, the macro verifies for each row of this sheet if there is a factor code in column NEPCT. Then:

-If the cell is blank, it does nothing;

-If it exists, it uses the code to obtain the corresponding value from sheet DERIVED (using the combination product-item1);

e)After obtaining it from sheet DERIVED, the value is stored in the respective row of sheet FINAL, in column NEPCT [please notice that, as explained previously, for cases where the code is preceded by a minus, the figure presented is not the factor, but (1-factor)]. The factor to be applied in each case is defined in DEFINITION in the column with the same name.

7Procedurefor calculation of statistical discrepancy (BALANCE)

7.1Description

This procedure aims at calculating the statistical discrepancy resulting from the conversions of units done by the PEFA builder.

This procedure is valid for the following 6 solid fuels coming from the Coal Questionnaire:

Product Code / Product
ANTCOAL / Anthracite
COKCOAL / Coking Coal
BITCOAL / Other Bituminous Coal
SUBCOAL / Sub-Bituminous Coal
LIGNITE / Lignite/Brown Coal
PEAT / Peat

For these products, the PEFA builder uses specific NCV as provided in the COAL Questionnaire to convert from 1000 t to TJ. Like this the former (in 1000 t) product-wise input-output-balance is not exactly maintained. The differences between input and output in TJ then need to be included as"statistical discrepancies". Consequently, the objective of this procedure is to calculate these values and include the statistical differences in the final PEFA tables, in order to maintain the balances.

7.2Calculation

The calculation of the “statistical discrepancies” is initiated by pressing the button “Balance” in sheet MAIN. This macro should be used after “Convert”, since the latter erases and recalculates sheet FINAL, so it would delete the additional lines to be included as statistical discrepancy. The calculationproceeds as described below:

a)Worksheet BALANCEDEF includes the “instructions” needed for the calculation (mainly, a “1” or “-1” list which gives the position on the equation to calculate the supply-use balance; the zeros are applied when “Item1” is not to be considered for the balance). Column F includes the number of occurrences, which may beused for validation;