Money MDB File Reverse Engineering
Smart Data Extraction
Existing tools for extracting user data from proprietary Microsoft Money data files are limited in the quality and coverage of what they extract, or are geared to move user data from one proprietary format to another, or don’t leverage understanding of the Money schema to produce usable subsets of data matching Money transaction register data and resolving many of the related field links into user-readable results. For instance, Money reports exported to .CSV have many coverage limitations, suffer data loss from issues associated with wide fields, and overload data stored separately in transactions such as transaction number and void state. They also don’t provide related record information directly. The Ultrasoft MoneyLink Excel Add-In cannot extract classification or parents of split transactions and has many other coverage limitations. The Quicken importer just moves your data (how much of it? how can you tell?) to Quicken.
Building on Hung Le’s prior work developing the Sunriise Java tool for working with Microsoft Money files, which uses the Health Market Science, Inc., Jackcess and Jackcess Encrypt code to provide decryption and Jet-engine access to the files, a set of tools have been developed in Microsoft Access to take Sunriise output (.MDB format) export of the Money data, and extract Money user data in usable forms, based on reverse engineering and empirical analysis of the Money storage schema.
Transaction extract has been validated by summing all transactions currency amounts by account with account opening balance and comparing the resulting balances to the live Money file data. Likewise, investment share amounts have been summed by account and investment and compared to live Money file data. Viewer queries are provided for both transactions and bills to present the extracted data in a more user-friendly, limited, format so that it can be compared to specific Money data.
A data dictionary is provided that documents what is known or suspected about the tables and columns in the schema and augments the understanding reflected in the queries.
The tool provided is not a fully-formed end user solution, nor will it be useful for users who do not have some level of Microsoft Access and relational database skills.
Prerequisites to use this capability:
Both standard Java runtimes from http://java.com and Java IDEs, e.g., NetBeans from http://netbeans.org, have been used in this effort
Sunriise in executable JAR format, with dependencies, is available from https://code.google.com/p/sunriise/downloads/list. Present status of this effort, as well as relationship of this executable to code in http://sunriise.sourceforge.net/, and to the apparently newer Sunriise2 effort, is unknown. Hung Le, if you are reading, drop me a line…
Note that one of the dependencies of Sunriise is Jackcess. Jackcess is a Java library of functions for reading and writing Microsoft Jet database files, from which the Money file structure is derived. Jackcess has recently refactored its API and continues in development. Sunriise uses the original API so will not benefit from future Jackcess development. Perhaps it doesn’t need to? The day I get a wild hair about learning Java, my first project will be refactoring that tiny subset of Sunriise I use to better suit my purposes here, and get it up to the current Jackcess API would seem a worthy thing to so. Until then, what I have now is good enough.
This file is included in the deliverables, as discussed below. This file includes a very limited amount of VBA code required to decode Money bitmapped fields and calculate scheduled bill dates. You will have to enable this code when opening the file, or by using trust settings in the Access Trust Center.
The general process to use this capability to get from your Microsoft Money .MNY format data file to smart extracted data in Access:
The most basic way to do this is from command line Java using the JAR executable. Example, assuming the current directory is where the JAR file is stored:
java.exe -cp sunriise-Build_20130216_50-app.jar com.le.sunriise.export.ExportToMdb "c:\users\dick\documents\money data file.mny" [password in quotes if the file has a password] "c:\users\dick\documents\Money Reverse Engineering\MnyExport.mdb"
After opening Money Reverse Engineering.accdb with Access, use the Access Linked Table Manager, select all linked tables except for RealAcctNumLkup and CAT M08 US ver defaults, and re-link them to the MDB file produced by Sunriise in the previous step. Now all the extract queries in the Money Reverse Engineering tool will map to your exported Money MDB. As delivered, theses tables are linked to the sample.mdb file, also delivered here.
Many of the extract queries depend on a local table of foreign keys used in “real” transactions. That table is built and populated by running the UpdateLocalUsedFKeysTable macro. (My Money file has literally thousands of transaction records that are not real register transactions or current bill scheduled transactions. The UsedFKeys table is used to keep from picking up Payees, Categories, Currencies, Investments, or classifications from any of these cruft transactions. This table was originally implemented as a query, but the number of underlying queries nested and union-ed promptly exceeded Access open database limits.)
The Extract* queries shown in the “ExtractUserData” object group produce the formatted and linked extract data as described below. You can either export their results directly from Access—how the Excel sample extract file was made—or create your own Access objects (queries, et al.) to do other things with them.
A number of other queries are included in the “UsefulMoneyQueries” object group. The following examples are initially the most useful in validating the extraction:
AcctBalancesInvClean – This query produces sums of Investment shares by Account and Investment and should produce results that match the Money Portfolio page shares values.
AcctBalancesTRNplusACCTAmtOpen – This query produces sums of account transactions and opening account balance and should produce results that match the Money Account List page balance values.
ViewBills – This query and its subdatasheet shows current, i.e., next scheduled instance, bill definitions and their transaction and split components.
ViewTxns – This query and its subdatasheet shows register transactions and split components.
What the tool described here can do, and, perhaps more importantly, what it can’t do and why:
All deliverables are included in an archive file: Money Reverse Engineering 20160920.zip. Contents include:
Money Reverse Engineering.accdb – The primary product of this effort, a Microsoft Access 2007 version database file. Note that the data dictionary has column information as a subdatasheet for each table. As distributed, supported/required tables in this file are linked to the sample.mdb file, the sample real account number lookup.xls file, and the CAT M08 Dlx Sunset US table in the Money default CAT tables.accdb file.
sample.mdb – The Money Deluxe 2008 Sunset US edition sample file converted to .MDB by Sunriise and linked to by Money Reverse Engineering.accdb. Included so you can use Access to look around in a Money file without all the required setup to use Sunriise to look in your own Money file.
sample HandB.mdb – The Money Home and Business 2008 Sunset US edition sample file converted to .MDB by Sunriise. Included so you can use Access to look around in a Money H&B file without all the required setup to use Sunriise to look in your own Money file.
sample -- extracted.xlsx – An Excel workbook with tabs for each extract formatted by the Money Reverse Engineering.accdb extract queries, as extracted from the sample.mdb file. Included so you can see what the provided tool can produce in a file format more accessible to Excel users.
Money default CAT tables.accdb – A Microsoft Access 2007 version database file with copies of the default CAT tables from both M08 Deluxe, and H&B, Sunset US. Money Reverse Engineering.accdb contains a helper subroutine, FlopCatDefaults, to link to one or the other of these tables and two useful Money queries, CategoriesM08defaultsNotInMyData, and CategoriesNotInM08defaults, to show how the categories in the currently linked exported Money file differ. As distributed, Money Reverse Engineering.accdb is linked to the categories from the Deluxe edition.
encrypted account number cracking samples.xlsx – An Excel workbook with a number of real world and test case examples of how Money encrypts Account and Payee account numbers in both clear and cypher text. The real world account numbers are all from long extinct accounts. If you have any code breaking skill or know anybody who does, here’s a great opportunity to further understand how to extract data from a Money file. What encryption is used? How is it seeded? Most importantly, how can we decrypt them programmatically from data in the Money file?
sample real account number lookup.xlsx – An Excel workbook for defining the translation of Money encrypted account numbers to clear text. Note that Money Reverse Engineering.accdb has a query, ObfuscatedAcctNumLkupDiagnostics, to compare the account numbers present in the linked exported Money file with the manually extracted lookup account numbers in this file.
While I hope this proves helpful to some people, and welcome feedback and assistance to improve the quality of extraction or extend its coverage, it is offered without any warranty of function or obligation to repair or support. Please contact me with questions or comments.
I was asked, about this effort, “extract to where?” There is no great answer to the “extract to where” question. I guess my first best answer is to anywhere but a proprietary .MNY file, for the sake of access to as much of your data as I've figured out how to get. Does this make it usable as a personal finance management tool? No. The point, first most, was to get the data out of the proprietary write only memory that is a .MNY file. Since I can now get out data that is otherwise only accessible, through a healthy Money app and a healthy file, via navigation, copy, and paste (e.g., Payee details or Category or Account comments), if that (I’m thinking transaction tax year assignment), I’m feeling like the first objective is mostly satisfied. What’s the use/who’s it useful to? I can’t speak for anybody else, but I have a lot of important history in my data file that, even in the absence of using Money as a tool for PFM, I’d really hate to lose. To me, yes, this is a huge insurance policy against that eventuality. Past that, I’m working toward using my extracted data set, and specialized queries, as replacements for lots of manual labor I presently do with information I collect in, and extract from, Money, alternately via customized Money reports and Export to Excel, or via MoneyLink extracted data. The benefit, for me, of doing this, is to reduce time and effort spent in household data management as well as increase its repeatability through automation of manual tasks. This is also insurance against the day MoneyLink breaks or I decide to install 64-bit Excel and break it. Now if only I could figure out a way to cut down on the hour or so it takes to extract all the data via Sunriise.
This work would not have been possible without the prior efforts of Health Market Science and Hung Le.
Please “use the force” wisely and endeavor to maintain a low profile in use of this capability. While it is reasonable to expect that Microsoft has bigger fish to catch than us Microsoft Money customers just trying to get at our data, it is entirely possible that aspects of what we are doing here could be considered problematic in terms of the Microsoft Money EULA and/or the Digital Millennium Copyright Act, neither of which probably care that it’s our data we are going after.
|10/12/2015||Grammatical, format, and editorial cleanup. Added version limitation.|
|10/12/2015 (2)||More editorial cleanup.|
|11/10/2015||A number of minor changes in the extracts, mostly driven from experience working with the data they produce. The most significant of these for most users is likely to be removal of Watch account stuff.|
|1/4/2016||Documented the limitation about non-register Tax Estimator entries.|
|8/16/2015||An editorial fix and additional comments about H&B limitations. Added comment about Home Inventory / Important Records non-support. Added comments about Jackcess and Sunriise2. Added the final comments about “extract to where?” Table-ized this revision history.|
|9/8/2016||Editorial fixes. Changed limitations re. currencies and currency exchange. Updated the deliverable archive and the description of its contents here.|
|9/12/2016||Minor editorial cleanup.|
|9/20/2016||Added the default CAT tables. Revise the description of the linking process as well as the description of the sample real account number lookup table. Minor editorial cleanup.|