You are going to create your Monthly Expenses report and also display it as a chart. This will give you the flexibility to extend reporting and charting capability beyond what is available in the canned GnuCash reports.
- Prerequisites: Make sure that you have saved your MACS accounts as a SQLite3 database from GnuCash.
- Prerequisites: Make sure that you have Microsoft Office Excel installed on your system. This tutorial and the screenshots are based on Excel 2003 running on Windows XP. If you have a different version or another operating system, then please look for the equivalent steps.
- Prerequisites: In order to connect to this SQLite3 database from Excel, you need an ODBC driver. Make sure you have installed an ODBC driver for SQLite3 and created an ODBC DSN to the exported GnuCash accounts file.
- While you are in a new worksheet in Excel, from the menu, select Data | Import External Data | New Database Query. The Choose Data Source dialog will open.
- You will see the Databases tab in the Choose Data Source dialog showing a list. Now that you have already set up an ODBC DSN, you should see MACS in that list. Select it and uncheck the Use the Query Wizard to create/edit queries, as shown in the following screenshot:
- Click OK. The Microsoft Query application will launch with the Add Tables dialog open. Select accounts and click Add. Similarly, add splits and transactions tables as well.
- You will find that the guids in the three tables are joined, which is incorrect. You can click on each join and click Delete to remove these joins. From the menu, select Table | Joins to open the Joins dialog. Now create the two joins, as shown in the next screenshot.
- Drag and drop the four columns in the Data pane, as shown. We want the transaction description, not the account description. Click the Query Now button to retrieve data, as shown in the following screenshot:
- Once the data looks OK, select from the menu File | Return Data to Microsoft Office Excel.
- You will get the option of putting the data in a worksheet or creating a pivot table. The pivot table feature is well suited for presenting summarized data as well as for cross tabulations and is available in most spreadsheet applications. Choose to create a pivot table. This will help you to do the monthly report.
- As we cautioned you earlier, the GnuCash raw data needs to be formatted to be made presentable. For example, the value_num field needs to be divided by the
value_denomfield to arrive at the amount. - You can then copy the data from the pivot table into a worksheet and format it as $ to create the report and chart, as shown in the following screenshot:
Though we didn't walk you through the preceding tutorial step-by-step, rest assured you can create such reports and charts without writing a lot of custom code. However, you do need proficiency with spreadsheets to get your reports and charts just right.
Oh, by the way, looking at the MACS monthly expenses chart, here is a quick friendly tip. The expenses seem to be running out of control. Unless the business of MACS is booming and has consciously decided to spend more money to gear up for better execution, MACS should take a close look at what is happening on the expense side and get that under control. No wonder they said that a picture is worth a thousand words.
In OpenOffice.org Calc, you should be able to view the ODBC DSN created for the SQLite3 database from View | Data Sources. You should also be able to view queries saved in OpenOffice.org Base and execute the queries and retrieve data. Data Pilots are the OpenOffice.org Calc equivalent of pivot tables. You can access them through Data | Data Pilot | Start….
In addition, there are a ton of other query and reporting tools available in the market for SQLite3, including open source, freeware, and commercial tools. Moreover, when you add the tools available for MySQL and PostgreSQL as well, the options are extensive indeed. Depending on your business needs and familiarity, evaluate the options available out there and pick what will work for you and your team.
- When you look at the tables, you can understand some of the things better that we said earlier about accounts, transactions, and splits. The memo field is stored in which table?
a. accounts
b. transactions
c. splits
d. none of the above