This help article gives a step by step overview on how to fetch data from a Bkper book and leverage the power of Google Sheets to report a P&L Statement. More than the P&L itself it explains how you can get to this result.
Introducing the key parts
A Bkper book is a ledger that tracks transactions between accounts. With each posted transaction the balance values of both accounts are updated, giving consistent balance values in time.
The Chart of accounts on a Bkper book organizes accounts into categories that often resemble a Balance Sheet and an Income Statement. But they can also be organized in other more managerial categories.
The Bkper Add on for Google Sheets seamlessly integrates Bkper with Google Sheets and enables users to fetch financial data from a book into a google sheet.
A Bkper function is created on the Google Sheet by the Bkper Add-on to fetch data from your book so it stays connected to your book. From this moment on, each newly posted transaction on your book, updates your P&L report.
To accompany the detailed description below we have prepared:
You can access these samples online and you can make your own copies of both to experiment it yourself.
Chart of Accounts
Key to a P&L report is a well organized chart of account that resembles the data that you want to present in your P&L report.
The Bkper Group Hierarchy lets you organize accounts to resemble accounting definitions. For example Gross Margin = Revenue - Cost of Goods Sold.
This hierarchy on the chart of accounts continues with the equations Income = Gross Margin - Expenses and further Net Income = Income + Income from non operational activities.
The Bkper Add-on for Google Sheets
The Bkper Add-on for Google Sheets seamlessly integrates both Google Sheet with Bkper and vise versa. This enables the effortless retrieval of financial data from your Bkper Book directly to your Google Sheets.
The Bkper Add-on Sidebar opens within Google Sheets and here you can use a simple form on the fetch tab that will help you to define the scope of the data that you would like to retrieve.
The add on form shows that the Bkper Query on the input field is of the Net Income group and the year 2024.
Once you press the blue Fetch button, the Add-on will insert a Bkper Function into your Google Sheet that fetches the corresponding data from the book you selected via the form.
This is the function
=BKPER_BALANCES_TOTAL("agtzfmJrcGVyLWhyZHITCxIGTGVkZ2VyGICAwMWc48ELDA", 1, "group:'Net Income' on:2024", 2, FALSE, FALSE)
This is the data
The Bkper Function
The last element to look at in detail is the Bkper Function that is connected to the Book. You might find this function challenging in the beginning, but remember it is possible to generate it with the Bkper add-on sidebar as described above.
In Cell B5 on the P&L Sheet is this Bkper Function
=BKPER_BALANCES_PERIOD("agtzfmJrcGVyLWhyZHITCxIGTGVkZ2VyGICAwMWc48ELDA", 1, "group:'Net Income' on:2024" ,5, TRUE, false)
Where each item is:
- BKPER_BALANCES_PERIOD is the function that fetches the totals of a period (not of a moment) it has 6 parameters:
1 - "agtzfmJrcGVyLWhyZHITCxIGTGVkZ2VyGICAwMWc48ELDA" is the bookid parameter.
2 - 1 is a cache number it services to update functions dynamically. This number is generated and updated automatically, you can change it manually to force a new fetch.
3 - "group:'Net Income' on:2024" is the query that defines the corresponding data that is fetched from your book
4 - 5 is the level of debt that should be fetched from the Hierarchy on the chart of accounts.
5 - TRUE is the parameter that indicates to transposes the result.
6 - FALSE is the parameter that indicates to hide the date (or not)