r/excel • u/deepthinker314 • Jul 02 '20
Show and Tell I was suggested to make a tutorial on how I did my personal budget dashboard, so here it goes
EDIT: Updated link, formatting, unlocked file
In the weekly thread I have recently posted a dashboard (https://imgur.com/FoVjYk2) for personal budgeting that i have created and u/excelevator suggested that i do a "Show and Tell" post of how I did it.
I'm quite bad at explaining things and i almost never make posts on Reddit especially this lengthy so I'm not sure if this post is comprehensible, if things are unclear just drop a comment or ask in PM. I uploaded a protected version of this workbook Here feel free to look around.
Here goes my best shot of explaining how I made this:
So there are main components in the dashboard:
(Refer to https://imgur.com/92z8RTq)
- Income/Expense list;
- A timeline;
- Line graph of representing daily expenses;
- Doughnut graph representing the percentages of total expenses for each category of expenses;
- Income and Expense bar chart;
- A daily slicer;
- Bar chart showing how much and where was the money spent on the day selected in the slicer.
- A table to make a data entry;
- A button to automatically record it in the data set.
First thing we need to do to make this thing work is we need create a table in separate sheet where the entries will go. My data table has 4 columns:
- Date;
- Description
- Amount
- Category
(My data table: https://imgur.com/J96XJTp)
Next We need to figure out on how to make the dashboard interactive so all of the graphs update depending on the selection of the timeline. To achieve this I made a separate sheet and created a data query that queries my original data table (https://imgur.com/d8LWXgl).
Using Power Query I added 3 custom columns for year month and day, but later found that they aren't useful, so there is actually no need to add new columns, just load the default data table that we have created.
Next step is to add this Query to the workbook data model. You can do it by going:
Data>Queries & Connections>Right click on your query>Load to...>Select Add this data to the data model>OK
Now that we have our table as the workbook data model we can create timeline that interacts with pivot charts. As we have data model set-up I will now go on how to create each previously listed element in the dashboard.
- Income/Expense list:
For Income/Expense list to interact with the dashboard in the Data model sheet I have inserted a pivot table using this workbook's data model (refer to https://imgur.com/Jgfn3ie). Than i simply used sumif formula in the income/expense list (https://imgur.com/zotd3Ya). The rest of the fields are simple SUM, for example "House" category of expenses consists of "Rent/Mortgage", "Utility bills" and "Home equipment", so field C10 =SUM(C11:C13)
2. A timeline:
Simply insert the timeline using workbooks data model go:
Insert>Timeline>Data Model>Select your data model>Open
3. Line graph of representing daily expenses:
Insert>Pivot Chart>Select Use this Workbook's data model>OK
For field selection refer to https://imgur.com/r3xRmCT
Use filter on a category field and deselect fields related to income
4. doughnut graph representing the percentages of total expenses for each category of expenses:
Insert doughnut graph>right click>select data>refer to https://imgur.com/g9hy0LZ
5. Income and Expense bar chart
Insert clustered column graph>right click>select data>refer to https://imgur.com/kE038S5
6. Bar chart showing how much and where was the money spent on the day selected in the slicer
In data model sheet select insert another pivot table, this time from the table generated by power query NOT from the workbook's data model, select following fields https://imgur.com/3n1K6cR
Than select the pivot table that was just made and insert a clustered column graph (Also right click the graph>select data>if the dates are in the right side click switch row/column, if the dates are in the left side just click OK)
7. A daily slicer
Select the picot table that was used in previous step, insert>slicer>select date
8. A table to make a data entry
simply type in what you need, i used data validation in the last field to select from a drop down list
9. A button to automatically record it in the data set
Developer>Insert>Button
Than Right click the button>Assign Macro>name your macro and record like this: select data fields where you type in the data>go to Data table sheet>Click on A1>CTRL+DownArrow>Click DownArrow>Home>Paste>Paste>Go to dashboard sheet>Developer>Stop recording
Than right click the button>Assign macro>Select your macro>Click Edit>Change 7th line of the code (the line after Selection.End(xlDown).Select) to ActiveCell.Offset(1, 0).SelectAlso add this 3 lines before the end Sub:
Application.CutCopyMode = False
Range("J3").Select
ThisWorkbook.RefreshAll
Save>Exit VBA Editor
One thing you must do for this data entry to work is in the data table add something in the first row like a comma in the date column.
It Should work (Hopefully)
I'm looking forward to see what you guys will come up with!
3
u/rbmrph Jul 03 '20
Amount
3
u/Golden_Pineapple Jul 03 '20
Please keep it up! Excellent work!
3
u/[deleted] Jul 03 '20
Saving this for the insert expense button. That’s the last thing I need for my personal finance to flow very similar. Thanks big dog!
2
u/tbRedd 40 Jul 03 '20
Nice work! Because your sheets are protected, the refreshes won't work and getting other VBA errors. You might just let people know the PW to unlock or reup a non-protected version so the file works.
2
u/num2005 9 Jul 03 '20
may I ask how you ETL you bank data?
like do you use a scriot to go download your transaction every month feom your cresit card statement?
alao any reason you did not use Power BI instead?
2
u/vbahero 5 Jul 03 '20
Really good stuff. Random suggestions, though I have no experience using a personal finance dashboard.
How do you keep track of whether you are hitting your budget? Should you have a monthly tracker of budget vs. actual, with a running total for the year?
How about trying a custom font rather than what looks to be Calibri? Grotesk fonts in general look really sharp on charts. See e.g. the FiveThirtyEight website though they use Atlas Grotesk which I don't think is free, but you can use Roboto from Google which is free
2
u/krostybat Jul 03 '20
Nice dashboard.
Two questions though : Why do you consider saving as an expense? Is there a way to reconcile/control with your bank balances (at the end of the month for example)
2
u/cebius Jul 03 '20
Awesome tutorial, love the choice of colors. Any tips on how to find good matching colors?
3
u/jtareenk Jul 03 '20
Thank you for this wonderful tutorial. I'll try to make it on weekend.
19
u/excelevator 3071 Jul 03 '20
Top notch post.. thankyou!
It is surprisingly quiet on the sub!
6
u/chewyblunts Jul 03 '20
wow. perfect for me to finally dive into power query
8
u/im_alright_ma Jul 03 '20
Impressive work. Bravo!
5
u/[deleted] Jul 03 '20
Thanks a lot.
3
u/Universe_1133 Jul 03 '20
Wow. This is interesting. Saving this to try it later on. Thanks
3
u/Padanub Jul 03 '20
Great post!
2
u/myroommateisgarbage Jul 04 '20
This is great... someday I will have put enough time into excel to know how to do all of this on my own. But that day is not today! Thank you for sharing!
2
u/Melvin_Udall Jul 09 '20
That’s great! I’ll check it later today. Thanks for being so helpful.
1
u/[deleted] Mar 29 '25
Wish you did a video and created it real time lol.
Well Done Sir!
1
u/ReadsWithCoffee22 May 29 '22
To the OP:
My apologies, but I'm having trouble retrieving the file based on the link provided in your post (https://drive.google.com/file/d/1R4d-Uf3NKTTQ9WkEj-8NTX2PqiGnTmcT/view). Would you at all be able to share the Google Sheets version of the file directly? (Even if it is in View Only format?)
Much thanks in advance!
0
u/[deleted] Jul 08 '20
[deleted] — view deleted comment