Importing Data into a Time Series from a Spreadsheet

You can link a Time Series element directly to a spreadsheet. This allows you to automatically import data from a spreadsheet at the beginning of each simulation.

You link a Time Series element to a spreadsheet by selecting “Imported from MS-Excel Spreadsheet” from the Data Source field in the Data Definition portion of the dialog:

When you do so, a new tab (Excel) is added to the dialog that allows you to define the properties of the spreadsheet link:

You must first enter the name of a Microsoft Excel spreadsheet file by pressing the Options >> button. This will provide options for either selecting an existing file, or creating (and then selecting) a new file.

Once you have selected a file, you can subsequently use the Options >> button to select a different file. You can also use the Options>> button to open the selected file in Excel.

Note: GoldSim supports both .xlsx and .xls Excel files. However, if you have an older version of Excel (prior to Office 2007), you will need to install Microsoft’s Office Compatibility Pack in order to read .xlsx files. Excel 2007 and later support an extended worksheet size (1,048,576 rows by 16,384 columns) than earlier versions (65,536 rows by 256 columns). If you wish to import data from an extended worksheet range into GoldSim, you must use Excel 2007 or newer, and the file format must be .xlsx. Note that GoldSim does not officially support versions of Excel prior to Excel 2003.

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. However, under some circumstances this may not be possible and could lead to errors. Hence, as a general rule, you should not use Excel while a Goldsim model that references Excel is running.

Warning: Within Excel, a date is stored internally as the number of days (which can be fractional) since December 31, 1899 00:00:00. When GoldSim imports a date from Excel, it converts this Julian value to a date. Unfortunately, Excel mistakenly adds an extra day into its calendar that did not actually exist (February 29, 1900). As a result, the effective Julian date reference for all times after March 1, 1900 in Excel is actually December 30, 1899 00:00:00. Within GoldSim, the Run Property Time represents the elapsed time since the reference date December 30, 1899 00:00:00. Hence, for all dates after March 1, 1900, Excel and GoldSim have the same effective Julian date reference. However, for dates prior to March 1, 1900, GoldSim and Excel will differ by one day.

After selecting the spreadsheet file, you must specify the direction of the data in the spreadsheet. That is, the data can be organized into Columns or Rows. Columns implies that the data exists in the spreadsheet vertically: there is one column containing all of the time entries with a different time entry for each row, and another column containing all the value entries with a different value entry for each row. Rows implies that the data exists in the spreadsheet horizontally: there is one row containing all of the time entries with a different time entry for each column, and another row containing all the value entries with a different value entry for each column.

The time and value data do not need to be contiguous rows or columns in the spreadsheet. They must, however, either both exist as rows, or both exist as columns.

You must also specify when GoldSim should stop importing the data. There are two options:

Read data until first empty time value cell is found. Data is read until an empty or non-numeric time value cell is encountered.

Read specific number of rows or columns. A specific number of time value entries are read.

You then specify the location of the data. You do this by specifying the Excel Sheet and the Start Cell (e.g., A2) for both the series of Elapsed Times and for the series of Values. You can do this directly by typing this information into the Starting Cell field. Alternatively, you can press the Edit Location button (located at the end of each row in the dialog), which opens the spreadsheet and provides a dialog allowing you to directly select the desired cell.

Note: GoldSim allows you to define a time series of vector (or matrix) data and import it from the spreadsheet. If you define your element as a vector (or matrix), rather than specifying an Excel Sheet and a Start Cell for a single data value at each time point, you specify these for multiple data values (one for each item of the vector or matrix).

Note: The time column must increase monotonically in the spreadsheet. If it does not, GoldSim will stop the import just prior to the first time data point that is not monotonically increasing.

The row or column of time entries that you import from the spreadsheet can exist either as numbers or as dates in the spreadsheet. If they are formatted as dates in the spreadsheet, they will always be imported as dates into the Time Series element. If they are formatted as numbers in the spreadsheet, they will always be imported as elapsed time values into the Time Series element. Once they are imported into the Time Series element, GoldSim provides a mechanism by which you can convert dates to elapsed times, and vice-versa.

You must also specify the units in which the time and value entries are represented in the spreadsheet. Obviously, the time entries must have dimensions of time. The units for the value entries must have the same dimension as the Display Units specified in the Time Series dialog (and defaults to those units).

GoldSim automatically imports data from the spreadsheet at the start of a simulation if either 1) any of the properties on the Excel tab have been modified; or 2) the Excel file itself has been changed since the last import. You can also import data manually at any time prior to running a simulation (e.g., so you can view it) by pressing the Import Data button in the Excel tab.

Note: If GoldSim cannot find the spreadsheet when you try to run the model but you have already imported the data previously, it will use the data that was imported previously and will log a warning message to the Run Log.

If you want to ensure that you do not import data from a spreadsheet that has been inadvertently edited since you last imported the data, you can choose to “Lock onto” a spreadsheet file (by checking “Lock onto selected file” from the Options>> button). If you are locked onto a file, GoldSim will not allow the simulation to run if the file has been modified in any way (the file is set to read only when it is locked onto). In order to run a simulation with a changed file, you must first remove the lock (by clearing “Lock onto selected file” from the Options>> button).

The example file (ImportTimeSeries.gsm) in the TimeSeries subfolder of the General Examples folder of your GoldSim directory (accessed by selecting File | Open Example... from the main menu) includes an example of how a Time Series can be imported from a spreadsheet.

Note: When you link a Time Series to a spreadsheet, the Edit Data button changes to View Data. If you press this button to access the dialog displaying the time series data, you will note that they are no longer editable. Once you are linked to a spreadsheet in this way, you can not edit the data manually unless you change Data Source back to “Locally defined data”. When you do so, GoldSim keeps the imported data and makes it editable.

Note: Spreadsheets cannot be used to import multiple time series. That is, if a Time Series is linked to a spreadsheet, it can only import a single series. Importing multiple time series from a spreadsheet into a Time Series element is not supported.