User Tools

Site Tools

Mortgage Calculator (Excel Range Wizard)

An organization may require a spreadsheet to:

  • Run a product pricing model
  • Estimate a future budget, revenue, and costs
  • Analyze insurance risk and calculate a premium

Multiple Users may:

  • Need secure access to an up-to-date 'master' version of a spreadsheet
  • Be in different locations
  • Use different devices to enter and receive data
  • Need results in different report formats
  • Save results to various repositories

Distributing the raw spreadsheet is not an ideal solution in these cases as:

  • A new spreadsheet version may be confused with a prior version
  • Proprietary information may be embedded in the spreadsheet
  • An end user might inadvertently modify the spreadsheet and introduce error
  • A User might need to share a final report with many colleagues or store it in a central archive

The following tutorial demonstrates a turnkey EASA solution to the problems above.

Tutorial Summary

We will create a basic Mortgage Calculator EASAP that derives its user interface and functionality from an existing Excel spreadsheet.

The underlying spreadsheet defines an EASAP's input and output. Certain cells, ranges and charts link directly to browser-visible fields in the EASAP GUI. These Excel objects generate media that may be included in HTML or PDF reports.

On the EASAP's GUI Excel-linked values and graphs will change in realtime when a User modifies an input.

As we build this application, think about your organization's key spreadsheets and the potential value created if they could be easily transformed into standalone web applications. (They can be!)

Below is our complete EASAP user interface. The second image is an example of the report output.

Please ensure a compatible version of Excel is installed on the server.

This tutorial involves a number of straightforward steps:

  1. Launch the New Application Wizard to set basic properties
  2. Use the Excel Range Wizard to create a default working application from a spreadsheet
  3. Modify the new EASAP in the Builder
    • Add buttons to the GUI that activate events and trigger actions
    • Import live Excel charts to the EASAP GUI
    • Create finished HTML and PDF formatted output with User results

An Author uses the EASAP Builder to create an EASA application without writing any code.

The EASAP Builder interface consists of a Tree of EASAP objects on the left and their parameters on the right.

In this tutorial we build a fully-functioning application via iterations of EASA's design process:

  1. Create an EASA object with a right-mouse-click
  2. Configure the object's parameters, either
    • Type in literal text
    • Select a value from a drop-down list

There will be no coding involved.

This tutorial contains seven parts A - G:

A. Create a new EASAP

1. Access tutorial files and login to EASA:

  • With a browser download the following archive
  • Unzip the archive and save the files locally
  • Obtain from the EASA Administrator a user ID with Author privileges
  • Open a browser window
  • Navigate to the EASA Server URL sent by the Administrator
    The URL has the following structure
    'domain-name' is the computer name or domain name of the EASA Server, often 'localhost'

