Model Creation in Excel

When you create a model in Excel, you can use it with the Epicenter platform by:

Creating and uploading your model file (.xlsx), including
- Creating decision variables in named cells
- Creating a named table or range expressing your model

Then, when you create a user interface for your Excel model on Epicenter, each user's interaction includes:

Creating a run
Retrieving variables
Updating variables

There are no model operations for Excel models.

Creating and Uploading your Model File

Epicenter supports Microsoft Excel models (.xlsx files): You can use the Epicenter APIs to retrieve and update named tables, ranges, and cells within each Excel worksheet. When you update a value, the Excel worksheet automatically recalculates, so if you update a value, other values will update also — executing your model based on end user decisions.

First, create "decision" variables in your Excel worksheet. For example, you might enter:

500 in cell A1, and name the cell initialSavings
0.07 in cell A2, and name the cell interestRate

Next, create a named table or range expressing your model in your Excel worksheet. For example if you are using a named table, you might enter:

`Time`	`Savings`
`1`	`=initialSavings(1+interestRate[@Time])`
`2`	`=initialSavings(1+interestRate[@Time])`
`3`	`=initialSavings(1+interestRate[@Time])`

(The `[@Time]` notation is only available if you are working with a named table in Excel that has a column named `Time`. If you are working with a named range instead, this formula might be `initialSavings*(1+interestRate*B5))`, for example.)

This table models savings as it grows over time based on an interest rate. Select the entire table within your Excel worksheet and name it savingsTable.

Creating a Run

You can create a run using Flow.js:

<body data-f-model="excel_interest.xlsx">

<script>
var options = {
    channel: {
        strategy: 'always-new',
        run: {
            model: 'excel_interest.xlsx',
            account: 'acme',
            project: 'interestRates'
        }
    }
};

Flow.initialize(options);
</script>
</body>

Or with the Run API:

curl -X POST \
    'https://api.forio.com/v2/run/acme/interestRates' \
    --header 'Content-Type: application/json' \
    --header 'Authorization: Bearer eyJhbGciOiJSUzI1NiJ9' \
    --data '{"model": "excel_interest.xlsx"}'

Retrieving Variables

Variables can be referenced using any of several forms.

Using Excel coordinates, for example A2 (a single cell) or A1:G10 (a two dimensional range of cells).
Using a Named Range. This is the easiest way to work with Excel variables, and is recommended by Forio.

For example if you make a table (as above) named savingsExcelTable, with column headers Time and Savings you can reference the following:

savingsExcelTable (a two dimensional range)
savingsExcelTable[0, *] (the first row)
savingsExcelTable[0, Time] (the first row and the column labeled Time)
savingsExcelTable[*, Time] (the column labeled "Time")
interestRate (since this is a single cell, it will return a single number)

If you are using Flow.js, you can retrieve variables either by name or by Excel cell coordinates, using data-f-bind, data-f-repeat and similar attributes. The following example will show the single value for interest rate, and then make a table row by looping through all cells in the row with Time and the row with Savings.

<span data-f-bind="interestRate"></span>
<span data-f-bind="A2"></span>

 <table border="1">
     <tr>
         <td>Time</td>
         <td data-f-repeat="savingsExcelTable[*,Time]"></td>
     </tr>
     <tr>
         <td>Savings</td>
         <td data-f-repeat="savingsExcelTable[*,Savings] | ###.#"></td>
     </tr>
 </table>

You can also retrieve data with the Run API:

curl -G \
    'https://api.forio.com/v2/run/acme/interestRates/0000015c306863c1a1889316c9ab366f3464/variables?include=interestRate,A2' \
    --header 'Authorization: Bearer eyJhbGciOiJSUzI1NiJ9' \

Updating Variables

If you are using Flow.js, you can update variables the same way you retrieved them, using data-f-bind and an input element:

<!-- if 'interestRate' is a single-cell named range in Excel, located in A2, 
     the following requests all return the value of that cell;
     however, only "A2" returns it as a scalar
-->
<input data-f-bind="interestRate"></input>
<input data-f-bind="interestRate[2,A]"></input>
<input data-f-bind="A2"></input>

If you are using the Run API, use a PATCH to update the variable:

curl -X PATCH \
        'https://api.forio.com/v2/run/acme/interestRates/0000015c306863c1a1889316c9ab366f3464/variables' \
        --header 'Content-Type: application/json' \
        --header 'Authorization: Bearer eyJhbGciOiJSUzI1NiJ9' \
        --data '{"interestRate": 0.08, "A2": 15}'