Hands-on Introduction
The purpose of this section is to introduce NA Builder to first-time users interested in working with supply use tables (SUT). This first case will start from scratch and set up some elementary data structures which have been specified in advance.
To work through this case, the following files should be available:
Course_Data.xlsx
Course_example.xlsx
Course_Settings.xlsx
These files should be contained in the folder “\Course”. This folder should be contained in the folder where the NA Builder application is present.
Case 1 - Setting Up a New Framework for the Supply Table
Make sure the following directory and files exist (the version number - here 0.998 - could be different):
Starting the application
Open NA_Builder_0.998.xlsb in EXCEL (version 2007 or later; make sure there are no other Excel workbooks open). Because this workbook contains macro’s you will be asked to enabled them:
Click on “Enable content”; you will see the following screen
Note the following:
The application as shown here is password protected (“Partial locked”, “Hidden”); Note: NA Builder can be obtained in three versions: 1. unsecure = unlocked, not hidden, settings files unprotected, help unprotected; 2. weakly secure = partially locked, hidden, settings files unprotected, help protected; 3. strongly secure = locked, hidden, settings files protected, help protected)
The application is empty (no NA framework specified)
Next, click on the Start button, you will now go to the following screen (the “switchboard”), from which all main application functions are controlled:
Capturing an existing Excel table
Let us use the “table capture” feature to quickly create a simple NA framework, a supply table. To do this open the workbook “Course_example.xlsx” and copy the table on sheet “Sup1”:
In NA Builder go to the sheet “Scrt” (from “Scratchpad”, an empty sheet that is used by the program for various tasks), and paste the table with the left upper corner (a cell with the value “Sup1”) in cell A1. The table name is “Sup1” and the suffix “[2,2]” indicates 2 header and 2 stub rows (could be different; just “Sup1” would indicate the default 1 stub, 1 header case).
Press <ctrl>e (i.e. the <control> and the <e> button at the same time). The program will ask the following:
This means that it will take the table with 18 rows and 13 columns and “translate” it into a template “tSup1” (and create the proper classifications) and set up an implementation with the name “Sup1” on the basis of this template. Click on “Yes” to do this.
Next the program will ask if it needs to import the data contained in the table as well:
Again, click on “Yes”.
NA Builder has created the following table (in the sheet “Sup1”):
This sheet is specified on the “Impl” sheet (containing the sheet implementations):
Note that this is a simple data sheet, based on the template “tSup1”; we can inspect this template on the “Templ” sheet (containing the template definitions):
Note that this template has 2*6 = 12 partitions, of which 3 data partitions (blue) and 9 formula partitions (yellow). Each partition has a name, e.g. the first data partition is called “Data1” (the “output” table) and the first formula partition (on the first row) is called “t1” (output totals). Note that the full entry for this formula partition is: “t1 'TotA' 'Domestic supply (basic prices)' : <Data1>”. The two entries between single apostrophes are labels which will appear in the yellow formula rows / columns; the part after the colon (”<Data1>”) is the instruction for the program to create totals for the partition “Data1”.
The actual partitions are defined on the “Part” sheet:
For the partition “Data1” we see that the automatic capture featrue has generated a header classification “tSup1_Clas1” and a stub classification “tSup1_Clas4”. Place the cell pointer on one of these calssifications and click on “Go to clas.”. On the sheet “Clas” you can see the classifications:
Click on “Go to clas. items” to inspect the classification items (on the class items sheet “ClasIt”):
Note that each item has a numerical value (which determines the sort order), a code (single label without spaces) and two text labels (e.g. for the description in English and a local language).
Note also that a “summary representation” of the table has been set up in the sheet “Aggr” (short for “Aggregations) where for each partition its single total is given:
So total output = 1948, as can be checked in the actual table. Note that the partition names are given as comments in the partition cells.
Modifying the structure
So far the whole process has been fully automated. The next step is to modify this automatically generated structure. Let us do some examples. First let us save the data in the table. Go to the sheet “DataOut” and click on the button “Save framework to DataOut”:
Note that the data are “normalized” (i.e. a single value column, with the other columns specifying: sheet name, partition name, and row and column classification codes), and the there are 45 “records” with a total value of 2610. On the switchboard sheet “SB” specify an export file
Specify file name and path in the blue cells (click on “…” to get a file selection dialog, or type directly in the cells; clicking on the “p” button will add the path where the application is stored). Use the name “NA_Builder_data.xlsx”. Once specified click on “Export DataOut”, to actually save the data. Open the new file to inspect the result.
Next go to the “Impl” sheet and click on the cell with the implemented table (which is yellow, meaining it is implemented). Then click on “Remove sheet”.
By doing this you will loose the actual sheet with all its data. Note that the cell will go from yellow to white. Set the table up again by clicking on “Set up sheet”. Go to the table (use the keyboard shortcut <ctrl>Q when the cell pointer is on the name of the table). Note that the table has been made, but the data are still missing. Getting the data again is a two step process:
1. Get the normalized data in the sheet “DataIn”
2. Click on the button “Load DataIn into framework”
The first step can be done with simple copy / paste. Open the data file you created earlier, select the records (without the header) and click on the “Paste” button on the “DataIn” sheet. Then carry out step 2 by clicking on the button “Load DataIn into framework”. Check that indeed the same data are now back in the “Sup1” sheet.
Let us now create a second data sheet, based on the same template. Go to the “Impl” sheet, and click on the “Copy” button:
Click on “Yes”.
Go to the sheet “Sup1_1” that has been made.
Next we will make a new sheet (based on the same template as the other sheets) that adds together the two data sheets. On the “Impl” sheet, and click on the “+” button:
Type the name “SupT”, select the template “tSup1” and select the type “Calc”. Next, click on the “Links” tab. Here you can specify the “sheet calculation”: build up the desired formula in the “Calc formula” box; sheet names can be added by selecting them from the combo box and clicking on “Add to formula”.
When the formula is finished (it should be: “[Sup1]+[Sup1_1]”) click on the “Save” button. Then click on the button “Set up sheet”. Verify that sheet “SupT” has been created with the correct formulas in place.
This way the framework can be extended by building up more sheets.
You can save the framework (consisting of the three sheets “Sup1”, “Sup1_1” and “SupT”) by specifying name and path on the sheet “SB” and then clicking on “Export framework”:
Also, you can save the framework specification (templates, partitions, classifications etc.) to a “settings file”, by specifying a settings file name and path (and description), and then clicking on the “Save settings” button:
To test this, remove the existing framework (the three sheets) by clicking on “Remove existing framework” and then removing the framework definitions by clicking on “Remove all settings and data”:
Check that the application is empty.
Next load the settings again by clicking on the button “Load settings”, followed by “Create framework”. Of course there are not yet any data. You can copy / paste them into “DataIn” and then load them into the framework as before.