Automation

Automation facilities are an important bonus of NA Builder. The structure of a complex NA table is specified first, independent of classification details, as a “template”, of which each cell (= “partition”) stands for either a data table or a formula row, column or cell. This can be done for any NA area, but let’s take the SUT as example. Formula partitions are defined in terms of other partitons, e.g. for “total use” or for “product discrepancies” (=supply -/- use -> formulas involving more than one template are also possible). Next, classifications are assigned to the data partitions. Now there is a simplification: instead of having one template for the SUT and then two versions for 3 and 2 digits NACE / CPA, NA Builder is pragmatic and has separate templates for 3 and 2 digits, which – at classification level – are then linked via a “bridge”. No “mixing” of different classification levels occurs in individual tables.

So far, all is abstract, not a single (Excel) formula in sight. Then the templates are “implemented” in sheets (one template -> many sheets is possible). Here, NA Builder will “translate” the partition formulas and set up all the correct Excel formulas automatically. Moreover, there is also a second kind of formula (next to the partition formula), which allows one to set up non-additive formulas such as ratios. Again, these are not Excel formulas but are specified in terms of template classifications codes rather than Excel cell addresses. During implementation, these formulas are similarly “translated”. So, once a template has been “implemented” in an actual Excel sheet, Excel formulas are in place (without the user having to specify a single one), and the actual automatic recalculations use the Excel engine.

“Rules” (and “scripts” as ordered collections of rules) are another automation feature. Rules are also specified abstractly at template / classification level and will do the required calculations implemented automatically. E.g. the RAS rule could be used to carry out the final SUT belancing (possibly holding certain collections of cells “fixed”). NA Builder has many rules specifically aimed at National Accounts compilation. Whereas templates can be non-NA as well (though pre-made NA ones are available for SUT, IEA, SAM, … so that one only has to specify the classifications to get going), the power of the toolkit lies in its rules, which are in many cases dedicated to NA problem solving.

A rule carries out a specific action on a data cell range. E.g. the EMPTY rule clears the contents of a range of cells. A rule is specified via “arguments”.

Arguments can be ranges, flags (e.g. yes/no) or numeric values.

Different rules have different numbers of arguments. Some rules (such as EMPTY) can have an unlimited number of arguments (currently for practical purposes restricted to a maximum of 11).

