Notion Masterclass: Build an Expense Tracker from Scratch
Vložit
- čas přidán 24. 07. 2024
- In this tutorial, I'll show you how to build an expense tracker in Notion that alerts you to upcoming recurring expenses. It'll even connect to Notion Calendar.
📝 Video Resources:
---
Free Expense Tracker Template: thomasjfrank.com/templates/ex...
Recur Date Formula: thomasfrank.notion.site/Expen...
➡️ Watch These Next:
---
How I Take Notes in Notion With My Voice: • How I Use ChatGPT to T...
Build a Second Brain (PARA Dashboard): • Notion Masterclass: Bu...
🛠 Courses and Resources:
--------
📚 Notion Fundamentals (Free & Complete Notion Beginner's Course)
thomasjfrank.com/fundamentals/
🧠 Ultimate Brain (Full Second-Brain Template - use LETSGO2024 to get $50 off!):
thomasjfrank.com/brain/
✈️ Flylighter (Our ultra-powerful web clipper - sign up for early access!):
flylighter.com/
🎥 Creator's Companion (The Best Template for Content Creators - use LETSGO2024 at checkout):
thomasjfrank.com/creators-com...
💪Get Started with Notion:
--------
thomasjfrank.com/usenotion/ - (this is my referral link)
🐦Connect With Me:
--------
Twitter ➔ / tomfrankly
Instagram ➔ / tomfrankly
Music ➔ @ThomasFrankMusic
Main Channel ➔ @Thomasfrank
⏱️ Timestamps:
--------
00:00 - Intro
01:36 - Detailed Look into Expense Tracker
03:34 - Start Building
04:09 - Underlying Database
04:52 - Expenses Database
15:15 - Formula Fun
20:07 - Adding to Notion Calendar
21:43 - Expense Tracker Dashboard
28:11 - Cancellation Dashboard
Quick update: I've fixed a bug in the Renewal Date formula. If you're building this yourself, or you duplicated the template earlier today, check out the formula page to grab the new formula and replace your old with it: thomasfrank.notion.site/Expense-Tracker-Formulas-7138b54c81b346d8b07d487c17e7a165
The new formula gives a different result than the original. For Charge Date = some future date (eg., Feb 29) the new formula gives March 29 2024 for monthly, but the original formula gives Feb 29 2024. Similarly, for yearly, the new formula gives Feb 29 2025, but the original gives Feb 29 2024.
This one seems tp work:
if(
prop("Recurrence") == "Monthly",
if(
dateBetween(
now(),
prop("Charge Date"),
"days"
)
Opps .... I provided a suggested formula, and added it as a comment here, but it was wrong and might confuse people. so I deleted it.
When I paste in the new formula and link up the charge date properties, my monthly expenses get a recurrence date one year from the charge date, same as the yearly recurrance. Am I doing something wrong? 😢
If I dup the template now is this all good or do I have to still update the formula?
The term you were looking for is "Synesthesia", when certain senses affect certain other senses, like if a certain sounds feels like a certain flavour for example
Setting this up in my business immediately. Thank you!
Thanks!! Very helpful, especially for someone who isn't so great at the formula side! I have my current tracker in Excel
Was waiting for this one, thank you!!
You may not hear it but I am clapping, great tutorial and I love your enthusiasm and energy
Exactly what I was looking for, thanks Thomas!
This is great! Thank you so much! I've already implemented it for my business :) As I do have some quarterly expenses (and some bi-monthly expenses even) I will try to adapt thr renewal formula accordingly :P let's see if I succeed!
I really needed this. Thank you!
While I wouldn't use Notion for this, for privacy reasons, watching this video has helped me to seriously think about creating a template in Logseq, for this sort of thing,. So ta for that. 🙂
Was looking forward to this thank you!
Hey Thomas, do you think you can create a complete finance tracker tutorial?
To track expenses, income, credit cards/accounts, investments,etc
Really appreciate all the content you put out ❤
Firstly, cudos and thanks a lot for the great video. You really manifesting your position as THE Notion expert on CZcams. I thought using the template as an individual expense tracker but face one issue. Is there a way to adjust the formula for "onetime" expenses (maybe also the case for businesses) so that the formula is not picking up a recurrence date?
Very nice idea, thank you!
Hey Thomas, Nice video 👍.
A small note, Even with the updated formula, I think there is an oversight. Even after canceling an expense, it would still have a renewal date. This is not gonna break anything, but just putting it out there. It can easily fixed by checking if the prop cancelation date exists.
Cheers 💯
Awesome stuff here, thank you!!
Thats great Tom, but I’d really like to see a way to see analytics for previous months, such as how much I’ve paid in total. Maybe find a way to make automatic monthly and yearly reports or if not automatic at least manual. This would be super interesting and useful for not just personal finances, but also keeping track of paying your employees etc. It would mean so much if you actually were able to crack that!
Great stuff just what i needed!
Question: what about multiple payment currency like my Adobe i pay in UK pounds but Make i pay in USD how can i handle that with out converting to one currency manually
Thank you for this tutorial. With a monthly recurrence, will it only appear in the calendar for the following month once I have updated the charge date? Eg it won't appear on the 16th of April, until I set it to the 16th of March, even though we are in February. Would I need to look at something like Make or Zapier to automate that?
Great masterclass! Thanks for this.. A key feature that I am trying to add in to this dashboard is "total monthly cost per category" as a Gallery tiles, so you can see these at glance, any ideas if this is doable?
Those reoccurring charges are not joke
I suggest that you input status maybe active in order to consider possible new subscriptions
would you happen to know how to make financial ratios what to input in formulas to look like 1:2 for example? thanks
Hi, do you have formula for weekly recurrence and quarterly?
I wish there was a "semester" date unit in notion. any suggestions on how to create that manually?
THANK YOU! There are definitely times my procrastinating is good.
The link for recurring dates is not found.
Ha, silly mistake on my part! (or maybe a bug in Notion)
Head to the Databases page at the bottom of the template, then to the Calendar tab on that page. Change the filter labeled Status is Cancelled - uncheck Cancelled, check Active and Maybe Cancel.
Question - I have a bi-yearly expense. I'm wondering how to add it to the recurring date formula. I managed to add to the monthly cost formula thanks to some comments below, but can't figure out the data one. Thank you for this, Tom!
How would I add "fortnightly" into the reoccurring date formula ??
Hi, Thomas! Appreciate your work a lot! I correct Renewal Date formula, but it calculate a year from now. How I can fix it? For example if the Charge Date is on 20th of Feb 2024, appears 20th of Feb 2025, not March of 2024, if the payment is monthly. Moreover, which should be the formula if the payment has to be made each half of the year? Or each 2 months? Thanks in advance for your kind reply ;=)
Really enjoying this template!
I was curious as to how you added the info reference bubble in the properties to explain what it is?
@ThomasFrankExplains
That’s a new feature! You’ll see a little “i” icon on properties, views, and IIRC even options in Select/Status props where you can add a description.
Great for building systems used by a team!
How can I integrate this in my UB+CC bundle?
The formula in the monthly cost tab does not work for me- to break down a yearly cost into monthly costs? And I have gone over it and over it and it just does not do what it does in your video? What might I be doing wrong? Very happy with the rest! 😄
Bought my Giant Defy 2024 a few weeks ago and couldn’t be happier. It’s so damn comfy and gives me enough options to change the fit. Would’ve gone for the Canyon, but since I couldn’t try before buying it became the Giant.
how can I link this with a monthy income Template, so I can see how much money I earn vs expense and see how much I made each month ( money made - expenses)
Question : The monthly subscription is not updating itself for all months. How to make it work for all months?
Hi, I. amnew with notion, could someone explain. mewhy creating a data base first? whu not doing it just once?
Any thoughts about how to handle an expense that's semi-annual? Doubling the cost would work but the renewal dates won't calculate correctly. Is there a way to modify the formulas to account for this? Thanks!
Not too hard to add!
Monthly cost formula would be:
ifs(
prop("Recurrence") == "Yearly",
prop("Cost") / 12,
prop("Recurrence") == "Semi-Annual",
prop("Cost") / 6,
prop("Cost")
)
And I believe Renewal Date would be:
ifs(
prop("Recurrence") == "Monthly",
let(
monthGap,
dateBetween(
now().dateSubtract(1,"days"),
prop("Charge Date"),
"months"
),
prop("Charge Date").dateAdd(monthGap+1,"months")
),
prop("Recurrence") == "Semi-Annual",
let(
monthGap,
dateBetween(
now().dateSubtract(1,"days"),
prop("Charge Date"),
"months"
),
prop("Charge Date").dateAdd(monthGap+6,"months")
),
let(
yearGap,
dateBetween(
now().dateSubtract(1,"days"),
prop("Charge Date"),
"years"
),
prop("Charge Date").dateAdd(yearGap+1,"years")
)
)
In each formula, I change "if" to "ifs" so it can handle 3+ cases, then add the semi-annual case with its logic.
How would the recurrance date and monthly cost formulas be adjusted if you also have quarterly expenses? So there would be 3 situations, yearly, quarterly and monthly. Appreciate any help!
Managed to figure it out, you need to use two if functions:
if(
prop("Recurrence") == "Monthly",
let(
monthGap,
dateBetween(
now().dateSubtract(1,"days"),
prop("Charge Date"),
"months"
),
prop("Charge Date").dateAdd(monthGap+1,"months")
),
if(
prop("Recurrence") == "Quarterly",
let(
monthGap,
dateBetween(
now().dateSubtract(1,"days"),
prop("Charge Date"),
"months"
),
prop("Charge Date").dateAdd(monthGap+3,"months")
),
let(
yearGap,
dateBetween(
now().dateSubtract(1,"days"),
prop("Charge Date"),
"years"
),
prop("Charge Date").dateAdd(yearGap+1,"years")
)
))
Would also like to know!
here is code:
ifs(
prop("Recurrence") == "Monthly",
let(
monthGap,
dateBetween(
now().dateSubtract(1,"days"),
prop("Purchase Date"),
"months"
),
prop("Purchase Date").dateAdd(monthGap+1,"months")
),
prop("Recurrence") == "Yearly",
let(
yearGap,
dateBetween(
now().dateSubtract(1,"days"),
prop("Purchase Date"),
"years"
),
prop("Purchase Date").dateAdd(yearGap+1,"years")
),
let(
yearGap,
dateBetween(
now().dateSubtract(1,"days"),
prop("Purchase Date"),
"quarters"
),
prop("Purchase Date").dateAdd(yearGap+1,"quarters")
)
)
if(
prop("Recurrence") == "Yearly",
prop("Cost") / 12,
if(
prop("Recurrence") == "Quarterly",
prop("Cost") / 3,
prop("Cost")
)
)
there is a "quarters" date unit type, as well as "days", "weeks", "months", and "years"
What software do you use to record your computer screen and your camera? In particular, what program do you use to highlight the mouse cursor?
I use Cursor Pro for the mouse and Keystroke Pro for the keystrokes. Both Mac-only, but you can use MouseHighlight and Carnac on Windows as alternatives. For recording I'm using OBS!
Would be great, if there is a possibility to integrate it to Ultimate Brain!
Feel free to ask our support team about integrating it if you like! We probably won't bundle this with UB since it's pretty much a standalone template, but you could easily just move it into UB if you wanted it organized with all your other pages.
I've done something similar but I've given up on using it, I wanted to keep track of price variations (for instance, a subscription service that changes its monthly price and then I have to update the price without being able to keep the prices I've paid in past months...). And I can't do that easily in a database...
Yeah price variations are a bit more annoying. The moment you want to actually track changes like that, you typically need some sort of "events" database.
For a similar reason, I haven't released a database-driven habit-tracker for Notion.
@@ThomasFrankExplains I'm a Clickup user that's just dabbling with Notion. In Clickup we would solve this by creating a formula that records a date when you check a box (assuming you want a box style habit tracker). Is this not possible in Notion? Or did I misunderstand the context?
Hey TF! do you think Notion is ripping off your UB template? the similarities are kinda Sus ngl
Hi thomas, your video is amazing! However i am having an issue that i can only write the name of the expense but i cant edit the value, when i click it it just appears the formular and there is no place to write like yours
Yep, the Monthly Cost formula property is read-only. You want to edit the Cost property, which the Monthly Cost property uses for one of its inputs.
Synosthesia(sp?): when two or more senses are triggered simultaneously- I wish I had this with numbers being coloured, Math, school, and pretty much everything would probably be much easy! I am not good at anything math related 😔😳
Thomas, can you teach us how to connect emails other than Gmail? I have a professional email, but I don't know how to do it. My main Notion account is not signed with Gmail and I want to use the new Notion calendar app. Thanks!
For Notion Calendar? If so, right now you have to use a Google account to sign in. If you want to manage other calendar accounts in Notion Calendar, I think you'll need to sync them to a Google Calendar first.
Quick question. Once the new Charge Date has passed, do you have to change charge date again for recurrence date to change again ? this doesnt seem to be automatic. Is there a way to achieve this ? Thanks heaps for the video btw !
The Charge Date is just the first time you were ever charged. The Recurrence Date is calculated based on the Charge Date AND the current date, so it'll always show the next upcoming charge.
That makes perfect sense now.@@ThomasFrankExplains Thanks for the explanation :) I went through this step by step and learnt heaps. I also added another database to store incomes recieved and combined the two to show incomings and outgoings. Thank you so much !
How can I add to the "used by" to split expenses? For example, in my case there's a subscription that needs to be split by 3.
In the Monthly Cost formula, you can simply tack on "/ prop("Used By").length()" to the end (no quotes).
@@ThomasFrankExplains Got it, also, they don't use Notion, how can I add dummies account so I can add however many I want?
Not sure what is going on, but I downloaded and used your template. Filled in a few of my expenses, linked it to my calendar, which it shows on my side bar. However, not a single one of my expenses is actually showing on the calendar. Never had that happen before.
Ha, silly mistake on my part! (or maybe a bug in Notion)
Head to the Databases page at the bottom of the template, then to the Calendar tab on that page. Change the filter labeled Status is Cancelled - uncheck Cancelled, check Active and Maybe Cancel.
Yep, that worked! Thank you!
A minor problem, if you have a monthly recurring expense that gets charged at the end of the month and the next month has less day, the renewal date stays empty.
Oh wow, you are 100% right. Funnily enough, we didn't have a single expense in the business with a charge date at the end of the month! So this never came up in testing.
Just updated the template and formula page with a new formula. This one handles that case, and is also more elegant to boot:
if(
prop("Recurrence") == "Monthly",
let(
monthGap,
dateBetween(
now().dateSubtract(1,"days"),
prop("Charge Date"),
"months"
),
prop("Charge Date").dateAdd(monthGap+1,"months")
),
let(
yearGap,
dateBetween(
now().dateSubtract(1,"days"),
prop("Charge Date"),
"years"
),
prop("Charge Date").dateAdd(yearGap+1,"years")
)
)
@@ThomasFrankExplains Thanks for the swift update. I've been using this template for a bit now, pretty sure you posted a preview in the UB circle community and only just noticed this today. Awesome sheet and goes along nicely with the Ultimate Brain template.
Thomas, thank you for the update, but I think there is still a minor problem unless I am doing something wrong. If you have a monthly recurring expense that started in a month that has less days than the following month (April for example) the recurrence date will always show the next month (May) even if the date has not passed yet or is Today. @@ThomasFrankExplains
Neither of the links (Free Expense Tracker Template and Recur Date Formula) work for me.
I do get to the template page, but there's no "Duplicate."
It says I'm not logged in, but I am. So, I log in, which takes me to my environment. (So far so good.) But when I go back to your template, it still thinks I'm not logged in. When I click for the formula, it says it's either not there or not shared with me.
I just fixed the formula link - sorry about that!
For the template page, if there’s not an explicit Duplicate or Start With This Template button, there should be an icon that looks like two squares. That should let you duplicate the template.
Thanks so much. The fix and the explanation resolved it.@@ThomasFrankExplains
It won't let me download the expense
tracker and there is no duplicate button - can you help?
Just tested it myself in a logged-out browser! There should be a little icon with two squares in the top-right corner. You'll likely need to make sure you're using a computer (not a mobile device) to duplicate it.
I copied and Pasted link and nothing came up
or maybe just use todoist's tasks instead of recurring formulas for Notion calendar?
Those many subscriptions would scare me!
Maybe this will help me lower my ADHD tax. LOL Thanks!
Can't access to the formula. Anyone else having the same issue?
Fixed the link!
Working now! Thanks!
I’m just wondering how to get my expenses from my credit card into this…
I enter these manually. I don't need the amounts to be exact (e.g. Cloudflare changes each month slightly based on use) - I've got actual P&L reports in Xero for that. For this, I mainly want a table of all the paid tools we're using and a calendar of upcoming renewals.
For getting actual transaction data, I unfortunately don't know of anything I can trust enough to recommend. Apps can use Plaid to connect with banks and credit card accounts, but they then need to be held to a secure standard themselves.
For personal stuff, I've been using Monarch Money and quite like it!
@21:42 I lold much harder than I should have :D
Hi Frank! I needed to go through your last video so you can read this important comment, I wanted to let you know that the problem with your recurring tasks system in notion has been solved, so you can check it so you can update the notice you posted 5 months ago that there were problems with it and a solution was pending. This is your video:
czcams.com/video/gHVTJWIB2No/video.html
A big hug from Perú, I love your work and your effort!
synesthesia... I have it also :)
For those of you who are trying to add a "Quarterly" condition to the Monthly Cost if statement, the correct formula is below!
if(
prop("Recurrence") == "Yearly",
prop("Cost") / 12,
if(
prop("Recurrence") == "Quarterly",
prop("Cost") / 3,
prop("Cost")
)
)
You can reduce some complexity in the Renewal Date formula by using the formatDate() function. Like this:
lets(
day,
prop("Charge Date").formatDate("DD"),
month,
prop("Charge Date").formatDate("MM"),
thisMonth,
now().formatDate("MM"),
ifs(
prop("Recurrence") == "Monthly",
(now().year() + "-" + thisMonth + "-" + day).parseDate(),
prop("Recurrence") == "Yearly",
(now().year() + "-" + month + "-" + day).parseDate()
)
),
Shipped a new version in the pinned comment! Another commenter pointed out that constructing dates from numbers and strings like this and then parsing the date makes the formula break in certain cases - e.g. when a Charge Date is on the 31st, since the next month won't contain a 31st.