Quick expenses dashboard

[vsbabu]

From installing SQLPage to this dashboard with search - took about a day to parse the data into a sqlite3 database and attaching categories to transactions. SQLPage made it so easy from then onwards.

I've attached some screenshots below with a random(0,1) multiplier applied to all records - 1 year records are as it is duplicated 5 times for testing.

• Default URL queries current month beginning to today
• Quick menu options set date range and refreshes page via GET
• Dashboard has following sections
  • Top large number bar showing expense, income and net amounts with number of records in each
  • A chart showing expense, income and net by month
  • A treemap of only expenses to get an idea where money is going
  • A barchart by category of spends for each month
  • A weekly split of net flow by date as a table. I tried showing the same data using heatmap component, but doesn't look good - was trying to get it somewhat like github contrib heatmap.
  • A search form which is pre-filled with the query string parameters. You can multi select categories to filter in OR use the "exclude" checkbox to filter out only those categories.

Note - this screenshot above is actually 3 screenshots attached one below the other - causing some misalignment.

Menus are all made using SQL looking at min and max date of data available. ie., each menu starts from today and goes as back as the minimum date of transactions. Added the dark/light mode toggle from examples, but the icon change doesn't seem to work.

Todo Next

1. Add a plain text search field for narration using FTS5.
2. Figure out RapidFuzz to automatically group merchant names so that I can add lat,long manually to those groups and show spends as a map.

Code details

• index.sql runs shell.sql, search_results.sql and search_form.sql as dynamic components one after the other. search_results.sql call has parameters set to default date range if not present.
• shell.sql queries the data along with a recursive CTE to generate months, quarters and financial year ranges. Then these CTEs are used with json_group_array to print menus as json.
• search_results.sql creates a temporary table and fills it with data from main tables applying query string or default parameters. Then on, all the dashboard items are selected from this temporary table. Initially I wrote all these using CTEs, but got fed up of changing the CTEs in multiple places and switched to temporary table. Is it possible to use dynamic table name in "FROM"? #874

[alexisrc1]

Hello @vsbabu great job on building this app ! can you please tell me how you found out about SQLPage and what is your end goal with this Expenses dashboard (is it a personal project or something that will be shared) ?
I'd be happy to have a quick chat with you if you want to discuss this further https://cal.com/alexisrc/15min

Alexis

[vsbabu]

Hi @alexisrc1 - found about SQLPage from YC site. End goal is just a personal project - had time in hand and wanted to code something; so started by parsing payment app extracts into CSV; once I did that, got it into LibreCalc to build some analysis. That is when I found SQLPage, it was very interesting to read about and just built this for fun. Original idea/goal I had was to use SQLPage for quick prototyping of data models and expose as APIs when designing software - previously I used node-red for this (writeup on how I do this) and SQLPage makes it much more easy.

[alexisrc1]

ok great thanks for the feedback! if you need to host your app you can try our hosting solution at editor.datapage.app, and i'd be interested to see the final version of your app so if you dont mind sharing it it would be great :)