Exercise 20: Exporting model data to Excel

Now we are going to explore a much easier way to create external references in the correct form in Excel, by first exporting some sample data from STEM.

Export model data

1. Close any open dialogs in the STEM Editor and then select the DSL access service icon.
2. Select Export Model Data… from the File menu. The Export Model Data dialog is displayed, with options for which elements and fields should be exported.

3. Click OK to accept the default settings. The inputs for the selected service are exported to Excel, but only those inputs which have been defined (the remainder are suppressed, as they are with tooltips). Any formulae embedded in the service will be converted to values in Excel.

‘Print to Excel’

4. STEM first creates a new workbook for the exported data, naming the workbook to match the STEM model name, WiMAX-DSL20.xlsx. It then asks if you wish to proceed to modify the STEM model itself so that the data for the DSL access service are linked from the spreadsheet.

5. If you click No, then the export will have had no impact on the original model. You can think of this like ‘printing to Excel’.

Import data from Excel through an external reference

6. Click Yes. STEM creates an external reference in the model to link all of the exported attributes back into STEM. The difference now is that, if you change the values in Excel and save the workbook, then all the values will be updated in STEM next time you update the link.
7. Select Defaults from the icon menu for the DSL access service. The Defaults dialog is displayed, showing a similar formula to the ones we created in the earlier exercises:

8. The export process has created a named range which locates the exported data in Excel. The name is the same as the element name, except that the space has been converted to an underscore (‘_’). Use the Go To command in Excel to check the name: you will see that it identifies the first two columns of the exported data in Excel.

Linked set and unset values

9. Go to the Demand dialog for the service. You will see that the data has changed colour. The blue cells indicate that these values are linked via Defaults from elsewhere, and the green cells indicate that the remaining values are not currently defined in the source location, but could become defined in a subsequent update.

Note: you can protect individual values within such an element link by over-typing a new value, which will then appear in black, to show that it overrides the new default value linked from elsewhere. If you later unset such an value, then it will revert to the value linked by the formula. Only if you remove the formula altogether will it revert to the grey system default.