Notion Masterclass: Build an Expense Tracker from Scratch

No video

Notion Masterclass: Build an Expense Tracker from Scratch

Рет қаралды 38,996

Thomas Frank Explains

Күн бұрын

Пікірлер: 110

@ThomasFrankExplains 6 ай бұрын

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

@crescentlights 6 ай бұрын

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.

@crescentlights 6 ай бұрын

This one seems tp work: if( prop("Recurrence") == "Monthly", if( dateBetween( now(), prop("Charge Date"), "days" )

@crescentlights 6 ай бұрын

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.

@eclbirkeland 6 ай бұрын

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? 😢

@verts_tv 6 ай бұрын

If I dup the template now is this all good or do I have to still update the formula?

@alv4ro 6 ай бұрын

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

@user-zg2bm5yk1p 5 ай бұрын

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 ❤

@torspedia 6 ай бұрын

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. 🙂

@thegoldenfang179 3 ай бұрын

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 💯

@trasharchives4661 6 ай бұрын

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!

@MPKKAAS 6 ай бұрын

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!

@hot_dammn9025 6 ай бұрын

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?

@bonnytiley3998 5 ай бұрын

Thanks!! Very helpful, especially for someone who isn't so great at the formula side! I have my current tracker in Excel

@MarkWarner1 6 ай бұрын

Setting this up in my business immediately. Thank you!

@codenamerishi 6 ай бұрын

You may not hear it but I am clapping, great tutorial and I love your enthusiasm and energy

@MaxRuso 6 ай бұрын

Those reoccurring charges are not joke

@mementomori25 6 ай бұрын

Firstly, cudos and thanks a lot for the great video. You really manifesting your position as THE Notion expert on KZfaq. 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?

@krays23 6 ай бұрын

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

@YamaGory 6 ай бұрын

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!

@YamaGory 6 ай бұрын

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") ) ))

@LucasSinclair 6 ай бұрын

Would also like to know!

@vv_vybortanksletsplay8385 6 ай бұрын

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") ) )

@JarynJamal 6 ай бұрын

if( prop("Recurrence") == "Yearly", prop("Cost") / 12, if( prop("Recurrence") == "Quarterly", prop("Cost") / 3, prop("Cost") ) )

@SaraTalefe 6 ай бұрын

there is a "quarters" date unit type, as well as "days", "weeks", "months", and "years"

@YamaGory 6 ай бұрын

Was waiting for this one, thank you!!

@ReyGlean 5 ай бұрын

Hi, do you have formula for weekly recurrence and quarterly?

@justtere 6 ай бұрын

THANK YOU! There are definitely times my procrastinating is good. The link for recurring dates is not found.

@ThomasFrankExplains 6 ай бұрын

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.

@Excelradionica 4 ай бұрын

I suggest that you input status maybe active in order to consider possible new subscriptions

@choubari 5 ай бұрын

Exactly what I was looking for, thanks Thomas!

@rubamageed1511 6 ай бұрын

I really needed this. Thank you!

@add3449 6 ай бұрын

Was looking forward to this thank you!

@electric-m 6 ай бұрын

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.

@farzadmf 6 ай бұрын

Very nice idea, thank you!

@JarynJamal 6 ай бұрын

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") ) )

@user-qw7be6xf4i 6 ай бұрын

Awesome stuff here, thank you!!

@Onlyextrinsic 2 ай бұрын

would you happen to know how to make financial ratios what to input in formulas to look like 1:2 for example? thanks

@gypsywestwood9675 4 ай бұрын

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! 😄

@maddiepatullo4650 3 ай бұрын

How would I add "fortnightly" into the reoccurring date formula ??

@vallabhravan 4 ай бұрын

Question : The monthly subscription is not updating itself for all months. How to make it work for all months?

@KelseyFowlerNYC 5 ай бұрын

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!

@ericazevedo8351 6 ай бұрын

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...

@ThomasFrankExplains 6 ай бұрын

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.

@mobilestephanie 6 ай бұрын

@@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?

@fg.0 6 ай бұрын

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?

@Ayoubased 3 ай бұрын

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)

@SaraTalefe 6 ай бұрын

I wish there was a "semester" date unit in notion. any suggestions on how to create that manually?

@Renee_egan 6 ай бұрын

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 😔😳

@pablodominguez19 3 ай бұрын

Hi, I. amnew with notion, could someone explain. mewhy creating a data base first? whu not doing it just once?

@jakoblaurinehret9018 4 ай бұрын

Would be great, if there is a possibility to integrate it to Ultimate Brain!

@ThomasFrankExplains 4 ай бұрын

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.

@NataliyaAi 6 ай бұрын

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 ;=)

