Before you can use the BitCurb Crunch Excel Add-in, you would need the following prerequisites:
- Client key and secret, obtained by your client portal administrator. Detailed steps can be found below.
- Excel Online or Excel 2016
- Excel data, which will be used for the filtering/matching operations, should be formatted into tables and must have so called system columns depending on the operation you are executing. Check below for details about the name of the system columns.
Excel Add-in connection details
To configure the excel add-in client key and secret, a user assigned in Administrator role would need to login to https://portal.bitcurb.com and open the Client Profile page under Admin section. Under the Excel Add-in Connection Details, click on the “+” button next to the Client Id and Client Secret fields to generate new keys. Once the Id and Secret are generated, save the details and take a note of the values, these would need to be provided to every user using the BitCurb Crunch Excel Add-in.
Install and configure BitCurb Crunch Excel Add-in
There are 2 ways to add BitCurb Crunch Excel add-in to your file
- By searching for BitCurb Crunch in Microsoft Office store
- By searching your organisation add-ins if an administrator has enabled the add-in globally in your tenant.
Installing BitCurb Crunch is for free, however you would need to have a valid BitCurb account with assigned licence to be able to use it.
To complete the configuration, you will need to provide the portal URL and the Client Id and Secret obtained as per the instructions above. If you do not have a dedicated environment set up, you can set the portal URL by clicking on the “Set Default” link above the field, which will link to the default service.
Filter and Reconciliation operations settings in Excel spreadsheets
We support functionality for the following data processing operations:
- Matching data between custom table selections (supported in Excel Online and Excel 2016)
- Filtering data from custom table selections (supported in Excel Online and Excel 2016)
Before starting work with the BitCurb Crunch Add-in, please make sure your sheets are not in read-only mode. The operations performed by our service return result, which has to be written in your files.
Please, note that we don’t reset the content of our custom used columns “BitCurbResult”, “BitCurbGroup” and “SourceBitCurbGroup”. Our add-in will try to locate these columns in your spreadsheets (depending on the operation you are running) and to persist the results in them.
In order to see the results from filtering operations inline in the source data tables, you will need to add “BitCurbResult” column to all tables used in the templates. Filtering operations create in addition a new sheet with the filtered data returned as result.
Ideally you are going to filter only one data set, but we also support filtering two sets placed and formatted as table in two different excel sheets.
In order to see the results from matching operations, you will need to add “BitCurbGroup” and “SourceBitCurbGroup” columns to all tables used in the templates. Matching operations output the result group Id in the “BitCurbGroup” and in case of variance results, a new row containing the variance value will be added to side 1 table with the group Id in “SourceBitCurbGroup” column.
If you reconcile two data sources they could be placed in two different excel sheets. Each of the sources must be formatted as table and must have the “system” columns “BitCurbGroup” and “SourceBitCurbGroup”.
Please, refer our demo files for the filtering and reconciliation scenarios in Excel. The same files are used for our API examples.