Wednesday, July 02, 2008

Money Manager Ex - SQL Query

Money Manager Ex is a personal finance management software. It lets you manage your personal faineance. You can manage  your expenses, investments and stocks using Money Manager Ex. The most appealing feature of this software is custom reports. It lets you run custom queries against its Sqlite database.

In my previous post on Money Manager, I explained its different features. In this post, I will explain how you can get category wise report of your expenses. Money manager manages your accounts in categories and sub-categories. It has many pre-defined categories. Each category is further classified into subcategories. Usually these are sufficient for the general use.

However you can create new category or subcategory easily. One lacking feature of this software is that it does not provide category-wise report of your transactions. You can see transactions summed up by sub-categories however.

To overcome this shortcoming, I wrote a simple SQL statement that allowed me to see the amount I spent on different categories.

SQL to view category-wise report:
The SQL statement to see categori-wise report is written below.

select a.categid, categname, round(sum(transamount),0) amount from checkingaccount_v1 a inner join category_v1 b on a.categid = b.categid group by a.categid having transdate >= '2008-06-01' and transdate < '2008-07-01'

The above statement displays amount spent on different categories for the month of June 2008 i.e. between 1st June and 30th June (both inclusive).  You may need to replace the dates i.e. '2008-06-01' and '2008-07-01' as per your requirement.Custom SQL Report option of Money Manage Ex

To execute this statement, select 'Custom SQL Report' from the 'Reports' menu on the Money Manager. If you cannot see 'Custom SQL Report' option, you may need to expend the Reports section by clicking on the '+' sign.


Selecting the 'Custom SQL Report' will open a 'Custom SQL Dialog' box that will ask you to type the query. Copy and paste above statement into the white box and click on the Run Query. It will show your desired report.

Custom SQL Dialog of Money Manager Ex

If you have any problem running the query, please let me know through comments of this post.

If you need any specific query for your requirement, please put your requirement in the comments and I will be happy to help you out.

Note: In future, I will be writing more SQL queries for MME. If you are interested in these queries, please subscribe the RSS.

Related Post:
Money Manager Ex - Described

Related Posts