Skip ahead:
Introduction
The goal of this post is to help you connect the Karbon API to Excel.
I will be updating this post with better code (more comments), and instructions on how to get this set up. I may do a series of posts loosely related to pulling in data from Karbon to model and use in Excel using Power Query and Power Pivot.
If you use any of these functions or have better ways of executing this code, I would love to see your comments.
Specific use cases should be addressed in new topics and then linked back to this topic. The most useful and interesting use cases posted in the comments will get added to this OP.
Template Files
Link: Human Accounting Mastermind Document Repository
In the link above, you will find template files for Excel and Power BI (coming soon). These files have everything you need to get started, but there are some additional setup steps that I will cover below.
Instructions
How to add your tokens
Add your credentials to the workbook
You need two tokens to access Karbon’s API:
- Bearer Token sent to you by Karbon when you register
- Access Key found in your Karbon settings:
Once you have your tokens, open the Karbon API-connected Excel Template file (see link above). Follow these instructions to add your bearer token and api access key to the file:
- Add your bearer token to the token_bearer() query “Current Value” field
- Add your API Access Key to the token_access_key() query “Current Value field
Next, you’ll need to select each query and tell it to connect anonymously:
After the API runs, you’ll start seeing a preview of your data right away. Make sure you complete this step for all the queries in the list. Your list should look like this:
If everything looks good, tap the close and load button on the Home ribbon:
How to model your data
I was able to get dummy data, so the information below is for you to use and learn, but the steps have already been completed in the template file.
Before you can use the data that comes from the carbon API, you need to connect it so that it makes sense. Get started by taking a look at the tables in your data model.
Next you will connect your data tables. This can get a little complicated, and a lot can go wrong, so don’t expect your reports to work flawlessly right away. Depending on the kind of reports you want to get out of Karbon, you will connect your keys in different ways. The scope of building specific reports is beyond this post. Here’s an example of how you might setup your data model:
[I plan to release a file that has the connections already in place, but I’m working through a technical limitation right now. Eventually, you’ll be able to download template files with complete reports built out]
How to refresh your data
Pull in new data from Karbon
Once you enter your keys and model your data, you’ll need to refresh your data to update pull in any changes you make in Karbon.
- Navigate to the “Data” ribbon
- Tap “Refresh All”
In the bottom corner you’ll see the refresh running. Once it’s complete, your visuals should update automatically.
LIMIATAION: If you protect the sheet, the pivot tables may not update until you re-select any filters applied to the table.
Troubleshooting
If you get a firewall error
Excel and Power BI are careful not to send your data from one source to another source (like sending Karbon data to another API from which you are receiving data). For this case, you do not need to worry about it, because the only connection is to Karbon, so there is no concern if Karbon data gets mixed with Karbon data.
Navigate to privacy settings and select Ignore privacy levels:
Open the Power Query Editor by double-tapping on one of the queries in the query pane:
From the query editor, follow these instructions:
You may find it useful to try the top and bottom setting to get the queries to work properly.
Use-Cases and Examples
Basic Analytics
One of the template files now contains very basic analytics and charts. I would love to see what you come up with, so drop some screenshots in the comments if you put this to good use. See the repository link at the beginning of this post.
