Difference between revisions of "Excel and Analytica"

Revision as of 01:52, 6 January 2017

Many modelers use Analytica instead of Microsoft Excel because they find Analytica's influence diagrams make for more transparent models, Intelligent Arrays provide greater flexibility and power, and/or they want to treat uncertainty using Analytica's Monte Carlo tools. But, many organizations are not ready to give up all their spreadsheets. So they use Analytica in combination with Excel -- for example, getting input data for their Analytica models from spreadsheets and writing results to spreadsheets so people can review, share, and analyze results in Excel.

Analytica plays nice with Excel. There are several ways to read inputs from a spreadsheet into Analytica and write results back to a spreadsheet:

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

These functions work equally well in the Analytica Cloud Player. When opening a spreadsheet from ACP, it can prompt the user to upload a spreadsheet to ACP. Similarly, when closing a spreadsheet, it can prompt the user to download a spreadsheet with the results. For details see Spreadsheets in ACP.

You can link cells or ranges in Excel with Analytica variables with OLE linking. OLE supports "live" updates: Any changes to the spreadsheet propagate automatically to the Analytica model and vice versa. If they are both are open, the update propagates immediately. If not, the update propagates when you next open the closed file (Analytica model or Excel spreadsheet).This is a standard Microsoft technology for creating live links among Microsoft Office products, including MS Excel and Word.

You may also access Excel from Analytica using COM Integration (Microsoft Component Object Model), another widely used method for communicating between Windows applications. Analytica's COM functions let you access just about all features of Excel and other Microsoft Windows applications. See COM and Excel examples for how this works.

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. This is a great way to learn Analytica for a new user. It's also a powerful way to verify the validity of a spreadsheet by comparing the results of the Analytica model with the spreadsheet. This process often surfaces errors that were hidden in the spreadsheet.

If you plan to convert a spreadsheet into Analytica, you should consult Excel to Analytica Translation, a step-by-step guide for the process. You may also use Excel to Analytica Mappings, which gives Analytica functions or expressions that are equivalent for most Excel functions.

@@ Line 1: / Line 1: @@
 [[Category: Excel to Analytica mappings]]
-Many people use Analytica to build quantitative models as an alternative to Microsoft Excel, because they find Analytica's influence diagrams make for more transparent models, Intelligent Arrays provide greater flexibility and power, and/or they want to treat uncertainty using Analytica's Monte Carlo tools. But, it's also common to use Analytica in combination with Excel -- using a spreadsheet to provide inputs into Analytica and/or a spreadsheet to save, share, and analyze results.
+Many modelers use Analytica instead of Microsoft Excel because they find Analytica's influence diagrams make for more transparent models, Intelligent Arrays provide greater flexibility and power, and/or they want to treat uncertainty using Analytica's Monte Carlo tools. But, many organizations are not ready to give up all their spreadsheets. So they use Analytica in combination with Excel -- for example, getting input data for their Analytica models from spreadsheets and writing results to spreadsheets so people can review, share, and analyze results in Excel.
-== Converting a spreadsheet into Analytica ==
+Analytica plays nice with Excel. There are several ways to read inputs from a spreadsheet into Analytica and write results back to a spreadsheet:
-If you have have a spreadsheet that is too cumbersome, opaque, or inflexible, you may wish to translate it into Analytica. This is a great way to learn Analytica for a new user. It's also a powerful way to verify the validity of a spreadsheet by comparing the results of the Analytica model with the spreadsheet. This process often surfaces errors that were hidden in the spreadsheet.
+* Usually, the simplest way is to use a set of standard Analytica functions that [[Excel spreadsheets read and write|read from and write to Excel spreadsheets]]. These can open and close a spreadsheet, read from or write to a cell, cell range, named range, or tab.   They can read and write formulas and formats as well as simple numbers or text values.
-If you plan to convert a spreadsheet into Analytica, you should consult [[Excel to Analytica Translation]], a step-by-step guide for the process.  You may also use [[Excel to Analytica Mappings]], which gives Analytica functions or expressions that are equivalent for most Excel functions.
+* These functions work equally well in the [[Analytica Cloud Player]].  When opening a spreadsheet from ACP, it can prompt the user to upload a spreadsheet to ACP. Similarly, when closing a spreadsheet, it can prompt the user to download a spreadsheet with the results. For details see [[Spreadsheets in ACP]].
-:[[File:Excel to Analytica.png|400px]]
+* You can link cells or ranges in Excel with Analytica variables with [[OLE linking]]. OLE supports "live" updates: Any changes to the spreadsheet propagate automatically to the Analytica model and vice versa. If they are both are open, the update propagates immediately. If not, the update propagates when you next open the closed file (Analytica model or Excel spreadsheet).This is a standard Microsoft technology for creating live links among Microsoft Office products, including MS Excel and Word.
-== Integrating Analytica with Excel spreadsheets ==
+* You may also access Excel from Analytica using [[COM Integration]] (Microsoft Component Object Model), another widely used method for communicating between Windows applications.  Analytica's COM functions let you access just about all features of Excel and other Microsoft Windows applications. See [[COM and Excel examples]] for how this works.
-Analytica plays nice with Excel. It offers several ways of reading inputs from a spreadsheet into Analytica and writing results back to a spreadsheet:
+== Converting a spreadsheet into Analytica ==
-* Often the simplest way is to use a set of standard Analytica functions that [[Excel spreadsheets read and write|read from and write to Excel spreadsheets]]. These can open and close a spreadsheet, read from or write to a cell, cell range, named range, or tab.   They can read and write formulas and formats as well as simple numbers or text values.
+If you have have a spreadsheet that is too cumbersome, opaque, or inflexible, you may wish to translate it into Analytica. This is a great way to learn Analytica for a new user. It's also a powerful way to verify the validity of a spreadsheet by comparing the results of the Analytica model with the spreadsheet. This process often surfaces errors that were hidden in the spreadsheet.
-* These functions also work with the [[Analytica Cloud Player]]. For details see [[Spreadsheets in ACP]]. When opening a spreadsheet from ACP, it can prompt the user to upload a spreadsheet to ACP. Similarly, when closing a spreadsheet, it can prompt the user to download a spreadsheet with the results.
+If you plan to convert a spreadsheet into Analytica, you should consult [[Excel to Analytica Translation]], a step-by-step guide for the process.  You may also use [[Excel to Analytica Mappings]], which gives Analytica functions or expressions that are equivalent for most Excel functions.
-* Another way to link cells or ranges in Excel with Analytica variables is to use [[OLE linking]]. This supports "live" update, so that changes to the spreadsheet propagate automatically to the Analytica model and vice versa.
+:[[File:Excel to Analytica.png|400px]]
-* You may also access Excel from Analytica using [[COM Integration]] (Microsoft Component Object Model), a widely used method for communicating between Windows applications.  Analytica's COM functions let you access just about all features of Excel and other Microsoft Windows applications. For examples, see [[COM and Excel examples]].
 ==See also==