47.3 Send model outputs back to Excel

This section shows you how to take model outputs and place them directly into Excel. To illustrate the points, we will again work with the BilinksExample Model MarkovCancerDecision_before.trex and the Excel Workbook MarkovCancerDecision_inputs_and_outputs.xls. We will specifically focus on sending the Cost (payoff) for the SOC strategy to Excel.

Create Named Cells in Excel

Before an Excel cell (or range) can be connected to TreeAge Pro, it must first be named. This applies when sending data in either direction.

In the workbook, we want to connect our model output for the Cost for SOC to cell D4 in the Excel worksheet “Model Outputs”.

  • Open the Excel workbook MarkovCancerDecision_inputs_and_outputs.xls.

  • Select the worksheet Model Outputs.

  • Select cell D4 (the cell we want our TP output to go to).

  • Click in the Name box in the top-left corner (as highlighted in the figure) and type the name we are assigning to this cell as “cost_output_SOC” then press Enter.

    • Ensure the name is a single word, using underscores if required.

  • When you select the cell, the new cell name should appear in the Name Box in Excel.

We will assume here that you have already connected the TP model to the Excel workbook. If you have not, please refer to the prior section.

Create the TreeAge Pro Bilink to send data from TreeAge Pro to Excel

  • Open Tree Preferences.

  • Navigate to the category Excel Workbook > Bilinks Connections.

  • In the "Send Analysis Results to Excel” section (the bottom table), click the Add button. This adds a single row representing that specific model output. (See figure below).

  • To add the link for the Cost of the SOC Strategy, adjust the values in the table as follows:

    • Output: Use the drop down menu to select: Payoff 1 (Cost).

    • Statistic: Select from the drop down menu the statistic you want to capture about the output (mean, standard deviation, etc). For this cohort model, it is the Mean value.

    • Strategy: Select SOC as the Strategy. You might later connect the Cost for a different strategy to a different Excel named cell.

    • Named Cell: Use the drop down menu to select the Named Cell cost_output_SOC in the Excel file.

    • Notes: Nothing required, but this is an optional editable text to describe this link.

  • Click Apply and Close.

The figure below shows the Tree Preferences with a single output from TreeAge added. The table for Send Analysis results to Excel shows the named cell the output is linked to: cost_output_SOC.

Repeat this process for as many TP model outputs as required. The example model MarkovCancerDecision_BiLinks.trex, has connections for several TP model outputs – cost and effectiveness for both Strategies.

The links are now set up to send the TP model analysis outputs to Excel. When the TP model is analysed, the outputs are sent to Excel and they can be used to plot graphs etc. As the model inputs change, new analyses send different outputs to Excel.

If you remove the path to the worksheet, TreeAge Pro will look for the worksheet in the same folder as the model. Use the Edit button to remove the path.