Tutorial: Map inputs and outputs

See Tutorial: Get started in 5 minutes for an overview of this process.

If you have not done so already, we recommend applying the recommendations from How to: Prepare an Excel file for Spark to your candidate Excel file. Spark converts Excel calculations into integration-ready APIs. To convert your Excel calculations, the inputs and outputs of the file need to be identified.

• Inputs are information you need to run the Excel calculation.

• Outputs are the information you want to take from the Excel calculation.

For example, from the Tutorial: Get started in 5 minutes, to calculate the output for the volume of the cone, we need the inputs for radius and height.

Excel names

Spark uses Excel names to identify calculation inputs and outputs. If you are not familiar with Excel names, the Name Box, or the Name Manager please review the following articles from Microsoft.

Define and use names in formulas | Microsoft Supportsupport.microsoft.com

Use names in formulas | Microsoft Supportsupport.microsoft.com

The first character of a name must be a letter or an underscore character (_). Remaining characters in the name can be letters, numbers, periods, and underscore characters. In some languages, Excel may replace certain characters with underscores.

For Spark names, . is a reserved character used to Sub functions with subservices.

Sample file unmapped

In this tutorial you will learn to map inputs and outputs against a loan amortization schedule. The completed file is available in Sample file.

loan unmapped.xlsx

12KB

Open

Map inputs and outputs

Syntax: Xinput_<parameter> and Xoutput_<parameter>

1. Load the sample file in Excel.

2. The file has cells that are cells shaded in orange as Input Cell Style. These are the input values that determine the repayment schedule of the loan.

3. Map the first input for the loan amount. Select cell C3.

4. For this tutorial, instead of using the Name Box, use the New Name dialog box. The New Name dialog box includes the ability to review the name scope and to add a comment. This can be opened from:

   1. Select the Formulas tab and select Define Name OR,

   2. Use the keyboard shortcut Ctrl+Alt+F3.

5. In the New Name dialog enter Xinput_loan as the Name. Spark recognizes an input if it is prefixed with Xinput_.

6. The scope by default is Workbook. Avoid using worksheet scoped names for Spark inputs and outputs.

7. Under the Comment, enter Loan amount. Name comments are read in Spark and incorporated into the API documentation.

8. Verify Refers to is =Sheet1$C$3.

9. Continue for the remaining inputs. The comments are optional and can be skipped.

   Address	Name	Comment	Refers to
   C3	Xinput_loan	Loan amount	=Sheet1$C$3
   C4	Xinput_rate	Annual Interest rate	=Sheet1$C$4
   C5	Xinput_years	Loan period in years	=Sheet1$C$5
   C6	Xinput_payments_per_year	Payments per year	=Sheet1$C$6

10. Note that each input already has a value in the file. These are used as the default values for these inputs. Spark will use these values during execution if these inputs are not provided and a warning will be raised.

11. Note that Data Validation has already been applied to each input in order to define the minimum and maximum allowable values. These input rules will be processed by Spark during execution and an error will be raised if an invalid input is provided. You can see the validation rules:

    1. Select the input cell.

    2. Select the Data tab and select Data Validation.

    3. Use the keyboard shortcut Alt + A + V + V.

12. This workbook already contains all of the formulas to calculate the loan amortization schedule. The file has cells that are shaded in grey as Output Cell Style. These are the output values that we want to take from the calculation.

13. Map the single value outputs. Outputs must be prefixed with Xoutput_. The comments are optional and can be skipped.

    Address	Name	Comment	Refers to
    F3	Xoutput_payment	Payment amount	=Sheet1$C$3
    F4	Xoutput_periods	Total number of payment periods	=Sheet1$C$4
    F5	Xoutput_interest	Total interest paid	=Sheet1$C$5

14. Tables in Spark need to be defined carefully.

    1. The first row of a table is considered as the header row. Each field should be populated with a static value and be unique.

    2. The remaining rows are considered as the data rows.

    3. For input and output tables, make sure to define the length of the table to be sufficient for the table content.

       • If the table is not defined to be long enough, then the expected data will be truncated.

       • In this example, because the validation limits the maximum number of payments to 2 years × 12 months = 24 payments, the output range will have a height of 24+1=25 rows.

       • Any excess blank rows at the end of the table are trimmed by Spark in the API response.

       • Never assign an Xinput or Xoutput to a spill range such as D3#. The range always must be defined using cell addresses.

15. To map the amortization schedule, select the entire table range of B9:G33 and give it a name.

    Address	Name	Comment	Refers to
    B9:G33	Xoutput_amortization	Loan amortization schedule	=Sheet1!$B$9:$G$33

16. The mapping is complete and the file is ready to become a Spark service.

17. You can review all of the mappings in the Name Manager, either:

    • Select the Formulas tab and select Name Manager OR,

    • Use the keyboard shortcut Ctrl+F3.

18. Read more about input and output mappings in Inputs and outputs (Xinput and Xoutput).

Sample file

loan.xlsx

12KB

Open