Transactions in Excel via ODBC

[Renato]

Step 1: Install SQLite ODBC Driver

Since Windows does not come with a driver for SQLite databases by default, you have to install it once:
4. Download the free driver from the official developer site (e.g. the widely used SQLite ODBC Driver by Christian Werner).
5. Be sure to choose the version that fits your Excel architecture (usually the 64-bit version if you use a modern 64-bit office).
6. Perform the installation.

Step 2: Set up ODBC data source (DSN) in Windows

To help Excel know where your Money Manager EX file is, create a system connection:
6. Press the Windows key and type ODBC data sources . Choose the version that suits your Excel (64-bit).
7. Switch to the System DSN or User DSN tab and click Add....
8. Select the SQLite3 ODBC Driver from the list and click Finish.
9. A configuration window will open:
Data Source Name: Give a name (e.g. MMEX_Transactions).
Database Name: Click Browse and select your Money Manager EX .mmb database file.
10. Confirm with OK

Step 3: How to include this query in Excel

In Excel, if you start the import via the installed SQLite ODBC driver, you can pass this SQL script directly instead of loading the raw tables one by one:
6. In Excel, go to the tab Data → Get Data → From Other Sources → From ODBC.
o Select your MMEX data source that you have set up. (e.g. MMEX_Transactions).
7. In the connection window, click the small arrow next to Advanced Options (or Advanced).
8. A text box will open with the label SQL statement (optional).
9. Copy the SQL query below completely into this field and click OK.
10. Excel runs the command directly on your Money Manager EX database and shows you the finished translated spreadsheet.
 
Note: You can now create reports using pivot tables, for example

Code: Select all

SELECT 
    t.TRANSID AS [Transaction ID],
    t.TRANSDATE AS [Date],
    a.ACCOUNTNAME AS [Account],
    t.TRANSCODE AS [Type],
    p.PAYEENAME AS [Payee],
    CASE t.TRANSCODE 
        WHEN 'Withdrawal' THEN -t.TRANSAMOUNT 
        ELSE t.TRANSAMOUNT 
    END AS [Amount],
    c.CATEGNAME AS [Category],
    t.NOTES AS [Notes],
    t.STATUS AS [Status],
    target_a.ACCOUNTNAME AS [Transfer Account]
FROM CHECKINGACCOUNT_V1 t
LEFT JOIN ACCOUNTLIST_V1 a ON t.ACCOUNTID = a.ACCOUNTID
LEFT JOIN PAYEE_V1 p ON t.PAYEEID = p.PAYEEID
LEFT JOIN CATEGORY_V1 c ON t.CATEGID = c.CATEGID
LEFT JOIN ACCOUNTLIST_V1 target_a ON t.TOACCOUNTID = target_a.ACCOUNTID
ORDER BY t.TRANSDATE ASC, t.TRANSID ASC;

Here is some updated code for the automatically generated offsetting entry for a transfer

Code: Select all

SELECT 
    t.TRANSID AS [Transaction ID],
    t.TRANSDATE AS [Date],
    a.ACCOUNTNAME AS [Account],
    t.TRANSCODE AS [Type],
    p.PAYEENAME AS [Payee],
    CASE t.TRANSCODE 
        WHEN 'Withdrawal' THEN -t.TRANSAMOUNT 
        WHEN 'Transfer' THEN -t.TRANSAMOUNT 
        ELSE t.TRANSAMOUNT 
    END AS [Amount],
    c.CATEGNAME AS [Category],
    t.NOTES AS [Notes],
    t.STATUS AS [Status],
    target_a.ACCOUNTNAME AS [Transfer Account]
FROM CHECKINGACCOUNT_V1 t
LEFT JOIN ACCOUNTLIST_V1 a ON t.ACCOUNTID = a.ACCOUNTID
LEFT JOIN PAYEE_V1 p ON t.PAYEEID = p.PAYEEID
LEFT JOIN CATEGORY_V1 c ON t.CATEGID = c.CATEGID
LEFT JOIN ACCOUNTLIST_V1 target_a ON t.TOACCOUNTID = target_a.ACCOUNTID

UNION ALL

SELECT 
    t.TRANSID AS [Transaction ID],
    t.TRANSDATE AS [Date],
    target_a.ACCOUNTNAME AS [Account],
    t.TRANSCODE AS [Type],
    p.PAYEENAME AS [Payee],
    t.TOTRANSAMOUNT AS [Amount],
    'Transfer' AS [Category],
    t.NOTES AS [Notes],
    t.STATUS AS [Status],
    a.ACCOUNTNAME AS [Transfer Account]
FROM CHECKINGACCOUNT_V1 t
INNER JOIN ACCOUNTLIST_V1 target_a ON t.TOACCOUNTID = target_a.ACCOUNTID
LEFT JOIN ACCOUNTLIST_V1 a ON t.ACCOUNTID = a.ACCOUNTID
LEFT JOIN PAYEE_V1 p ON t.PAYEEID = p.PAYEEID
WHERE t.TRANSCODE = 'Transfer'

ORDER BY 2 ASC, 1 ASC;