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.
Detailed Description
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.
Learn more
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)
Getting Help
If you are thinking this is really nice but you will require professional assistance to organize and maintain your books, you may consider reaching out to the following firms:
Contabnet - Located in Brazil, Contabnet provides accounting and bookkeeping services. They offer expertise in local regulations and you can outsource your bookkeeping to them.
RVG - Based in Dubai, RVG offers professional accounting services. Their team can help with bookkeeping, financial reporting, and other related tasks, ensuring compliance with local requirements.
If you are a professional that is already working with Bkper please let us know since we always receive customer requests for professional guidance.
Of course you can always drop us a line via our support channels if you have any questions related to this help article.
Remember to conduct your own due diligence when selecting a professional firm and ensure that they align with your specific needs and requirements.
Disclaimer
Please note that this article is intended to serve as an example and should not be considered as professional advice.
While we have made every effort to ensure that the information provided is accurate and up-to-date, we cannot guarantee its completeness or suitability for your particular circumstances. We strongly recommend that you work with a local professional, such as a tax advisor or accountant, to ensure that you are compliant with local regulations and laws. Using this help article does not establish a professional-client relationship.
By using this help article, you agree that the author and publisher of this article will not be held liable for any damages or losses arising from the use of it.