Interested in extracting your data from .MNY file? / umpmfaq.info RRS feed

  • General discussion

  • If you've used Money for a long time and have frequented the support communities over the long haul, you may recall much discussion a number of years back about how we could recover our data from the proprietary Microsoft .MNY files after Microsoft abandoned us.

    Another Money user, Hung Le, developed a Java tool, Sunriise, that provided access to the raw Money data as stored in the .MNY file. But that effort didn't resolve the relational links and data conventions required to enable, say, identification of which transaction records were related to bills, which were from scheduled transactions and so on, or reading the transaction data in a format similar to Money register views: with Payee names and Category names rather than the foreign key numbers used in transaction records in the Money RDBMS format.

    At the time, I supported the effort by working to reverse engineer the MDB schema and semantics for storing Money user data. Before much progress was made, my day job and real world life intervened. My involvement in MDB reverse engineering and Money user-to-user support both pretty much ended.

    Now sans day job, I got back to this problem. I now have an Access database that can, with help from Sunriise and Jackcess, extract almost all of my data from my Money file. The only exception of any consequence at present is Account and Payee account numbers.

    If you are interested in how this is done, see http://littlegreengecko.com/MSMstuff/Money_reverse_engineering.html. If this proves useful to enough people, I'll consider adding a sticky post to this forum (I still have the power!) I'll let feedback guide me.

    On a separate subject, I hosted this on a different domain than the umpmfaq.info domain that I have and where I still host a FAQ for the long lost microsoft.public.money Usenet newsgroup. (I hear some of you thinking "Usenet newsgroup? What's that?") Many of the FAQ Q&A are still relevant to using Money. Much of the other content on the site, however, has been overcome by events. So have many of the URL links in the Q&As. Rather than continue to pay annual rent on the umpmfaq.info domain name, to host a site whose time has long since passed, I am tentatively planning to migrate some of the remaining good bits, including probably the long form, single document, HTML FAQ as it exists today, over to this other site and discontinue the umpmfaq.info domain at some point late this winter, before the next rent comes due again.

    Interestingly enough, the top hitter Q&A are still getting over 1,500 requests in the past 30 days. That's maybe 5x more than they were getting when the FAQ site went up almost a decade ago. But there's a sharp drop-off from the # 13 bestseller at 1,466 hits to the # 14 one at 102 hits. The hits are also very similar numbers (1,602, 1,591, 1,589, 1,588, 1,588, 1,588, 1,584, 1,583, ...) so I suspect search robots and links to the heavy hitter questions out there on the web somewhere are responsible.

    I'll have to go look at the detailed traffic statistics to understand this before I pull the plug. I don't want to pull it if it's still a useful source for Money users. But I don't want to pay for it just for the robots to search and I really don't want to put the effort into it that would be required to make it freshly relevant, mobile-friendly, and so on. Feedback from the community is welcome.

    Sorry for the long post. Lots to cover.

    Happy Discoverer's Day and Aloha,


    Monday, October 12, 2015 5:28 PM

