MS Excel Link

During the last three decades MS Excel has become the de-facto standard for developing and executing financial, business and engineering models of quite different nature. Because of this widely spread familiarity with its look and feel, being able to do everything from within Excel may be an attractive proposition to professionals in many application domains. However, this does not necessarily mean that doing everything by means of the Excel functionality alone is reasonable and indeed even possible. While using Excel as a standard front-end (user interface) presentation facility, behind the scene many complementary modelling technologies and tools may seamlessly play their part as well. While serving as a powerful language tool for extending Excel’s built-in functionality, the Excel-specific part of VBA also greatly facilitates the deep integration of such foreign technologies and tools. This, of course, requires some kind of compatibility in terms of methodology and internal representation of the relevant pieces of information.

Although iConceptStore^™ is designed and implemented in C++ as a general-purpose toolset (also reasonably well integrated with MS Word/Outlook/Project, MS Internet Explorer and MS SQL Server), Excel based modelling is one of its most promising applications as there exists an important architectural and methodological compatibility between iConceptStore^™ and MS Excel:

	For many application domains, Excel could naturally complement iConceptStore^™ (predominantly a back end tool) by serving as a flexible universal front-end to it. Conversely, iConceptStore^™ provides to Excel an advanced application infrastructure and serves as its intelligent back-end engine, seamlessly linked to it through COM/VBA interfaces.
	Both Excel and iConceptStore^™ support creation of self-contained yet flexibly structured (modular) models.
	The style of modelling in both Excel spreadsheets and iConceptStore^™ CML scripts is declarative by nature, focusing on domain model description rather than particular procedural computations, which may be altered (e.g., by substituting one function for another) without changing the model structure in general.
	Both Excel and iConceptStore^™ support general purpose persistence of models on secondary storage.
	Both Excel and iConceptStore^™ provide powerful run-time engines for model execution and exploration of hypotheses.
	Unlike conventional software (but much like in Excel spreadsheets), no predefined hard-coded control flow exists in iConceptStore^™ models but is rather formed dynamically (in response to requests) as a logical consequence of inter-attribute dependencies and entity links.
	By default, for performance reasons, iConceptStore^™ entity attributes are evaluated strictly on demand only. However, entities and attributes, explicitly designated as active, can respond to external events in a number of ways, particularly by asynchronously propagating value changes towards their dependants (functionally related attributes). This mode of operation, similar to dynamic recalculation of Excel spreadsheets with embedded formulae, allows for better understanding of model behaviour and model sensitivity to variations of model parameters.
	Most importantly, Decision Support is the priority direction of further iConceptStore^™ development as its dominant application domain. Again, most advanced Excel models are designed to serve precisely this purpose.

Capitalising on these and other architectural/methodological similarities, a tight integration between the two complementary tools has been implemented (see the EntityNet-Excel integration architecture below). The easy-to-use high-level COM application programming interface (API) of iConceptStore^™ provides for general purpose custom application development by means of MS Excel VBA. In addition, iConceptStore^™ comes with an integrated Excel Add-in, providing direct interactive access to most of its sophisticated conceptual modelling capabilities from within Excel based application domain models (e.g., for the purposes of What-If Analysis).

This mixed-paradigm modelling is enabled by means of embedded dynamic spreadsheet links to arbitrarily complex conceptual model fragments, contained within iConceptStore^™ modelbases, in turns possibly referencing SQL Server database tables as entity attribute Variations. In order to keep selected (active) spreadsheet cells and those external model fragments synchronized, any spreadsheet cell value change can be immediately propagated to its corresponding entity attribute in the respective conceptual model fragment. Conversely, dynamic feedback advise channels (loops) between Excel and EntityNet^™ can be automatically turned on (and off) at run-time to provide timely updates to dependant attributes, embedded in active spreadsheet cells, whenever values of their conceptual model dependencies (possibly themselves depending on some other attribute or spreadsheet cell values) change.

