To be frank, I'm disappointed by accounting apps. Each one is functionally limited, buggy or ugly—sometimes all three. Most recently, I was attracted to the sleek aesthetic and affable tone of FreshBooks, only to find its savvy branding to be lipstick on a pig. One particularly agonizing experience was the last straw, so I committed to taking my bookkeeping elsewhere.
This was shortly after the debut of Notion's API, which introduced a world of automation opportunities. Already, Notion's unique blend of documents and databases made it an excellent tool for managing finances, and the API resolved all lingering inadequacies. Thus, I endeavored to build a more dependable, versatile and gratifying accounting system in Notion.
Even more useful than the outcome of constructing this system is the educational value of the process. Notion is best learned through practical implementation, and everyone manages finances in some capacity. Let's construct a simple version of my accounting system for a practical understanding of Relations and Rollups, Formulas, database templates, and even the API. This specific implementation works best for me, but you can apply the concepts to your own bespoke accounting system and other functions of your custom workspace.
Watch the video above for a detailed demonstration or read the text below for an overview. You can also duplicate the system from Notion Market for your own analysis and customization.
If you're entirely new to these concepts, you may want to start with these introductory posts:
If you're familiar with my Notion resources, you know I approach every workspace with an essential tenet: Use databases, not pages.
As the foundation of my Bulletproof framework, this strategy is known as The Bulletproof Principle. I detail it further in the post Optimize Your Workspace for the Notion API, but here's the general approach:
Linked Databases, which are filtered, formatted and sorted to display your information in various contexts. For example, you view tasks filtered for a particular project, and resources filtered for a particular category.Linked Databases live in two primary locations:Relation properties. For example, a Project template might filter the Tasks, Resources and Meetings databases for the project's related items.
These concepts will make more sense as we develop our accounting system.
For our contained system, we'll have four interrelated databases within a page called "Accounting Databases."
The Accounts database contains three types of accounts:
For each account, the database will display the current balance.
The Transactions database aggregates all transactions, granularly logging the movement of very dollar. It's the only place you input information; all other databases perform calculations based on transactions. Filtered views allow you to manage and summarize transactions in helpful segments.
Each transaction is categorized and Related to an account, as well as an invoice if applicable.
Transaction types include:
The Invoices database displays helpful information for each invoice. It's entirely automated.
The Organizations database includes the vendors you pay and the clients who pay you. An Organizations database typically serves many other needs in a workspace.
Create a page called "Accounting Databases" to contain the databases that comprise your system.
Within "Account Databases," create four full-page databases:
For the page and database icons, consider using my Notion Icons tool.
Open the Organizations database and configure these properties:
Title)Multi-Select) Relation) 
A more widely used Organizations database typically contains multiple views, but for our demonstration, we need only to sort the default Table view by Organization, then hide the Transactions property.
Within "Accounts," configure these properties:
Title)Select)Date) Relation) 
You're unlikely to require multiple views of the Accounts database, so I recommend:
For initial configuration, add one account for each type. Choose the type within the Type property.
The Transactions database is the hub of your accounting system. It's where you input all information, which other databases use for calculations.
The Transactions database can seem overwhelming, but it's much simpler when you consider one transaction type at a time. Filtered views make it easy to manage.
Here's a breakdown of the transaction types, each of which will have corresponding options in the Categories property:
Balance → These are used to adjust your balance when conventional transactions are inapplicable. They can increase or decrease an account's balance. Categories include Opening Balance and Balance Adjustments.
Equity → Equity transactions are owner contributions and withdrawals that do not affect the business's reported revenue or expenses. They can increase or decrease an account's balance. Categories include Owner's Draw and Owner's Contributions.
Expense → Expenses are the purchases of goods and services that are deducted from revenue to calculate net income. In this system, expenses always reduce an account's balance. Example categories include SaaS, Transaction Fees, Hardware and Healthcare.
Revenue → Revenue transactions include full invoice amounts and other income prior to transaction fees and other associated expenses. In this system, revenue always increases an account's balance. Also worth noting is that revenue for an invoice is registered when the invoice is issued. The selected account is Account Receivable. When the invoice is paid, the amount is transferred from Accounts Receivable to the funded account. Example revenue categories include Invoice Issued and Other Income.
Transfer → Transfers migrate assets from one account to another. Each transfer includes two transactions: one for the origin account, another for the destination account. Example categories include Invoice Payment and Payment Gateway Payout.
Title)Formula) Formula property blank. We'll populate it after creating the other properties.Date)Select) Select) Relation) Relation) Relation) Text) Number) Number) Select) Select) Formula) if(prop("Status") != "Planned", prop("Money In") - prop("Money Out"), 0)With all properties created, you can populate the Title Generator property. It's important to use a unique title for all database items, so this property automatically generates a title from other property values, which you can easily copy and paste into the Title property. Here's the formula:
formatDate(prop("Date"), "YYYY-MM-DD") + " › " + prop("Account") + " › " + prop("Type") + if(empty(prop("Payee")), "", " (" + prop("Payee") + ")") + if(empty(prop("Invoices")), "", " (" + prop("Invoices") + ")")
Your bookkeeping is unlikely to begin with empty accounts. With the "Balance" type and "Opening Balance" category, you can establish your starting balance.
A typical monthly expense is a subscription to Zapier.
When an invoice is created and delivered to a client, it's logged as a "Revenue" transaction:
When a client makes an invoice payment, it's logged as a "Transfer" from Accounts Receivable to the funded account. This involves two transactions—one for the origin account, the other for the destination account. The values are the same for both transactions unless otherwise noted.
Many businesses receive revenue without issuing invoices. For these, you can create specific categories, such as "Template Sales," or use "Other Income."
Payment gateways, such as PayPal and Stripe, charge a fee for most transactions. You'll log these fees as expenses. I often aggregate a month's worth of fees into a single transaction, particularly when payouts are monthly.
When an owner contributes or withdraws equity from the business, you'll log an equity transaction.
A payout from a payment gateway is typically a transfer from the gateway account to a bank account. You'll log a transaction for each account, where most of the values are the same.
Preconfigured views make it easy to manage your Transactions database. You can view your transactions by Type, Category, Account, Status and other properties. Here are the views I find most helpful—each formatted as a table and sorted by Date:
Is not "Complete"Is "Expense"Is "Invoice Issued" Or Category Is "Invoice Payment" Or Category Is "Invoice Cancellation"Is "Transfer"Is "Other Income"Is "Balance" Or Type Is "Equity"You can also view transactions by account after creating the Accounts database template.
The Invoices database populates all properties from Related transactions. Prior to creating the properties, populate various invoice transactions, which will create a few sample invoices.
Title)Relation) Relation to the Transactions database will already be in place; you simply need to rename it.Rollup) Rollup determines retrieves the payee from the Related "Invoice Issued" transaction. To configure it, choose "Transactions" for the Relation, "Payee" as the Property, and "Show unique values" as the Calculation.Rollup) Rollup determines the date issued from the earliest associated transaction. To configure it, choose "Transactions" for the Relation, "Date" as the Property, and "Earliest date" as the Calculation. Because the date is displayed in a useless format, we'll hide it, and instead view the date in a formatted Formula.Formula) formatDate(prop("Date Issued (Rollup)"), "MM/DD/YYYY")Rollup) Rollup determines the final payment or cancellation date from the latest associated transaction. To configure it, choose "Transactions" for the Relation, "Date" as the Property, and "Latest date" as the Calculation. Because the date is displayed in a useless format, we'll hide it, and instead view the date in a formatted Formula.Formula)formatDate(prop("Date Completed (Rollup)"), "MM/DD/YYYY")Rollup) Rollup determines the total amount invoiced from the highest "Balance Effect" value among linked transactions. To configure it, choose "Transactions" for the Relation, "Balance Effect" as the Property, and "Max" as the Calculation.Rollup) Rollup determines the amount deducted from the invoiced amount, whether through payment or cancellation. It only serves for other calculations, so we'll hide it. To configure it, choose "Transactions" for the Relation, "Money Out" as the Property, and "Sum" as the Calculation.Rollup) Rollup retrieves the categories for all associated transactions. The property is only used as a reference for other properties, so we'll hide it. To configure it, choose "Transactions" for the Relation, "Category" as the Property, and "Show original" as the Calculation.Formula) if(contains(prop("Transactions › Categories"), "Cancel"), "Cancelled", if(prop("Paid / Cancelled") == 0, "Unpaid", if(prop("Paid / Cancelled") == prop("Issued Amount"), "Paid", "Partially Paid")))Formula) 0. Here's the formula: if(prop("Status") == "Cancelled", 0, prop("Paid / Cancelled"))Formula) if(prop("Status") == "Cancelled", 0, prop("Issued Amount") - prop("Paid / Cancelled"))A handful of the Invoices properties are used only as references for other properties. Therefore, you can hide them from all views. Those properties include:
With the remaining properties, I recommend a view for "Open Invoices" and "All Invoices"—both sorted by Date Issued. For "Open Invoices," filter for the items where:
Is "Paid"Or "Status" Is "Partially Paid"
With all Relations established, you can calculate the balance for each account:
Rollup property called "Balance."Relation, choose "Transactions."Property, choose "Balance Effect."Calculation is "Sum."
In addition to your preconfigured views of the Transactions database, you can automatically filter transactions within each account by creating a template:
New button and choose + New template.Linked Database. Search for and select your Transactions database.Contains "[Template]." The template should be the topmost option.Thereafter, when you click the template within a project, it will automatically display transactions filtered for that project.
Just as you've summarized your finances by account, you could do the same with years by Relating a Years database to Transactions. The "Year" template could include multiple Linked Databases—one for each transaction type—to generate an intuitive report for accountants and other stakeholders.
With integrations facilitated by Notion's new API, you can automate much of this accounting workflow. By connecting Notion to your banks and payment gateways, you can automatically add transactions based on predefined conditions.
You can also populate invoices that are issued from tools like Harvest, AND.CO, and my dreaded FreshBooks. To do so, you'll use Zapier, Automate.io or Integromat to build an automation that's triggered when a new invoice is created. The action is then to create an item in your Invoices database.
If you hit any roadblocks while exploring these concepts, I'm all ears @WilliamNutt. If you're able, consider duplicating and dissecting the template from Notion Market.