locked
sunriise2: export to JSON -> MongoDB RRS feed

  • General discussion

  • Recently I've started some code to export the content of *.mny into JSON which I then was able to import into MongoDB for further processing. The data model is at level of: Account, Category, Transaction ...  so hopefully it will be a bit easier to deal with comparing to previous export dataset (which was at the db.table level -> CSV or *.mdb)

    Here is a sample output of the Sunset's Sample *.mny.

    Link to the zip file SunsetSampleJSON.zip

    The JSON format is not documented at all but hopefully the fieldName is obvious enough to get you going if you are interested in this kind of output. If something not clear, ask and I will try my best to answer.

    Best,

    Wednesday, August 9, 2017 9:28 PM

All replies

  • Overview of the SunsetSampleJSON directory

    • categories.json: array of Category
    • currencies.json: array of Currency
    • payees.json: array of Payee
    • securities.json: arrayof Security (stock ...)

    A bunch of directories, each represents an Account. For example: Woodgrove_Bank_Checking.d

    In each account directory, 

    • account.json: Account information
    • transactions.json: This account transactions

    Output of account.json need work.

    • account.json has a LONG list (array) of filteredTransactions and transactions which I will skip at the next rev.
    Wednesday, August 9, 2017 9:43 PM
  • And the current binary to run the export tool

    • Download from sunriise-export-0.0.1-SNAPSHOT-exec.jar
    • Make sure you have Java 1.8 (if not download from Oracle http://www.oracle.com/technetwork/java/javase/downloads/index.html)
    • The best way to try this is to put the above *.jar file and your *.mny file into a directory. NOTES: you should make a copy of your *.mny. And use that copy. Though I am opening the *.mny file in read-only mode, it is best NOT to operate on your actual *.mny file.
    • Start shell/command window, go to above directory 
    • Assume that the your *.mny file has a password '123@abc!' and you want to save the export JSON to directory named out.d. Here is a sample run

    java -jar sunriise-export-0.0.1-SNAPSHOT-exec.jar sunset-sample-pwd.mny 123@abc! out.d

    ....

    998 [main] INFO  com.hungle.sunriise.export.ExportToJSON.visitAccount(ExportToJSON.java:167) - > accountName=ETF Brokerage Account (Cash)
    1007 [main] INFO  com.hungle.sunriise.utils.StopWatch.logTiming(StopWatch.java:103) - TIMING - > endExport, outDir=out.d (took 0 seconds) ms=874
    1007 [main] INFO  com.hungle.sunriise.export.ExportToJSON.main(ExportToJSON.java:340) - < DONE

    Now check content of directory out.d/

    Wednesday, August 9, 2017 10:25 PM
  • I've made some changes to the JSON format and created a home on github

    https://github.com/hleofxquotes/mnyjson/wiki

    Similar to many "old timer" here, I have a lot of data in my *.mny.  I know that some day, I will have to move on to another personal finance app/tool. When that day comes, I hope to be able to export and and have my data imported into that new app/tools and preserved as much as details as possible. My hope is that by documenting what sunriise can export as JSON, I will encourage other app/tool to take a look and see if the exported JSON can (or cannot) map to their data model. If cannot, this is a good time to try to sort that out.

    I believe the current JSON format cover pretty well what I believe are the "core" objects:

    •   Account
    •   Transaction
    •   Payee
    •   Category
    •   and relation between them (Transaction belong to Account, Transaction has split and can be transfer ..., Categories, Payees, Investments (as related to Transactions)

    *.mny objects has some peculiarities that I hope be able to document on the above Wiki soon (such as Bill is represented an transaction with recurring information)

    If you have questions, input, please leave a message here or log an issue at https://github.com/hleofxquotes/mnyjson/issues

    Best,


    Monday, August 14, 2017 7:10 PM
  • A simple python example on how to parse accounts.json

    https://github.https://github.com/hleofxquotes/mnyjson/wiki/Simple-python-examples

    More examples are coming ...



    Wednesday, August 16, 2017 10:13 PM
  • Python example on how to iterate through list of transactions

    https://github.com/hleofxquotes/mnyjson/wiki/Simple-python-examples#transaction

    Wednesday, August 16, 2017 11:06 PM
  • I guess being able to export to some editable format, make changes, and write back to a *.mny file is not foreseeable, is it?
    Thursday, August 17, 2017 2:02 PM
    Moderator
  • Unfortunately, no.

    • For some simple, specific case. That might be doable. For example: update a comment/note on a transaction.
    • For general case, it is next to impossible since there are columns in the *.mny table (such as the transaction table) that I have no clue. For example: update a split transaction to add a split would be quite hard.


    Thursday, August 17, 2017 3:43 PM
  • Python example

    # Example on how to read the top-level accounts.json
    # Then traverse each account directory to get 
    #  * Number of transaction
    #  * Account Type
    #  * Current balance

    https://github.com/hleofxquotes/mnyjson/wiki/Simple-python-examples#traverse-account-directory

    Thursday, August 17, 2017 5:49 PM
  • Another Python example:

    Generate a report showing security that in use (has transaction(s)) vs. security not in use (has no transaction)

    https://github.com/hleofxquotes/mnyjson/wiki/Simple-python-examples#securities-report

    See section: Security Report

    Context:

    •  Show a process to export *.mny data into JSON format.
    • Use other external tool such as Python to parse the JSON files and generate report

    Monday, November 6, 2017 8:09 PM
  • I haven't been by here in quite a while. Looks like I've been missing some interesting developments.

    Building on the export from Money to .mdb enabled by your earlier Sunriise work, I have a working ability in Access to reconstruct most all of my financial data in Access query form. The extracted information includes:

    • Account List
    • Categories & Classifications
    • Countries
    • Currencies
    • Investments with key details
    • Payees List with key details
    • Transactions with foreign key links, and human readable resolution of these links, to related Accounts, Payees, Categories, Classifications, Investments, Split Parent/Children, and Transfer transactions
      • Includes flag, attachment file path, tax year assignment, Print/ATM/Debit and other text flags.
    • Scheduled Bills and Deposits including edited occurrence exceptions
    • Security share price history with key details and stock split history
    • Currency exchange rate history
    • Investment lot trace data

    A data dictionary for what I think I know about the Money schema is included. There are caveats and limitations to all of this. To see more and download the tools, go to http://www.littlegreengecko.com/MSMstuff/money_reverse_engineering.html.

    One thing I've never solved and would like to solve is how to decrypt the account numbers stored in PAY and ACCT. Any insight would be greatly welcomed.

    On one other subject, I've lost the cone on what/which of your various repositories of code in SourceForge, GoogleCode (archive) and GitHub is which. I did recently have occasion to mess with your Java viewer (from I can't recall exactly which version). I was trying to use it to edit the parent of a bunch of classification entries. Failed miserably with all kinds of errors. Ended up doing it by hand in Money for all affected transactions, Took hours. Is there a newer version of the MnyViewer somewhere?

    Saturday, November 11, 2017 6:48 AM
    Moderator
  • Hi Dick,

    I am currently keeping the latest build at https://github.com/hung-le/sunriise2-misc (in directory out/)

    If you have git you can clone it as

    git clone https://github.com/hung-le/sunriise2-misc

    Otherwise, download from the out/ directory (https://github.com/hung-le/sunriise2-misc/blob/master/out/sunriise-mdb-explorer-0.0.1-SNAPSHOT-exec.zip?raw=true

    Unzip to get file sunriise-mdb-explorer-0.0.1-SNAPSHOT-exec.jar

    Run it as java -jar sunriise-mdb-explorer-0.0.1-SNAPSHOT-exec.jar

    I just did a quick test to export to *.mdb and re-open the *.mdb file of the Sunset sample MNY file to make sure that things are still exporting to MDB OK.

    I will take a look at the 'account numbers stored in PAY and ACCT' question.

    Best,



    Saturday, November 11, 2017 4:07 PM
  • I see what you mean about the encrypted data for PAY and ACCT now. Just to make sure, were are talking about the following columns, correct

    • ACCT.mNum
    • PAY.mUserAcctAtPay

    I just realized I did not try to pull those values in my JSON extraction so I don't know how to decode them. Will try to play around with it.

    Saturday, November 11, 2017 4:27 PM
  • Thanks for replying!

    Yes, those are the account numbers columns I'd like to break back. However it's done, the encryption appears to be seeded differently in each table--I suspect this because the same account number in each yield a different cyphertext.

    Is there a way to run an export from a specified .MNY file to a specified .MDB file from the command line using your mdb-explorer tool? I have a tool chain that does an entire export from .MNY/import to .ACCDB/export to .XLSX without interaction...

    I'll re-visit the problem editing CAT.hCatParent with the mdb-explorer and report back.

    Saturday, November 11, 2017 10:22 PM
    Moderator
  • Still having issues using the viewer to change Cat.hcatParent. I'm trying to change it from one known nLevel=0, hct-1, parent to another. Buy I'm getting errors that log like this:

    2017-11-11 13:07:56,477 [AWT-EventQueue-0] ERROR com.hungle.sunriise.viewer.table.MnyTableModel - com.healthmarketscience.jackcess.ConstraintViolationException: New row [317, 1, 1, 310, null, Desktop III.75, was Confuser for R, false, -2207.9500, true, 0.0, -1, -1, false, Mon Sep 01 17:07:28 HST 2014, false, 0, {77A4A17A-1F8C-4EEA-B6B7-7101AFCF4406}, true, false, false, false, null, false, false] violates uniqueness constraint for index (Db=Money data file - copy pre split of Class Other - Copy.mny;Table=CAT;Index=HctLevelParentAls)
    com.healthmarketscience.jackcess.ConstraintViolationException: New row [317, 1, 1, 310, null, Desktop III.75, was Confuser for R, false, -2207.9500, true, 0.0, -1, -1, false, Mon Sep 01 17:07:28 HST 2014, false, 0, {77A4A17A-1F8C-4EEA-B6B7-7101AFCF4406}, true, false, false, false, null, false, false] violates uniqueness constraint for index (Db=Money data file - copy pre split of Class Other - Copy.mny;Table=CAT;Index=HctLevelParentAls)
    at com.healthmarketscience.jackcess.impl.IndexData.prepareAddEntry(IndexData.java:657)
    at com.healthmarketscience.jackcess.impl.IndexData.prepareAddRow(IndexData.java:626)
    at com.healthmarketscience.jackcess.impl.IndexData.prepareUpdateRow(IndexData.java:710)
    at com.healthmarketscience.jackcess.impl.TableImpl.updateRow(TableImpl.java:2458)
    at com.healthmarketscience.jackcess.impl.CursorImpl.setCurrentRowValue(CursorImpl.java:726)
    at com.healthmarketscience.jackcess.impl.CursorImpl.setCurrentRowValue(CursorImpl.java:717)
    at com.hungle.sunriise.viewer.table.MnyTableModel.setValueAt(MnyTableModel.java:230)
    at javax.swing.JTable.setValueAt(Unknown Source)
    at javax.swing.JTable.editingStopped(Unknown Source)

    ...

    It says this this is somehow violating a uniqueness constraint for Table=CAT, Index=HctLevelParentAls, but I'm struggling to see why this index ever works. It says it's composed of

            CAT.hct
            CAT.nLevel
            CAT.hcatParent
            CAT.szAls

    But I'm looking right at data in the viewer that already violates uniqueness of this combination before I do anything. I'm not sure what's going on here.

    Saturday, November 11, 2017 11:32 PM
    Moderator
  • To get back the export MDB functionality in command line, see file in README-export-to-mdb.txt

    https://github.com/hung-le/sunriise2-misc/tree/master/out


    Sunday, November 12, 2017 12:25 AM
  • Will take a look. Having said that, the 'editing mode' in the viewer is pretty dump. It just edit the row value without attempting do do any cascading changes (to any of the reference keys ....). In general, I have not really explore modifying the content of MNY file at all.


    Sunday, November 12, 2017 12:30 AM
  • Yes, I completely understand that just diving in and editing data in .MNY tables arbitrarily is a risky proposition. I've only ever used the viewer's ability to edit in "emergency" cases where I thought I knew what was going on enough to get away with it--with a safety net of backup files, et al. I thought this case of changing a Classification's CAT entry's parent seemed like such a case--since Money doesn't provide the same Move capability for classifications that they provide for categories.

    I got the command line export working again with your newer code. Wow is it fast compared to what I was using. Thanks!

    I will update the info on Sunriise on my MRE tool page accordingly. Soon.


    Sunday, November 12, 2017 2:54 AM
    Moderator
  • I've created a publicly accessible mongodb and populate it with the data from the Sunset sample *.mny file.

    Access info

    To connect using the mongo shell:
    mongo ds117316.mlab.com:17316/sunsetsample -u hello -p helloWorld

    To connect using a driver via the standard MongoDB URI
    mongodb://hello:helloWorld@ds117316.mlab.com:17316/sunsetsample

    It might be useful to open the Sunset sample file and compare with the documents in the mongodb to see how/which types of the data were extracted.

    Some helpful pointer




    Thursday, November 23, 2017 6:11 AM