This example model links to an Excel spreadsheet that contains user-defined functions that are written in Python. The Excel file uses an add-in and Python library called xlwings.
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
- 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 isnotthe 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.
One 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.
Here is a link to download the model files:
Comments
0 comments
Please sign in to leave a comment.