2. Login to the EASA Server web page.

  • Select EASA > Set Mode > Author (enter an Author's login credentials)
  • From the above local archive we will need the following two files,
    'app_image.jpg' to identify our new EASAP
    'Mortgage Calculator.xls' with tables, formulas and charts

3. Create a new EASAP.

Under EASA > Applications :

  • Select New
  • Click on the 'Create' button

4. Launch the EASAP Builder:

  • Click on the EASAP Builder link
  • Download the file atos.easa
  • Open the file

5. The New Application Wizard will appear.

The New Application Wizard opens any time we create a new EASAP.

Here we set some parameters which will be stored and easily changed in PROPERTIES :

  • Set Title: Mortgage Calculator
  • Set an icon as the Image:
    Click the folder icon
    Browse to: app_image.jpg
  • Set Category: Training
  • Click: Next

We now have some options on how we wish to define the user interface of our new EASAP. The first option is to open the EASAP Builder and begin to add objects to our EASAP manually. However, in our case we want the EASAP to quickly be able to use the functionality we already have implemented in our Excel spreadsheet.

6. We will use the Excel Range Wizard.

  • Select 'Use Excel Range Wizard (2.0)'
  • Click 'Finish' and the screen below should open

B. The Excel Range Wizard

The Excel Range Wizard effortlessly reproduces the look and function of an Excel spreadsheet within an EASAP.

Later we add additional interface objects to the EASAP.

Our EASAP will not only mimic the look of our spreadsheet, it directly links to the relevant cells in the secured master version of the spreadsheet on the server.

The EASAP User will update input cells, the Excel spreadsheet will do calculations based on the updated values, and the EASAP will return and display updated values to the User in real time.

1. Let's begin using the Excel Range Wizard:

  • Click on the 'Choose File' button
  • Navigate to and select the tutorial Excel file 'Mortgage Calculator.xls' from above.
  • Click: Next

2. Expose parts of the original spreadsheet to Users.

For now, we will add just one range of cells to the EASAP:

  • Click on the 'Add a Range from Excel' button ( Add Range )
    … Mortgage Calculator.xls will open in Excel
  • Select cells A1 through G23 , click and drag to highlight these cells
  • Click on Finish button to process these ranges and exit the Excel Range Wizard
    …a window shows 'Building Application…' with a status bar
  • Once this has completed, the EASAP Builder will appear

3. The Tree will be populated with new objects created by the wizard.

Click 'Save' ( Save ) and a dependency calculation will be initiated:

  • Excel 'named ranges' will be added to the Tree as EASAP NAMED RANGE objects under
    EASAP > SPREADSHEET LIST > spreadsheet_Mortgage_Calculator > NAMED RANGE LIST

The EASAP Builder with EASAP > SPREADSHEET LIST expanded is shown below,

We now have a functional EASAP.

4. The Excel Range Wizard replicates the formatting of the Excel spreadsheet.

Let's test it:

  • Click the Open Web Browser Test button as shown below,

  • Once the test EASAP has opened, below
  • Change a few inputs
  • Click 'X' in the corner of the browser window or tab to close the test

C. EASAP Builder, Add GUI buttons to the Tree

Every new EASAP begins with some existing objects that form a template that is common to every EASAP.

In the EASAP Builder this is called the Tree, the highest level branches are always the same, their names are in ALL CAPS.

1. Let's add 'Save' and 'Create report' buttons to the USER INTERFACE

We will implement good GUI design and keep our web interface simple and intuitive for a User:

  • Expand the USER INTERFACE object by clicking on the “plus box” () icon
  • Find USER INTERFACE > TABBED PANE LIST > tab_Basic_Information > layout_panel

layout_panel and its child objects were automatically created when we ran the Excel Range Wizard to create the form for basic input and output.

Add a SPACER to create some space between the existing form and the new buttons:

  • Minimize layout_panel by clicking on its “minus box” () icon
  • Right-click layout_panel
  • Select: Sibling > Add SPACER
  • The default name spacer1 is fine

Add a second LAYOUT PANEL for the buttons:

  • Right-click spacer1
  • Select: Sibling > Add LAYOUT PANEL
  • The default name is fine
  • Set Layout: HORIZONTAL

2. Let’s add two BUTTONs to this LAYOUT PANEL:

  • Right-click the LAYOUT PANEL
  • Select: Child > Add BUTTON (twice)
  • Rename the first BUTTON on the Tree to button_submit
    …Modify the rectangular text field to the right of ' BUTTON: ' above the parameter table
  • Set Label: Save to Results & Create a Report

The Label: text will be displayed inside the rectangular button.

To be clear this new BUTTON is named, button_submit which we, as Authors, will use elsewhere within the EASAP Builder.

button_submit has a Label: which is the text that a User will see enclosed within the button's outline in the GUI.

Press <Enter> after typing a parameter value to ensure it updates.

For the moment button_submit will not do anything.

  • Rename the second BUTTON to button_exit
  • Set Label: Exit without saving
  • Optionally, we can also add a Tooltip: that will be visible when the User places the mouse pointer over the button
    For example set Tooltip: Submit with these inputs and create a report

ACTION's may be added as child objects under a BUTTON, each will be executed sequentially when a BUTTON is pressed.

3. Let's create a way for the User to exit the EASAP gracefully.

We now introduce our first ACTION called MENU ACTION which initiates activities common to many EASAP's:

  • Right-click button_exit
  • Select: Child > Add MENU ACTION
  • Rename it menu_action_exit
  • Set Action: Exit

We have now defined what happens when a User presses the exit BUTTON.

In a moment we will define another ACTION and associate it with button_submit.

4. Since LAYOUT PANEL doesn't add an space around its child objects, we add space between each button.

  • Right-click button_submit
  • Select: Sibling > Add SPACER
  • Set Size: 10,10 (a ten by ten pixel space)

5. Let's check our work:

  • Save
  • Click on the 'Test Web Browser' () button
  • Once the test EASAP has opened try the new exit button, button_exit

D. Create an EVENT that triggers an ACTION

In this section, we will introduce an EVENT and its associated ACTION.

In this case, the BUTTON PRESSED EVENT will be activated and an ACTION triggered when button_submit is pressed.

A BUTTON PRESSED EVENT is an alternative way that a BUTTON can trigger an ACTION.

Here we use BUTTON PRESSED to introduce a general EVENT/ACTION model.

1. Let's add an EVENT PROCESSING to the Tree:

  • Right-click tab_Basic_Information
  • Select: Sibling > Add EVENT PROCESSING

An EVENT PROCESSING has two child objects:

Child objects of an EVENT will trigger any child ACTION objects under the associated ACTION GROUP.

  • Click the 'plus box' () to expand the EVENT PROCESSING to see each child.
  • Rename the EVENT event_submitted
  • Right-click event_submitted
  • Select: Child > Add BUTTON PRESSED
  • Set Buttons: button_submit (choose from the drop-down list)

When a User clicks on button_submit a BUTTON PRESSED EVENT (child of event_submitted) is activated.

2. Let's define an ACTION to trigger a submit for the EASAP.

  • Under event_submitted
  • Select the ACTION GROUP rename it action_submitted
  • Click Yes to rename the 'other usage' of this ACTION
    the Actions: parameter of event_submitted will update to the new name
  • Right-click action_submitted
  • Select: Child > Add MENU ACTION
  • The default name menu_action2 is fine
  • Set Action: Submit

After a BUTTON PRESSED EVENT this MENU ACTION submits a run of the EASAP with current inputs.

The timestamped output is recorded at: EASA > Authoring > Testing

Click Save ( ), if there were an error, an error message would identify which object to fix.

E. Add charts to the EASAP

The EASAP is now perfectly usable, but let’s suppose that, after its published, a few Users provide feedback. In this case, they ask to include two charts from the spreadsheet and display each of them on new TABBED PANEs in the EASAP.

1. Let's add these charts to enable a User to see live results graphically even before the EASAP is submitted.

  • Re-run the Excel Range Wizard from within EASAP Builder, select: Tools > Excel Range Wizard (2.0)
  • Click on the 'Choose File' button
  • Select the Excel file from the tutorial *.zip archive: Mortgage Calculator.xls
  • Click Next
  • On the 'Select Excel Ranges' screen, click on the 'Add a Range from Excel' button ( )
  • Add a range for the amortization chart, select a rectangle that entirely includes the chart
  • Select cells M1 through V24
    …the Range Wizard will show: Basic Information!M1:V24
  • Add a range for the rate history chart, select a rectangle that entirely includes the chart
  • Select cells M24 through V46
    …the Range Wizard will show: Basic Information!M24:V46
    If the selection rectangle is too small it will not create an IMAGE reference
    If the selection rectangle is too large extra SPACERs will show up on the Tree, which we will delete
  • Click Finish

These selections were made from the 'Basic Information' Excel tab so there are two new LAYOUT PANELs under tab_Basic_Information which contain the Amortization and Rate History charts.

2. Let's make sure we've captured the Excel-derived objects we wanted.

  • Expand each LAYOUT PANEL
  • For each insure there is one IMAGE
    if not, delete the LAYOUT PANEL and repeat the steps using: Tools > Excel Range Wizard 2.0
  • Delete (or CTRL-x) any SPACERs that may have been captured…. We don't want this extra padding

3. We may change the position of these objects in the user interface by changing their location on the Tree.

  • Right-click tab_Basic_Information
  • Select: Sibling > Add TABBED PANE (twice)
  • Rename each of them, respectively
  • Set for each TABBED PANE, respectively
    Tab: Amortization
    Tab: Rate History
  • Drag and drop…
    layout_panel2 into tabbed_pane_Amortization
    layout_panel3 into tabbed_pane_Rate_History

4. The Tree should look like the following panel.

Save the EASAP: click the Save button or type CTRL-s

5. Test the EASAP, click 'Web Browser Test'.

When the application opens, select each new tab, the Amortization tab should look something like the following figure:

F. Create a report

When a user submits an EASAP, a report is created. We define the content and presentation of this output in a REPORT.

1. We are going to create two images from the spreadsheet and write them to the report.

In the Tree, select the PROCESSES branch.

We could have simply defined the actions for button_submit by adding child objects in much the same way as we did for button_exit. However, we wanted to demonstrate the BUTTON PRESSED EVENT within the more general EVENT PROCESSING object which can use various EVENT's (eg. VALUE CHANGED, RESULTS COMPLETED, APPLICATION OPENED).

2. To pull an image from the spreadsheet we use an IMAGE EXCEL ACTION in the PROCESSES branch.

PROCESSES contains a sequence of tasks that begin when the User 'submits' the EASAP.

  • On the Tree
  • Right-click EASAP > PROCESSES
  • Select: Child > Add IMAGE EXCEL ACTION
  • Rename it image_excel_action_summary
  • Set
    File Name: summary.png (type in this new filename)
    Sheet: sheet_Basic_Information (from parameter dropdown list)
    Range: A1:L25
    Queue: TRUE → to avoid running out of free Excel processes
  • Right-click image_excel_action_summary
  • Copy and then right-click again and: Paste (Sibling)
  • Rename it image_excel_action_table
  • Set
    File Name: table.png (type in this new filename)
    Range: A26:L387

These IMAGE EXCEL ACTIONs will create images of the summary region and output data table, respectively.

Later we will add the two images 'summary.png' and 'table.png' as PLOT objects in our report.

First let's insert summary notes in the Results page so that a User later browsing Results may distinguish one submission from another.

3. The first step is to format the loan amount (displayed as dollars) and the term length (as an integer number of years).

  • Right-click EASAP > OUTPUT
  • Select: Child > Add DATA PROCESSING
  • The default name data_processing1 is fine
  • Expand data_processing1 and right-click its child SCALAR
  • Select: Child > Add FORMAT
  • Rename it format_loan_amount
  • Set
    Value: cell_Basic_Information_loan_amount_1
    Input Type: Currency
    Decimal Places: 0
    Currency Symbol: $
  • Right-click format_loan_amount
  • Select Copy or CTRL-c
  • Right-click format_loan_amount
  • Select: Paste (Sibling)
  • Rename it format_term
  • Set
    Value: cell_Basic_Information_term_1
    Input Type: Number
    Thousand Separator: FALSE

4. We want to store a few bits of data about the particular submission as NOTES in a text file.

  • Right-click data_processing1
  • Select: Sibling > Add NOTES
  • Set Value: %format_term% year loan for %format_loan_amount%

The EASAP Builder will highlight the text between the percent (' % ') delimiters in blue and remove the percent (' % ') signs, this indicates that it recognized the strings as references to pre-existing SCALAR's.

The value of the Value: parameter should show as format_term year loan for format_loan_amount

The EASAP Builder allows object references (green for lists or blue for scalars) to be entered as parameters for many objects giving the Author the power and flexibility of conventional computer programming as explained in more advanced examples (see Intro Batch Tutorial ).

5. Let's assemble the rest of the report.

  • Right-click NOTES
  • Select: Sibling > Add REPORT
  • The default name report1 is fine
  • Right-click report1
  • Select: Child > Add TEXT OUTPUT
  • Set
    Style: Heading 2
    Text:Report for loan amount %format_loan_amount% for %format_term% years
    …the SCALARs should be recognized and be highlighted in blue without '%' symbols
    Text: Report for loan amount format_loan_amount for format_term years
  • Right-click the TEXT OUTPUT
  • Select: Sibling > Add PLOT
  • Set Image: summary.png (type this in, the file will be generated when the EASAP is submitted)
  • Right-click this PLOT
  • Select 'Copy' then right-click again and: Paste (Sibling)
  • Set Image: table.png (type this in manually)
  • Click Save () or type CTRL-s
  • Minimize or close the EASAP

6. Let's test the EASAP to generate the output that a User will see:

  • From the EASA web pages go to: EASA > Authoring > Testing > Test EASAP
  • Change some values, and click: Save to Results & Create a Report
  • The test results should appear once you reload: EASA > Authoring > Testing
  • Check the Notes reflect the values just entered
  • Check the report by clicking on the icon under Output

G. Create PDF output from Excel

Our mortgage calculator stores the results of each EASAP run as an HTML page at: EASA > Authoring > Testing

A User may vary inputs for a number of EASAP runs and and view the results in the browser.

Once an EASAP run meets the User's requirements we want the output to be exported to a PDF.

1. Let's add an EXPORT TO PDF to our EASAP:

  • Close the window with the EASAP that we just tested
  • Return to the Tree in the EASAP Builder
  • Expand the EVENT PROCESSING to show event_submitted and action_submitted
  • Right-click action_submitted
  • Select: Child > Add EXPORT TO PDF
    this will be run after the user clicks 'submit' and before the report is generated by the MENU ACTION

2. For the EXPORT TO PDF set:

  • Spreadsheet: spreadsheet_Mortgage_Calculator_xls
  • Output File Name: Report.pdf
  • Open in Browser: FALSE
  • Sheet: sheet_Basic_Information
  • Range: A1:L111 (to illustrate monthly payments for seven years to keep the PDF short)

3. We put a link to the output PDF file in the HTML report.

  • On the Tree
  • Right-click OUTPUT > report1
  • Select: Child > Add HTML OUTPUT
  • Set Text: <h2>This report in PDF form: <a href=Report.pdf>Report.pdf</a></h2>
  • Save
  • Test
  • Confirm there is a link to the PDF below in the HTML report under 'Output' at: EASA > Authoring > Testing

We have completed the Excel Tutorial.