Learn Notion by Building an Accounting System

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:

• Notion Explained: Relations & Rollups
• Meet Notion’s Formula Property
• Notion Explained: The API Debut
• Optimize Your Workspace for the Notion API

The Bulletproof Principle

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:

• A “Master Databases” page centralizes all information within master databases, such as Projects, Tasks, Companies, People and Resources.
  
  
  
• You rarely visit those databases directly. Instead, you access them through 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.
• These contextual Linked Databases live in two primary locations:
  1. A top-level “Headquarters” page, which might be the only non-database page in your workspace
  2. Within other databases, where preconfigured templates automatically filter for items linked by 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.

System Snapshot

For our contained system, we’ll have four interrelated databases within a page called “Accounting Databases.”

Accounts

The Accounts database contains three types of accounts:

• Bank Account (Asset)
• Payment Gateway (Asset)
  These are services used to collect payments, such as Gumroad, PayPal and Stripe. Their balances reflect received payments, along with transaction fees, before payouts.
• Accounts Receivable (Liability)
  This account reflects the unpaid invoice balance.

For each account, the database will display the current balance.

Transactions

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:

• Balance
• Equity
• Expense
• Revenue
• Transfer

Invoices

The Invoices database displays helpful information for each invoice. It’s entirely automated.

Organizations

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.

Build the databases.

Create a page called “Accounting Databases” to contain the databases that comprise your system.

Within “Account Databases,” create four full-page databases:

• Organizations
  A centralized Organizations database is common in workspaces. If you already have one, you can simply add the properties for the accounting system rather than creating a new one.
• Accounts
• Transactions
• Invoices

For the page and database icons, consider using my Notion Icons tool.

Organizations

Create the properties.

Open the Organizations database and configure these properties:

• Organization (Title)
• Categories (Multi-Select)
  Preconfigure the options “Client” and “Vendor.”
• Transactions (Relation)
  Relate this to the Transactions database.

Configure the view.

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.

Accounts

Create the properties.

Within “Accounts,” configure these properties:

• Account (Title)
• Type (Select)
• Last Reconciled (Date)
  I like to use this property to indicate the date through which I’ve reconciled the balance with the statement.
• Transactions (Relation)
  Relate this to the Transactions database.

Configure the view.

You’re unlikely to require multiple views of the Accounts database, so I recommend:

• hiding the Transactions property; and
• sorting by Category, then Account.

Add the accounts.

For initial configuration, add one account for each type. Choose the type within the Type property.

• Checking → Bank Account
• Stripe → Payment Gateway
• Accounts Receivable → Accounts Receivable

Transactions

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.

Create the properties.

• Transaction (Title)
• Title Generator (Formula)
  Leave this Formula property blank. We’ll populate it after creating the other properties.
• Date (Date)
• Type (Select)
  Preconfigure the options: Balance, Equity, Expense, Revenue, Transfer
• Category (Select)
  Each transaction type has its own categories, some examples of which I offered above when explaining each type. I like to color my categories to match their “parent” type. Add your own to suit the nature of your operations.
• Account (Relation)
  This property will already be present as a reciprocal of your Transactions property in your Accounts database. Simply rename it “Account.”
• Payee (Relation)
  This property will already be present as a reciprocal of your Transactions property in your Organizations database. Simply rename it “Payee.”
• Invoices (Relation)
  Relate this to the Invoices database.
• Additional Context (Text)
  Use this property to add any notes about the transaction you may need to reference in the future.
• Money In (Number)
  For transactions that increase your account balance, you’ll add the amount to this property. Format it in your preferred currency.
• Money Out (Number)
  For transactions that decrease your account balance, you’ll add the amount to this property. Format it in your preferred currency.
• Status (Select)
  The Status identifies which transactions to include in the calculated account balance. It also adds another helpful method of filtering your transactions. I use the statuses “Complete,” “Processing,” “In Preparation” and “Planned.”
• Frequency (Select)
  The Frequency property makes it easy to recreate recurring transactions and monitor subscriptions. My two frequencies are “Monthly” and “Annual.”
• Balance Effect (Formula)
  The Balance Effect property is referenced by the Accounts database to calculate each account’s current balance. For all transactions without the “Planned” status, it subtracts Money Out from Money In: 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") + ")")

