Bkper Functions for Google Sheets

Bkper Functions for Google Sheets provide a dynamic way to fetch financial data from Bkper into Google Sheets.

functions can be used to create Balance Sheets, Profit & Loss, Cash Flow report or any other financial statement that you want based on your books.

See an example sheet of what can be accomplished with Bkper Sheets Functions:

Simple General Ledger Report

Creating a Bkper Sheets Formulas

1. Create or open a spreadsheet in Google Sheets.
2. Open Bkper Sheets add-on: select the menu item Add-ons > Bkper Sheets > Open (see how to install the add-on).
3. Write custom formulas on Google Sheets. They are composed by: bookID, cache, query, expanded, transposed
4. All done! Now you can use the Bkper Functions for Google Sheets.

You can also create the Formulas Using the Bkper Sheets Add-on sidebar:

Learn more about the Bkper Sheets Add-on.

Available Google Sheets functions:

Fetch transactions:

BKPER_TRANSACTIONS(bookId, cache, query)

bookId: string - The universal bookId.
cache: number - Cache control. Increase to clean cache and force fetch update.
query: string - The transactions query.

Fetch transactions is useful to export your book transactions into your spreadsheet.

Fetch period balances:

BKPER_BALANCES_PERIOD(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 balances query.
expanded: true/false - Expand group accounts
transposed: true/false - Transpose the result

Fetch balances over a period references to the overall balance credited/debited into accounts and groups in a window of time. It's usually used with Incoming and Outgoing accounts and its very useful to build Profit and Loss statements.

Fetch cumulative balances:

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 balances query.
expanded: true/false -  Expand group accounts
transposed: true/false - Transpose the result

Fetch balances cumulatively, considering the balance of the previous period. It's usually used with Assets and Liabilities accounts and its very useful to build Balance Sheet statements.

When used with Incoming and Outgoing accounts, it will start in 0 and accumulate over the period fetched.

Fetch total balances:

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 balances query.
expanded: true/false -  Expand group accounts
transposed: true/false - Transpose the result

Total balances are balances accounts and groups on a period of the query, without splitting into a time table. Is the same as the last period of the CUMULATIVE balance. Useful to present a simple Balance Sheet or Profit and Loss report for a total period, with details of each month/day.

Advanced concepts

The Bkper Sheets Formula support dynamic queries

With functions, you can create the formulas with query dynamically, based on references to other cells (rows or columns), using &. 

Take a look at this example when we show how the query works dynamically between different pages of the same spreadsheet:

1. On page "Instructions"  there are information that we want to appear on page "Cashflow", so we can make references.

2. On page "Cashflow", we will write the formula based on some informations that are on page "Instructions", for example, the date. 

Notice that:

•  "Instructions!$C$15" in the formula above is the page's name, row and column of the page "Instructions", where we want the information.
•  "TEXT" on formula define how the date appear: May/2016, for example
  

Build the query dynamically, based on that group

You can add the Group name to a cell and build the query dynamically, based on that group, with the & concatenation option, example:

=BKPER_BALANCES_CUMULATIVE(A1; 2; "group:'"& A2 &"' on:" & TEXT(E2; "mm/yyyy");  true; true)

Where A2 is the cell with the Group name. So, you can easily reuse the query and change groups.The date range param on: also dynamic. 

You could also do this using the after: before: so you can easily change the date range by simply changing a cell.