The following EntityNet menue items (also partially accessible through a short-cut menu, displayed by pressing the mouse right-button over any worksheet) expose the current functionality of the iConceptStore^™ Add-in for MS Excel, as described in the corresponding Help file:

iConceptStore^™ links between its EntityNet^™ run-time engine and MS Excel are implemented in the form of special-purpose suitably parameterised formulas, embedded in selected spreadsheet cells. This can be done either manually or typically by means of the EntityNet-Excel VBA Add-in – all entity attribute operations are activated by pressing the corresponding button of the ‘Entity Attribute Operations’ dialogue-box, shown below. The EntityNet-Excel Options dialogue-box (menu item seen above) allows to configure either ‘Embed Dynamic Links’ or ‘Insert Static Data’ mode of operation.

For example, requests for retrieval of entity attribute values, contained in the current iConceptStore^™ modelbase, can be embedded within the currently selected spreadsheet cell(s) by pressing the Request button. If any advise (dynamic update) link from EntityNet^™ to Excel (as opposed to ordinary retrieval Request) is required, then the @ (Advise) check-box must be checked in order to send to EntityNet a specific set of request location details as shown below:

Likewise, pressing the Add/Change button creates in the current iConceptStore^™ modelbase a new entity attribute or changes the value of any existing one according to the attribute class and data type selected. If any Change advise link from Excel to EntityNet (opposite to Request advise), related to this entity attribute value(s), is required, then again the @ (Advise) check-box must be checked and the Params/Values edit control should contain a reference to the target cell to be synchronized, as shown in the example below. Consequently, whenever the value(s) in the target cell(s) change, the corresponding new value(s) is(are) sent to EntityNet for modelbase update.

The value in any of the upper three edit control windows can be entered either directly or preferably (if entered in a cell on the worksheet before hand) by means of any valid Excel R1C1-style reference – merely collapse the dialog box by clicking on the small control on the right of the respective edit window to browse and select the target cell (refer to the scenario screenshots below for details).

Note the new feature (shown on the following picture below) that allows switching dynamically (at request level through the R check-box) between local and remote (across LAN/Internet) access to iConceptStore^™, possibly also dynamically changing the current request context – pressing the Context button would display a simple dialog-box with remote server, user name and modelbase details to alter:

The iConceptStore^™ (SQL-Link Edition) main instances query operation - retrieval of the values of selected number/range of the instances set (CML Variations), if any, of the Entity specified – is activated by pressing the Request button of the ‘Entity Instances Operations’ dialogue-box (shown below). The extracted instances subset is selected in accordance with the optional SQL expression in the Select Criteria edit-window. Only the properties (columns), optionally listed (comma separated) in the Attributes List edit-window, would be included (in the order listed) in the subset retrieved. If the Attributes List edit-window is empty (or contains only the default ‘*’ character) and the Select Criteria edit-window is empty, then the complete set of instances (CML Variations) of this entity would be extracted. The retrieved instance values are placed in an automatically sized square range of cells with its top-left corner located at the current cursor position and a number of down-right adjacent cells populated as needed. In the simplest case, the Entity name is enough to retrieve the complete set of values.

The iConceptStore^™/EntityNet^™ Excel Add-in provides for dynamic retrieval and update of relevant values by embedding in the target cell range the instances request in the form of a special-purpose suitably parameterised formula, reflecting exactly the contents of the ‘Entity Instances Operations’ dialogue-box controls - note the contents of the formula bar on the screenshot above.

Within the iConceptStore-Excel modelling framework, varying selected cell values during What-If Analysis can be done in three different (but combinable) ways:

	Changing values in different cells ad-hoc (individually, one after another), e.g., those in the columns in red on the figure below, and observe the corresponding resulting value changes in other cells.
	Changing several pre-defined cell values pseudo-simultaneously by using the MS Excel Scenario Manager.
	Changing several pre-defined cell values pseudo-simultaneously by using the iConceptStore^™ scenarios (a simple example shown on the screenshots below).

