- VBA (in Excel)
- Python, which sends plot images to Obsidian (to be uploaded soon)
This is a flexible, highly automated post-processor that I created in order to alleviate my eyes and brain from constant clicking and watching code for many consecutive hours. Once I saw how much it helps me organize my work and understood its potential, I thought it'd be a pitty not to share it.
This is a usefull tool for everyone who's work requires running numerous simulations (automotive simulations, dynamic systems etc) and wants to be able to seamessly compare results and track signal changes with respect to system parameter changes.
I am not suggesting that such a thought is unique to myself. There are already decent post-processors out there. One of them is the Data Inspector of Simulink-MATLAB. However, similar to the latter, most are only available in certain simulation environments and require payment.
The tool presented here is generic and can be merged with a significant range of external software.
- Integration with MATLAB
- Integration with Python
- Integration with GAMS
Note that ANY simulator that is able to parse textfiles (e.g. MATLAB and Python) is capable of integrating with the tool. My money is on that most, if not all, simulators that can't parse textfiles can still be integrated to this tool (more on the details later). If you happen to use any other simulator, please consider uploading your simulator - specific integration scripts and add that simulator to the above-given list.
- Have excel installed in your machine
- Be relatively comfortable with excel (no need for VBA in case you don't plan on further developing the tool)
- For the MATLAB example: have MATLAB installed
The main goals of this long-awaited tool are:
- To introduce human factors and aesthetics to simulation studies with platforms that do not come with lean and flexible post-processors in order to minimize errors, eye strain and gasps of tiredness associated with assessing, understanding and writing code for extended periods of time.
- To automate tasks related to simulation studies and simulation results organization and comparison.
It has been years since I wished to be able to
-
effortlessly navigate through numerous simulation results
- without having to search them with traditional and inefficient methods (such as folder searching, file searching, etc.)
- without the need to manually write simulation logs by noting parameter variations by hand. For instance, say you need to investigate effects of parameters you set in a simulation. Imagine having to write “parameter1: 0 --> 1” by hand for every simulation and then having to write assessment on effect of that parameter change by hand every time!
-
Minimize the strain in my eyes that is generated by going through code all day! No matter how much you like to code, a combination of asimplistic GUI with smart automations and coding only when necessary is so much more human-oriented than a command line (sorry command line fans).
Of course, if you happen to be working on an already developed software that offers an efficient and pleasing post-processor, you do not need this tool right now. But often you will need to work on projects in which you’ll need to develop scripts (or use someone else’s) that perform the calculations.
“What?? VBA?? How are you still using this old-fashioned, limited programming language in 2021??”
Ok, before you report me to the Gods of programming, don’t forget that as a user you most probably won’t need to open a single VBA script in the tool.
Since many years I needed to enrich my excel automation skills for a few projects until it became a frequent “weapon of choice”. I was astounded by the vastness of possibilities excel can offer, while making it easy to check large chunks of numbers in a clean way.
For me, excel IS, in a way, a programming language. It is not tailored to computationally intensive tasks and this is the reason that I opt to use it only for organizing files, work and make visualizations. Especially regarding visualizations, it is easy to connect simple function results with different colors and styles in excel, as well as with icons. For a person who knows how to program in VBA, excel can be transformed into an optimal user-specific GUI.
If you have never used excel in that way and are skeptical, trust me, it is worth the small effort to get yourself familiarized with some of its capabilities, at least for the scope of this tool. I tried to keep the need to modify VBA scripts to a minimum. You are, however, required to have knowledge on how to use excel without VBA.
In addition, I believe that one should prefer this tool from other means of creating apps, because coding in VBA and writing functions in excel is much easier than building web-based apps, or apps on languages such as C++ and Java. After all, this tool is meant to be used by simulation engineers, researchers and generally analyzers, not strictly by developers. It is, therefore, easier to manipulate code in an excel-based tool and tailor it to your needs, than a web-based Java app.
Do not forget that given the fact that VBA is a very old and widely used language, it is so easy to google-search solutions to problems and find more than one suggestion in various websites. The VBA community is very large and eager to provide help.
Last but not least, it is easy to create simple functionalities in excel, something that most people know how to perform, as opposed to better programming languages.
There are two possible operation modes for which this tool works.
· “Mode 1” is the mode in which the tool connects to a simulator that is capable of text-parsing (usually a scripting environment that engineers use, like MATLAB, or Python). Check Figure below.
· “Mode 2” the one in which the simulator is unable to parse text files. That is usually a closed-source software that comes with a GUI. Check Figure below.
In the “control room” sheet you can:
- Define new parameters and change their values
- Add notes to parameters that only appear when you click on the parameter itself
- Trigger a simulation
- Save a simulation’s results
- Stop a simulation
A control room that contains a few variables looks like the one in the provided example.
- Go to “project_paths” sheet. Set the following paths:
- “print parameter file”: This is the path where the parameter file is printed, ready to be parsed from your simulator (applicable for Mode 1)
- “write trigger text file”: This is the path where the trigger file is printed, ready to be parsed from your simulator (applicable for Mode 1). See next section to get info on how the trigger file works.
- “dropbox_folder_linking”: This is the path where the simulator prints some user-wanted information (such as deviations from a mean variable, computation time, etc). This user wanted information is set in a script inside the simulator
The trigger file is the "middle-man" in the communication between excel and the simulator. The way that they communicate is up to the user - developer. For example, to trigger the simulation, the excel tool writes "Runs sim: 1". The simulator reads this file instantaneously and triggers the simulation. After a fixed amount of time (which is larger than the amount of time that it takes the simulator to read the trigger file, excel writes "Runs sim: 0", which is the neutral-"no simulation" state)
Type in the name of the parameter (in the same way that it will be read from your simulator). Once written, it is automatically generated in the vault. The corresponding value should be written next to the parameter (at its right). Check the example file for clarification. ==IMPORTANT NOTE==: Parameters should ONLY be written in odd columns (e.g. “A”, “C”, “E”, etc) and never before row 6!
The simulator obtains the parameters in a proper syntax (eg MATLAB syntax, Python syntax, etc). Since so far the supported simulators are MATLAB and Python, a convenient and organized way to parse parameters is via struct variables and dictionaries correspondingly. If, for instance, you create a parameter titled “mass” and set a value of, say, “100”, then if your simulator is:
- MATLAB, the parameter file will include a line: “d.mass = 100;”
- Python, the parameter file will include a line: “d[“mass”] = 100”
Where “d” is the struct or dictionary variable that carries the parameters. You can change the name of that struct variable in the “parameter_map” sheet, cell “A2”, or variable “struct_name”.
add_new_parameter.mp4
To enhance organization of your parameters, you can create parameter categories, or “families”. To do so, simply write “fam__familyName (optional_field_name)”.
- Why write “fam__” before the name?
- So far, the program I developed understands parameter families that way
- What is “optional_field_name”?
- The parameters are written in a struct (if simulator is MATLAB) or dictionary (if simulator is Python). If in your code you want to further categorize some parameters and make it easy to read, you can have something like: “d.deb.set_to_zero_when_inf”. The field “deb” can be referring to debugging related, on/off type of parameters. In that case, you write“fam__debugging (deb)”. If you do not want such a categorization, simply set the family like this: “fam__familyName ()”.
add_note_to_parameter.mp4
- If you select a parameter cell, its notes automatically pop-up
- To add a note, click on the "
" icon at the top of your current view - To add a picture:
- Copy the picture from your source
- Paste it in the excel sheet (NOTE: note view mode should NOT be on)
- Press the "
" icon (it basically attaches it to the group of notes that correspond to this parameter)
add_image_to_parameter.mp4
That way, you won’t need to navigate to other files in order to take notes on the effects of parameters. In addition, parameters can be linked to effects of specific metrics (say, “vehicle acceleration”, “fuel consumption” etc), and you can systematically document effects of parameters on metrics.
Navigate to “parameter_map” sheet, column “C” (Picture ???). Simply go to the specific cell that contains that variable and press “Delete” from your keyboard. No need to insert anything in that list, since it is automatically generated. NOTE: You can only delete one variable at a time. Also, you have to delete that variable from the “control_room”. This command deletes all the notes of that parameter**.**
Find supported simulators (developed so far) in Appendix.
Steps (for MATLAB, more simulators to come in the future):
-
Run the “autorun.m” script in your MATLAB that reads the autorun file every (e.g., every 500ms). Leave that script running, do not interrupt it! You will have to edit this script first, to set which scripts/functions should run for each different event/trigger.
-
Go to the user interface --> "control_room" sheet and choose an action:
-
Pressing “
” icon runs a simulation with the specified parameters (excel writes to autorun file a trigger that is understood by the simulator parser, for instance: “RUN_SIM 1”. Simulator reads this and triggers the simulation process. -
Pressing “
” saves the simulation results. Press it only once the simulation is finished.
Navigate to simulation dashboard by clicking the “
” icon for the options to appear and then the “
” icon. As a default mode, some live results are parsed from a textfile when you click on a new cell.
In the example I provided for mode 1, MATLAB prints the computation time for each iteration and the word “PROBLEM” whenever a specific signal problem is encountered. In the live dashboard, the figure shows the CPU time of each iteration (blue curve) and whether a problem was encountered (red curve: 1 when “PROBLEM”).
To modify how the live dashboard works, you will have to change some cell equations in this sheet, so excel knowledge is required.
In order to view the simulation results, simply press the "
" icon in the simulation dashboard. The plots are refreshed when you press double left click on a random cell.
checking_simulation_results_while_simulation_runs.mp4
Most people are not aware just how much you can achieve with excel. One of the things that is not common knowledge about excel is that you CAN easily make it look good. In this section, you will find some tips that help you make your dashboard easier to and more pleasurable to read.
Go to: View --> Appearance and deselect the boxes, so that you don’t have to always see those column letters and row numbers and equations that occupy your “eye space” when you don’t really need them.
- Cell formatting --> Check “conditional formatting” on the internet 😉
- Format of parameter value bearing cells that contain formulas (so that you don’t just delete them).
- Copy the cell and paste as a connected icon. Then, each time the value and format of the cell changes, the same thing will happen to the icon.
- Flagging parameters with expressive icons
- For parameters you consider critical for your results, you can have the GUI flag them with icons of your preference. Simply navigate to “parameter_map” sheet à “T5” cell and add the exact name of the parameter in the list (leave no empty cells between those parameters!). Once you go back to the control room, the icon will appear next to the parameter.
- To change that icon, search for the icon named “potential_hazard__” in the “control_room” sheet, right click on it and select “change icon”.
parameter_value_change_log.mp4
For me, this is the most important feature of the tool. Its capabilities are:
- Rigorous and easy organization of parameter changes (e.g., if I change parameter “m” from 50 to 100 in the “control_room”, the tool will automatically print: <dd/mm/yyyy> m: 50 --> 100)
- Gather notes of each simulation (write it below the parameter change) and effects of parameters to specific signals and/or indexes. See Picture below for an example.
- Gather simulation results and have them available for the user when he/she wishes to view them. By pressing the “save sim” button at the control_room (after a simulation has been completed), the plots with the desired results are loaded to the “simulation_history” sheet. The user can view them by clicking once on the number of the simulation. See Picture below for an example. Information on how the simulator stores the results in Section ???.
Of course, upon preference you can apply the same functionality of integration between excel and your simulator and have your simulator, at the end of the simulation, show the plots. The script that prints lines in text files from VBA is very simple. Refer to the ??? script. NOTE: Whenever you create a new variable, simulation history also changes. If you do not want to keep those changes, simply navigate to the simulation history and manually delete them.
Again, this is up to you. The important detail to keep in mind is to maintain the continuity between the user interface and the simulator. They both organize the results using the prefix “@run” (for run 10, write "@run10"). I have placed a script titled “report_struct.m”, for the case that simulator is MATLAB.
It is possible to change the simulation parameters and trigger a new simulation from another device. I have chosen dropbox for this task, mainly for its simplicity and reliable connection to local folders.
To be able to use this functionality, first set the excel variable “trig_sim_dropbox” to 1 (excel variables). This commands the tool to scan the dropbox parameter excel file periodically and triggers a simulation if it encounters differences between parameters. Set this variable to 0 if you are not using this functionality, since it slows down the performance of the tool.
Not all devices can use VBA with excel (e.g., smartphones). So, it’d be best to be able to read some of the results (e.g., some live plots) by having the tool print them in a shared dropbox folder periodically.
- Set the excel variable “repeat_read_sim” to 1. Set this variable to 0 if you are not using this functionality, since it slows down the performance of the tool.
- charts, images: Get the name of the image or chart(s) you wish to export to the shared folder. Add this name to the “Charts to print to dropbox” category in the “project_paths” sheet. Leave no blank cells between your inputs!
If you are working on a project with a software that does not allow any coding, at least to the level of textfile parsing you applied to trigger a simulation, then there is another way.
An important prerequisite is to be able to at least use some textfiles, in the format of the specific software that you are using, in order to specify parameters (up to my experience, you can use both a gui and a text file to manipulate parameters for your simulation in a closed-source software).
You can use the parameter writing functionality of the excel tool to pass the parameters and variable names to your preferred programming environment (in case you don’t opt to use VBA) and generate a parameter file in the format that your simulator uses. That will require programming from your side since any different simulation software uses different formats.
The initiation of the task is identical to the one you have learned so far. You run the “auto_run” script. The difference here stems from the fact that instead of triggering a simulation in the same programming environment of the “auto_run” script, it prints the parameters in the simulator format. Meanwhile, the excel tool periodically checks the last-edited date of the simulator parameter file. Once it recognizes that this file has been modified, it directly opens the simulator to the user’s screen. At this point, the user triggers the simulation manually.
The excel tool periodically checks for production of result files (with already-known file names) and once it recognizes a most recent modification, it can trigger a script that parses those results and prints them in the tool.
If you have been working with a large number of simulations (so basically, a huge number of results), you might consider this functionality extra helpful.
Let’s say that you are running a large number of simulations and you need to visualize the effect of some parameters in specific signals. You can always perform this in MATLAB/Python. However, excel can offer a quick and more relaxed visualization of those effects, which works with a few clicks, instead of having to work with code (again).
Whenever you save a simulation, the signals that are loaded to the excel tool are stored in another excel file (or csv). Navigate to simulation dashboard and click on the “
” shape. To view the results of different runs, simply type in the run numbers (e.g., 10 for run10) below the “Run” cell. Notice that the parameters that have different values between those simulations immediately appear next to the plot (that is one of the essential goals of this tool, to be able to get that kind of information in a live manner, without the need for many clicks and display changes).
Shapes that act as buttons cannot be moved by left-clicking because left-clicking triggers their macro-script. To be moved, it must be selected with right click, then press “Esc” in order to close the right-click options, and then drag it with the left click in the desired new position.
CAUTION: Most shapes are not safe from accidental deletion!! If you happen to delete a shape, just close the sheet WITHOUT SAVING IT. Exceptions are:
- Flag type shapes: they are regenerated if the tool notices that one is missing
- Up till now, there is no protection from accidental delete of cells that are critical parts of the script --> You delete it, you suffer :P
- The excel file that contains signals from previous simulations takes time to load --> maybe use a database software to integrate with excel. Temporary solution is to "break-down" the signal_excel_file to smaller signal files that contain less runs.
- Saving the document takes many seconds
- Before closing the document, signal data have to be deleted (in case that they are too many for excel to handle in real time). Otherwise, it can cause problems upon reopening with "insufficient memory". After a run is saved, this is done automatically.
- Writing multiple signals from a single text file to excel range of cells is not real time!
- Create video on saving the simulation
- Comparing simulations from different runs
- Choosing signals to print
- Generating On-Line diagnostics
- Trigger text file: write that I do no need to change that as a user
- "Control room" should specify that it is a sheet
- What "parameter_file" does
- User problem: cannot rename parameters, only delete the old ones and add the new ones
- AutoRun: Need to only run it once (rerun if interrupted)
- Delete the "Save" case in MATLAB when save is triggered
- Faster saving with "range" function
- List with runs: maybe can hide older runs