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

Advertisements

10 thoughts on “Money Manager Ex – SQL Query

  1. Hi Yanesh,

    I 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.

  2. 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.

  3. Hi cool boy,

    Thanks 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.

  4. One more query (For Augusts accounts):

    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-08-01′ and transdate <= ‘2008-08-31′

  5. Hi!
    I have a little problem with SQL Queries. Whatever I tried to run, I got the message “Error Executing SQL. SQLITE_ERROR”. Is there something do you have to install except Money Manager?

    1. You don’t need to do anything else except installing Money Manager. You may try to reinstall it. But don’t forget to take backup of your database before reinstalling.

  6. Hello,
    I am a new Money Manager Ex user, and I have no expertise in SQL but I know (a little) DB structures.
    So, I get a book about SQL (SQL for Dummies) and i explore MMEx Database with sqliteBrowser.
    I appreciate your query for Category Expenses, but I have 2 problems :
    – In my DB I enter (as MMEx says) Withdrawal and Deposit : also one must be + and the other –
    – I enter also split expenses : splittransaction for wich CATEGTID = -1 wich are after tagged with categories and sub-categories, and theses one aren’t included when category is sumed.

    In order to avoid to write SQL query, I thought use a query application : Navicat Lite but I don’t know enter asked link between Navicat and MMEx database (it aks IP adress, port, … and the MMEx DB is included in MMEx application and I don’t know how to get the database only).
    Could you give me some advises for helping me.
    Many Thanks.
    JCA

  7. @JCA,
    I haven’t used Navicat before so I visited its site. It says that Navicat is available for MySQL, Oracle and PostgreSQL. In other words it’s not for SQLite database. So you cannot use Navicat with SQLite.

    I am not able to understand why you need to write direct queries in MMEx database. I suggest you should do data entry using MMEx only. Then you can write SQL queries to view data using the MMEx Custom SQL Report option.

    If you need any specific query, post your requirements here. I will provide query to you.

  8. Hello,
    Many thanks for your fast reply.
    Infortunaly, I thought MySQL and SQLite DB are compatible !
    To enter data, I only use MMEx tools. I’ll try to write a query with the right syntax but if I have too much errors, I’ll ask you for help.
    Last question, do you know if it is possible to export MMEx SQLite DB (in MMEx tools I found Export DB in text/CSV file but after, how to rebuild the DB ?).
    JCA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s