Linking GoldSim to Python-Enabled Excel

Posted by Jason Lillywhite

I recently presented a webinar called "Effectively Working with Excel", demonstrating some of GoldSim's Excel interface features. In addition to demonstrating how GoldSim can import/export data, I also talked about how GoldSim can dynamically interact with Excel during a simulation. One of the attendees asked me if GoldSim will wait for user-defined functions to be called by external programs, DLLs, or scripts used by Excel before reading in values from Excel. At the time, I didn't have a ready answer because I couldn't think of a time that I've actually seen this done.  

To answer his question, I built a simple GoldSim model that interfaces with an Excel file and this Excel file relies on user-defined functions written in a Python script. I thought this example might also be useful for a wider audience, so I decided to blog about it.

I originally built this simple model to demonstrate how GoldSim interacts with Excel when Excel is relying on external tools to perform calculations and return the results back to GoldSim. The method I chose was to use a Python library that easily interacts with Excel and also allows you to reference user-defined functions that are written in a Python script. Though there are multiple ways to do this, I decided to use the library called xlwings, which you can read more about here: Automate Excel with Python (Open Source and Free) (xlwings.org).

One potential limitation of this approach is significant increases in model runtime. The main reason for this is that GoldSim must open Excel and read/write to a spreadsheet during a simulation. If you want to interact with the spreadsheet many times during a GoldSim model simulation, you might see significant increases due to the time required to interact with Excel. But if you are using Excel mainly for pre or post processing, it shouldn't be an issue. 

Using the xlwings library, I was able to quickly build a script that defines a custom function, callable in cell expressions in an Excel file. Once I had xlwings installed, it was just a matter of some file configuration and basic model structure to get it working. Then I was able to get GoldSim to send arguments to these functions and import the result back. This could all be accomplished behind the scenes during a GoldSim simulation. I was pretty excited when I starting to think about how easy this was to get Python scripts that work seamlessly with a spreadsheet. 

Below is a list of tools I used and to develop this model. If you choose to test and use it, please make sure to carefully review the versions listed below and compare them to what you are using. There is a link at the bottom of this blog post for downloading the model files I used for this tutorial.

Tools used:

• GoldSim 14.0 R1 #383
• MS Excel (Office version 2210)
• Python 3.9.0
• xlwings version: 0.28.5
• xlwings Excel add-in

The following files are used to run the model and they must be saved in the same directory. The Python file must have the same name as the Excel file but this can be customized later.

• GoldSim Excel Python.gsm
• myproject.xlsm
• myproject.py 

The GoldSim model, as shown in the screen capture below, contains 2 inputs that are exported to Excel. These are defined as Data elements A and B, which are linked to a Spreadsheet element, called "Excel_Interface". 

The Spreadsheet element is configured to wait for Excel to recalculate before importing spreadsheet data. This is done by checking the boxes as shown below.

The Spreadsheet element, "Excel_Interface" exchanges data between GoldSim and Excel at the beginning of the simulation and each time the variables A and/or B change. Something to note about the "Recalculate in Excel during simulation" setting:

• Regardless of the Recalculate in Excel during simulation setting, by default, Excel will recalculate whenever cell values change.  As pointed out below, however, GoldSim will only retrieve the updated data if Recalculate in Excel during simulation is selected.  You can change this setting in Excel (so it does not automatically recalculate) in the Calculation tab of Excel's Options dialog.
• The Recalculate in Excel during simulation setting does two things: 1) it forces Excel to recalculate, and 2) it retrieves the recalculated data back into GoldSim. Hence, if the Recalculate in Excel during simulation setting is off, GoldSim will not retrieve updated data from the spreadsheet, even if some cells have changed (and Excel automatically recalculated).

MS Excel is configured to trust access to the VBA project object model as shown below. Note this is not the default setting for Excel so please consider any risks of making this selection. I decided to enable this setting for the purposes of running this example but will disable it afterwards.

By enabling this setting, you will now see the xlwings settings tab in the Excel ribbon.

The next step is to write a Python script that defines a custom function. For my example, I define a function called double_sum(). This is the function I will call from inside the Excel file. You can read more about how this is done here: Quickstart - xlwings Documentation

import xlwings as xw 

def main(): 
    wb = xw.Book.caller() 

@xw.func 
def double_sum(x, y): 
    """Returns twice the sum of the two arguments""" 
    return 2 * (x + y) 


if __name__ == "__main__": 
    xw.Book("myproject.xlsm").set_mock_caller() 
    main()

Below is a screen capture of the Excel spreadsheet (sheet1) of the file called "myproject.xlsm" along with the custom function defined in the Python script called "double_sum()". Before using the function in Excel, make sure to click on the Import Functions button so it can be used.

After you import the function from the Python script, you can use it like any other function in Excel. This output (cell B7) is what GoldSim imports after the value gets updated.

This type of setup could be useful in some scenarios where you need to rely heavily on Python scripts for functions and Excel for the data along with a GoldSim model to carryout simulations using the Excel values that these python functions calculate.

Even though it seems like a lot of complexity just to get a custom function, but it is nice to know that with very little effort, you can quickly start leveraging some of Python's data analysis libraries to support GoldSim models in a more efficient way. 

Here is a link to download the model files: Link Spreadsheet with User-Defined Functions – GoldSim Help Center

Alternatively, you could look into GoldSim's example model that interfaces directly with Python using Embedded Python and a compiled DLL. Read more about this example here: GSPy: GoldSim/Python Interface for Scripting Support – GoldSim Help Center. The benefit of this approach is removing the Excel component and thus decreasing model runtime significantly.