Example Transactions

Opening Balance

Your bookkeeping is unlikely to begin with empty accounts. With the “Balance” type and “Opening Balance” category, you can establish your starting balance.

• Transaction — (Pasted from Title Generator after other properties are populated)
• Date — Jun 1, 2021
• Type — “Balance”
• Category — “Opening Balance”
• Account — “Checking”
• Money In — $96,750.00
• Status — “Complete”

Monthly Expense

A typical monthly expense is a subscription to Zapier.

• Transaction — (Pasted from Title Generator after other properties are populated)
• Date — Jun 10, 2021
• Type — “Expense”
• Category — “SaaS”
• Account — “Checking”
• Payee — “Zapier”
  The Payee is selected from the Organizations database. If necessary, create a new organization.
• Additional Context — “Automation service”
• Money Out — $50.00
• Status — “Complete”
• Frequency — “Monthly”

Invoice Issued

When an invoice is created and delivered to a client, it’s logged as a “Revenue” transaction:

• Transaction — (Pasted from Title Generator after other properties are populated)
• Date — Jun 4, 2021
• Type — “Revenue”
• Category — “Invoice Issued”
• Account — “Accounts Receivable”
• Payee — “Blue Ribbon Sushi”
  This is the client, selected from the Organizations database. If necessary, create a new organization.
• Invoice — 103
  This is the invoice from the Invoices database. You’ll create it as a new item when logging its first transaction, “Invoice Issued.”
• Money In — $4,000.00
• Status — “Complete”
• Frequency — “Monthly”

Invoice Payment

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.

• Transaction — (Pasted from Title Generator after other properties are populated)
• Date — Jun 5, 2021
• Type — “Transfer”
• Category — “Invoice Payment”
• Account — “Accounts Receivable” for one transaction, “Checking” for the other The second account is the one funded by the payment.
• Payee — (Blank)
  The client was indicated in the initial “Invoice Issued” transaction.
• Invoice — 103
  Select the invoice from the Invoices database, which was created at the “Invoice Issued” transaction.
• Additional Context — “Paid with Ripple” Add any information that may be helpful useful in the future.
• Money Out — $4,000.00 (for the Accounts Receivable transaction, blank for the other)
• Money In — $4,000.00 (for the destination transaction, blank for Accounts Receivable)
• Status — “Processing”
  You’ll update the status once the transaction fully processes in your receiving account.

Non-Invoice Income

Many businesses receive revenue without issuing invoices. For these, you can create specific categories, such as “Template Sales,” or use “Other Income.”

• Transaction — (Pasted from Title Generator after other properties are populated)
• Date — Jun 14, 2021
• Type — “Revenue”
• Category — “Other Income”
• Account — “Stripe”
  Unlike invoice payments, non-invoice payments bypass Accounts Receivable. Instead, you’ll use the funded account.
• Payee — “UNC-Chapel Hill”
  This is the customer, selected from the Organizations database. If necessary, create a new organization.
• Money In — $4,000.00
• Status — “Complete”

Transaction Fee

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.

• Transaction — (Pasted from Title Generator after other properties are populated)
• Date — Jun 1, 2021
• Type — “Expense”
• Category — “Transaction Fees”
• Account — “Stripe”
  Typically, you’ll log these fees in the payment gateway account. Its payouts will then deduct the fees from its revenue to determine the payouts.
• Payee — “Stripe”
  In this case, the vendor is likely to be the same as the payment gateway; however, note that one selects from the Accounts database, the other from Organizations.
• Additional Context — “Fees for May 2021 transactions”
  If you aggregate fees, you can note the transactions covered in this property.
• Money Out — $122.39
• Status — “Complete”

Equity

When an owner contributes or withdraws equity from the business, you’ll log an equity transaction.

• Transaction — (Pasted from Title Generator after other properties are populated)
• Date — Jun 1, 2021
• Type — “Equity”
• Category — “Owner’s Draw”
• Account — “Checking”
• Money Out — $10,000.00
• Status — “Complete”

Payment Gateway Payout

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.