Unlike Excel scenarios, iConceptStore^™ scenarios are represented in an open native Excel 2-D table format as shown on the screenshot above. Created manually or by means of the iConceptStore^™ Excel add-in to simplify the process, they may be located anywhere on the worksheet without any relation to each other and are saved as part of their host worksheet – see also the larger scenario in the previous ‘Entity Attribute Operations’ Add/Change example.

The ‘iConceptStore^™ Scenarios’ dialog box can be collapsed by clicking the small control on the right of any of its edit windows to browse and select the appropriate cell – circling dashed line animation outlines the selected cell and its R1C1-style reference appears in the small edit-control window as shown on the screenshot below. Clicking again on the small control on the right brings back the normal-sized dialog-box.

Any such scenario can be activated directly by selecting the Apply Scenario item of the iConceptStore^™ Excel add-in short-cut menu (displayed by pressing the mouse right-button) or clicking on the Apply button of the ‘iConceptStore^™ Scenarios’ dialogue-box, if displayed.

The interpretation of the information in some of the multipurpose controls of the aforementioned ‘Entity Attribute Operations’ and ‘iConceptStore^™ Scenarios’ dialog boxes is operation-specific. Instructions for the relevant operation are displayed in a small pop-up tooltip window, appearing when the cursor is hovering over any dialog-box control. Further detailed explanations are also contained in the help files, displayed by pressing the Help button.

Models serve as controlled environments in which to test hypotheses. They are aimed at producing insights that support problem-solving and decision making, as shown on the Business Analysis Lifecycle picture below.

Business Analysis Lifecycle, by Professor Thomas Grossman, University of San Francisco, School of Business Studies

The following definitions of the action terms in the above figure are borrowed (with slight modifications/extensions) from an earlier article of its author with the possible role of iConceptStore^™ in the whole process added by us in red. Such potential usage of iConceptStore^™ introduces an intermediate stage of model development as well as another level and component in the model structure. This allows to actually achieve one of the central ideas of the above scheme - separation of modelling from spreadsheet engineering.

“UNDERSTAND” = Form a Conceptual Model in Mind

“iCS MODELLING” = Create a Conceptual Model in Conceptor^™ CML, compiled into iConceptStore^™ modelbase

“ENGINEER” = Implement a Spreadsheet Model, linked to Conceptual Model fragments in iConceptStore^™ modelbase

“ANALYZE” = Perform Analysis on Model

“INTERPRET” = Interpret and Explain Model Insights

“COMMUNICATE” = Explain, Recommend, Persuade, Decide, Act

In general, the iConceptStore^™ original declarative modelling language, dynamic software infrastructure and supporting tools enable sophisticated multi-facet approach to modular structuring of the relevant information, directly represented in the specific individual terms of the corresponding application domain. This natural object-oriented paradigm allows focusing on understanding model behavior rather than getting the model to work.

The iConceptStore^™ technologies, including the area of conceptual modelling within MS Excel by means of the iConceptStore^™ COM APIs, tools and run-time services, enable Excel/VBA programmers to reach beyond the spreadsheet paradigm. Integration of the compatible and complementary iConceptStore^™ technologies with Excel/VBA aims at creating a technological foundation for collaboration with professional VBA programmers and/or Excel modelling experts in different application areas, who are able to appreciate the iConceptStore^™ potential as a modelling tool, in general, and Excel companion, in particular.

Armed with the iConceptStore^™ technologies, any team of professional VBA programmers and Excel modelling (problem-solving) experts in selected application areas may be able to undertake more complex modelling tasks, whether as relatively isolated specific parts of large bespoke application systems or more generally applicable software and/or information modules as products for wider commercial distribution. We may be able to provide some methodological and technological support to such efforts as far as they involve in some way the iConceptStore^™ infrastructure and tools. This might encompass participation in the system-level analysis and conceptualization of the application domain at hand with respect to problem decomposition and solution architecture alternatives as well as more routine activities, including but not limited to design and implementation of any specific high performance C/C++ interfaces, extensions and/or alterations to iConceptStore^™ as required.