Excel
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.
Excels can also output text to provide information about the current state of the Session or provide insight into the calculation of some value. To make information provided in this fashion clear and understandable to a user, the output can be styled with HTML. The Tygron Engine will interpret the text output of an Excel file as HTML if possible.
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 Unreferenced cells.
Output cells
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 cells
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 cells
In between, 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.
Unreferenced cells
Lastly there are unreferenced 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. Unreferenced cells can contain values or even formulas, or even reference input cells. Unreferenced 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).
- Open the Excel file in Microsoft Office Excel
- Select the cell you wish to use as output cell
- In the top left of the window, find the "Name" field (just under the ribbon)
- Enter the name for the cell (for example:
EXPLANATION) - Press "Enter"
Correcting and removing cell names
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.
- Open the Excel File Manager
- Select the file you wish to use
- Select "Apply"
- Open the Excel File Manager
- Select the file you wish to use
- Select the "Download" option
- Select the location to save the file and select "Save"
- Select "Cancel" in the Excel File Manager to close it
- Open the Excel File Manager
- Select "Import new Excelsheet"
- Browse to the Excel file you wish to import and select "Open"
- Select the file you wish to use (which may or may not be the imported Excel) in the Excel File Manager
- Select "Apply"
- Open the Excel File Manager
- Select the file you wish to replace
- Select the "Update" option
- Browse to the Excel file you wish to import and select "Open"
- Select the file you wish to use (which may or may not be the replaced Excel) in the Excel File Manager
- 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.
- Select the item with the relevant Excel File in the editor
- Select "Debug Excelsheet"
- Select the location to save the file and select "Save"
- Download the original Excel file as well
- Open the Excel file with the current values and step through it to find any issues
- Make any desired changes in the original Excel file
- Upload the modified original Excel file
Advanced Excel Functionalities
Besides the basics described above, a number of more advanced functionalities exist.
ID queries
ID in the query will be replaced with the specific AREA the file is applied to, and thus the name of the AREA concerned is retrieved.Excel files can be used for general calculations which are basically predictable. You always know which elements in the world you want to retrieve or manipulate. However, in some cases, you may not know that beforehand. Examples are when an Excel is used for a Panel Template or a Zoning permit calculation. In these situations, you may likely want a query which refers to whatever element the Excel is applied to. This can be done using an ID query. For any given query with uses an ID, the ID can be replaced with ID, with the result being a query such as SELECT_UNITS_WHERE_BUILDING_IS_1 turning into SELECT_UNITS_WHERE_BUILDING_IS_ID. Only one ID term may exist in a query, but multiple ID queries may exist in an Excel file.
X Queries
In some cases, especially when developing projects which are prone to change, or when creating project 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 all the items 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. Each cell which now has a query will be properly filled in by the Engine. Note that not every cell in the column is given a query. If there are more cells with values than are overwritten by queries, those excess cells retain their value. From this point on, the file is used as normal.
The original Excel file is saved by the Tygron Engine. When a change occurs which would affect how many and which queries should be generated from an X query, it's possible the Excel file needs to be re-prepared for use in the Engine. It is possible to trigger the Engine to do this, by using the "Reset X Queries" option in the editor. Template:Editor ribbon
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.
Maptype postfixes
_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, sometimes it may be desirable to output different scores for the current state and the maquette state of the project. One such situation is when the Simulation Type of the project is set to timeline. 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.
VARs
VAR_2 is ignored. However, it is calculated.In some situations, you may find that the contents output of the Excel file remains mostly constant, save for a number of numerical values. In this case, you can choose to use VAR cells, which serve as additional output cells. The values placed in these cells are read by the Tygron Engine, and inserted into the explanation output. The Tygron Engine will also format the numbers for readability. Specifically, thousands separators will be added (if needed), a decimal separator will be added (if needed), and the number will be rounded to 3 decimal digits.
To define one or multiple VAR cells, name a cell VAR_N, where N is a sequential number starting at 0 (so VAR_0, VAR_1, VAR_2, etc). To indicate where in the explanation the VARs should be inserted, you can create an insertion point by writing "%s" in the explanation. The Engine will insert each VAR, in order, into these places in the text.
Note that while it's possible to use formulas or conditional structures in your Excel to come to an explanation with zero, one, or multiple insertion points parts, tt is important to remember that the Engine will only look at the text which ends up in the EXPLANATION cell to insert VARs. If you remove a segment of the explanation which contained an insertion point, the VARs which used to be inserted there will be inserted in the next ones instead. If this is undesirable, either keep the insertion points for the VARs in the text, or restructure the VAR cells in the excel so that the correct values are inserted in the correct locations.
Also note that the Engine will ignore additional VAR cells if there are not enough insertion points (although the cells will still be evaluated), but that the Engine will be unable to display the explanation properly if there are too many insertion points. If that is the case, an error will be displayed instead of the proper explanation.
Lastly, when using VARs, the Engine will check the output text for any "%" symbols. If you wish to use a literal "%" in your explanation while using texts, you can instruct the Engine to leave it as is by writing "%%" instead.
Excel tips and tricks
Besides the advanced functionalities available when using excel sheets, much of the effectiveness comes from possibilities inherent to Excel. There are a number of techniques which allow Excel files to become more functional and maintainable.
Filtering data
It is possible that, although you retrieve all elements which exist in the Engine, you only wish to perform calculations with a subset of them. . In these cases, Excel formulas can be used to determine which data should or should not be processed in your calculations. Especially functions which perform conditional sums work well for this purpose, or statements which multiply irrelevant data with "0".
Combining text
Excel does not provide a dynamic solution for appending large amounts of text. However, there is a way to put text together from consecutive rows or columns. By creating a formula that appends the contents of the current row to the contents of the next row, data from all rows can be appended together, and thus placed in the explanation in one go.
Do note that using this technique makes it very easy to create a very deep chain of commands to process by the Tygron Engine. When combining many hundreds of rows, the Engine may indicate it was unable to process the entire file due to something called a "Stack overflow". Make sure you use this combining technique only for limited amounts of rows.
Excel formatting and descriptions
When dealing with larger spreadsheets and workbooks, it is easy for the structure to become overwhelming and thus less easy to understand. For this reason, while creating Excel files, it is important to describe in the file itself what cells do, and why. Text next to or above cells to explain the reason of method of a calculation are a simply method for this. To help with creating a quick overview of the structure of sheets, you can apply borders and colors to cells, rows, and columns. When cells are provided with colors in a consistent fashion, it becomes much easier to recognize the overall structure of an Excel file.