I spent the whole morning today taking 3 hours to complete this 1 hour long tutorial. I got distracted here and there lol. But I am glad I spent the time to do this. Now I can definitely see how useful this could be. Also, I am so happy that I convinced myself to continue on even after getting stuck at some sections. It looks like you have other videos that I can watch and learn from. I have subscribed and liked this video. Thank you for the work you put into creating these videos.
This tutorial is incredible. I can't believe how weak the market is, in 2024, for personal finance software. My first experience with the potential of using Notion to create a database was organizing a large library of my photography and related metadata. I decided to go through this tutorial to see if I could use Notion to organize my personal finances. I always liked the "accounts" management in YNAB (but never liked the budgeting tools). I will use some screenshots and a "free trial" from YNAB to get a feel for how I want to further organize and structure my data. I feel confident I can create a duplicate of the results of this tutorial (to keep as a reference) and continue modifying the duplicate to serve my own needs. Thanks!
Thanks for sharing! 🙌 Followed it along and was able to learn so much! This was my first time doing anything in Notion, so I'm glad I found this video first. The interface layout has changed a bit compared to when you made the video so I had to figure out some parts on my own. Still managed to do it. Thanks again! 👏
As a Notion essentials certified myself... I must say that this system is pretty solid, but I believe it has one problem from my opinion, it is all rotated around ME as a user, what if I want to have something that I can filter as much as I want and shows that this person owes this person that much, or this organization owes this organization that much, do you have a live example of that Mr. William?
Thanks Shrief. You're describing additional types of liabilities in a conventional accounting system. My demonstration is the foundation of a system that's intended to be expanded for your unique needs. It introduces all the fundamental concepts needed to do so.
William, this is great. I bought the template as well. One question - how do you hand invoices that have multiple items? Wouldn’t that require a separate database for each invoice? Or turn each invoice into its own related DB that finds transactions that match that invoice relation? Or do you just create a single transaction item for the entire invoice amount and then ignore individual items of the invoice? The latter seems like you’d be missing the ability to really track categories of revenue items by client or job. Any recommendations on how to handle that? The paid/cancelled field looks like a roll up of any transactions with payments against the invoice. Should the Issued amount field then be a roll up SUM instead of MAX field?
Transactions, invoices and line items are separate entities and thus warrant separate databases. Invoices will relate to both of the others. A rollup property could calculate the invoice total. When the invoice is paid, that's a related transaction.
@@WilliamNutt I changed the "Issued Amount" field on the invoice database to use the sum calculation so it's adding together all the transaction items that match the invoice number. I think that's what I was looking for and it seems to be working. Now the invoice shows all the transactions that match that invoice, instead of just the first item only in the amount. field.
Great video! I not only got an awsome accounting system on my hand but more importantly, I learned the intricacies of Notion! Thank you! I have just one follow-up question: At 12:40, in the Accounts (AS) page, you add "Last reconciled". I don't quite understand the purpose of this column. Can you explain how you would use this?
Thanks so much. By regularly comparing my account balances in Notion with the actual bank accounts, I can catch any unintended charges or entry errors. The "Last Reconciled" property represents the last time I confirmed the balances match.
Thank you so much for the incredible content❤ Unfortunately the formula in transaction database - title generator is not working and I cannot find the Balance effect either, could you help me with those 2 things please 🙏🏻 many thanks William
Is there a reason you didn’t opt for a double bookkeeping entry and to debit and credit affected accounts? Because I’m thinking of building a system like that. Right now it would have multiple entries for each transaction. But I’m wondering if the double bookkeeping could also be done in a single entry that keeps track of both debit and credit.
Thank you. Great question. I experimented with many approaches to handling both sides with a single entry. Ultimately I concluded that separate entries was most streamlined overall. Same with using "In" and "Out" rather than "Debit" and "Credit"; however, you could definitely use the traditional approach with various account types. I encourage you to experiment. It's such a great way to earn. 👍
Thanks @@WilliamNutt for your response! Much appreciated! By the way, with a single entry I meant to have 1 row in the transactions database for a single transaction event.. Rn I have two rows for a single transaction. For example, making a sale of a $100 I would have: ID 001 > Product Sale > Cash Account > Debit 100 > Credit 0 ID 002 > Product Sale > Revenue Account > Debit 0 > Credit 100 But this would mean two separate rows/entries in the transactions database.. I’m not sure if that is a problem that a single event would have two unique transactions in this way? (Still learning about accounting as well haha). The other version would be something like this: ID 001 > Product Sale > Debit Account: Cash Account > Debit Amount: 100 > Credit Account: Revenue > Credit Amount: 100 But I haven’t figured out how to do it yet. Also I’m not sure if this would be best practice.. Would you say one would be better than the other with all your knowledge? Thanks a lot in advance!
@@WilliamNutt One last question if I may:) I think I understand u better now after watching it again. You indeed use a double bookkeeping entry as well but the only difference then with your version would be that the 100$ of a product sale would be a 'money in' for the revenue account in your version, whereas for my version it would be under the credit property. Right?
Thanks Stephanie. Not currently. I encourage you to learn the underlying functionalities of the demonstrated system so you can build on it. Sounds like you'd make a great candidate for Notion A-to-Z: premier.notion.vip/a-to-z
I'm on a mac and on 43:42-43:44 you copy the text from all your title generator cells in a bulk mouse selection and simply paste them into the transactions column, I'm assuming with a keyboard shortcut that we can't see. That said I am unable to do this bulk copy and paste as you do, I have to one by one go through a series of mouse clicks to get past the coding box to actually select the text in each cell to copy it over. That said as well if I can't scroll down far enough to uncover the cell I'm trying to select when the coding box first pops up I am completely unable to even select the text in the first place. As well just the act of simply drag selecting a bulk amount of cells and right clicking causes the entire row of each selected cell to highlight and brings up another menu. How do you do what you do in the video? Thank you kindly, I really enjoy your videos!
Thanks James. I think the solution to all facets of your question to press and hold on the top cell of the generator property, then drag to the bottom. Then you can use the convention cmd/ctrl + c to copy and cmd/ctrl + v to paste in the Title property. Also, in the Title property, hard press and release to avoid a blinking cursor to edit the property, as that will paste all contents into a single cell.
Thanks Ashraf. That’s correct, but if you pre-order Notion A-to-Z, I’ll be happy to send you the accounting template. Just email me with your order ID: premier.notion.vip/a-to-z
Do you anticipate any issues a few years (months) down the line when the Transactions DB undoubtedly grows to a considerable size of 2-3,000+ line items? Also, are you aware of any current tool to configure relations between databases through the API?
I have my household finances in Notion, still okay at 1,300 rows :) The problem is I do rollups by expense categories (18 rollup properties in a database) and it is extremely slow.
Thanks Taylor. I'd be comfortable with that volume *now*, so the technology will certainly support it in a few years. I'm not quite sure what you mean by using a tool to configure relations, but with tools like Zapier, you automatically populate transactions and invoices through integrations with other apps.
Currently, I haven't been able to configure relational properties via the API and a third-party service. It's probably a waiting game, although there is a service in Chrome-Notion Web Clipper-allow me to select relational properties.
The best value that Notion can provide is it can save the documents (receipt scan, agreement etc) together with the transaction. This usually takes up a lot of time from retrieving supporting for auditor or management.
Thank you. I do plan to do a tutorial, as this is a popular request. In the meantime, you may find this video helpful: czcams.com/video/PuVYCm4eUqs/video.html
Amazing William, I love your work. One question how would you go about if you wanted to integrate a column to make a zero based budget, or a budget kinda like YNAB in this same table. Thanks!!
Thanks Ricardo. I'd need to experiment, but my inclination is to recommend a "Budgets" database that's related to Transactions and Years. Each item of that database would be a category-year pair, such as "2022 Travel." The database would include a "Budget" property (currency), then a Rollup property that sums the transactions related to that category.
Thank you. You can use a tool like Zapier, Automate.io or Integromat to connect the Notion database with other services. You might find these helpful: www.notion.vip/notion-explained-the-api-debut/ www.notion.vip/advanced-notion-automation-with-integromat/
Thanks for the comment. You can access the developed databases here: market.notion.vip/item/learn-notion-by-building-an-accounting-system/ If you email me, I'll be happy to create a custom promo code. Just reply to any Notion VIP email, including the confirmation.
Hey this is great, I have a question regarding timestamp 39:00, I run an online business, most of my sales are generated by the customer. I have shopify, ebay and etsy. How would you categorise a system like mine where I don't really have open invoicing per se. I really need help! This is my first business :S
I think you answered my question at 42:00 So to be clear, any third party service that is paying me out is a "gateway' that I transfer from the gateway to my checking account, is that right?
@@hello_emrah Exactly. - Each payment gateway, e.g., Shopify Payments, is an item of the Accounts database. - Depending on your sales volume, you could enter (or import) each individual purchase, or group them by ranges of Order IDs. - When the gateway pays out, that's a transfer from the gateway account to your checking account.
I spent the whole morning today taking 3 hours to complete this 1 hour long tutorial. I got distracted here and there lol.
But I am glad I spent the time to do this. Now I can definitely see how useful this could be.
Also, I am so happy that I convinced myself to continue on even after getting stuck at some sections.
It looks like you have other videos that I can watch and learn from.
I have subscribed and liked this video.
Thank you for the work you put into creating these videos.
Delighted to hear it. Sounds like you'd make a great candidate for Notion A-to-Z 🙂: premier.notion.vip/a-to-z
This tutorial is incredible. I can't believe how weak the market is, in 2024, for personal finance software. My first experience with the potential of using Notion to create a database was organizing a large library of my photography and related metadata. I decided to go through this tutorial to see if I could use Notion to organize my personal finances. I always liked the "accounts" management in YNAB (but never liked the budgeting tools). I will use some screenshots and a "free trial" from YNAB to get a feel for how I want to further organize and structure my data. I feel confident I can create a duplicate of the results of this tutorial (to keep as a reference) and continue modifying the duplicate to serve my own needs. Thanks!
So glad you found it helpful. If you're not already a member of Notion A-to-Z, we'd love to have you onboard: notion.vip/a-to-z
This is is so creative! You can be cautious or you can be creative, but there's no such thing as a cautious creative. :D
This is exactly what I have been looking for, thank you!
Delighted to hear it.
Thanks for sharing! 🙌 Followed it along and was able to learn so much! This was my first time doing anything in Notion, so I'm glad I found this video first. The interface layout has changed a bit compared to when you made the video so I had to figure out some parts on my own. Still managed to do it. Thanks again! 👏
Thanks so much for the positive feedback. Sounds like you'd make a great candidate for Notion A-to-Z: premier.notion.vip/a-to-z
Incredible tutorial and system! I learned a lot about Notion and will actually probably use this accounting system, at least for the start. Thank you!
Thanks Benny. Glad you found it helpful.
As a Notion essentials certified myself... I must say that this system is pretty solid, but I believe it has one problem from my opinion, it is all rotated around ME as a user, what if I want to have something that I can filter as much as I want and shows that this person owes this person that much, or this organization owes this organization that much, do you have a live example of that Mr. William?
Thanks Shrief. You're describing additional types of liabilities in a conventional accounting system.
My demonstration is the foundation of a system that's intended to be expanded for your unique needs. It introduces all the fundamental concepts needed to do so.
William, this is great. I bought the template as well. One question - how do you hand invoices that have multiple items? Wouldn’t that require a separate database for each invoice? Or turn each invoice into its own related DB that finds transactions that match that invoice relation? Or do you just create a single transaction item for the entire invoice amount and then ignore individual items of the invoice? The latter seems like you’d be missing the ability to really track categories of revenue items by client or job. Any recommendations on how to handle that?
The paid/cancelled field looks like a roll up of any transactions with payments against the invoice. Should the Issued amount field then be a roll up SUM instead of MAX field?
Transactions, invoices and line items are separate entities and thus warrant separate databases.
Invoices will relate to both of the others. A rollup property could calculate the invoice total.
When the invoice is paid, that's a related transaction.
@@WilliamNutt I changed the "Issued Amount" field on the invoice database to use the sum calculation so it's adding together all the transaction items that match the invoice number. I think that's what I was looking for and it seems to be working. Now the invoice shows all the transactions that match that invoice, instead of just the first item only in the amount. field.
@@openlebo Beautiful 👍
Great video! I not only got an awsome accounting system on my hand but more importantly, I learned the intricacies of Notion! Thank you! I have just one follow-up question: At 12:40, in the Accounts (AS) page, you add "Last reconciled". I don't quite understand the purpose of this column. Can you explain how you would use this?
Thanks so much. By regularly comparing my account balances in Notion with the actual bank accounts, I can catch any unintended charges or entry errors. The "Last Reconciled" property represents the last time I confirmed the balances match.
Thank you for this. Very useful indeed!
super helpful. thank you!
Delighted to hear it. Thank you.
wow thanks a bunch, man. Keep it up!
Thanks for your in-depth guide to your system! Always extremely helpful in detailed :)
Amazing! Thank you for your time and creativity 🙏🏿😍🤗
You're very welcome.
Thanks for this great content William!
Thank you so much for the incredible content❤
Unfortunately the formula in transaction database - title generator is not working and I cannot find the Balance effect either, could you help me with those 2 things please 🙏🏻 many thanks William
Notion released "Formulas 2.0" since this video was published, which may be contributing to the issue you're experiencing
Is there a reason you didn’t opt for a double bookkeeping entry and to debit and credit affected accounts?
Because I’m thinking of building a system like that. Right now it would have multiple entries for each transaction. But I’m wondering if the double bookkeeping could also be done in a single entry that keeps track of both debit and credit.
Thank you. Great question. I experimented with many approaches to handling both sides with a single entry. Ultimately I concluded that separate entries was most streamlined overall.
Same with using "In" and "Out" rather than "Debit" and "Credit"; however, you could definitely use the traditional approach with various account types.
I encourage you to experiment. It's such a great way to earn. 👍
Thanks @@WilliamNutt for your response! Much appreciated!
By the way, with a single entry I meant to have 1 row in the transactions database for a single transaction event..
Rn I have two rows for a single transaction. For example, making a sale of a $100 I would have:
ID 001 > Product Sale > Cash Account > Debit 100 > Credit 0
ID 002 > Product Sale > Revenue Account > Debit 0 > Credit 100
But this would mean two separate rows/entries in the transactions database.. I’m not sure if that is a problem that a single event would have two unique transactions in this way? (Still learning about accounting as well haha).
The other version would be something like this:
ID 001 > Product Sale > Debit Account: Cash Account > Debit Amount: 100 > Credit Account: Revenue > Credit Amount: 100
But I haven’t figured out how to do it yet. Also I’m not sure if this would be best practice..
Would you say one would be better than the other with all your knowledge?
Thanks a lot in advance!
@@WilliamNutt One last question if I may:) I think I understand u better now after watching it again. You indeed use a double bookkeeping entry as well but the only difference then with your version would be that the 100$ of a product sale would be a 'money in' for the revenue account in your version, whereas for my version it would be under the credit property. Right?
@@Michael.design Bingo 👌
Hello. Do you have a video to add cost of goods, sales receipt for goods and service, products (inventory)to your accounting systems?
Thanks Stephanie. Not currently. I encourage you to learn the underlying functionalities of the demonstrated system so you can build on it. Sounds like you'd make a great candidate for Notion A-to-Z: premier.notion.vip/a-to-z
I'm on a mac and on 43:42-43:44 you copy the text from all your title generator cells in a bulk mouse selection and simply paste them into the transactions column, I'm assuming with a keyboard shortcut that we can't see. That said I am unable to do this bulk copy and paste as you do, I have to one by one go through a series of mouse clicks to get past the coding box to actually select the text in each cell to copy it over. That said as well if I can't scroll down far enough to uncover the cell I'm trying to select when the coding box first pops up I am completely unable to even select the text in the first place. As well just the act of simply drag selecting a bulk amount of cells and right clicking causes the entire row of each selected cell to highlight and brings up another menu. How do you do what you do in the video? Thank you kindly, I really enjoy your videos!
Thanks James. I think the solution to all facets of your question to press and hold on the top cell of the generator property, then drag to the bottom. Then you can use the convention cmd/ctrl + c to copy and cmd/ctrl + v to paste in the Title property. Also, in the Title property, hard press and release to avoid a blinking cursor to edit the property, as that will paste all contents into a single cell.
Great work , willam
Appreciating your efforts .. :)
Asking about the template .. is it cost 25$ to get it from VIP market ?
Thanks Ashraf. That’s correct, but if you pre-order Notion A-to-Z, I’ll be happy to send you the accounting template. Just email me with your order ID: premier.notion.vip/a-to-z
Do you anticipate any issues a few years (months) down the line when the Transactions DB undoubtedly grows to a considerable size of 2-3,000+ line items? Also, are you aware of any current tool to configure relations between databases through the API?
I have my household finances in Notion, still okay at 1,300 rows :)
The problem is I do rollups by expense categories (18 rollup properties in a database) and it is extremely slow.
Thanks Taylor. I'd be comfortable with that volume *now*, so the technology will certainly support it in a few years.
I'm not quite sure what you mean by using a tool to configure relations, but with tools like Zapier, you automatically populate transactions and invoices through integrations with other apps.
Currently, I haven't been able to configure relational properties via the API and a third-party service. It's probably a waiting game, although there is a service in Chrome-Notion Web Clipper-allow me to select relational properties.
@@Caped_Crusader You can configure Relations. You just need a search action that finds the related item using its ID.
Can you also do a video on generating the Profit and Loss and Balance Sheet report from these data?
The best value that Notion can provide is it can save the documents (receipt scan, agreement etc) together with the transaction. This usually takes up a lot of time from retrieving supporting for auditor or management.
Thanks Sam. It's in the long-term pipeline.
26:02 How to auto-populate the title property with the API? Hope you can do a tutorial on this!
Thank you. I do plan to do a tutorial, as this is a popular request. In the meantime, you may find this video helpful: czcams.com/video/PuVYCm4eUqs/video.html
Amazing William, I love your work. One question how would you go about if you wanted to integrate a column to make a zero based budget, or a budget kinda like YNAB in this same table. Thanks!!
Thanks Ricardo. I'd need to experiment, but my inclination is to recommend a "Budgets" database that's related to Transactions and Years. Each item of that database would be a category-year pair, such as "2022 Travel." The database would include a "Budget" property (currency), then a Rollup property that sums the transactions related to that category.
Just saw this and it is very impressive. Do you have to manually transaction or you can connect to your bank and download them?
Thank you. You can use a tool like Zapier, Automate.io or Integromat to connect the Notion database with other services. You might find these helpful:
www.notion.vip/notion-explained-the-api-debut/
www.notion.vip/advanced-notion-automation-with-integromat/
@@WilliamNutt Great. Thank you so much
the problem is how to generate and print invoice, profit & loss, and balance sheet into PDF or paper.
If that's a priority for you, I recommend creating the same system in Airtable, which offers extensions for PDF exports.
Hi William, does it comes with P&L and balance sheets? Thank you. :)
Thank you. P&L and balance sheets are not currently part of the system but could be easily added.
I would like to see a P & L video please
thanks for this.
Where are the formulas? :)
Thanks! They're in the linked template, but it's quite old now. An updated version is available to Notion A-to-Z members: notion.vip/a-to-z
Did you ever get the formula?
@@gretahill4886 Still available in Notion A-to-Z 🙂 www.notion.vip/a-to-z
would you mind copy pasting the formula in 25:06 here, couldn't find the little thing that looks like a comma but isn't..
Thanks for the comment. You can access the developed databases here: market.notion.vip/item/learn-notion-by-building-an-accounting-system/
If you email me, I'll be happy to create a custom promo code. Just reply to any Notion VIP email, including the confirmation.
Hey this is great, I have a question regarding timestamp 39:00, I run an online business, most of my sales are generated by the customer. I have shopify, ebay and etsy. How would you categorise a system like mine where I don't really have open invoicing per se. I really need help! This is my first business :S
I think you answered my question at 42:00
So to be clear, any third party service that is paying me out is a "gateway' that I transfer from the gateway to my checking account, is that right?
Sorry to be annoying, I have another question. How would you work with staff wages?
@@hello_emrah Exactly.
- Each payment gateway, e.g., Shopify Payments, is an item of the Accounts database.
- Depending on your sales volume, you could enter (or import) each individual purchase, or group them by ranges of Order IDs.
- When the gateway pays out, that's a transfer from the gateway account to your checking account.
@@hello_emrah Staff wages could simply be a category of the "Expense" transaction type. The payee would be the staff member.
Hi Did you get my info?
I replied to your thread on the A-to-Z video, thank you.
Okay, one of the worst video on CZcams to exist
Thanks for your feedback.