The rules library currently (October 2020) contains the following rules:

  1. ABSORB - Distributes on a pro rata basis a given range (arg.1) over a series of other ranges (arg. 2,…); for each of the target ranges the change can be added or subtracted or the original value replaced with the "operation" parameter (operation=1,-1,3); with the "sheet" parameter the results can be written to the same range on a different sheet, keeping the original range fixed

  2. ADD - Add range 1 to range 2 and write the result in range 3; if arg.4=1 then range 2 will be subtracted from range 1

  3. CALCULATE - Set up EXCEL formulas in a given range (arg.1); the actual formula needs to be set up first in the formula list and then copied (with <ctrl>C) and pasted (in edit mode, with <ctrl>V) to arg.2; with the "copy down" and "copy to the right" parameters ()arg.3, arg. 4) the formula can be copied to an arbitrary contiguous range; with arg.5 formulas can be converted to values

  4. COMPARE - Given ranges for current values in periods T-1 and T, and a range with values in T in prices of T-1 ("constant values"), this rule sets up implicit value, volume and price indexes referenced to T-1

  5. COPY - Copies a given range to another range; the target range can be the same range; with the "convert to values" parameter formulas are converted to values; with the "move" parameter the original range is removed (ranges should be different here); convert to values in combination with move is not allowed

  6. CROSSCUT - Converts the cells of the range given in arg.1 into the stub (arg.3=1) or header (arg.3=2) in a new sheet with the name given in arg.2; these cells will be coded in the following triples: <partition name>|<row code>|<column code>, with the codes coming from the template of the sheet that contained the cells; the other axis will contain the sheet names of all sheets that share the same template, except for those sheets listed in arg.6, arg.7,...; if arg.4 contains a number n (n=1,2,3,...) a sum formula will be set up after n rows/columns; if arg.5=1 then corresponding FOLD / UNFOLD rules will be set up when the crosscut rule is saved, enabling the data migration between the included sheets and the new crosscut sheet

  7. DATALOAD - Loads all records in sheet DataIn or DataOut into any other sheet in the framework

  8. DIAG - Take a row or column and places their values on the diagonal of a square table

  9. DENTON - This rule implements the proportional Denton method, using the solution given in Annex 6.3 of the Quarterly National Accounts Manual, IMF, 2001

  10. DISAGGR - This rule will enable data at a particular level of aggregation (say NACE / CPA 2-digit) to be disaggregated proportionally to a lower level of aggregation (say NACE / CPA 3-digit), using the correspondences in the bridge table that connects both levels of aggregation; the bridged sheets are in arg.2 (aggr.) and arg.3 (details); the range to disaggregate is in arg.1.; the results will go to arg.4.; the parameters in arg.5 and arg.6 are the same as in the pro rate rule.

  11. DIVIDE - Divide range 1 by range 2 and write the result in range 3

  12. DISTRIBUTE - Distributes the values in the range in arg.1 either vertically (arg.3 = 1) or horizontally (arg.3 = 2); results will be written to the range in arg.2; the results will be multiplied with the scalefactor in arg.4; if arg.5 = 1 then the results will be set up as formulas

  13. EMPTY - Makes empty all cells in a series of ranges

  14. EMPTYSHEET - Makes empty all cells in all partitions of a series of sheets

  15. FILEREAD - Carries out a single file import using the filespecification in arg.1; if a column number is specified in arg.2 then the column nr. for the values column (column operation 5) will be set to this column; if arg.3 = 1 then the imported data in sheet DataIn will not be written to the framework; if arg.4 = 1 then the sheet DataIn will not be emptied before the import

  16. FOLD - Converts the values from a set of sheets with a common template (of the sheet given in arg.1) into one single sheet given in arg.2; This single sheet must have the sheet names in column B or row 2; the other row / column must have as codes the following triples: <partition name>|<row code>|<column code>, with the codes coming from the template of the sheets that are folded; these triples can be created with <ctrl>Q on multi-selected cells in the sheet Scrt; if arg.3=1 then the values are appended, otherwise they replace the originals; if arg.4=1 then formulas linking to the sheet cells will be set up

  17. FORMULA - Sets up the formula(s) with the specified name(s) in column A of sheet Frml

  18. INDEX - Calculates indexes from the values in the range in arg.1 either vertically (arg.3 = 1) or horizontally (arg.3 = 2); results will be written to the range in arg.2; the lag of the index is in arg.4 (default 1); the offset for the reference period (=100) is in arg.6 (default 1); the length of the reference period is in arg.5 (default 1); the results will be multiplied with the scalefactor in arg.7; if arg.8 = 1 then the results will be set up as formulas

  19. IO - Given ranges for output, intermediate consumption (IC), final demand (FD) and value added (VA) this rule will calculate IC, FD and VA according to 6 possible methods; 1 = Product table, product technology assumption; 2 = Product table, industry technology assumption; 3 = Industry table, assumption of fixed industry sales structures; 4 = Industry table, assumption of fixed product sales structures; 5= Product table, hybrid product technology assumption (combination of 1. and 2.); 6 = Product table using Almon method

  20. LABEL - Adds a series of labels (arg.2,3…) to a range (if the range has more cells than there are labels specified, the label series will be repeated); the label “\n[I,j]” will add the value of sheet Info, range C18, row offset i-1, column offset j-1

  21. MULTIPLY - Multiplies range 1 by range 2 and write the result in range 3

  22. NORMALISE - Writes data from the specified sheets to sheet DataOut

  23. PARAM - This rule is used to change: 1. parameters, 2. file specifications, 3. sheet specifications, 4. rule specifications; 1. Change a parameter value in sheet SB: ColWidth=1, NrDec=2, Sep=3, AppendMode=4, CalcMode=5, HideMode=6, AddLog=7, VwMode=8, VwZero=9, CtrlE=10, Setup=11, CompMode=12, CaptMode=13, Message=17 (user input asked), Range=18 (user input asked), Export file name=51, Export file path=52 (example: SIMPLE, rule 23); 2. Change file specification in sheet Files: path=61, file=62, sheet=63, range=64; 3. Change sheet specification for external files in sheet Impl: path+file=71, sheet=72; 4. Change rule specification in sheet Rls: Arg1=81, Arg2=82, Arg3=83, Arg4=84, Arg5=85, Arg6=86, Arg7=87, Arg8=88, Arg9=89, Arg10=90, Arg11=91;Note: if arg2="msg" then the SB sheet message cell is used;if arg2="rng" then the SB sheet captured range cell is used

  24. PROJECT - Writes the row / column / table totals of a range to a row / column / cell; example: SIMPLE, rule 16; if arg3 contains a code or index (starting with "idx_") then an individual row / column / cell is taken; if the projection is a single cell then arg4 and arg5 can be used to update a file / sheet / rule specification as for the PARAM rule

  25. PRORATE - Distributes on a pro rata basis a given range (arg.1) using the distribution in arg.2 to a third range (arg.3); in case arg.3 is not specified the results will go to the range in arg.1; arg. 4 specifies what will happen with the results ( 1=add,-1=subtract,2=add differences,-2=subtract differences,3=replace with differences); when arg.5 is set to 1 results are subtracted from arg.1

  26. RAISE - Calculates rf*(sf + scf*data)), with rf = raising factor, sf = shift factor and scf = scale factor

  27. RANGESAVE - Saves the data from range(s) to DataOut; DataOut can be exported; the export file name can be set with the PARAM rule

  28. RAS - Applies the "RAS" method (interpreted here as an iterative pro rate) to a table (arg.1) to given row (arg.2) and column (arg.3) totals, such that the sum of squared differences between the row/column totals of the table and the given totals becomes minimal; arg.6 = nr. of iterations (-1=only rows, -2=only columns, even -> first row then column, odd -> first column then row); arg.7 = convergence limit; arg.4 may contain a table with the same dimensions as arg.1 where non zero entries are interpreted as "fixing" the corresponding entries in the arg.1 table (i.e. these will not be changed by RAS); if arg.5 is specified the RASsed table will be written here, otherwise it will overwrite the original table; when arg.8=1 the table will be written if not converged; when arg.9=1 changes with respect to the original table will be written; if arg10=1 the Generalized RAS (GRAS) method will be used, which will also work with negative values, here it is also possible to use the SHOWSCRT parameter to show the formulas set up on the Scrt sheet; arg11 can be used to extend the ranges with extra columns and / or rows

  29. RECODE - Applies the bridge in arg3 to the data in arg1 and writes the results in arg2

  30. ROUND - Rounds a range

  31. SHEET - Sets up or removes the sheet(s) with the specified name(s) in column A of sheet Impl; for sheet type = "Ext" sheets the rule can be used to label data cells (=3) or to remove labeled data cells (=4)

  32. SHEETCOPY - Copies data from one sheet to another; the original sheet can be an aggregation sheet; the destination sheet must be a sheet of type data

  33. SHEETSAVE - Saves the data from sheet(s) to DataOut; DataOut can be exported; the export file name can be set with the PARAM rule

  34. TRANSPOSE - Transposes a given range to another (different) range; with the formulas parameter links to the old values will be set up; the ranges must be of consistent size

  35. UNFOLD - This rule carries out the reverse of the FOLD rule, i.e. it takes the values in the arg.2 sheet, coded in header / stub by sheet names / code triples (or vice versa) and appends or replaces them in the designated sheet + cell; arg.2=1 appends the values, instead of the default replacement

  36. VIEW - Sets up a named (linked) view for the range in arg.2