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.
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.
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
Hi Yanesh,
ReplyDeleteI started using MME from April this year. I always wonder why they missed such functionality. Your query really helped me out.
Can you write a query that can show category-wise expenses for a single day.
Money Manager Ex© is a easy-to-use personal finance software. It primarily helps organize one’s finances and keeps track of where, when and how the money goes. It is also a great tool to get a bird’s eye view of your financial worth. It includes all the basic features that 90% of users would want to see in a personal finance application.
ReplyDeleteHi cool boy,
ReplyDeleteThanks for sparing time to read my blog. Here is your query:
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-07-02'
You need to replace the date with the desired date in yyyy-mm-dd fromat.
I am a new user to MME.
ReplyDeleteI have a basic understanding of databases but I am struggling with using SQL.
Can you give a basic description of the databases used in MME or at least point me in the right direction.
Thanks