Excel calculations and lookups
Last updated
Last updated
In Clause9, there is native functionality to perform a number of calculations, both traditional mathematical calculations (addition, subtraction, multiplication and division, even in complex combinations) as well as other types of calculations or conversions, e.g. of durations.
In addition, with its spreadbase integration for questions in a Q&A, Clause9 can leverage simple databases to look up information and use it in an automated document.
However, sometimes more power is required:
complex calculations involving multiple inputs and/or outputs (e.g. automatically calculating a loan amortisation table)
conversions from/to data types not available in Clause9
lookup of information (e.g. pricing) in a large Excel sheet
...
First you need to upload the Excel file to the Clause9 library.
The location of the Excel file must be stored in a location to which the end user of the automated document will have access. If the end user (even e.g. an anonymous user through a magic link) does not have access, the Excel functionality will not work.
Good practice is to store the Excel file and the document in which it is used in the same library, group or folder with uniform access rights to ensure access to both is aligned.
Secondly, you will have to use the @excel special function in the clause where you want to extract the information/results from the Excel sheet. The parameters are as follows:
The first parameter is the name of the Excel sheet file in the library between parentheses, e.g. "pricing-sheet".
The second parameter is the cell or range of cells you want to use the values from, e.g. "D3" or "B1:B8".
The third parameter is (strictly speaking) optional: here you can refer (between parentheses!) to a cell or range of cells the value of which you want to change to some other value, e.g. "D4".
The fourth parameter then contains the value you want to input into the cell referenced in the third parameter. Using the cell "D4" referenced above, you could input e.g. the value in the datafield #loan^number-of-payments.
The fifth (and seventh, ninth, eleventh,...) parameter is again optional and can contain another reference to a cell or range of cells the value of which you want to replace.
The sixth (and eighth, tenth, twelfth,...) parameters again refer to the values you want the cells referenced in the preceding parameters replaced with.
Some basic examples:
@excel("pricing-sheet", "D1", "E1", #product^name) where you look up the price for a product with name #product^name where the Excel sheet outputs a price in cell E1 for the name of the product in cell D1.
@excel("loan-sheet", "B7:B31", "B1", @extract-number(#loan^principal-amount)) outputs a list of values (from B7 to B31), for example the decreasing principal amounts at each payment date of a loan with 24 monthly payments at a fixed 5% interest rate (all information which is included in the Excel sheet in this case, save for the principal amount which is entered into the B1 cell).
Instead of entering a fixed range of cells, you could refer to a dynamic range using the @str special function and a datafield reference.
If, for example, you were using the Excel sheet to calculate a loan amortisation table you could use the (user determined) number of payments as the end of the range:
In the above table, B1 would contain the principal amount, B3 contains the interest rate, D1 contains the number of payments and cells B7 and below could e.g. contain the automatically calculated decreasing amount of principal (given the other parameters in the Excel sheet).
The @excel function above then outputs the list of principal amounts, which can be used in e.g. a bullet list, table or @for-loop.
This of course also pre-supposes that the source Excel sheet caters for this option, i.e. that the relevant cells in range all contain a formula to calculate the desired results.
