Personal Finances Web App

Share Your Projects
1

Well, I haven't yet got to the "web" part. :rofl: But I will do and will, of course, be using UIBUILDER to do it and Node-RED as the data orchestrator.

Having recently retired, I find myself in need of doing rather more sorting through my personal finances than I have for years. I normally run up some over-complex spreadsheet but in more recent years, I realised that I lean heavily on PowerQuery which is unique to Microsoft Excel.

As I might not always have access to that, I tried out a number of personal finance apps. But none of them did what I wanted and all of them were too complex for my needs.

So - break out the coding skills!

Been a long time since I did any serious SQL but SQLite seems the best tool for this job. So with some major help from the Claude AI app (I didn't use GitHub Copilot this time as I'm saving my Claude credits for uibuilder work), I got started.

And at the end of day 1: I have a set of node.js scripts that create the database, populate the reference data and import (most) of the downloaded transaction CSV's from our various accounts. (Some imports not quite working, a job for another day).

There is a categories/sub-categories table and a rules table that automates assignment of categories to transactions using a list of rules that can be full text, wildcard or regular expression tests against the transaction description.

Added some SQL Views to start being able to summarise data. Here is some test output to the console for a single year, nowhere near a complete set of categories yet:

image
image432×246 9.44 KB

Ultimately, a watch node in Node-RED will trigger import of new data as it is dropped into a folder and uibuilder will have a series of SPA pages showing different views of the data.

In later tranches of work, I'll allow inputs to the categories, ability to re-run the auto-categorisation and allow manual overrides (the auto-categorisation already caters for manual overrides). There are also comments and "who" (who triggered the spend or income) fields which can be manually input.

By-and-large, the summary views are sufficient for my use.

So while I am starting by building everything in code, it will all move to Node-RED eventually. Node-RED as a data orchestrator is a really powerful capability. And no need to have to manage multiple ExpressJS web hosts since Node-RED and UIBUILDER between them have all of that covered as well. :smiley:

4 Likes
2
  • uncategorized
    • Did you at least opt for the non-descript packaging material :winking_face_with_tongue:
1 Like
3

Import:

image
image1145×352 36.1 KB

Update the transaction auto-categories:
image
image575×376 18.7 KB

Only takes a second.

Summary for last year:

image
image726×482 22.6 KB

Also only takes a second.

All MUCH faster than Excel. :smiley:

4

Looking forward to this!

5

And we have the first web page!

image
image654×1144 50.1 KB

The flow - very simple:

image
image1195×740 49.7 KB

The front-end code, also pretty simple:

<h2>Summary for this year</h2>

<div id="output"></div>

<style></style>
<script>
    // Give VS Code IntelliSense for uibuilder
    /// <reference path="../types/uibuilder.d.ts" />

    function formatCurrency(value) {
        value = parseFloat(value) / 100 // Assuming the value is in pence, convert to pounds
        return new Intl.NumberFormat('en-GB', { style: 'currency', currency: 'GBP' }).format(value)
    }

    uibuilder.onChange('msg', (msg) => {
        console.log('Received msg:', msg)
        uibuilder.createTable(msg.payload, { parent: '#output' })
        // Select all the currency cells and format them
        document.querySelectorAll('[data-col-name="debits"], [data-col-name="credits"], [data-col-name="diff"]').forEach(el => {
            el.textContent = formatCurrency(el.textContent)
            // If the text starts with a minus sign, make it red, otherwise green
            if (el.textContent.startsWith('-£')) {
                el.style.color = 'hsl(0, 50%, 70%)' // Red
            }
            // right align the currency cells
            el.style.textAlign = 'right'
        })
        // Add another row at the end of the table with the totals for each column
        const table = document.querySelector('#output table')
        const totals = { debits: 0, credits: 0, diff: 0 }
        msg.payload.forEach(row => {
            totals.debits += row.debits
            totals.credits += row.credits
            totals.diff += row.diff
        })
        const totalRow = document.createElement('tr')
        totalRow.innerHTML = `
            <td>&nbsp;</td>
            <td><strong>Totals</strong></td>
            <td data-col-name="debits"><strong>${formatCurrency(totals.debits)}</strong></td>
            <td data-col-name="credits"><strong>${formatCurrency(totals.credits)}</strong></td>
            <td data-col-name="diff"><strong>${formatCurrency(totals.diff)}</strong></td>
        `
        table.querySelector('tbody').appendChild(totalRow)
    })

    console.log('ROUTE: "Summary for this year"')

    const elOut = document.querySelector('#output')

</script>

Because SQLite is rather basic, it is best to leave values as integers which is why I'm dividing by 100 in the front-end code. I could, of course, do this easily in Node-RED.

I could also have used a uib-element node to create the table. But since the table creator is exposed as a function in the uibuilder client library anyway, it's as easy to do it there as it is in Node-RED.

2 Likes
6

And the table is now sortable and highlights the top/bottom values.

image
image899×805 41.6 KB

7

And the 2nd page - the list of categories with the ability to add new ones or change existing ones.

Categories are a flat table but allow a parent_id so that we can have sub-categories. These are shown in sub-tables under the parent. Clicking on any category row pre-fills the form for you. Changes to the actual data happen in Node-RED.

image
image780×1132 44.2 KB

8

Gaining features rapidly! Looks almost similar to one of my reports from GnuCash.

9

To be expected I suppose.

What I like is that I just have what I need and nothing more. Also, imports of CSV files are already linked to CSV schema's and easily changed. So keeping up with banks, etc changing formats is easy enough.

I've used the Claude web site to assist with developing this, it has made development MUCH faster and I haven't had to expose any personal finance data. It does tend towards over-complex CSS and occasionally over-complex code but most of what it has given has worked OK. The initial prompts were just a few paragraphs too, not difficult at all. Starting with command-line only I think did help a lot though as I could focus on the core logic and not worry about the visuals.

2 Likes