Bkper functions fetch financial data from Bkper into Google Sheets.
With the real-time balance values from Bkper available on Google Sheets it is very easy to create dynamic financial statements. The ability to share and quickly reuse such reports on Google Sheets greatly increase financial productivity.
Get Bkper Functions
The Bkper functions are part of the Bkper Add-on for Google Sheets. So, to use functions, all you have to do is install Bkper from G Suite Marketplace.
Once installed, the functions will be available in the Add-on menu on all your Google Sheets.
Create Bkper formulas
You can create bkper Formulas with a wizard on the add-on or you can write them directly in cells.
- Using the Fetch wizard on the Bkper add-on sidebar.
- Select the menu Add-ons > Bkper > Open (the sidebar opens)
- Select your book from the drop down listbox
- Click on the Fetch Tab
- Write a query
- Select your options
- Press the blue Fetch button.
The Fetch wizard, writes your formula in the selected cell and the data appears within an instance.
- Directly writing formulas in cells.
- Start writing =BKPER_ in a cell. The function autocomplete appears which will guide you through your options of parameters.
Bkper functions
BKPER_TRANSACTIONS
Fetches Transactions from your Bkper book
Sample usage
=BKPER_TRANSACTIONS("agtzfmJrcGVyLWhyZHITCxIGTGVkZ2VyGICAgKD_4bMLDA", 1, "acc:'Bank Account' after:01/2019")
=BKPER_TRANSACTIONS(A10,1, “after:01/2019 before:01/2020”)
Syntax
=BKPER_TRANSACTIONS(bookId, cache, query)- bookId: string - The universal Book Id.
- cache: number - Cache-control. Increase to clean cache and force fetch update.
- query: string - The query.
Note: Useful for backups of your books.
BKPER_BALANCES_PERIOD
Fetches periodic account balance values related to a time range.
Sample usage
=BKPER_BALANCES_PERIOD("agtzfmJrcGVyLWhyZHITCxIGTGVkZ2VyGICAgKD_4bMLDA", 1, “group:'Revenue' after:01/2016 before:01/2017”, TRUE, TRUE, FALSE)
=BKPER_BALANCES_PERIOD(A10, 1, “acc:'Bank Account' after:01/2019 before:01/2020”, TRUE, TRUE, TRUE)
Syntax
=BKPER_BALANCES_PERIOD(bookId, cache, query, expanded, transposed, hideDates)- bookId: string - The universal Book Id.
- cache: number - Cache-control. Increase to clean cache and force fetch update.
- query: string - The query.
- expanded: TRUE | FALSE - Expand Groups into Account details
- transposed: TRUE | FALSE - Transpose the result
- hideDates: TRUE | FALSE - Hide dates row/column
Note: - The result is ordered by account names.
- If not referenced in the query the default period is monthly.
- The periodic balance values are fetched for the debits / credits in a range of time.
- Useful for P&L
BKPER_BALANCES_CUMULATIVE
Fetches the cumulative account balance value related to a time range.
Sample usage
=BKPER_BALANCES_CUMULATIVE("agtzfmJrcGVyLWhyZHITCxIGTGVkZ2VyGICAgKD_4bMLDA", 1, “group:'Assets' after:01/2016 before:01/2017”, TRUE, TRUE, FALSE)
=BKPER_BALANCES_CUMULATIVE(A10, 1, “acc:'Transport' after:01/2019 before:01/2020”, FALSE, TRUE, TRUE)Syntax
=BKPER_BALANCES_CUMULATIVE(bookId, cache, query, expanded, transposed)- bookId: string - The universal Book Id.
- cache: number - Cache-control. Increase to clean cache and force fetch update.
- query: string - The query.
- expanded: true/false - Expand group accounts
- transposed: true/false - Transpose the result
- hideDates: TRUE | FALSE - Hide dates row/column
Note: - The result is ordered by account names.
- With Asset and Liability account types, the balance value of the previous period is considered.
- With Incoming and Outgoing account types the balance value starts at 0 and accumulate over the fetched period.
- Useful for Balance Sheets
BKPER_BALANCES_TOTAL
Fetches the account balance value.
Sample usage
=BKPER_BALANCES_TOTAL("agtzfmJrcGVyLWhyZHITCxIGTGVkZ2VyGICAgKD_4bMLDA", 1, “group:'Revenue' after:01/2016 before:01/2017”, TRUE, FALSE)
=BKPER_BALANCES_TOTAL(A10, 1, “acc:'Bank Account' ”, FALSE, FALSE)
=BKPER_BALANCES_TOTAL(bookId, cache, query, expanded)- bookId: string - The universal Book Id.
- cache: number - Cache-control. Increase to clean cache and force fetch update.
- query: string - The query.
- expanded: true/false - Expand group accounts
- transposed: true/false - Transpose the result
Note: - The result is ordered from the largest to the smallest amount.
The bookid parameter can be found in the URL of each book.
The cache parameter in all the Bkper functions is needed to be able to update itself. With each update this number increases. You can use any value for the cache parameter, but normally 1 is a good reference to start the sequence when you write a Bkper formula.
Reorder results
Use the Google Sheets Query function with "order by" to reorder Bkper function resultsets.
For example, given a Bkper Function on cell A1, with a resultset on the data range A2:B5, on a different cell, you can reorder it like this:
=QUERY(A2:B5, "Select A, B order by A desc")
Ordering in the query function
asc = A -> Z
desc = Z -> A
A working Book and Financial Statement as Example
On our template gallery you can copy working examples of Bkper Books and their corresponding Financial Statements in Google Sheets.
Cells with Bkper formulas are marked grey with a note indicating Bkper Formula on the Financial Statements that comes with the Simple General Ledger Template,
The dynamically constructed Queries are marked with a note indicating the cell where it is used in a Bkper formula.
Troubleshooting
If you have any issues editing the Bkper Functions, please head to this article.