STEM newsletter

How does linking to Excel work?

30 October 2002

STEM brings clarity and structure to the telecoms business-planning process, but while the STEM Model Editor program offers powerful drag-and-drop editing for the expert, a more familiar interface is a realistic necessity for the peripheral user, from a colleague providing detailed tariff and cost data to a customer entering key assumptions.

Recognising this, STEM is designed to accept external inputs from Excel and also from any database which supports the Open Database Connectivity (ODBC) standard, such as Access, SQL Server or Oracle. STEM also accepts the inputs or results of other STEM models. This article, written as one in a series of how-to articles intended to raise awareness and understanding of STEM, focuses on the principles of how to link scalar values, time series or even default values for an entire element, and highlight the new STEM 6.2 format for linking data for Resource Requirements from Excel. The process of creating such links, using the External Links dialog, is explained in detail in Section 5 of the STEM User Guide.

With the addition of the add-in interface for running models and retrieving model results, this close integration with Excel keeps the inputs and outputs of a STEM model readily accessible to the non STEM expert.

Linking scalar values and time series

The syntax for linking single numbers from Excel is directly comparable with the syntax in Excel for references to cells in other workbooks. However, the majority of STEM model inputs are time-series and so the format for linking an Interpolated Series is of particular importance.

The form of an external reference to Excel is ‘<filename>’!<reference>. The <filename> is enclosed in single quotes to prevent special characters being misinterpreted as another part of a formula, while the <reference> may be a cell reference (e.g. R1C2 or $A$2) or a name which is defined in the workbook. In order to link the Capital Cost of a Resource to cell $A$2 in the spreadsheet C:\DATA\COSTS.XLS, then you would enter the formula:

          ‘C:\DATA\COSTS.XLS’!$A$2

More generally, if a workbook contains several sheets, then the name of the sheet must also be specified, using the same syntax as is used in Excel for an inter-workbook link:

          ‘C:\DATA[COSTS.XLS]Sheet3’!$A$2

However, this is not necessary for references to global named ranges; and these are also generally preferable, as a named range will generally remain valid when rows are inserted in, or deleted from, a worksheet.

The syntax for scalar external references can also be used to link, for example, the individual parameters for an Exponential Growth, but a richer format is required for an Interpolated Series, where a single link is used to retrieve the entire series. The essential point to understand is that, when a formula is intended to define the entire series (rather then just an individual cell in the series), then the formula should be entered on the time-series button, and not within the Interpolated Series dialog (where the individual periods and values are displayed). This is most generally achieved with reference to a named range which identifies a 2x2 block of cells as follows:

The left-hand column tells STEM, first that the data is an Interpolated Series, and then that the years are in the first row and corresponding values in the second row. The range should only include this label column and the first column of values; STEM keeps reading to the right until a blank cell is encountered.

Typically you may link several time series with the same set of years. If you first associate the name ‘Years’ with the actual row of years, then you can omit the years row from individual links.

STEM can calculate results by quarter or month from standard annual inputs, but if you need to specify inputs for specific shorter time periods, then just replace ‘years’ with ‘periods’ in the examples above.

Although these techniques for scalar values and time series are fairly straightforward, it is well worth the investment of working through these simple examples. Perhaps the syntax is so like Excel that it is then easy to be caught out by the material differences? Certainly these topics consistently represent the most frequent requests to our STEM Support staff!

Linking multiple inputs as new default values for an element

Although this partly comes down to personal style and preference, there is an argument that an excess of inter-file links makes a model hard to maintain. So an alternative mechanism is provided which allows several inputs for a given element to be defined through a single link.

As you might expect, a formula defining values for an entire element is entered via its icon. For example, you can select Defaults… from a Resource icon menu to access a dialog with a single button for each Resource in a model. Here you can enter a formula referring to a 2×n block of cells as follows:

Just like the format for an Interpolated Series, the left-hand column identifies the individual data, while the right-hand column defines the relevant values, with Interpolated Series data again being read to the right until a blank cell is encountered.

The same principle works for every type of element, and the full set of labels is provided in an appendix to the STEM User Guide, as well as in an example workbook, MODEL.XLS, which is supplied with STEM, and linked directly from, the Start menu for STEM 6.2. The following example shows the new format for linking data for Resource Requirements as part of a selection of defaults for a Service:

Notice how the names of individual Resources are used to identify individual Requirements, and thus form part of the actual labels. In order to facilitate use of this format, the relevant labels are constructed by formula in Excel, linking the Resource names from the immediately adjacent column.

The qualification of where the individual data belongs is thus provided within the spreadsheet itself, significantly reducing the number of inter-file links. The order of items is completely flexible, and only those items of interest need be included, which means that you are not constrained by a fixed template format. The layout in the spreadsheet may be reasonably compact, with the attendant benefits of being easier to verify and maintain.

       Buy SSL

© Implied Logic Limited