Run Excel Calculation Model is a powerful action step that takes a set of inputs and iterates through an excel workbook to generate the specified outputs.
This article will show users how to create the action step and then provide elementary instructions on how to utilise this step. We will then go through a simple example to demonstrate the functionality of this step in a practical situation.
Creating the Step
1. Add an action step to the process.
2. Go to the Third party tools tab and select Run Excel Calculation Model
Using the Step
There are three main components to the "Run Excel Calculation Model" step.
- The first component is the 'Model Set Up' which involves specifying the source (input) parameters and output range of your calculation model.
- The second component is specifying what actions the step should perform for each input parameter.
- The third component is specifying what the step should do with the calculated output.
Model Set Up
The first section requires the user to specify the source/input data and the excel model to be used.
1. Firstly you will need to link an excel file that holds the source/input data.
- You will then need to provide the range and worksheet that holds the input data.
- Note: this range is what the step will iterate the model over (for each row of data in the input range).
2. Secondly, you will need to link an excel file that holds a model that can take the input data you specified in Step 1.
- Note: This excel file can be the same as the file in Step 1 if the Worksheet that comprises your model is in the same file as the worksheet that comprises your source, i.e. you don't need two separate files for input data and the model.
Specify Actions to take with each Iteration of the Model
The next part would require you to specify how the step should use the source data for the model.
1. Select an option from two possible actions:
- FROM_MODEL_WORKBOOK (go to step 3 after step 2)
- FROM_SQL_QUERY (go to step 4 after step 2)
2. Click "Add" to add the instruction and configure the instructions according to the option you selected in step 1.
3. "FROM_MODEL_WORKBOOK" will copy the value of a cell from the input source range into a specific cell in the Model Worksheet. When this option is added, you will be presented with the following box to fill in.
a. Enter the input column number (relative to the input range you entered). For example, if you specified your input range as B1 to D4, and you want to use Column B in this instruction, the relative column will be "1".
b. Enter the name of the Worksheet you require the input data to be copied over to.
c. Enter the cell (in the Worksheet you entered in b.) that you want the step to copy the input data into for each iteration. Note: You will need to specify the excel cell reference here. For example, "E2".
4. "FROM_SQL_QUERY" will run an SQL query for each row of data. When this option is added, you will be presented with the following box to fill in.
a. Type in an SQL Query that you require. To use the conditions specified in your input data, you will need to provide the column number (offset by -1) and wrap it round "{}" brackets.
For example, if you require your query to use the first column of input data range as a condition, your query may look like the following.
b. Additionally, the boxes you are required to fill are similar to the boxes to the boxes in "FROM_MODEL_WORKBOOK". The differences to note are:
- You can enter multiple column numbers instead of just one
- The cell address you specify is the start reference of where the output query will be extracted. For example, if your query produces 3 columns with 4 rows of data and you specified the cell "A1". The data will encapsulate the range "A1:C4" or "A1:C5" (if you check the tick box to include the headings of the SQL query).
5. Specify the parameters for each action. These checkboxes are self-explanatory and dependent on user preference.
Producing Outputs from the Model
The final part of this step requires the user to specify how the output files should be created.
Each checkbox is a different method to obtain your output files, users can select as many options as preferred. We will go through what the user must supply for each method.
- Create excel output files per iteration and collect into zip file:
a. The column number from the source range which contains the file names
b. The name of the output zip file
c. The name of the output sheet to be copied over. (In general, this is the name of the worksheet you specified in the Model source - in section "Model Set Up"). Remember to click "Add" after entering the worksheet name.
- Create worksheets output per iteration and add into consolidated excel file
a. The column number from the source range which contains the file names
b. The name of the output consolidated excel file.
c. The name of the output sheet to be copied over. (In general, this is the name of the worksheet you specified in the Model source - in section "Model Set Up"). Remember to click "Add" after entering the worksheet name.
- Create excel output file and copy data from the calculated model into the output workbook
a. The column number from the source range which contains the file names
b. The name of the output excel file.
c. The name of the output sheet to be copied over. (In general, this is the name of the worksheet you specified in the Model source - in section "Model Set Up").
d. The cell ranges that need to be copied. Remember to click "Add" after all the required information.
Example - Interest rate forecast model
Disclaimer: This model is only to be used with the intention of demonstrating the "Run Excel Calculation Model". Any other use besides its original intention is not supported.
In this section, we will go through a short example on how to use the Run Excel Calculation Model step to predict spot rates for the next 12 months for three different economic scenarios (High-Interest Rates, Medium-Interest Rates, Low-Interest Rates).
Attached to this article are two excel files to view. The input file and model file are stored in "OU Input and Model" and the corresponding output is stored in "OU Scenario Outputs".
If you open the "OU Input and Model" and examine the "Inputs" tab, you should see the following input table.
We note that the input data is contained within the range "A3:D5". Hence, in the source data range, we will specify the following information.
We will now need to specify the name of the Worksheet that contains the model. In this case, the worksheet is named "Model".
If we take a look at the "Model" worksheet we see that the input parameters of this Model is held within the range "A2:B4".
Hence, we specify for each iteration the step should perform the following instructions with the input data.
We then set the parameters:
Now the remaining step is to determine how the outputs should be presented. In this case, we want to create a worksheet named after each economic scenario and consolidate it into one workbook.
Hence, in the final section, we will put the following details.
Once we run the step, click "View file" to open the output file.
In the output file, we should see the following worksheets:
You can confirm that the step successfully run by comparing your output file with the attached output file "OU Scenario Outputs". For example, the "High Interest" output should be similar to the below image.
Note: This example involves forecasting spot rates, therefore your Outputs may not have the exact same values as the output file attached to this article. However, the Input Parameters should be the same as the ones in the desired output file.
Files used in the example:
Comments
0 comments
Please sign in to leave a comment.