Finance Manager Website

S0ULphIRE

Golden Master
Messages
9,232
Location
Australia
What are you doing!
As of last year I decided to start keeping a record of my finances and setting a monthly budget. Google Sheets was great at first, but as things got more complicated with interest/stocks/etc, I decided to move to using some actual accounting software.

GnuCash, is free, the learning curve isn't too steep, and it's ugly as hell :p I also hate the fact that I can't view or update my details from anywhere like I could with Google Sheets.

The goal of this project will be to setup a locally hosted webpage capable of pulling and pushing data to my PostgreSQL GnuCash Database, and to display that data in a much more eye pleasing way than this:

OMpehio.png


Like seriously, ew.

Getting Started
You will need to install:
  • Apache Httpd (Web server)
  • PHP (To connect our webpage to our database)
  • PostgreSQL (To store our GnuCash data)
  • Bootstrap (Because who wants to write everything from scratch :p)

For the top 3 items in the list, EnterpriseDB has conveniently packaged it all together, Download Here

There's plenty of guides out there on how to setup the above programs, so I'll leave that out of this tutorial. The only thing I'll add is that you probably want to set your apache server to run as a service:
Code:
/path/to/httpd.exe -k install
If you used the EnterpriseDB installer to install Apache, your Apache directory will be "C:\Program Files (x86)\PostgreSQL\EnterpriseDB-ApachePHP\apache", so go there for any initial config setup you need to do (e.g. selecting your web directory)

Next download the GnuCash Website and put the contents in the root of your web directory (whatever you set that to in httpd.conf)
If you have Git, you can clone it with: https://github.com/S0ULphIRE/GnuCash.git

Modify line 35 of index.php to reflect your Postgres login details and the name of your gnucash database, then you should be good to go! Browse to http ://localhost (or whatever the server is set to in httpd.conf again) and you should see your pretty data :p

I'll be modifying the github repo over the next few weeks to slowly tweak it better, and hopefully I haven't forgotten anything in the setup that was required.
 
Last edited:
Update on what it looks like at the moment. Still a few formatting errors to fix up :p but getting closer!
NfBxli2.png


Still to do:
  • Add budgeting page
  • Enable transactions to be added/edited/removed
 
Last edited:
Looking good!

Your transactions table header isn't lined up correctly with the data :p.
 
Looking good!

Your transactions table header isn't lined up correctly with the data :p.
 
Yeah I know lol, it's the friggin scroll bar -_-
I didn't want the whole table showing, so I did this in the css (stole someone else's code :p):
Code:
table {
    width: 436px; /* 140px * 5 column + 16px scrollbar width */
    border-spacing: 0;
}

tbody, thead tr { display: block; }

tbody {
    height: 400px;
    overflow-y: auto;
    overflow-x: hidden;
}

tbody td, thead th {
    width: 140px;
}
thead th:last-child {
    width: 156px; /* 140px + 16px scrollbar width */
}

I'll change it to a class instead of applying to all tables when I can be bothered, but IDK why it just refuses to line up. I have a feeling it's because I've formatted my table wrong here too with the td class definitions:
Code:
<table class="table table-bordered table-hover table-striped">
<?php
	echo("<thead>");
	echo("<tr>
	<th class='col-xs-3'>Date</th><th class='col-xs-7'>Description</th><th class='col-xs-2'>Amount</th></tr>");
	echo("</thead>");
	echo("<tbody>");
	echo("<tr>");
	echo("<td class='col-xs-3'>$date</td>");
	echo("<td class='col-xs-7'>$row[2]</td>");
	echo("<td class='col-xs-2'>$$value</td>");
	echo("</tr>\n");
	echo("</tbody>");
?>
</table>

edit: urgh so I removed the td classes from my php table, now it lines up but it's still not quite right -_- murdering this XD
PmpEzEu.png
 
Last edited:
Honestly I'd just use a pre-built table library. Something like JTable, or if you wanted to do AngularJS... Angular's UI-Grid. Then you can just spit JSON to it, and configure options on it. Much easier than manually configuring tables and such.

I use UI-Grid at work all the time, and have used JTable a couple of times at work as well.
 
God damnit. I googled JTable at home yesterday, first results were about Java stuff so I was like psshhhh nope not using that!

Got to work today, my coworker informed me I'm an idiot, and I now have the right JTable link :p going to refactor and get my SQL data in appropriate format to be passed to that next. Thanks for the suggestion lol
 
God damnit. I googled JTable at home yesterday, first results were about Java stuff so I was like psshhhh nope not using that!

Got to work today, my coworker informed me I'm an idiot, and I now have the right JTable link :p going to refactor and get my SQL data in appropriate format to be passed to that next. Thanks for the suggestion lol

Hahaha, yeah I saw JTable results also listed Java stuff....but I assumed you would be able to figure out that I meant JavaScript :p.
 
I've spent the morning redoing the code so it's not all in one giant file :p all PHP calls are now properly abstracted...I think...

After adding JTable though, I've run into an issue with it messing with my Morris bar chart :mad:

If I comment out the highlighted line below, my Morris chart works like it should but the JTable table completely disappears.
TgQtLPx.png


If I leave the line uncommented, my Morris chart doesn't float the column labels like it should.

Morris correct, table missing: https://i.imgur.com/1NEggoP.png
Table shows, Morris screwed: https://i.imgur.com/69ppM06.png

So their custom ui bit is messing things up -_- but without it the table never shows up. Le sighhhh

EDIT: Well, don't I feel stupid lol, downloaded a NORMAL FRIGGIN COPY of jquery ui, and everything works :p ****ing doh
Next step, fixing the table data
 
Last edited:
I got a friend at work to give me some tips on the SQL side of things, now I've done a whole bunch of changes to the SQL calls and everything's starting to make more sense :grin:

Also integrated the Budget part of GnuCash, so you can now easily see whether a particular expense account is under/over budget. As you can see, I've spent way more on gifts this month than planned, also a little over budget on transport + takeaway :p but everything else is ok
Over:
JGhsebU.png


Under:
ZIsMe6T.png
 
Last edited:
Back
Top Bottom