• Transaction — (Pasted from Title Generator after other properties are populated)
• Date — Jun 3, 2021
• Type — “Transfer”
• Category — “Payment Gateway Payout”
• Account — “Stripe” for one transaction, “Checking” for the other
  The first is the gateway; the second is the account receiving the funds.
• Additional Context — “Template sales: May 2021”
  Add any information that may be helpful useful in the future.
• Money Out — $4,087.61 (for the gateway’s transaction, blank for the other)
• Money In — $4,087.61 (for the destination transaction, blank for the gateway)
• Status — “Processing”
  You’ll update the status once the transaction fully processes in your receiving account.

Configure the views.

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:

• Non-Complete — Status Is not “Complete”
• Expenses — Type Is “Expense”
• Invoices Category Is “Invoice Issued” Or Category Is “Invoice Payment” Or Category Is “Invoice Cancellation”
• Transfers — Type Is “Transfer”
• Other Income — Category Is “Other Income”
• Equity and Balance Type Is “Balance” Or Type Is “Equity”
• Admin — (Unfiltered)

You can also view transactions by account after creating the Accounts database template.

Invoices

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.

Create the properties.

• Invoice ID (Title)
• Transactions (Relation)
  This reciprocal Relation to the Transactions database will already be in place; you simply need to rename it.
• Client (Rollup)
  This 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.
• Date Issued (Rollup) (Rollup)
  This 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.
• Date Issued (Formula)
  This property displays the value of “Date Issued (Rollup)” in a friendlier format. Here’s the formula: formatDate(prop("Date Issued (Rollup)"), "MM/DD/YYYY")
• Date Completed (Rollup) (Rollup)
  This 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.
• Date Completed (Formula)
  This property displays the value of “Date Completed (Rollup)” in a friendlier format. Here’s the formula: formatDate(prop("Date Completed (Rollup)"), "MM/DD/YYYY")
• Issued Amount (Rollup)
  This 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.
• Paid / Cancelled (Rollup)
  This 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.
• Transactions › Categories (Rollup)
  In order to determine whether an invoice has been cancelled, this 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.
• Status (Formula)
  This property references the “Paid / Cancelled” and “Transactions › Categories” properties to determine whether the invoice is “Unpaid,” “Partially Paid,” “Paid” or “Cancelled.” Here’s the formula: if(contains(prop("Transactions › Categories"), "Cancel"), "Cancelled", if(prop("Paid / Cancelled") == 0, "Unpaid", if(prop("Paid / Cancelled") == prop("Issued Amount"), "Paid", "Partially Paid")))
• Paid (Formula)
  If the invoice is not cancelled, this formula returns the value of “Paid / Cancelled”; otherwise, it returns 0. Here’s the formula: if(prop("Status") == "Cancelled", 0, prop("Paid / Cancelled"))
• Outstanding (Formula)
  If the invoice is not cancelled, this property returns the difference between the invoiced amount and the amount paid. Here’s the formula: if(prop("Status") == "Cancelled", 0, prop("Issued Amount") - prop("Paid / Cancelled"))

Configure the views.

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:

• Transactions
• Date Issued (Rollup)
• Date Completed (Rollup)
• Paid / Cancelled
• Transactions › Categories

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:

• “Status” Is “Paid”
• Or “Status” Is “Partially Paid”

Calculate balances.

With all Relations established, you can calculate the balance for each account:

• In the Accounts database, add a Rollup property called “Balance.”
• For the Relation, choose “Transactions.”
• For the Property, choose “Balance Effect.”
• The Calculation is “Sum.”

Create an account template.

In addition to your preconfigured views of the Transactions database, you can automatically filter transactions within each account by creating a template:

• At the top of the Accounts database, click the arrow within the New button and choose + New template.
• Give it a title, such as “[Company] Account,” or just “Account.”
• Within the page body, add a Linked Database. Search for and select your Transactions database.
• You may want to display all of the account’s transactions or create a view for each type. What’s important is that you filter them all by the Account template (the one you’re editing). Configure the filter to display items where the Account property Contains “[Template].” The template should be the topmost option.
• You can then apply additional filters, sort, and selectively display properties as per your preferences.

Thereafter, when you click the template within a project, it will automatically display transactions filtered for that project.

Create annual reports.

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.

Automate with the API.

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.