Linking GoldSim to Spreadsheets

1. Linking GoldSim to Spreadsheets GoldSim Technology Group Issaquah, Washington USA

2. Excel Enabled Elements • Five GoldSim elements will interface with Excel:

3. Agenda • We’ll discuss how each of the elements interacts with Excel and we’ll demonstrate how to set up linkages for each element • We won’t do any examples (there’s a lot of stuff to cover) but if you want to follow along on your own machine you’re welcome to.

4. Database Type Elements • Both of the Time Series elements and the Lookup Table can use Microsoft Excel as a database • Import to these elements is automatic. They check the linked spreadsheet prior to the beginning of a simulation run and update themselves with any changes

5. Formatting the Spreadsheet for the Time Series Element • Data can be in columns or rows • You can read in a specified number of rows or until you hit the first blank cell • The Excel import functions expect a column of time data (this can be Elapsed Time or Date/Time) • Scalar or vector data can be imported for each time point • Let’s create a simple spreadsheet and then import it…

6. The Lookup Table Element • Similar to the Time Series element, import to the Lookup Table is automatic. • Again, the lookup table has a spreadsheet format that it expects.

7. The Lookup Table’s Excel Dialog

8. 1D Table Format • Data is expected to be in columns • Data is read in until the first blank row This cell is the Row Variable Starting Cell

9. 2D Table Format • Data is expected to be in columns • Data is read in until the first blank row/column This cell is the Row Variable Starting Cell This cell is the Column Variable Starting Cell

10. 3D Table Format • Data in the 2D format • Each Worksheet is a layer of the 3D table This cell is where the Layer Variable is specified

11. The Time History Element – Export to Spreadsheets What to Export Where to put it in the spreadsheet

12. Disabling Automatic Export • Sometimes you may not want to export (e.g. when debugging a model) • You can globally disable Time History export in the Model|Options dialog

13. The Spreadsheet Element

14. Important Notes About the Spreadsheet Element • Using offsets • The Shift button • Updating Logic • If a Spreadsheet element exports data, the spreadsheet is only called if the exported data or and offset changes • If a spreadsheet element only imports data, the spreadsheet is only called at the start of the simulation and when an offset changes

15. Important Issues About Using Spreadsheets • ALWAYS use relative paths – if you use a full path running the simulation or a Player file on another machine can be difficult. • Do not work on connected spreadsheets while the simulation is running.