LogoLogo
ClausesDatafieldsSpecial FunctionsStylingQ&AAPI
  • Welcome!
  • Getting started
    • What is Clause9?
    • Structuring your clause library
    • Structuring your clauses
    • Drafting modes in Clause9
    • Creating a questionnaire
    • Sample clauses
    • Videos
      • Concepts and datafields
      • Conditions
      • Q&As
      • Binders
      • Styling
      • Enumerations
      • Tables
      • Definitions
      • Snippets
      • Cross-references
      • Special functions
      • Examples of common clauses
      • Import clauses from MS Word
      • Grammatical conjugations
      • Action buttons
      • Alternative clauses
  • Assemble document
    • Document toolbar
    • Clause hierarchies
    • Focus Mode
    • Bulk generation of documents
    • Exporting documents
    • Assemble Document - FAQ
    • How to: Assemble Document
      • Insert images
  • Assemble Document Operations Panel
    • Operations panel
    • File pane
    • Edit pane
    • Document pane
    • Binder pane in the operations panel
    • Search pane
    • Browse pane
    • Terms pane
    • Data dashboard
    • Advanced pane
    • Styling pane
    • Miscellaneous pane
    • Visibility settings & actions menu
  • Binders
    • Binders: general
    • Styling cross-references to subdocuments
    • Global and local definition lists
    • Document and binder properties
    • Styling of a Binder versus subdocuments
    • (Un)locking documents in a binder
    • Binders - FAQ
    • How to: binders
      • Make a subdocument in a binder conditional
  • Clauses
    • Introduction to clauses
    • Clause structure
    • Grammar sheet
    • Writing conditions
    • Examples of conditions
    • Using codes instead of text fragments
    • Bold, italic and underline
    • Special codes
    • Enabled?
    • Links
    • Cross-references
    • Introduction to tables
    • Deviating table styling
    • Shrinking clauses
    • Action buttons
    • Enumerations
    • File position
    • Snippets
    • Parameters
    • Conjugations
    • Mixing data types
    • For-loops
    • Clause versioning
    • Abstract article references
    • Advanced multi-language features
    • Clauses - FAQ
    • How to: clauses
      • Create an ad-hoc clause
      • Create a library clause
      • Make a clause repeat
      • Make a paragraph within a clause conditional
      • Use a shortcut to refer to a concept
      • Insert a line break or page break
      • Creating a list with both predefined options and free input
      • Defining alternative clauses
      • Creating cross-references
      • Creating signature blocks
      • Creating advanced party introduction clauses
      • Automatically numbered annexes or schedules
      • Reuse any clause in a different context
      • Setting MS Word document properties
      • Add action buttons to clauses
      • Electronically signing documents
  • concepts
    • Introduction to concepts
    • Creating concepts
    • Concept labels
    • Links
    • Organising concepts
    • Concepts - FAQ
    • How to: concepts
      • Add predefines to a datafield
  • Datafields
    • Introduction to datafields
    • Types of datafields
    • Rules of thumb for using datafields
    • Data-expressions
    • Datafield aliases
    • Datafield labels
    • Datafield special tags
    • Datafield descriptions
    • Repeating list datafields
    • Datafield predefines
    • Datafields - FAQ
    • How to: datafields
      • Change datafield type
      • Change the datafield's name or alias
  • Definitions
    • Introduction to definitions
    • How to: definitions
      • How do definitions work?
      • Create a definition
  • Files
    • How files are organised
    • Browse files
    • File types
    • Custom styling
    • Legal comments
    • File description
    • Attributes
    • Reporting
    • File name
    • File category
    • Access rights
    • How to: files
      • Creating advanced folders
      • Naming your files
      • Shortcuts to folders or files
  • Q&A
    • About cards
    • Cards pane
    • About changes
    • Changes pane
    • Types of changes
    • Adding conditions
    • Question options
    • Copying & pasting answers
    • Comments, notes & documentation
    • Interactive Q&A inspection
    • Embedding questions into a document
    • “Changes” button
    • Batch create pane
    • Identifiers pane
    • Import pane
    • Edit clauses pane
    • Q&A options
    • Q&A - FAQ
    • How to: Q&A
      • Create predefined answers to a question
      • Add disclaimers
      • Create categories of questions
      • Modify the exported filename
      • Create a question to change the language of a document
      • Send a questionnaire to someone without a ClauseBase account
      • Create questions for repeating list datafields
      • Selecting legal entities & addresses
      • Create a questionnaire using "batch create"
      • Launch other Q&As
    • Leveraging ClauseBuddy Smart Templates in Clause9
  • Import
    • Introduction to importing clauses
    • Uploading clauses
    • Defined terms in Import mode
    • Datafields in Import mode
    • Cross-references in Import mode
    • Assigning folders
    • Conversion process
    • Exporting
    • Stashing intermediate results
    • Tips, tricks & limitations
  • Styling
    • Styling overview
    • Base styling
    • Numbering
    • Definitions styling
    • Enumerations styling
    • Locale styling
    • References styling
    • Page styling
    • Styling of a Binder versus subdocuments
    • Styling: tips and tricks
    • Advanced styling topics
    • Copying headers and footers from an MS Word file
    • How to: styling
      • Using custom fonts
      • Change bullet styling
  • Special functions
    • Introduction
    • Calculations
    • Concepts
    • Conditions
    • Conjugations
    • Content Control Elements
    • Datafields
    • Dates & durations
    • Languages
    • Lists
    • Numbers
    • References
    • Repeating (looping)
    • Special items
    • Text structure
    • Text modification
    • User
    • Q&A
  • Settings
    • Account
    • Preferences
    • Access bundles
    • Favourites
    • Saved searches
    • Saved datafields
    • Styles
    • Default styles
  • Admin
    • General
    • Users
    • User rights
    • Profiles
    • Groups
    • Styles
    • Default styles
    • Attribute models
    • Usage page
    • Custom homepage
    • Global placeholders
    • Access rights
    • How to: admin
      • Adding a new user
      • Disabling a user
      • Managing group memberships
  • Miscellaneous
    • Advanced tips & tricks
    • Typing special symbols on your keyboard
    • Shortcuts
    • Grammar style guide
    • Inserting MS Word files
    • Globo-panel
    • Creating high-quality documents
    • Excel calculations and lookups
  • Integrations
    • Overview
    • Spreadbases
    • E-signing documents
    • Drag & drop integrations
  • For developers
    • Clause9 API
    • Custom functions
    • Example custom functions
Powered by GitBook
On this page
  • Rationale
  • How?
  • Dynamic cell range
Export as PDF
  1. Miscellaneous

Excel calculations and lookups

PreviousCreating high-quality documentsNextOverview

Last updated 8 months ago

Rationale

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 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

  • ...

How?

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).

Dynamic cell range

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:

@excel("loan-sheet", @str("B7:B", @EXCEL-INDEX)"B1", @extract-number(#loan^principal), "B3", @INTEREST-RATE, "D1", #loan^nr-payments)

INTEREST-RATE = {#loan^interest-rate/100}

EXCEL-INDEX = {#loan^nr-payments + 7}

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).

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.

Instead of entering a fixed range of cells, you could refer to a dynamic range using the and a datafield reference.

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
spreadbase integration
@str special function