The application

Introduction

The aim of NA Builder is to systematically describe and store National Account (NA) tables as "meta data" in a way that is independent of Excel column letters and row numbers. If we have the following fragment of a large table:

The number 33516 is stored in Excel cell X27. However, suppose that there is a new item added on row 27 and the number moves down one row:

Then the same number would be in cell X28. Wouldn't it be nice if this number could be stored in a way that is independent of the Excel address? This is what NA Builder indeed does. It stores this number in the cell containing codes: (CPA_C10T12; A01). But now suppose there are further data on the same row in a different column but with the same column code:

The number 994 would have to be coded as (CPA_C10T12; A01) as well which would make the coding ambiguous. NA Builder therefore introduces an extra dimension to accomodate these differences: the "partition". Suppose we use the partition "P2A" for the left part of the table containing the number 33516 and the partition "P2B" for the part of the table containing the number 994. The two numbers would then be coded as:

• (P2A; CPA_C10T12; A01)

• (P2B; CPA_C10T12; A01)

But now suppose this table is for the year 2017 and we have the same table for 2018 on another sheet as well with different numbers:

The number 1073 would then be coded as the number 994 (P2B; CPA_C10T12; A01) leading again to ambiguous coding. NA Builder therefore adds another dimension: the "sheet". Suppose the first table is on the sheet "Y2017" and the second table on sheet "Y2018". The three numbers introduced so far would then be coded as:

• (Y2017; P2A; CPA_C10T12; A01)

• (Y2017; P2B; CPA_C10T12; A01)

• (Y2018; P2B; CPA_C10T12; A01)

However, for specifying the table details the sheet code seems redundant since same table structure is used on both sheets. So for specifying the table the partition, row and column codes seem sufficient. But we would like to make the table independent of the particular codes used for the rows and columns. As an example, the same table could be specified for a different set of CPA codes:

Na Builder accomodates this possibility by separating the table structure (the "template") from the classifications which are "attached" to the structure. For the above example the template T could be specified as:

The partition "P2" stands for the table with on the "header" the activities A01, A02, ... and on the "stub" the products CPA_B09, CPA_C10T12, ... where these codes will now be collected in classifications "Act" and "Prod". These classifications are then attached to template T to produce the actual table:

Other classifications can be attached as well, making the table structure independent of a particular choice of classifications. Note that this table will also have row totals, column totals and a table total to the right / below the table because these have been defined in the yellow cells of the template. Moreover, once the classifications are attached, there must be sheets on which the template is "implemented":

It is in the sheets where data are entered and the above 4-part coding applies. Moreover, given this way of coding, data storage can be done efficiently as lists of records, each containing the codes for sheet, partition, row and column as well as the actual values:

Na Builder allows for the efficient conversion between these data lists and the implemented tables in sheets.

Once numbers are in such data sheets NA Builder can present them in various ways:

Such derived sheets containing particular views of the data or by a time-series presentation of the data can be set up automatically. Data edits can be made directly to such derived sheets. The program will ensure that the correct changes will be made to the underlying data cells.

In summary, NA Builder is an application for describing tables as arrangements of partitions, each defined in terms of classifications. Such arrangements are called templates and they are the blueprints for the actual tables which are set up in one or more sheets. There are tools in the application for building, editing and storing classifications, partitions, templates and tables. Also, there are tools for importing data into the tables and for exporting them from the tables. The natural way for data storage and import / export is by the above 4-part coding. Finally, there are many features in the application to view at the same data in different ways and making edits to the data from whatever view is most convenient. Such edits can be manual or "rule-based", using particular rules available in the application to automate complex tasks. Such rules are programs in themselves and more rules can be added relatively easily if the need arises.

The Application Interface

The NA Builder application start screen looks as follows (on sheet "Info" in the Excel workbook):

After clicking on the "Start" button the main "switchboard" sheet (abbreviated as "SB") becomes available:

It is from this sheet that most of the program actions can be undertaken; these are typically implemented on separate sheets:

These application sheets can be categorized as follows:

1. Structure building (i.e. abstract objects that are not yet part of an Excel table)

• Templ - Templates

• Part - Partitions

• Clas - Classifications

• ClasIt - Classification items

• Brdg - Bridges

• Cor - Correspondences

• Frml - Formulas

2. Implementations (The part where the above abstract objects become "implemented" into Excel tables)

• Impl - Implementations

• Aggr - Aggregations of set up sheets

• Dict - Dictionaries

• Vw - Views

3. Data input and output

• Files - Specification of external data

• DataIn - Data going into framework