All replies

  • Hi Dick,

    I am glad you are spearheading this effort. I will check this out and make contribution as appropriated.

    FYI, the link that works for me: http://littlegreengecko.com/MSMstuff/money_reverse_engineering.html

    Monday, October 12, 2015 6:34 PM
  • Glad to see you are still in touch. I will edit my post. I suspect Chrome was taking care of me when it made it work without the extension.
    Monday, October 12, 2015 7:11 PM
  • I've used some of your helpful information from both of you gents in my blog    http://microsoftmoneyoffline.wordpress.com   especially in the Reference pages.  I've also done my best to promote sites that replicate former online activities for the Money program, now that is offline.   If there is anything I can do Dick to help point to your new site or include as reference material, let me know - I remember how helpful your newsgroup was to so many of us Money users years ago.  I too will be checking out your new site!

    Thank you for hopping back on board, and yes Hung Le, glad to see you're still hanging around ;-)

    Dan (ameridan)

    • Edited by ameridan Tuesday, October 13, 2015 7:36 PM
    Tuesday, October 13, 2015 7:21 PM
  • Extract, and then where? Dinero? Moneydance? It would be cool if the extraction could be fed back into Money, but maybe cleaner or with quirks removed. Maybe the Money 2005 users with slow file syndrome but no bills removal tool could benefit mightily.

    Somebody running the old Italian version could maybe migrate that to a different Money version. I don't know if reading an Italian or even a Worldwide version would be very different than for reading a North American file. I suspect they have different passwords of some sort.

    Wednesday, October 14, 2015 6:44 PM
  • There is no great answer to the "extract to where" question. I guess my first best answer is to anywhere but in 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? 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.

    And, yes, I'm still dabbling with my own data management tool, I've called Dinero. Being able to get my data *with the keys* and without all the overlaid nonsense from the reports exported to .CSV, will make feeding data in way simpler, faster, and less error-prone.  I'm thinking of things like the Voids in place of transaction numbers when both are present. How really long, >250 character or so, Memos get truncated in the report exported to .CSV. There are also issues like how the Access .CSV handlers interpret column data type if the first several entries are blank vs. numeric, etc., that have consumed countless hours of headache in my existing import tools. When working from the exported reports, I have to go to a lot of mechanics to get the two separate transactions in a Transfer related, in the RDBMS sense, back together properly. Even harder if a currency exchange is involved. I could go on. The extracted data solves *all* of these issues and gets the account/payee/category details, except account numbers, as well. So, yeah, that was, for me, a big secondary objective and part of my personal answer to "extracted to where?".

    I have no clue what the Sunriise tool would do with back-level or non-US versions. But let's think this through. Sunriise export, as it exists today, is known to deal with .MNY files post when the .MNY file became MSISAM and not directly readable via JET (think this would be around M2K or so). So let's call them MSISAM .MNY files. So next question, that I have no answer to, is which of the international versions are MSISAM .MNY. Thinking the UK sunset files are MSISAM .MNY. (IIRC, an M98 .MNY file was pretty easy to open with Access.) I also have no idea if non-US files would be readily decrypted by Jackcess Encrypt, mostly because that code is a black hole to me so I don't have a clue whether passwords are involved, if so, how they got them, etc. I know Hung Le was working with the folks at Health Market Science on getting at the .MNY files from Jackcess in the first place. He may know a lot more about this than I do. And he may know more than he's willing/able to share.

    My semi-educated guess it that an MSISAM .MNY file would be readable and the transaction/account/category/payee/investment schema would be very similar and probably no big deal to adapt the extraction stuff to. The only issue I'm seeing for non-US files from my narrow porthole is the VAT-related column(s). My extraction is already currency aware as I have several non-default, non-USD, currency accounts already in my file, so I deal with it at that level. I'm not dumping any of the currency details or the exchange rate history because I didn't see much point, but it doesn't look like a large leap to do it. I haven't a clue what the Bills looked like pre- estimated bill series, whichever version that came in at. Suspect it would break my extraction queries. Can't fathom that it would be harder to extract Bills from whatever they look like than from the M08 Bills schema. (Getting Bills extracted in a sane way took an order of magnitude longer to figure out than any of the other ones I'm doing. And so much is empirical guess work and not repeatable in test cases, that it's the one I think most likely to break for other user's data.) My best suggestion for any M US user, not already on M08 Sunset, would be to get there first. At this point I'd recommend that regardless of the question of using Sunriise and my extraction tool.

    Short question. Probably an overly long answer.

    Wednesday, October 14, 2015 8:52 PM
  • I've made minor updates at http://littlegreengecko.com/MSMstuff/Money_reverse_engineering.html. There is also a newer version of the ZIP archive with several updated files. Changes are fairly esoteric.
    Tuesday, November 10, 2015 8:56 PM
  • Is there any way to import the data directly to Moneydance? That would be be very useful.
    Wednesday, November 11, 2015 1:07 PM
  • Wednesday, November 11, 2015 3:02 PM
  • Yes, I know the QIF route but it is very laborious and time consuming, and loads of relational data gets lost (e.g. all transfers between accounts in different currencies get lost, all "project" and "trip" tags get lost, all historic investment prices get lost.
    Wednesday, November 11, 2015 3:33 PM
  • No doubt. QIF has always been better than nothing. But not by much.

    Is someone knew Moneydance's schema, and how to store data in it, then the work I have done here, unraveling Money's schema, would make it easier to a) get your data into Moneydance, and b) verify that you've really done it. But I have no particular interest in Moneydance, so I haven't pursued that half of the problem. Now, if they were interested, what I've done, on top of Hung Le's and Health Market Science's work, demonstrates that it is possible to get the data (presently save only Account and Payee account numbers) out of a Money 08 Plus Sunset US file, subject to limitations and caveats cited on my page. Indeed, I've built a replacement schema and can now import all the data I care about, besides Bill series exceptions which I'm still working on, to it. Including transfers between different currency accounts, historic investment prices, and investment lots. Those were easy.

    (What are project and trip tags? Something from an H&B edition? I see tables PROJ and TRIP in Money schema. Mine are empty. Probably isn't a great leap to stitch them in unless they are obtuse like, say, BILLS, but they appear simple. But, quick look, I don't see how they get related to TRN, if that's how they are used. I never used an H&B edition, so I can't imagine how they might be related based on a use case I've never experienced. Surprisingly, my knowledge of how Money is used was very helpful to the process of figuring out what I've figured out about their schema. There are 1,361 columns in a M08 Deluxe Sunset Money US file. My guess it that maybe half of them are just used by Money to do the UI--messages, register view settings, report settings, custom reports, etc. I've only dealt with maybe 200 or so. I'm assuming, but haven't validated, that H&B schema are a subset of what is in the files I've played with. Was H&B even available in M08? Can't remember when they dropped it.)

    Wednesday, November 11, 2015 4:12 PM
  • Dick, they never dropped it.  There is even a Sunset edition of H&B which I use.  Columns that would be unique to H&B would be Invoice, Customer, Vendor, Products, Services, etc. and yes Project and Trip (mileage) as well.  Being that you can use the MNY file from H&B with Deluxe, I'm sure the 1,361 columns include the additional H&B data fields as well.

    Whether Moneydance is able to accommodate that data, my guess is no.  I've used Sunriise in the past and all of the H&B data is available.

    • Edited by ameridan Wednesday, November 11, 2015 5:36 PM
    Wednesday, November 11, 2015 5:33 PM
  • I see lots of H&B looking tables (PRODUCT, PROJ, TRN_INVOICE, TRIP) but don't see, in quick look, how they get related back to TRN. (Or how TRN_INVOICE fits in.) I'm not spotting vendor or customer, but it may just be coding/columns in Payee. To break it back, I'd have to have a file with data I could trace back into the UI. So I'd have to install a copy and not have it break my existing Money setup. I'll go find the sunset install. Is it subject to the same mnyob99.dll patch? Is there an H&B unique Sample data file?
    Wednesday, November 11, 2015 5:44 PM
  • Not the exact same patch in theory, but yes you'll need to do the dll patch specific to the H&B version.  Click on my profile and there is a link to my website (blog) that has links to everything.  By the way, I added a link to this Dinero discussion on the blog yesterday.  Another feature of H&B is Reimbursable Expenses checkbox, so that you can invoice for those directly.   There really is nothing like Sunset H&B out there without going to commercial accounting programs, and its FREE!

    Oh I dunno - you might want to check it out (or perhaps you are already using it).

    Regarding your last question, yes there is a sample data file that has business data in it.

    • Edited by ameridan Wednesday, November 11, 2015 6:49 PM
    Wednesday, November 11, 2015 5:53 PM
  • I extracted the sample.mny from Sunset biz and made a zip file.

    I put the zip file on  http://s000.tinyupload.com/?file_id=00913093975937881455

    Wednesday, November 11, 2015 6:13 PM
  • While you were posting, I dl'd the H&B Sunset and got the sample file out of it. Great minds think in same gutters. I dunno about TRN_INVOICE, but to the extent that some of this (TRIP and PROJ) is tagged onto account transactions, they may do it the same as they do the Flags and the URI attachments. They have an indirect way of relating those, but it's simple once you know the magic number they use in X_BAG.

    I'll try to take a few minutes to convert the H&B sample to .MDB and go skulking about in it.

    Wednesday, November 11, 2015 6:34 PM
  • I added a link to this Dinero discussion on the blog yesterday.

    In the spirit of brand management, you should probably make that a link to Money Reverse Engineering. The "Dinero" effort is a separate effort to do stuff with the data once I have it in hand.
    Wednesday, November 11, 2015 6:56 PM
  • Quick look results from an MDB dump of the H&B Sample.mny, without benefit of the H&B app UI: Customers appear to just be folded into PAY. There is some TRN_INVOICE data, but I haven't looked for how it relates to other stuff beyond the obvious hproduct link. There are rows in PRODUCT. PROJ and TRIP are empty in the sample so hard to tell how they are related to anything else.
    Wednesday, November 11, 2015 7:49 PM
  • Will change that Dick.  I use H&B for invoices, but I've never assigned work to Projects.  I didn't even know about the trips screens.  I just recorded them as expenses.  The sample file is probably pretty basic like most of us that use the H&B version.  You'll note that trips are not tied to any transactions or invoices, just for estimating taxes.

    • Edited by ameridan Wednesday, November 11, 2015 8:52 PM
    Wednesday, November 11, 2015 8:42 PM
  • What do you (or anybody else, FTM) know about interoperability of simultaneously installed Deluxe and H&B versions?

    Yeah, that totally makes it look like they don't use TRIPS for anything other than recording trips and, guessing, tax estimator and tax reporting. Since I've not endeavored to worry extracting the tax specific analysis or report information (e.g., the category to tax line assignment stuff) then I'd just extract this so the user could have it out of Money. Half an hour's work...

    So, re. H&B invoices, are they glued into your account transactions somehow or does this data just free-stand as well? (I only see one link to TRN, apparently something to do with reimbursable expenses. My guess is it lets an expense from a TRN be passed along to an invoice. And one to PROJ. To show a project name on an invoice?) Does an invoice know if it's been paid? Does your deposit of an invoice amount into an Account know it's from an Invoice? Or is H&B Invoice mostly just for printing sexy looking invoices?

    I can readily see how H&B stuff like TMI relates to TRN_INVOICE, PRODUCT to IVTY, and so on. I guess the bottom line is that it looks like there is nothing terribly complicated about extracting most of this H&B information in value-added--i.e., above and beyond Sunriise--ways.)

    Except for BILLS and the account numbers thing, I haven't found very many things I've gone looking for to be terribly obtuse or obfuscated in the Money schema. There does appear to be lots of non-normalized stuff. I suspect this was either for performance or to maintain compatibility with legacy code in the product or things like MoneyLink.

    Wednesday, November 11, 2015 9:30 PM
  • <blockquote>What do you (or anybody else, FTM) know about interoperability of simultaneously installed Deluxe and H&B versions?</blockquote>

    I don't think you can have both installed simultaneously.  But I have gone back and forth.  Installing H&B adds Accounts Rcvble and a bunch of Business Income and Expense categories that can easily be deleted if you go back to Deluxe with your regular Money data file (which you probably won't use).

    <blockquote>Does an invoice know if it's been paid? Does your deposit of an invoice amount into an Account know it's from an Invoice?</blockquote>

    Yes.  There is an account action "Receive payment for invoice".  And the special transaction category used is "Payment for Invoice".

    The invoice screens are part of Money.  It's only when you preview and print that the invoices appear "sexy-looking" with company logos and custom fonts if desired using the included separate program. 

    Using Sunriise I was surprised at how many investments and accounts that I had deleted over the years were in fact still in the database.  The only stuff I actually wrote back into the Money database with Sunriise are updated tax rates for the current year as an experiment (not worth all the effort though).

    • Edited by ameridan Thursday, November 12, 2015 2:32 AM
    Wednesday, November 11, 2015 9:51 PM
  • Hyper-V is your friend. Got it working. Salted up a file with data. Not much there. If anybody is interested in extraction support for these files, I don't sense, yet, a large hurdle. (But I haven't gone skulking about in the file I made either. I am kinda wondering where the put the vehicle I defined... An asset account?) Not sure receiving payment for an invoice is really coupled to the TRN in any way besides the UI doing the work for you. I.e., I'm not sure if I go into the account I received it to and delete the transaction the Invoice paid value will change or I'll be prompted that it's a bad idea.
    Thursday, November 12, 2015 1:23 AM
  • I must have been asleep this morning when I was looking at TRN_INVOICE. its very first column is an htrn foreign key to TRN. Every line in an invoice is added to both TRN (accounting $s) and TRN_INVOICE (invoice backup information). This is very similar to how they do TRN_INV (investments). The TRN item appears to just be a split in disguise.
    Thursday, November 12, 2015 1:52 AM
  • Dick, I was actually going to comment earlier that the Invoice was so similar to a split transaction in that the categories were defined within the invoice, hence the category Payment for Invoice.  And you're right, if I edit the invoice, the rcvd pymnt transaction kind of disappears.  All it really effects is the accts receivable and when working on a cash basis, reporting the transaction in Schedule C for the tax year that pymt was rcd.
    • Edited by ameridan Thursday, November 12, 2015 2:34 AM
    Thursday, November 12, 2015 2:31 AM
  • I sent a link to this thread to Infinite Kind (Moneydance). Hopefully they might be able to make use of this development to ease the transition from MSMoney to Moneydance.
    Friday, November 13, 2015 9:25 AM
  • I can see it has been a while since there was any activity here.  Still, I'd like to post so that the wonderful Mr Watson knows that his efforts are still bearing fruit.

    MS Money was a brilliant package and I'm still using it on a Windows 10 Pro machine (64-bit).  I dread the day that a Windows update makes it unusable.  I'm using the clever CSV2QIF utility to bring in my transactions from US and UK institutions.  Working well.

    Meanwhile, I was looking around for ways of extracting data so as to experiment with importing into Power BI and learning how to construct time intelligence DAX formulae.  It looks like the Money Reverse Engineering toolset will help do the job.

    Many thanks to all who have contributed.

    Monday, August 31, 2020 10:43 AM