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
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!
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
@@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.
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.
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 UA-cam video to a higher resolution in the video settings. UA-cam 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 UA-cam defaulted to high resolution.
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!
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.
isn't there an easier automatic way to do it??? this method ill need to edit the excel everytime i paste new data in. im using the data from a folder to make an automatic spreadsheet, but i dont think your method is compatible with mine, since new data will break this
It sounds like your data source is not generating unique identifiers. Does your data have a date or some other number at the front that varies each transaction? If so then I recommend using a MID formula instead of LEFT as shown in video to skip that data in the unique identifiers tab. Hope this helps as it definitely should not be that hard as you describe else I wouldn’t have created it.
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.
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.
Hi there. This video shows you how to open bank statements online: How to open BANK Statement CSV file in EXCEL ua-cam.com/video/pUo0kO60mB8/v-deo.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!
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! This is exactly what I needed!
Thanks so much!
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.
Amazing, thank you!
thank you!
Great video 👍 thanks for this!
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!
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: ua-cam.com/video/h_GTxRFYETY/v-deo.html
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 UA-cam video to a higher resolution in the video settings. UA-cam 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 UA-cam defaulted to high resolution.
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!
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.
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.
isn't there an easier automatic way to do it??? this method ill need to edit the excel everytime i paste new data in. im using the data from a folder to make an automatic spreadsheet, but i dont think your method is compatible with mine, since new data will break this
It sounds like your data source is not generating unique identifiers. Does your data have a date or some other number at the front that varies each transaction? If so then I recommend using a MID formula instead of LEFT as shown in video to skip that data in the unique identifiers tab. Hope this helps as it definitely should not be that hard as you describe else I wouldn’t have created it.
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: ua-cam.com/video/h_GTxRFYETY/v-deo.html
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.
You could just double click the corner to copy the formula down.
Sometimes you can.
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
ua-cam.com/video/pUo0kO60mB8/v-deo.html
INFO IS GREAT, VIDEO IS BLURRED
Thanks. I think UA-cam may have decreased the pixel rate for some users since I uploaded.
im getting #N/A :(
Sounds like the unique identifier is not matching. Maybe watch that section again.
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!