Excel Engine

Overview

QBO's Excel Engine leverages Microsoft Excel spreadsheets to perform calculations. The Excel Engine plugin handles two things:
  1. Injecting data into the spreadsheet, so Excel can perform calculations, and
  2. Extracting data out of the spreadsheet, storing this data in QBO's ScoreItem table
To leverage this plugin:
  • Ensure the qbo.Score.AsposeCells plugin is deployed
  • Ensure the Scoring Excel Engine package has been imported
  • Create a new Score Template, ensuring you select 'ExcelEngine' from the Score Engine drop down list
    • Upload an Excel spreadsheet (.xlsx file) as the Render attachment for the template
Below is information on how to configure your spreadsheet to work with the plugin.

Injecting Data

There are two methods of injecting QBO data into Excel:
  • Individual data points cell-by-cell, or
  • Entire data tables
Individual Data Points

The simplest method of injecting data into Excel is to enter a value starting with "?=":
  • ?=UPBAmount: substitute the Loan.UPBAmount into a cell
  • ?=Borrower.1.LastName: substitute the Borrower.LastName of the first borrower associated with the record being scored.
  • ?=MyTask.MyField: substituted the user-defined field "MyField" from the user-defined task "MyTask"
  • ?=//Some/Crazy/XPath/Expression: evaluate the XPath expression against the parent record's Summary output
If one enters a "?=" expressions into a cell's value, references to this cell may result in the dreaded #VALUE! errors. 

An alternative is to add a Name with a Comment, where the Comment contains the same "?=" expression above. To add a name and comment to a cell, either:
  • Right-click on the cell, and choose "Define Name", or
  • From the Formulas ribbon, choose "Name Manager"
See the image on the right for an example.

Injecting Entire Tables

To inject tables into your spreadsheet, in any cell, enter a method signature beginning with "?=" (without the quotes). For example:
  • List of users: ?=Person/Search?OrderBy=LastName&SqlColumns=FirstName,LastName,Address,Person,PersonID
  • Valuations associated with a Loan: ?=Valuation/Search?Object=Loan&ObjectID={LoanID}
  • Random ledger items: ?=LedgerItem/Search?DisplaySize=5&OrderBy=-LedgerItemID
The method signatures may use string substitution, including any data from the score's parent object, or the score row itself. For example, if you are scoring a Loan, you may substitute any column from the Loan table.  If you are scoring a Person, you may substitute any column from the Person table.

When the data is injected, spreadsheet cells to the right of the method signature cell will be overwritten with the resulting data. However, spreadsheet rows below the method signature line will remain in place; each row after the first row of data will cause a new spreadsheet row to be inserted. For example:

this Excel template

 ?=Person/Search?... Goodbye cruel world 
   
 Hello World  

will result in this Excel result

123John Doe ... 
124 Mary Smith ... 
...   
    
 Hello World   

Note that the 'Goodbye cruel world' cell was overwritten with 'John', but the 'Hello World' row remains intact.

Extracting Data from Excel to SQL

Scores can comprise zero or more Score Items, which are individual data points that contribute to a score. In the case of the Excel Engine plugin, each Score Item can be mapped to a cell in the spreadsheet by setting the name of the cell to match the name of a Score Item. For example, assume you have the following Score Items defined for a template:
  • Per Diem
  • NPV
  • Confidence
In your spreadsheet, choose a cell (any cell in any sheet) that will represent the value to populate your Score Item with. The cell will typically be an Excel formula, and may leverage any data injected into the spreadsheet you wish. Select the cell, and change the 'name' of the cell to match the safe name of the Score Item.  Safe name is the Score Item's name, with spaces and special characters removed. For example, the safe name of 'Per Diem' is 'PerDiem'.

Score/Calculate

When Score/Calculate is called, the following happens:
  • A spreadsheet is created from the 'template' uploaded as the Score Template's Render document
  • Each method signature is found, substituted, executed, and the method signature cell is overwritten with the resulting data
  • For each defined Score Item, if the spreadsheet contains a named cell matching the Score Item's safe name, the cell's value is saved to the Score Item
  • The final spreadsheet is copied to the default imaging store, and associated with the Score.RenderID
Technical Notes
  • If you include spaces in the Item name, the spaces will be ignored when looking for an Excel cell name. E.g. 'Per Diem' maps to a range named 'PerDiem'
  • Method signatures must specify a method that returns a DataSet (or DataReader); XmlReaders are not supported

Ĉ
ExcelEngine.Sample.xlsx
(9k)
Eric Patrick,
Jan 6, 2016, 6:10 AM
v.1
Comments