Excel

Please note: This page is currently being updated.

This article is a stub.

What is an Excel file

An Excel file is a file, consisting of one or multiple tables of values, generally used in business environments for administration and calculation purposes. A single file can consist of multiple sheets. Each sheet is a grid of cells, each possibly containing either a value or a formula.

Excel files can be created and edited using a variety of programs. The most obvious program is by using Microsoft Office's Excel. Alternative programs include OpenOffice or LibreOffice, although minor differences in both use and calculation can occur, and support for these differences may be limited.

For the Tygron Engine, the file format .xlsx is specifically required.

How do Excel files relate to the Tygron Engine

One of the core functions of the Tygron Engine is to calculate prospective effects of actions and scenarios. Depending on the case being explored, what exactly to calculate and the precise method of calculation can vary. To provide the greatest freedom in the ability to configure a project with relevant, accurate, and third-party approved calculation models, the Tygron Engine allows some calculation to be made in Excel files. Most notably indicators can easily be created or adjusted by inspecting and editing the underlying Excel file. By defining specific cells to act as input and output for the calculation, the Tygron Engine can perform calculations using user-defined Excel files.

General structure of Excel files

Excel files, in general, have three parts to them: input, calculation, and output. The input and output are "connected" to the Tygron Engine. The contents of each input cell is changed by the Tygron Engine to reflect some form of data from the project. Each output cell is eventually read by the Tygron Engine, and its content used in some fashion. Input cells, and some of the output cells, are defined using TQL.

Cell types

Cells can be divided into 4 categories: Output, Input, Calculation, and Unused cells.

Output

In this image the cell B2 is named "EXPLANATION". This serves as an output cell. For its content it refers to a different cell.

The most important part the Excelfile is the output. At least one cell in the file is required to be an output cell, the content of which the Tygron Engine can use during a session. In each excel file, one of the cells must be named EXPLANATION. Excel Indicators and Zone Excels have a number of other output terms available for use. More output cells can be defined using TQL's UPDATE statements.

Input

In this image the cell B4 is named "SELECT_UNITS_WHERE_STATE_IS_READY". This serves as an input cell. It has a value, which will be overwritten by the engine.

Next to the output cells, the input cells are most important. You will generally want your Excel file to produce different outputs based on the current state of the project or session. Input cells are always defined using TQL. When the Excel file is uploaded and used by the Tygron Engine, the content of the input cells will be overwritten by the Engine with the proper, requested value.

Calculation

In this image the cell D3 contains a formula. It's not directly overwritten by the engine, but the results change based on input from the engine. It is referred to by an output cell, so this is a calculation cell.

Lastly, there are the calculation cells. It's likely you don't want to output the data from the engine directly. Instead, you want to use it in some form of calculation, and display only the result, likely with some formatting. For example, you may not want to return the amount of green and the amount of houses, but the amount of green per house, rounded to a decimal or two, with some unit appended to it. Any cell, even empty cells, are deemed to be calculation cells if they are referenced by an output cell, because their contents are calculated (evaluated) to determine what the output will be.

Unused cells

In this image the cell C3 is empty and unnamed, so it is not an input or output cell. It is also not referred to, so its an unused cell.

Lastly there are unused cells. These are cells which are not input cells, and are not referenced directly or indirectly by output cells. Empty cells, when referenced directly or indirectly by output cells, are evaluated, and thus seen as used. Unused cells can contain values or even formulas, or even reference input cells. Unused cells with formulas can be useful when creating or inspecting excel sheets, to provide insight in how the calculation works.

Naming cells

The functioning of Excel files in the Tygron Engine hinges on the named cell functionality in Excel files. Input cells and Output cells require names to be set before they function as such. If the required output cells don't exist (for example, no cells are named EXPLANATION), the Excel file will not function as expected in the Tygron Engine. To set a cell name in an Excel file, select the cell and enter its name in the name field (just under the ribbon).

How to name a cell:

1. Open the Excel file in Microsoft Office Excel
2. Select the cell you wish to use as output cell
3. In the top left of the window, find the "Name" field (just under the ribbon)
4. Enter the name for the cell (for example: EXPLANATION)
5. Press "Enter"

Correcting and removing cell names

Where to find the Name Manager.

The Name Manager.

There may be a number of reasons to correct the naming of a cell:

• The cell is no longer required
• The cell no longer exists
• The cell has been given multiple names
• The same name has been applied to multiple cells, or a range of cells
• The name of the cell is no longer correct, and needs to be changed

To correct issues like these, you can use the Name Manager in Microsoft Office Excel. You can find the Name Manager under the "Formulas" tab in the ribbon.

The Name manager displays a list of all assigned names in the Excel file, as well as what cells they refer to, and what the current value of those cells are.

To edit a cell name, or change what cells are referred to by that name, select it, and select "Edit". The "Edit Name" panel will appear, where you can enter a new name, as well as redefine to what cell the name refers.

To delete one or multiple names, select them in the Name Manager, and select "Delete". You will be asked to confirm that action.

