by Ed Merriman
| October 26, 2015
Original Post Date: October 8, 2015
Excel is easily the most common and widely used tool in the Cost Estimation universe. It can be used as a scratch pad, report generator, import/export vehicle, data repository as well as countless other applications. The Excel / TruePlanning interface is one of the first topics new users want to dive into. Currently TruePlanning can export and import Excel files directly, create reports in Excel and has two Excel based solutions for working with TruePlanning directly in Excel.
The ‘big gun’ of the Excel interfaces is the “Excel Solution”. It is a macro enabled Excel file that contains VBA code to support working directly with TruePlanning projects. It contains multiple views of the inputs and metrics, allows users to specify which inputs and metrics they want to see, allows users to update and calculate TruePlanning estimates in Excel to see results real time. It also contains a calibration tool for the most commonly calibrated Hardware and Software inputs.
For users who need to change lots of data in large projects quickly there is the “Large Project Engine” (LPE). This is a .NET based Excel Add-in that is installed. It does not require a specific Excel file to use. The user interface is stripped down and simple to use and has been designed to work with large projects efficiently and reliably. It will work with data as long as the data is formatted correctly so Excel files from other sources can be used to work with TruePlanning. Tools have been created that translate from one Excel format to a format that works with the LPE allowing users to create TruePlanning projects from non-TruePlanning sources easily, quickly and reliably.
While both of these solutions have their advantages, users have expressed a desire for a way to combine the strengths of the two solutions into a single, fast, robust, easy to use solution. The following is a description and a mockup of what a solution like this might be like.
The following is a discussion of the strengths and weaknesses of the existing Excel interfaces.
The Excel Solution is written in Excel’s VBA environment. This was a natural place to start building an Excel solution. The integration into the TruePlanning API is simple. Accessing the data in the Excel files is easy. Building simple GUI interfaces is fast and convenient. But there are downsides to VBA. Error handling is difficult to manage. The environment is difficult to work with when the size of the source code grows. The development environment also lacks access to the more sophisticated development tools found in environments like .NET including source code control, database access, and access to the operating environment features like the registry.
The LPE is a .NET based Excel Add-in built using a specific type of Visual Studio project that makes creating and producing an installation for the Add-in simple. Being built in Visual Studio developers have access to a highly sophisticated development environment, a large array of features and functionality found the .NET languages, source code control, and easy access to the TruePlanning API.
The largest drawback to an Excel Add-in is its need to be installed. Fortunately there is a precedent for this as the PES “Excel Solution” is also an Excel Add-in. In the case of the PES Excel Add-in, it is installed during the installation of PES. Currently the LPE creates a TruePlanning menu in Excel that is always visible for users. If this is considered an issue there is way to have the menu created by the Add-in only visible when specific Excel files are used.
The Excel Solution provides a rich set of interfaces, and menus and allows users to pull both input and metrics data from a TruePlanning project. It allows users to define the sets of data they work with and allows users to define multiple views of data across multiple Excel sheets. The Excel Solution provides a “TruePlanning” ribbon with buttons to control behavior. It also provides ‘right-click’ menus on specific cells to control the set of inputs/metrics that are displayed. The sheets that allow users to perform calibrations also contain buttons to initiate the calibration behaviors.
The LPE is barebones. The only menu/GUI features are through the Excel Add-in ribbon. There is minimal help setting up or formatting the Excel sheets for use by the LPE. In order for the LPE to process Excel sheets, it needs the sheets to be in very specific formats, but except for when users pull data from TruePlanning into Excel, they get no help setting up the format of the Excel sheets. The LPE provides no mechanism for defining a subset of inputs to be displayed and has no mechanism for returning metric/output data.
Currently both the Excel Solution and the LPE will overwrite formulas when pulling data from TruePlanning into Excel. This is something users have complained about. Future solutions need a mechanism for preventing formulas from overwriting formulas in Excel sheets.
The Excel Solution gives users the experience of working on a specific project as if were open in TruePlanning. The Excel Solution allows users to maintain an open connection to a specific project through the COM API. It does this by opening individual projects and keeping them open to give users the sense that they are ‘working in TruePlanning’ through Excel. While a project is open, users can save the project to the database, they can update the project’s inputs and then get the results of that update back in Excel. The Excel Solution replicates how the PES Excel Solution works, but with one major difference, in TruePlanning users cannot access the project that is open in Excel in TruePlanning at the same time.
The LPE’s interaction with projects has a short lifecycle. The LPE opens a project to retrieve data and the closes the project. Similarly when it creates a project, the project is only open while the project is being created. With the LPE users are working with snapshots of projects.
Currently neither the Excel Solution, nor the LPE support the behavior of changing the PBS in an existing TruePlanning project. Both solutions require users to create a new TruePlanning project. At this time the API to TruePlanning does not support the alteration of PBS structures so this is a feature that will have to wait.
The following is a discussion of aspects of what a new and improved Excel interface may be like.
The new Excel interface should be built as a .NET Excel Add-in. There is a precedent, and the technology and tools are better. Error handling will be better and the Add-in will have better performance and be more reliable.
The main functions of the Excel solution will be:
- Open TruePlanning project (file or database)
- Close TruePlanning project
- Save TruePlanning project (file or database)
- Save As TruePlanning project (file or database) (creates new project based on content of Excel file)
- Calculate (push data to TruePlanning from Excel, calculate, return results
- Update From TruePlanning (pull from TruePlanning to Excel, filling in all properly formatted sheets)
The new Excel Interface should be able to process any Excel file that has the right formatting and not require user to use any menus to set up the sheets. Users should be able to create Excel files with other tools or by hand. That said, the Excel Interface should provide GUI/menu tools to help the user define configurations and select the parts of a TruePlanning project to be used.
The new Excel Interface should be able to use all possible information but not complain if something that is not absolutely needed is missing. The new Excel Interface, when creating a project, will use all sheets that are formatted correctly. Data that ‘fits’ the project being updated/created will be used. Data that doesn’t ‘fit’ will be ignored. Inputs/settings in the project not addressed by the Excel file will be left in their default state. For example, if a user creates a new TruePlanning project using an Excel file that does not have a sheet that contains Worksheet Set data, the project created will have the default Worksheet Sets. Or, if an Excel file is used that has inputs with values that do not belong to any of the Cost Objects listed in the PBS in the Excel file, those values will be ignored.
The Excel sheet that contains the “Cost Object” data will be the master sheet for the PBS and this sheet will be processed first. Other sheets that contain the PBS will be ignored if the PBS they contain does not match. Errors like this will be logged on the Log sheet. The Cost Object data sheet’s first four columns will be fixed to be: Order, Level (Indent), Cost Object Name, Cost Object Type. The Cost Objects sheet will have “Cost Objects” in cell A1.
The first cell of each sheet (A1) will define the type of data contained in that sheet. Some sheets will have additional information in cell B1. Calculators and sheets with the data from temporal inputs are an example of sheets having data in cell B1.
Users can configure the Excel interface through an Options dialog:
It will allows users to define how the Excel Interface functions for following settings:
- Select inputs/metrics button. This button allows users to access a second level dialog to define the set of inputs and metrics to be pulled from TruePlanning and their order. Inputs and metrics will be selected by Cost Object Type in the following sub-dialog. Users will be able to define the order of the inputs and metrics. NOTE: that this dialog will not enforce anything, but is merely a tool to help users define a list in a specific order. There is another button in the options dialog to create a Cost Object sheet based on the data in this sub-dialog. The reason for this is the ‘hands-off’ approach of the Excel solution. Users can do what they want… If they want to use the order set up in this sub-dialog, they can create the Cost Objects sheet based on it and then leave it alone, or they can update it.
- Include All Inputs checkbox. When this is checked, the “Select” button for input/metrics is disabled. With this option selection, the action of pulling of data from a TruePlanning project will result in the display of all inputs for all Cost Objects in the selected project. This will overwrite existing content on the Cost Objects sheet, including formulas.
- Preserve Formulas checkbox. When this is checked the action of pulling data from TruePlanning will test each target cell in Excel and if a formula is found data will not be put into that cell.
- Format for TrueFindings checkbox. When this is checked the action of pulling data from TruePlanning will result in the headers for inputs and metrics being formatted so the file can be imported into TrueFindings.
- Only Update Metrics checkbox. When this is checked inputs will in the Excel file will not be updated from the TruePlanning when a calculation is performed.
- Sheet Management list of checkboxes. This allows users to configure the types of data found in a project that will be brought back from a TruePlanning project. Each type of data will be contained on its own sheet. The advantage of allowing users to select only the data they want is that they can reduce the amount of data moving between TruePlanning and Excel.
o Project Properties
o Worksheet Sets
o Schedule data
o Risk Results
o Pivot Table data
- Calculators button. This button provides access to a secondary dialog that allows users to select the list of calculator sheets to be created. This dialog will create a reusable list of calculators. A button on the Options dialog will create sheets for all the calculators on the list and fill in the sheets with the appropriate calculator inputs.
- Create Sheets: Cost Objects button. This button will generate a Cost Objects sheet based on the list of selected inputs/metrics. When this sheet is created it will follow the order defined by the user. Again, the approach of the Excel Interface is that it will follow what is found in the Excel sheets. This button is a ‘helper’ button to create a sheet in the right format. A decision needs to be made regarding how much ‘force’ will be used if a Cost Object sheet already exists and contains data.
- Create Sheets: Calculators button. This button will create a sheet for each calculator the user has selected in the order they are selected in. Again, the approach of the Excel Interface is that it will follow what is found in the Excel sheets. This button is a ‘helper’ button to create sheets in the right format. A decision needs to be made regarding how much ‘force’ will be used if a calculator sheet already exists and contains data.
The new Excel Interface needs to work. It needs to function out of the box, it needs to handle error conditions with informative messages that allow users to resolve issues quickly. It needs to help users understand if there are data formatting issues and how to correct them. It needs to handle large amounts of data quickly. It needs to allow users to make changes and get results back quickly. It needs to help users successfully work with TruePlanning through Excel.