GET GL Net Change

This Excel example shows how to retrieve the net change in a GL account between certain dates. It introduces some concepts not covered in this blog yet such as building complex criteria with JSON and pagination – retrieving records in batches when there are expected to be many.

The net change code uses the API endpoint “gl/transactions” so it is suitable for getting the net change for a date range other than an entire fiscal period.

As with other examples, you will need to enter your company configuration information into the “CompanyInfo” tab in the spreadsheet.

A new feature here is the parameter “stepsize”. This is how many records will be retrieved from the API in one go. The larger you can get away with the faster the overall process will be.

The function, GetGLAcctDescription will retrieve the description of the GL account. It requires 3 parameters:

  • GL Division
  • GL Acct Number
  • Currency (blank for base currency)

The GetGLNetChange function requires 6 parameters:

  • GL Division
  • GL Account Number
  • Currency (blank for base currency)
  • The “normal balance” of the account C for a credit D for debit
  • The starting date
  • The ending date

This function will refresh if any of the parameters are changed. So if you are changing both dates, it will refresh all instances of this function in your spreadsheet twice, once when you change the start date and once when you change the end date. To avoid this, you can modify the automatic recalculation options in Excel.

If you want to force a recalculation even when you have not changed any parameters, use Ctrl-Shift-Alt-F9.

Here is a link to download the sample spreadsheet.

Note: All values returned are the base currency values, not the foreign currency values.