Using Excel files in the Tygron Engine

Excel files are used in a number of places in the Tygron Engine, including Indicators, Zones, and Panels. To allow for the reuse of Excel files, the Tygron Engine presents an Excel File Manager, in which Excel files can be uploaded, replaced, and selected for use.

Uploading and Downloading

When, in the Editor, an item is selected which uses an Excel file, it will present you with the option to "Select Excelsheet". This will open the Excel File Manager.

How to select an Excel File:

1. Open the Excel File Manager
2. Select the file you wish to use
3. Select "Apply"

How to download an Excel File:

1. Open the Excel File Manager
2. Select the file you wish to use
3. Select the "Download" option
4. Select the location to save the file and select "Save"
5. Select "Cancel" in the Excel File Manager to close it

How to import a new Excel File:

1. Open the Excel File Manager
2. Select "Import new Excelsheet"
3. Browse to the Excel file you wish to import and select "Open"
4. Select the file you wish to use (which may or may not be the imported Excel) in the Excel File Manager
5. Select "Apply"

How to replace an Excel File:

1. Open the Excel File Manager
2. Select the file you wish to replace
3. Select the "Update" option
4. Browse to the Excel file you wish to import and select "Open"
5. Select the file you wish to use (which may or may not be the replaced Excel) in the Excel File Manager
6. Select "Apply"

Debugging

Sometimes, when you look at the output of an Excel-based calculation in the engine, you may not directly recognize how a certain result was achieved. As more complex Excel files are uploaded, this may occur more often. At these times, it may be desirable to see how the Excel file has been filled by the Tygron Engine, and how these values are treated by the Excel file's calculation. This can be done by downloading the Excel file using the "Debug Excelsheet" option. This will allow you to download the Excel file with the current values filled in.

After download, you can open the Excel File in Microsoft Office Excel. You will see that all input cells are filled with values from the Engine. Inspecting the output cells and tracing back through your calculation will allow you to determine how certain values came to be, based on the provided input.

It is important to note that the downloaded file may have lost some formatting in the sheet, as well as contain some project-specific adjustments when "X" or "ID" queries were used. For this reason, we advise not to re-upload Excel files which were downloaded with current values, even when they require a change altered. Instead, download the original file using the Excel File Manager, adjust it, and re-upload that version.

How to debug an Excel File:

1. Select the item with the relevant Excel File in the editor
2. Select "Debug Excelsheet"
3. Select the location to save the file and select "Save"
4. Download the original Excel file as well
5. Open the Excel file with the current values and step through it to find any issues
6. Make any desired changes in the original Excel file
7. Upload the modified Excel file

Advanced Excel techniques

Besides the basics described above, a number of more advanced tricks and functionalities exist.

Maptype postfixes

The name of this cell is appended with _CURRENT, and the other with _MAQUETTE.

Non-query output cells use a word or phrase to indicate that the value should be output in some fashion. By default these values are used for both the current and maquette views in the Tygron Engine. However, in some cases t may be desirable to output different scores for the current state and the maquette state of the project. In these cases, the cell names can be expanded by adding a _CURRENT or _MAQUETTE to the name of the cell. Both will need to exist to make the Excel file valid.

This functionality does not extend to query (output) cells. For input cells, a switch between map views is possible via a built-in clause.

X queries

Example of what will happen with an X query with 3 neighborhoods.

In some cases, especially when developing projects which are prone to change, or when creating templates, it may be desirable to add queries to your excel sheet which retrieve all of a certain type of thing. For example, each project can have a different amount of neighborhoods. In these cases you can't create queries for item you wish to retrieve. Instead, you can use X queries to instruct the engine to dynamically create the queries for you. For any given query with uses an ID, you can replace the ID with X, with the result being a query such as SELECT_UNITS_WHERE_NEIGHBORHOOD_IS_1 turning into SELECT_UNITS_WHERE_NEIGHBORHOOD_IS_X. Only one X term may exist in a query, but you may add multiple X queries to an Excel file.

When an Excel file with one or more X queries is uploaded, the Tygron Engine prepares it for calculation by transforming the single X query into a column of queries which refer to specific IDs. When multiple X queries have their X refer to the same type of element from the project, the amount and order in which the IDs are filled in are consistent. The created queries are placed in the same column as the original X query, starting in the cell directly below the X query and continuing downward. The X queries are then removed. In other words, when using an X query, the first cell in that column with the intended query will be the cell under the X query.

The original Excel file is saved by the Tygron Engine. When the original file is downloaded, it will be the file with the X queries, before the exact queries were generated from them. When the file with the current values is downloaded, it will contain the generated queries, and not the X queries themselves. It is recommended that you don't reupload the file with current values, because it is no longer generically applicable.

TODO

Filtering data, HTML, combining text, VARs, X queries, ID queries, _CURRENT and _MAQUETTE postfixes, colors in attributes, colors in HTML/CSS

Excel tips

Performance, colors in excel formatting, using sheets, possible issues