• DataOut - Data coming out of framework

4. Automation

• Rls - Rules

• ScptRls - Script rule assignments

• Scpt - Scripts

5. Program sheets

• Log - Program messages

• Scrt - Scratchpad

When using the application one is in Excel itself and one has all Excel functionality available. This has both advantages and disadvantages. The objective of the application is to systematically describe (at meta level) NA tables, using NA codes, in a way that is independent of Excel column letters and row numbers. These descriptions are contained in the above system sheets, and one needs to follow the application procedures to work with them. But once a table is implemented in a non-system sheet a big advantage is that one is relatively free to use such a sheet in any way one likes in the familiar Excel environment. Of course there is plenty of useful NA Builder functionality available also for working with actual tables but one is not forced to use this.

A disadvantage of this close connection with Excel is that it is difficult to present an integrated application user environment in the usual sense. For example, there is no application menu nor are there contextual popup menu when right-clicking. Although it is technically possible to integrate to a certain extent with Excel this may lead to all sorts of potential errors and compatibility issues. Hence it was a conscious design decision not to do so. The only way to interact with the application is by clicking on one of the available buttons, or by using one of the supported "<ctrl> + letter" keyboard shortcuts. But here is a potential conflict with Excel because it also uses many such shortcuts. Therefore the number of allowed shortcuts has been kept to a minimum: <ctrl>Q, <ctrl>W, <ctrl>E and <ctrl>R (the top leftmost four keys on the QWERTY keyboard). Especially <ctrl>Q is meant to be a universal shortcut, to allow for quick navigation, for changing application settings, for initiating actions depending on the context, etc. The <ctrl>E is the main data entry shortcut, <Ctrl>W is for setting up special views and <ctrl>R is for recalculation of a special class of sheets ("CalcM sheets"). A consequence of this objective to keep the number of shortcut keys to a minimum is that the functionality of <ctrl>E and <ctrl>R can change depending on the application settings. These can be controlled using the <ctrl>Q shortcut as well, making this shortcut indeed the principle way of interacting with the application outside of the buttons.

Another disadvantage is that, when working with the application, one is not supposed to have other Excel workbooks open at the same time. Data import and export features are available in the application and when data are contained in other Excel workbooks the interaction with these workbooks will be under control of the application. An objective of the application is to store NA data as lists of records, with one value per record, and a number of codes to describe these values. These records can then be kept in a real database or in Excel itself, now used as database. It will therefore never be the case that a table in the application is linked to other Excel workbooks that need to be open at the same time. In practice this restriction rarely applies and one is often allowed to have other Excel workbooks open as well. When there is a potential conflict, the application will inform the user and ask for the closure of any other open workbooks.

One may think that storing a program with the complex functionality that NA Builder has in the same workbook which also contains all the implemented tables is a disadvantage. Wouldn't it make more sense to have the program work with separate workbooks so that the application would never contain more than the above system sheets? Perhaps yes, but this would make the application even more complex and prone to all sorts of additional synchronisation errors. It was another design decision to keep the system architecture as simple as possible, and this implied that meta data (table descriptions) and data (tables) be kept in the same physical workbook.

Since the above system sheets contain variable number of records isn't there a danger that such sheets may "wear out" over time and cause errors or slow down performance? Yes, this is indeed one of the downsides of working in Excel (as compared to a real database). For this reason there is a "repair" option (on the "SB" system sheet) which can create new versions of any (or all) system sheet(s) at any time, the needed layout information being part of the program. The only exceptions are the "Info" and "SB" sheets, but these do not contain any meta data. Also, the meta data itself can be stored in "settings" workbooks, which can at any time be generated or from which existing meta data can be loaded. And the actual tables can always be regenerated from the meta data at any time, with the data loaded again from the data files.

Finally, the main advantage of the NA Builder approach is that particular NA applications such as a SUT compilation system can be designed and tested on small test classifications, and then reused for different sets of real-life big classifications.

Further Information

More information on the design of the application can be found here

More information on the automation aspects of the application can be found here

Some hands-on cases to introduce NA Builder to first-time users interested in working with supply use tables (SUT) have also been included.

• The first case will start from scratch and set up some elementary data structures which have been specified in advance; it can be found here

• The second case will set up a real (small) SUT; it can be found here

Below follow presentations on various aspects of the program.

Here is a presentation on structure:

Here is a presentation on implementations:

Here is a presentation on the data import and export facilities:

Here is a presentation on data editing:

Here is a presentation on rules (the central building block for data editing automation):

Here is a presentation on the database back-end:

Finally, here is a presentation on some other program features: