OK, last time I introduced how to access the database of GrandTotal. Half the fun is figuring out how the author put together his database. I have to say that some of it isn’t how I’d have done it, but thus far it hasn’t been too hard to figure out. The author put in his invoice item tables rows that identify an invoice as a whole and then other rows that are the items in the invoice. I think that would have been cleaner and easier to have two separate tables. It’d certainly have made the SQL easier.

To demonstrate getting data out I wrote a small program that goes through all the clients, finds the invoices for each, and then searches through the items in each invoice summing up the invoice total.

Now this wasn’t an exercise. I actually had a need to do this but exclude certain types of fees. My invoices contain the items I ship and then shipping charges. However if I’m trying to figure out profits I can’t include shipping since I don’t make any money on shipping. You might not have this particular need, but I think it demonstrates the principle of how to utilize a script in a helpful way.

Read the rest of this entry »

GrandTotal is my invoicing program of choice. I’ve used quite a few others but this is my favorite. For one the developer is very quick about feedback. For an other, it uses a lot of open standards that makes it easy to expand the program’s weaknesses.

There are a lot of reasons why you’d want to script GrandTotal. (Sadly it doesn’t support Applescript – so there’s a lot more I’d like to script but can’t – but it’s more open than the other programs I’ve tried) One problem I have is that the program doesn’t do a lot of analysis reports. It’s great for generating invoices. Not terribly good for things like Aged Reports or client summation reports.

My first job out of college was doing IT for a multimillion dollar company. (I was the entire IT department and handled more of the accounting than I expected) Half of what I did was hack into their accounting program to create custom data and reports using PowerBuilder, FoxPro, Access and Excel. Effectively that’s what I’m doing here with GrandTotal and Python. But it’s quite easy.

Over the next few posts I’ll show how to figure out the database structure, how to get data from the database, on up to doing some simple reports.

Read the rest of this entry »