Bkper Functions: Turn Google Sheets into a powerful accounting tool
Today we are launching Bkper Functions for Google Sheets, making it easier than ever before to create real time Cash flow, Balance Sheet and Profit & Loss statements with data from Bkper, turning Google Sheets into a powerful accounting tool for reporting and analysis.
The Bkper Functions are shipped with the Google Sheets Add-on for Bkper, so you have no extra work on a separate installation.
Robust and Dynamic
The new Bkper Functions, fit the robust way of working with Google Sheets functions used to transform and normalize data for reporting or analyses. Bkper Functions build further on this proven concept so in essence the way you do your finances on Google Sheets does not change, besides that you now work with clean sheets and real time data.
Just like with Google Sheets Functions, you can dynamically generate Bkper formulas to consult different Books and periods. But instead of using data on Sheets, they fetch balance values and transactions directly from Bkper where you consistently keep your financial history in the double entry standard with real time balance values organized in Balance Sheet and Profit and Loss statements.
Once a Bkper report, analyses or dashboard on Google Sheets is ready and you want to use it for another client or entity it is a matter of copying & pasting it, use a different bookId and you’re ready to go. Increasing your agility, as you no longer need to maintain mappings between Sheets with query functions or adjust Sheets formulas.
Copy a Bkper General Ledger Template yourself
Bkper Functions to fetch balance values
The new Bkper Functions come together with the Google Sheets Add-on for Bkper. So for those already using the Add-on, we kept things easy and they only have to run an update on the menu of the Add-on for Bkper to update the cells where they fetched Bkper queries, with Bkper formulas.
For those new to the Google Sheets Add-on for Bkper, the great thing is that the fetch functionality on the Add-on works as a wizard, defining the Bkper formulas for you. So all you have to do is write the query and fetch it, to find the formula in the selected cell.
The Google Sheets formula autocomplete will guide you to write your own =Bkper( ) formulas manually without the need of using the fetch wizard on the Add-on.
Easier Reporting, Faster insights
To make reporting easier, we also introduce Expanded and Transpose as optional parameters on the Bkper Functions.
Just query for a Group, set Expanded to “true” and all the accounts and their balance values in that Group are listed on your Sheet. So, now, there is no more need to run separate queries on all accounts in order to fetch their balance values.
Setting Transpose to “true” switches from a column to a row listing of accounts and their balance values, making it easy to project cash flows for example.
Report Anything with 4 Bkper Functions
-
Fetch transactions: =BKPER_TRANSACTIONS(bookId, cache, query)
-
Fetch cumulative balances: =BKPER_BALANCES_CUMULATIVE(bookId, cache, query, expanded, transposed, hideDates)
-
Fetch period balances: =BKPER_BALANCES_PERIOD(bookId, cache, query, expanded, transposed, hideDates)
-
Fetch total balances: =BKPER_BALANCES_TOTAL(bookId, cache, query, expanded, transposed, hideNames)
Learn how to make a cash flow on Google Sheets with these formulas
Update: August 2020
We have added a new function
- Fetch the chart of accounts: =BKPER_ACCOUNTS(bookId, cache)