@felonioustub24 6 ай бұрын

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.

@ThomasFrankExplains 6 ай бұрын

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") ) )

@felonioustub24 6 ай бұрын

@@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.

@AndreinaValderrama. 4 ай бұрын

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

@brianlalley2864 6 ай бұрын

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

@ThomasFrankExplains 6 ай бұрын

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!

@RubensJunior-wu7fo 2 ай бұрын

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

@ThomasFrankExplains 2 ай бұрын

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.

@ayush8 6 ай бұрын

How can I integrate this in my UB+CC bundle?

@jamesgrossi 4 ай бұрын

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!

@ThomasFrankExplains 4 ай бұрын

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.

6 ай бұрын

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?

@ThomasFrankExplains 6 ай бұрын

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!

@Oopdie6721 6 ай бұрын

Hey TF! do you think Notion is ripping off your UB template? the similarities are kinda Sus ngl

@RiseandProcraftinate 6 ай бұрын

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.

@ThomasFrankExplains 6 ай бұрын

@RiseandProcraftinate 6 ай бұрын

Yep, that worked! Thank you!

@Boooo0000 4 ай бұрын

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 !

@ThomasFrankExplains 4 ай бұрын

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.

@Boooo0000 4 ай бұрын

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 !

@matiassuarez4396 5 ай бұрын

or maybe just use todoist's tasks instead of recurring formulas for Notion calendar?

@Rafael-nu3mm 6 ай бұрын

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!

@ThomasFrankExplains 6 ай бұрын

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.

@louiseh7302 6 ай бұрын

I copied and Pasted link and nothing came up

@pureremedyusa5655 6 ай бұрын

It won't let me download the expense tracker and there is no duplicate button - can you help?

@ThomasFrankExplains 6 ай бұрын

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.

@DrDarioColon 6 ай бұрын

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.

@ThomasFrankExplains 6 ай бұрын

In the Monthly Cost formula, you can simply tack on "/ prop("Used By").length()" to the end (no quotes).

@DrDarioColon 6 ай бұрын

@@ThomasFrankExplains Got it, also, they don't use Notion, how can I add dummies account so I can add however many I want?

@JordanJones5 6 ай бұрын

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.

@ThomasFrankExplains 6 ай бұрын

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.

@JordanJones5 6 ай бұрын

Thanks so much. The fix and the explanation resolved it.@@ThomasFrankExplains

@orange_district 6 ай бұрын

Those many subscriptions would scare me!

@rayganrambles 6 ай бұрын

Maybe this will help me lower my ADHD tax. LOL Thanks!

@lxGrimmJxl 6 ай бұрын

@21:42 I lold much harder than I should have :D

@etgdesignstudio 6 ай бұрын

synesthesia... I have it also :)

@ciizu4772 5 ай бұрын

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: kzfaq.info/get/bejne/na6Gh62IrqiWfqM.html A big hug from Perú, I love your work and your effort!

@jasueh 6 ай бұрын

Can't access to the formula. Anyone else having the same issue?

@ThomasFrankExplains 6 ай бұрын

Fixed the link!

@jasueh 6 ай бұрын

Working now! Thanks!

@matthiasmaier_muc 6 ай бұрын

I’m just wondering how to get my expenses from my credit card into this…

@ThomasFrankExplains 6 ай бұрын

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!

@crescentlights 6 ай бұрын

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() ) ),

@ThomasFrankExplains 6 ай бұрын

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.