Setup CATEGORIES to Track Expenses in EXCEL | SYNC for FREE #2
HTML-код
- Опубликовано: 21 авг 2024
- Learn how to track expenses in Microsoft Excel automatically by using simple formulas and functions that follows on from my previous expense tracking tutorials.
💰 You can buy my Excel Template that I use in this video at my online store here: moneywithdan.e...
This video will show you how to setup automatic categories to your expenses in Microsoft Excel using common formulas and your online bank statements for any date range extracted into a CSV file format. Once your bank transactions are in Microsoft Excel you can use all of the features of Microsoft Excel to organise your data anyway you like, including formulas like VLOOKUP, LEFT and Pivot Tables. Only an intermediate level of Microsoft Excel skills required.
LINKS
💰 Use SHARESIGHT to track your shares - www.sharesight...
(BONUS When you signup using this link, the first 4 months of subscription are free if you choose to upgrade to advanced plan and I may get a small my commission at no extra cost to you, so we both win!)
OTHER VIDEOS
Budget and Track Expenses in Excel AUTOMATICALLY and FREE: • Budget and Track Expen...
Excel Pivot Table: Track expenses and BUDGET: • Track Expenses by CATE...
How to open banks statements in Excel: • How to open BANK State...
Identifying Variances in Excel | What to do if you spent too much money: • How to track expenses ...
How to Track Expenses in Excel (full tutorial): • How to track expenses ...
How to setup the Barefoot investor buckets | 2 year review with tips : • How to setup the Baref...
Please subscribe to my channel to see more videos from me!
OTHER INFORMATION
Throw away your envelope budget system, highlighters, receipts, and pens and paper. Save more money by tracking your expenses and find out your spending habits and start the new year's resolution to save more money and invest now!
Adding Categories to your extracted bank statement in Microsoft Excel, is the second step to automatically tracking your income and expenses. By obtaining your data electronically you are potentially saving hours of time of writing down, avoiding duplicating data, and tracking receipts daily which gives you time back to focus on the important things in life without having to take your eye off your spending habits.
I created this step by step tutorial after being disappointed with the new tracking tool my bank offered. I was also nervous of uploading my personal bank transactions and details into the cloud application on their mobile phone when it could be hacked - not to mention the ongoing monthly fees.
#budgeting #personalfinance #excel
Thanks again Dan another helpful video.
One tip I discovered is to sort the data pasted in the ref sheet by ascending or descending order. This makes typing in a description faster
Great tip Grant! I am glad it is helping! Thanks for watching!
Thanks mate. Super helpful video! Trying to get a handle on where the money goes each month…
Thanks Joshua for watching. Good luck. I have a number of videos that may help you save money and more on the way so remember to Subscribe, if not already, to be notified. Thanks!
Amazing, thank you!
thank you!
Hi guys if you like what he did, there is a faster method below:
=sort(unique(left(b2:b9999,20)))
from there you can just keep tagging on the new csv files below it
Thanks for sharing!
Unfortunately, the data is not legible until you zoom in. Suggest using a much larger font and fewer rows/columns in your examples to make it easier to follow along. Also Excel has some very clever auto extraction facility that might make the creation step easier.
Thanks for watching. You can change the RUclips video to a higher resolution in the video settings. RUclips defaults to low resolution now so you can’t make out the detail as well as you could when I first uploaded the video 3 years ago when RUclips defaulted to high resolution.
Amazing! This is exactly what I needed!
Thanks so much!
Great video 👍 thanks for this!
Hi Dan! Regarding the Data Tab for statements with two columns, is that in your Etsy store?
Thanks for your question. Yes that is right, I included it as an optional extra tab that you can use instead of the one in my video.
Whats comma button mentioned repeated @5:26 ! I dint get that part.. Rest is excellent. Thanks
thanks for watching. this button “,”
@@MoneywithDan Thank you so much.
@@MoneywithDan Also can you please share the vlookup code? For some reason, im unable to reproduce what you are performing. After entering the comma, nothing shows up in the data cell because of which im getting the error (probably something to do with mac). Advance thanks. Really appreciate the support.
It worked fine now. Thanks... Great video:)
This is great but if i do a data export i have 3000+ rows. I cant put a category next to each one!
Thanks for watching. I assume many of those lines are repeated right? Perhaps start with a shorter date range and build up the the categories as you go so it won’t seem as daunting. I started with just a month of data to begin with.
This could be useful for larger datasets: ruclips.net/video/h_GTxRFYETY/видео.html
You can buy my Excel Template that I use in this video at my online store here: moneywithdan.etsy.com
In the template, I also have a dedicated page for frequently asked questions and my answers (FAQs). I also include an alternative Data tab for bank downloads with two columns!
I bought the excel spreadsheet. It only comes in Euro and Yen.
Are you sure you bought my spreadsheet? Mine is in dollars. In Excel you can easily change the setting to any currency you like.
Thanks, I like the level of automation. I would like to be able to track spending against each category during the month, so I can put the brakes on rather than find out at the end of the month that I was over budget in any particular category. I could just extract the CSV from the bank for each week but often pending transactions show up as 'confirmed' transactions a few days later and I want to avoid items being duplicated in the tracker. Do you have any suggestions for how to manage this?
Hi. Glad you liked the videos. I will be posting another video on how to track using pivot tables soon which will help. When I extract CSV files from my bank, my pending transaction don’t actually get included and are filtered out. This may be because of the date filter I use before I export inside my online bank website and they get included in the following month’s (or other period) extract file and are included there. Double check this is what happens with you bank. In the very least you can always delete pending payments manually knowing they will be included again in the next extract which avoids a duplication.
the problem is : I have 500 transaction (400 with the same name) how could I automatize in order to get the name of categories?
I haven’t come across this many identical transactions for personal transactions accounts before where they each for different different category types. Usually multiple transactions with the same description have the same category name so it hasn’t been an issue before.
Do the 400 transactions have exactly the same description? I assume you have tried increasing the number of characters in the Left formula until they are unique as I show in my rent example within the video. I also assume the 400 transactions are for completely different categories and you want to give them seperate category names.
If the transactions are bank transfers you could give them a different description when you transact so that they are unique in the future.
Perhaps a local LLM could be useful: ruclips.net/video/h_GTxRFYETY/видео.html
Is there a way to create it without the description?
It would have lots of lines in the description if you did. I don’t think it would be worth it without the descriptions.
Hi Dan, this is really useful but I'm coming across a problem - my bank inserts the date of the transaction before the description e.g. 23JUN22 TESCO. So, using vlookup doesn't work for me as the category would change every time based on the date and all my transactions can't then be grouped into categories. From online research it seems the index and match formula would do the job but I can't get my head around how to use it in this context. Do you know how? Thanks, Milly
Hi Milly, thanks for watching. There are a few options. You could extend the number of characters that the LEFT FORMULA brings across from 25 up to 50 or more to get a unique reference. Alternatively you could use a the MID formula which brings across characters starting from a middle of the data so in you case your formula would be =MID(cell with data,8,25) which means bring across data 8 characters after the date and the first 25 characters after that, which should be unique enough. Hope this helps!
INFO IS GREAT, VIDEO IS BLURRED
Thanks. I think RUclips may have decreased the pixel rate for some users since I uploaded.
What about when you use cash??
Unfortunately, you would need to allocate manually. This template is primarily focused on electronic transactions by card and electronic transfers so may not be the best for you if you primarily use cash.
How can download file
Hi there. This video shows you how to open bank statements online: How to open BANK Statement CSV file in EXCEL
ruclips.net/video/pUo0kO60mB8/видео.html
im getting #N/A :(
Sounds like the unique identifier is not matching. Maybe watch that section again.