Controlling When GoldSim Exchanges Data with the Spreadsheet File

Whenever a Spreadsheet element is used in a model, GoldSim uses the following information to determine when during the simulation it should import data from or export data to the spreadsheet file:

•   Is the Recalculate in Excel during simulation setting (accessed via the Options>> button) On or Off?  This defaults to being On (yes) when a new Spreadsheet element is created.

•   Are you importing data from the spreadsheet, exporting data to the spreadsheet, or both?

•   Are offsets defined, and if so, has an offset value changed?

•   If you are exporting data to the spreadsheet, have any of the GoldSim outputs being exported changed?

The following table summarizes how this information is used to determine when to exchange data with the spreadsheet file:

 

Type of Exchange Specified in Element

Recalculate Setting

Offsets defined

When does GoldSim exchange data with spreadsheet file?

Export only

 

Not available

No

Export all data at end of simulation.

Not available

Yes

Export data from just before its offset changes.*

Export all data at end of simulation.

Import only

 

Not available

No

Import all data at beginning of simulation.

Not available

Yes

Import all data at beginning of simulation.

Import data when its offset changes

Export and Import

 

Off

No

Export all data at end of simulation.

Import all data at beginning of simulation.

On

No

Export and Import all data at beginning of simulation.

Export and Import all data whenever an Export variable changes.

Export all data at end of simulation.

Off

Yes

Export and Import all data at beginning of simulation.

Export data from just before its offset changes.*

Export all data at end of simulation.

Import data when its offset changes

On

Yes

Export and Import all data at beginning of simulation.

Export and Import all data whenever an Export variable changes.

Export data from just before its offset changes.*

Export all data at end of simulation.

Import data when its offset changes

*The value that is exported when an export offset changes is the value from the update immediately before the offset changed.  Hence, if the offset was “Realization”, when Realization changes from 1 to 2, GoldSim would export the value from the end of Realization 1 using an offset of 1.  Similarly, if the offset was “Month”, when Month changes from 1 to 2, GoldSim would export the value from the end of Month 1 using an offset of 1.

Several points about spreadsheet recalculations are worth noting:

•   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).

   Note: When you use a Spreadsheet element, there may be a delay of several seconds at the start of a simulation as Excel is loaded into memory. Once it is loaded, however, the simulation should progress rapidly.

   Note: If you are only importing data from a spreadsheet and you have not specified any dynamic offsets and you have already imported the data previously, if GoldSim cannot find the spreadsheet when you try to run the model it will use the data that was imported previously and will log a warning message to the Run Log.

   Warning: You should never try to open a spreadsheet that is linked to GoldSim while a simulation is running.  In addition, prior to running a GoldSim model that is linked to a spreadsheet, all referenced spreadsheets should be closed. If you try to interact with a spreadsheet that is being used by GoldSim during a simulation, all interaction with the spreadsheet is blocked by GoldSim.

If you are importing data from a spreadsheet, you can tell GoldSim to import the data while in Edit Mode by selecting Update Spreadsheet Outputs via the Options>> menu button.  When you do this, GoldSim will import the data currently existing in the spreadsheet. If the information being imported is impacted by data that is being exported to the spreadsheet (and that data can be computed in Edit Mode), GoldSim will export the data to the spreadsheet, trigger a recalculation, and import the data from the spreadsheet to GoldSim, where it can be viewed as an output of the spreadsheet element.

Related Topics…