To start using our 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. Your data must be formatted into tables and must have BitCurb 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
Excel Add-In connection details are automatically provisioned upon registration. The user can find them on the Client Profile page under Admin section in BitCurb portal. You would need to provide your Excel App Connection Details to complete the BitCurb Crunch Excel Add-In configuration. For easier you can use the copy button next to Client Id/Client Secret fields.
To generate new keys, click on the “Create New Credentials” button under Excel Add-In Credential Details. Once the Client Id and Client Secret are generated, you need to change their values in the BitCurb Excel Add-In Configuration.
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. This is applicable to office 365 usage.
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.
Once the add-in is installed, you will see the BitCurb Crunch button under Data section in the ribbon:
Clicking on the button will open/close the BitCurb add-in side panel:
If this is the first time you use the Add-In, you would need to set the initial configuration. To do so click on “Add Configuration” button or the gear icon in the bottom right corner to open the Settings page. Registration through BitCurb Excel Add-In is also possible. To proceed with the registration, press the Register button.
To complete the configuration, you will need to provide 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 and API URL by clicking on the “Set Default” link above the fields, which will link you to the default service.
Save the configurations and you can now proceed to the login page.
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.