User Tools

Site Tools

Mortgage Calculator (Excel Ranges, Tables and Charts)

Your organization may rely on a spreadsheet to:

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

One or more 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 might carry the following challenges:

  • 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 an EASA solution to the problems above.

Tutorial Summary

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

The underlying spreadsheet will define the EASAP's input and output. Certain Ranges, Tables and Charts will be connected to the EASAP's User Interface and presented in a Web Browser. A PDF printout function will be added.

These Excel-linked objects will change in real-time in the browser 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!)

This tutorial assumes access to a properly configured EASA Server with a compatible version of Excel.

  • 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 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

Part A: Create a new EASAP

Step 1: Download tutorial files and go to EASA web pages

  • Open the archive and drag-and-drop the folder to somewhere convenient, e.g. your Desktop
  • Navigate to your EASA Server using a Web Browser

Step 2: Login and switch to Author mode

  • Enter your EASA username and password and click Log In
    • If you have installed the EASA Server locally, this might be the default:
      • User: author
      • Password: author
    • Otherwise, contact the EASA Server Administrator for your credentials
  • Navigate to EASA → Set Mode using the tabs displayed
  • Set Current Mode to Author

Step 3: Create a new EASAP and open EASAP Builder

  • Navigate to Applications → New
  • Click the Create button
  • Click on the EASAP Builder link on this page
    • This will download a file named atos.easa
  • Open the file to start EASAP Builder

Step 4: The New Application Wizard

The New Application Wizard is displayed the first time we edit a New EASAP. Here we'll set some initial PROPERTIES. These can be changed later.

  • Set the Title to Mortgage Calculator
  • Set the Category to Training
  • Upload an Image
    • Click the folder icon
    • Browse to app_image.jpg in our extracted folder
  • Optionally, enter a brief Description
  • Click Next

Step 5: Choose the Excel Range Wizard

We now have options on how we wish to define the user interface of our new EASAP.

The first option is to proceed straight to EASAP Builder and begin to add objects to our EASAP manually.

However, in our case we want to initialize our EASAP user interface to replicate our Excel spreadsheet, and utilize its functionality.

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

Part B: The Excel Range Wizard

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

Our EASAP will not only replicate the look of our spreadsheet, it will connect to the relevant cells in the secured master copy of the spreadsheet on the EASA Server.

As the EASAP User updates input fields in their Web Browser, the Excel spreadsheet will recalculate based on their inputs, and the EASAP will display the updated outputs to the User in real time.

Step 1: Upload an Excel workbook

  • Click the Choose File button
  • Browse to our extracted folder and select the Mortgage Calculator.xls file.
  • Click Next

Step 2: Select Excel Ranges to expose to Users

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

  • Click the Add a Range from Excel button ( Add Range ),
    • The Mortgage Calculator.xls file should open in Excel
  • Click and drag to select ranges A1 to G23
    • You should be returned to the Excel Range Wizard upon releasing the mouse
  • Click the Finish button

Step 3: Save and Test the EASAP

The main EASAP Builder window should appear with the EASAP Tree pre-populated with USER INTERFACE objects by the Excel Range Wizard.

  • Click the Save button ( Save )
    • Some additional objects will be added at this point

By now, the EASAP Builder with SPREADSHEET LIST expanded should be shown as per below.

  • Click the Open Web Browser Test button

The EASAP should open in your Web Browser in testing mode. Try it out by changing a few inputs and observe the outputs being recalculated.

When you're done, close the tab and return to EASAP Builder.

Part C: Add User Interface Objects

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

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

Step 1: Create a second Layout Panel

  • Expand the USER INTERFACE object by clicking on the “plus box” () icon

This LAYOUT PANEL and its Child objects were automatically created when we ran the Excel Range Wizard.

  • Collapse the layout_panel by clicking on its “minus box” () icon
  • Add a SPACER to create some space between the existing layout panel and the new one
    • 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
    • Again, the default name is fine
    • Set the property Layout: HORIZONTAL

Step 2: Add Button objects to the new 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 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
      • 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→ Here we 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 will add a space between our buttons now.

  • 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 implemented in an EVENT PROCESSING object.

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

  • right-click tab_Basic_Information
    • select Sibling→Add EVENT PROCESSING

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 and records the timestamped output at
      • EASA→Author→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 PANE's 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 V23
        • → the Range Wizard will show Basic Information!M1:V23
    • 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 SPACER's 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 PANEL's 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 SPACER's that may have been captured…. We don't want this extra padding

3→ We can move these objects wherever we want them 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
      • tabbed_pane_Amortization
      • tabbed_pane_Rate_History
      • 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

G. Create PDF output from Excel

  • our mortgage calculator stores the results of each EASAP run as an HTML page at
    • EASA→Author→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 it may be exported to a PDF.

1→ We add EXPORT TO PDF functionality to our EASAP.

  • close the window with the EASAP that we just tested
  • return to the Tree in the 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
    • 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
  • go to EASA→Author→Authoring→Testing
    • confirm there is a link to the PDF below in the HTML report under Output

We have completed the Excel Tutorial.