Hello everyone,
Thank you for the time to read my post! I've just joined this forum as I'm looking for some help. I'm looking to start using Excel VBA to automate some plots on a big excel file I have where I place all my monthly spendings.
I have each month as a smart table, something like you see below:
The first row is the day of the month, the second one is how much was spent, the third one the place where the purchase was made, the fourth one I call it type 1, the 5th one type 2 (this one is more important), the 6th one is if I made the spending for myself, if it is split with someone or not and finally, the last one is how much I really spent after accounting for divisions etc.
I was looking to use VBA to create a script that would:
1 - Ask for the data range to be edited (on this example, I would select the whole table above) - and this would be stored in a variable
2 - Ask for the number of days of the month - I think this might be important as each month has a different number of days and I might not make a purchase on every single day
3 - Split the main table into 4 smaller tables, one for each category of type 2 spending (MAIN, HOBBIE, EAT OUT, SINGLE)
4 - On each table, leave only 2 rows - the date of the purchase and the total value
5 - Create a cumulative line plot for each table - a total of 4 line plots - that would plot how my daily spending increases as the month goes on (x axis the days of the month, y axis the value spent)
6 - Optional - a cumulative plot with the total of the 4 categories
7 - Place these plots underneath the table one after the other so I could later compare one month next to each other
Sorry for the lenghty question but I am used to another programming language. For simplicity I would like to implement all of this on Excel and also start learning VBA is an added bonus.
Can anyone help me with this?
Thank you very much!
Thank you for the time to read my post! I've just joined this forum as I'm looking for some help. I'm looking to start using Excel VBA to automate some plots on a big excel file I have where I place all my monthly spendings.
I have each month as a smart table, something like you see below:
| Date | Amount | Shop | Type | Type 2 | Percentage | Total Value |
01/02/2021 | 24.92 | Q | STORE | SINGLE | SPLIT | 12.46 |
01/02/2021 | 19.5 | R | BIKE | SINGLE | NONE | 0 |
02/01/2021 | 160 | S | STORE | SINGLE | SPLIT | 80 |
02/01/2021 | 100 | T | STORE | SINGLE | SPLIT | 50 |
03/01/2021 | 83 | D | ELECTRICITY | MAIN | SPLIT | 41.5 |
03/01/2021 | 174.04 | P | STORE | HOBBIE | OWN | 174.04 |
04/01/2021 | 187.11 | C | STORE | HOBBIE | OWN | 187.11 |
06/01/2021 | 129.91 | A | SUPERMARKET | MAIN | SPLIT | 64.955 |
06/01/2021 | 27.39 | A | SUPERMARKET | MAIN | SPLIT | 13.695 |
06/01/2021 | 623.63 | O | HOUSE | SINGLE | SPLIT | 311.815 |
08/01/2021 | 100 | N | STORE | SINGLE | NONE | 0 |
08/01/2021 | 55.99 | H | STORE | HOBBIE | OWN | 55.99 |
09/01/2021 | 42.75 | A | SUPERMARKET | MAIN | SPLIT | 21.375 |
10/01/2021 | 119.66 | C | STORE | HOBBIE | OWN | 119.66 |
11/01/2021 | 29.97 | A | SUPERMARKET | MAIN | SPLIT | 14.985 |
12/01/2021 | 6.13 | A | SUPERMARKET | MAIN | SPLIT | 3.065 |
16/02/2021 | 92.59 | A | SUPERMARKET | MAIN | SPLIT | 46.295 |
17/02/2021 | 6.5 | M | RESTAURANT | EAT OUT | SPLIT | 3.25 |
17/02/2021 | 1.7 | E | BANK | MAIN | OWN | 1.7 |
18/02/2021 | 163.35 | B | STORE | HOBBIE | OWN | 163.35 |
18/02/2021 | 72.42 | B | STORE | HOBBIE | OWN | 72.42 |
18/02/2021 | 23.41 | B | STORE | HOBBIE | OWN | 23.41 |
18/02/2021 | 8.17 | L | MEDIC | MAIN | OWN | 8.17 |
19/02/2021 | 27.443 | A | SUPERMARKET | MAIN | SPLIT | 13.7215 |
19/02/2021 | 235.33 | C | STORE | HOBBIE | OWN | 235.33 |
20/02/2021 | 1.24 | A | SUPERMARKET | MAIN | SPLIT | 0.62 |
21/02/2021 | 47.86 | A | SUPERMARKET | MAIN | SPLIT | 23.93 |
23/02/2021 | 250.56 | B | STORE | HOBBIE | OWN | 250.56 |
24/02/2021 | 48.05 | F | WATER | MAIN | SPLIT | 24.025 |
24/02/2021 | 40 | G | NET | MAIN | SPLIT | 20 |
24/02/2021 | 75.25 | C | STORE | HOBBIE | OWN | 75.25 |
24/02/2021 | 52.9 | A | SUPERMARKET | MAIN | SPLIT | 26.45 |
24/02/2021 | 24.01 | H | STORE | HOBBIE | OWN | 24.01 |
24/02/2021 | 45.88 | C | STORE | HOBBIE | OWN | 45.88 |
25/02/2021 | 50 | I | RANDOM | MAIN | SPLIT | 25 |
25/02/2021 | 691.61 | U | STORE | HOBBIE | OWN | 691.61 |
25/02/2021 | 125.85 | V | STORE | HOBBIE | OWN | 125.85 |
25/02/2021 | 23.97 | X | STORE | HOBBIE | OWN | 23.97 |
25/02/2021 | 47.14 | C | STORE | HOBBIE | OWN | 47.14 |
26/02/2021 | 121.9 | J | MEDIC | MAIN | SPLIT | 60.95 |
27/02/2021 | 23 | K | RESTAURANT | EAT OUT | SPLIT | 11.5 |
27/02/2021 | 8.58 | A | SUPERMARKET | MAIN | SPLIT | 4.29 |
28/02/2021 | 78.33 | A | SUPERMARKET | MAIN | SPLIT | 39.165 |
The first row is the day of the month, the second one is how much was spent, the third one the place where the purchase was made, the fourth one I call it type 1, the 5th one type 2 (this one is more important), the 6th one is if I made the spending for myself, if it is split with someone or not and finally, the last one is how much I really spent after accounting for divisions etc.
I was looking to use VBA to create a script that would:
1 - Ask for the data range to be edited (on this example, I would select the whole table above) - and this would be stored in a variable
2 - Ask for the number of days of the month - I think this might be important as each month has a different number of days and I might not make a purchase on every single day
3 - Split the main table into 4 smaller tables, one for each category of type 2 spending (MAIN, HOBBIE, EAT OUT, SINGLE)
4 - On each table, leave only 2 rows - the date of the purchase and the total value
5 - Create a cumulative line plot for each table - a total of 4 line plots - that would plot how my daily spending increases as the month goes on (x axis the days of the month, y axis the value spent)
6 - Optional - a cumulative plot with the total of the 4 categories
7 - Place these plots underneath the table one after the other so I could later compare one month next to each other
Sorry for the lenghty question but I am used to another programming language. For simplicity I would like to implement all of this on Excel and also start learning VBA is an added bonus.
Can anyone help me with this?
Thank you very much!
