> For the complete documentation index, see [llms.txt](https://docs.coherent.global/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.coherent.global/build-spark-services/tutorial-map-inputs-and-outputs.md).

# Tutorial: Map inputs and outputs

See [Tutorial: Get started in 5 minutes](/tutorial-get-started-in-5-minutes.md) 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](/build-spark-services/how-to-prepare-an-excel-file-for-spark.md) 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 that we need to receive to run in the Excel calculation.
* Outputs are the information we want to take to use somewhere else from the Excel calculation.

For example, from the [Tutorial: Get started in 5 minutes](/tutorial-get-started-in-5-minutes.md), to calculate the output for the `volume` of the cone, we need the inputs for `radius` and `height`.

## Excel names

Spark uses Excel [names](https://support.microsoft.com/en-US/Excel/get-started/define-and-use-names-in-formulas) 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.

{% embed url="<https://support.microsoft.com/en-US/Excel/get-started/define-and-use-names-in-formulas>" %}

{% embed url="<https://support.microsoft.com/en-us/excel/use-names-in-formulas>" %}

> 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](/build-spark-services/additional-mappings/sub-functions-with-subservices.md).

## Sample file unmapped

In this tutorial you will learn to map inputs and outputs against a [loan amortization schedule](https://en.wikipedia.org/wiki/Amortization_schedule). The completed file is available in [#sample-file](#sample-file "mention").

{% file src="/files/p1cOraRj472YiRnCmnT9" %}

## 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**. We do not recommend 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](https://support.microsoft.com/en-us/excel/get-started/apply-data-validation-to-cells) has already been applied to each input to define the minimum and maximum allowable value. These input rules will be processed by Spark during execution and an error will be raised if an invalid input is provided. This can be reviewed in selecting any of the input cells:
    1. Select the **Data** tab and select **Data Validation**.
    2. 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)](/build-spark-services/additional-mappings/inputs-and-outputs-xinput-and-xoutput.md).

## Sample file

{% file src="/files/oDCHDFnz8iXqW9wcmtD5" %}