Excel and Analytica

Analytica plays nice with Excel and other spreadsheets. Even if you prefer to build models in Analytica, you may want to link it to spreadsheets. You may want to import input settings and data from a spreadsheet. Or you may want to save model results to a spreadsheet for others to review, share, or generate graphs.

There are several ways to read inputs from a spreadsheet into Analytica and write results back to a spreadsheet:

• Usually the simplest is to use standard Analytica functions that read from and write to Excel spreadsheets. These functions can open a spreadsheet, read from or write to a cell, cell range, named range, or worksheet. They can read and write formulas and formats as well as simple numbers or text values.

• In Analytica Cloud Player, these spreadsheet functions can prompt a user to upload or download a spreadsheet from or to their computer. For details see Spreadsheets in ACP.

• OLE linking lets you can link a cell or range in Excel with a corresponding Analytica variables. Any changes to the spreadsheet propagate automatically to the Analytica model, and vice versa. If both Analytica model and spreadsheet are open, each update to one propagates immediately to the other. If not, the update propagates when you next open the closed file (Analytica model or Excel spreadsheet). OLE is a standard Microsoft technology for creating live links among Microsoft Office products, including MS Excel and Word.

• COM Integration (Component Object Model) also lets you link Link from Analytica to Excel. It is another widely used Microsoft-developed method for communicating between Windows applications. Analytica's COM functions let you access just about all features of Excel and other Microsoft Office applications. See COM and Excel examples for how this works.
• You can also use ODBC, a widely used standard for reading and writing relational databases, to access Microsoft Excel.

Converting a spreadsheet into Analytica

If you have have a spreadsheet that is too cumbersome, opaque, or inflexible, you may wish to translate it into Analytica. You end up with a model that is much clearer, and often a tenth to a fiftieth of the size -- a measure of the effort to understand, verify, and maintain a model. Translating a spreadsheet is also a powerful way to verify the validity of the spreadsheet by comparing the results with the Analytica model. The translation process often surfaces errors that were hidden in the spreadsheet.

Consult Excel to Analytica Translation, which gives step-by-step guide for converting a spreadsheet into Analytica. We have yet to find an Excel calculation that can't be converted into Analytica.  Excel to Analytica Mappings shows the Analytica function or expression equivalent for most Excel functions. 

See also

• Excel to Analytica Translation
• Excel to Analytica Mappings
• Excel spreadsheets read and write
• Spreadsheets in ACP
• OLE linking
• COM Integration and COM and Excel examples