none
sunriise: adding Python binding RRS feed

  • General discussion

  • I've been experimenting with adding codes to support running Python script with Sunriise. Got a sample Python script working (will be limited to Python 2.7 syntax)

    import sys
    import os
    import csv
    
    if os.name == 'java':
        from org.python.core import codecs
        codecs.setDefaultEncoding('utf-8')
    
        print 'Jython', sys.version
    else:
        print 'Python', sys.version
        
    writer = csv.writer(sys.stdout, quoting=csv.QUOTE_ALL)
    
    for account in accounts:
        print '#', account.getName()
        transactions = account.getTransactions()
        for transaction in transactions:
            row = [transaction.getDate(),
                   transaction.getAmount(),
                   transaction.getPayee().getName(),
                   transaction.getCategory().getFullName()
                   ]
            writer.writerow([str(s) for s in row])
        
    

    Using the sample Sunset *.mny file, output will look something like this

    ...
    "Wed Mar 21 00:00:00 PDT 2007","-24.9500","Adventure Works","Bills:Telephone"
    "Thu Mar 29 00:00:00 PDT 2007","-446.9000","Woodgrove Bank Credit Card","Transfer from Woodgrove Bank Credit Card"
    "Fri Mar 30 00:00:00 PDT 2007","-53.3400","City Power & Light","Bills:Cable/Satellite Television"
    "Fri Mar 30 00:00:00 PDT 2007","-1928.1100","Mortgage Co","None"
    "Sun Apr 01 00:00:00 PDT 2007","-98.5000","Coho Vineyard & Winery","EXPENSE:Dining Out"
    "Sun Apr 01 00:00:00 PDT 2007","-45.0000","The Gas Company","Bills:Natural Gas/Oil"
    "Mon Apr 02 00:00:00 PDT 2007","1175.3100","AUTOMATIC DEPOSIT -- PAYROLL","None"
    "Wed Apr 04 00:00:00 PDT 2007","-87.2200","The Phone Company","Bills:Cellular"
    "Thu Apr 05 00:00:00 PDT 2007","-250.0000","","Transfer to Woodgrove Bank Savings"
    "Thu Apr 05 00:00:00 PDT 2007","-49.9500","The Gym","Bills:Health Club"
    "Fri Apr 06 00:00:00 PDT 2007","-179.8800","Contoso Ltd.","EXPENSE:Groceries"
    "Sat Apr 07 00:00:00 PDT 2007","-62.8800","The Water Company","Bills:Water & Sewer"
    "Fri Apr 13 00:00:00 PDT 2007","-135.0000","Electric Company","Bills:Electricity"
    ...

    Main point is that soon, using Sunriise, it will be possible to access object (for read-only) such as Account, Transaction in Python

    Saturday, June 30, 2018 2:32 PM

All replies

  • TL/DR: I'm wondering how big a leap it is from what you've already done to a 64-bit friendly COM interface that could functionally replace the MoneyLink USMNY DLL?

    I remain interested in any/all efforts along this line. Every three months or so I return to investigating the problem that MoneyLink is the only thing keeping me from installing 64-bit Office. So I'm back to exploring ways to piece together alternative solutions.

    In order to understand my dependence on MoneyLink, I whipped up an Access/VBA tool yesterday to walk all of my Excel files, scattered hither and yon, looking for MoneyLink usage, and, when found, to extract MoneyLink query definition where-used info to table rows in the Access db. I found 61 different places I've used 176 different MoneyLink queries over the years. There was only one case, save testing the tool itself, where I'd ever saved the results of anything besides Transactions or InvestmentTransactions queries. Only a dozen or so of these queries have ongoing relevance. (As opposed to one-time convenience use queries that are generally fairly easy to replace some alternate way like reports->CSV.)

    MoneyLink consists of a .XLA Excel add-in file with a bunch of VBA that, in turn, references a .DLL USMNY.DLL and associated type library. That, in turn, appears to depend, somehow, on MnyCoreFiles. (There are some strings that suggest the object is somehow able to assure it is only used from Excel or (how?) Access. I may try to use just the USMNY object from Access directly, with code borrowed/adapted from the original. I'm also wondering if I can learn enough VSE/VB.Net to test using the .DLL independent of the rest of the MoneyLink deliverable.)

    The vast bulk of their 5,700 lines of VBA (total including white space and comments--see * below) is about UI for defining queries, storing/retrieving/validating those query definitions in encoded range names and custom document properties, monitoring--forever--the Excel Worksheet ranges where they return query results, playing with their toolbar, and so forth. (I borrowed/adapted 390 or so lines of theirs for my MoneyLink census tool to extract the query definition information. 75 or so of that is just constants definitions. Probably another 50 or 100 is stuff I just kept since it was easier to do things their way than strip it down to basics necessary for my objective.) Precious little of their VBA translates the data as returned from their DLL into the nice human-readable form it splats out in Excel. And most of that has to do with putting on the headers, number and column formatting, the comment in the upper left corner, and so on.

    Their DLL provides 12 object interfaces: MoneyLink (for "about" and similar), MnyFile (define the file to be queried, its password, etc), and a set of record management/record contents objects: MnyAcctList, MnyAcct; MnyTranList, MnyTran; MnySecList, MnySec; MnyPriceList, MnyPrice; and MnyLotList, MnyLot. It also has enumerations for stuff like account type and so on--some of which were less obvious in use/meaning.

    I don't even know enough Python to be dangerous and know even less Java. Once upon a time, I had your Sunriise code at least viewable/usable in Netbeans. I was just playing around with the latest .jar I got from your GitHub for Sunriise2-misc and either it has no source or I'm too ignorant to figure out how to get at it. (Any suggestions?!? Hung Le? Over? Please?)

    I'm also pondering developing a purpose-built tool that takes just the MDB extracted data and queries it into more or less the MoneyLink retrieval formats directly. That wouldn't be as fast as MoneyLink--since it would be dependent on the sunriise Java complete extract to MDB cycle--but it would be a whole lot faster than going through the re-schema effort my other tool does importing from the extracted MDB.

    Here are the candidate ways I've identified to replace 32-bit MoneyLink:

    • replace MoneyLink with something simplified--only necessary queries--based on Sunriise/Jackcess work or similar
    • Reverse-engineer MoneyLink (USMNY.DLL) calls to hidden Money APIs; build new tool from there.
    • Use Money reports, .CSV export/import
    • Replace MoneyLink with look-alike Transaction and InvestmentTransaction queries from exported Money or from DineroII re-schema
    • is there a way to register a 64-bit interface to USMNY.DLL that would be usable from existing MoneyLink XLA code
    • is it possible (can I acquire the technology to) wrap the minimum necessary interfaces of USMNY.dll in something we can get at from 64-bit Excel VBA

    Does anybody else use MoneyLink still? Is anybody else interested in problems like this?

    Sorry for the ramble...

    * what's in the MoneyLink VBA, FWIW:

    Query1
    Type Name Lines
    Document EmptySheet 5
    Document MoneyLinkWorkbook 43
    ClassModule ApplicationEventHandler 84
    ClassModule PasswordRecord 17
    ClassModule QueryAccounts 206
    ClassModule QueryBalance 122
    ClassModule QueryInvestmentLots 232
    ClassModule QueryInvestmentPositions 262
    ClassModule QueryInvestmentPrices 169
    ClassModule QueryInvestmentTransactions 372
    ClassModule QueryTransactions 268
    StdModule DebugUtilities 103
    StdModule Globals 60
    StdModule MiscUitilities 97
    StdModule MoneyFileBinding 295
    StdModule MoneyLinkCommands 253
    StdModule MoneyLinkToolbar 368
    StdModule MoneyQueryBinding 784
    StdModule MoneyQueryUtilities 518
    StdModule SelectQueryWizard 181
    StdModule Strings 215
    MSForm EnterPassport 63
    MSForm EnterPassword 63
    MSForm SelectAccount 167
    MSForm SelectInvestment 109
    MSForm SelectQueryDestination 128
    MSForm SelectQuerySource 215
    MSForm SelectQueryType 256


    Tuesday, October 16, 2018 8:16 PM
    Moderator
  • Re: source code location.

    I've been trying (and switching back and forth) between various public repo. Current most update code is at bitbucket

    https://bitbucket.org/sunriise/sunriise/src/master/

    To clone via Git

    git clone https://bitbucket.org/sunriise/sunriise



    Wednesday, October 17, 2018 6:25 PM
  • I'm also pondering developing a purpose-built tool that takes just the MDB extracted data and queries it into more or less the MoneyLink retrieval formats directly. That wouldn't be as fast as MoneyLink--since it would be dependent on the sunriise Java complete extract to MDB cycle--but it would be a whole lot faster than going through the re-schema effort my other tool does importing from the extracted MDB.

    Along above idea. Currently sunriise can export the *.mny file into JSON format. The motivation here is to have an export format  that is richer (more structure) than CSV

    You can see the example output here: https://github.com/hleofxquotes/mnyjson/wiki

    COM is, unfortunately, out of my domain.

    Wednesday, October 17, 2018 7:01 PM
  • To see the current list of Object that sunriise can "export", you can get

    https://github.com/hleofxquotes/mnyjson/blob/master/apidocs/apidocs.zip

    Unzip to get apidocs, then use browser to open file apidocs/index.html

    In top-left panel, click on com.hungle.sunriise.mnyobject

    In bottom-left panel, you will see a list of interfaces (under section Interfaces).

    The most useful interfaces are: 

    Account

    Transaction

    Payee

    Securites

    look for the getter .. for example Account.getAccountType() will return the account's type

    Sunriise can do open and parse the tables into above objects and present

      list of accounts

      each account has list of transactions

      The export tool can export above objects into JSON that roughly represent the above object.

    Wednesday, October 17, 2018 7:32 PM
  • In theory, we can also construct a web service that perhaps VB (and or .NET, COM codes) can access.

    I think we both are striving to get the data out of the *.mny file into a format that can be useful and I think we are close BUT

    The "gap" is the middle-ware part where

      * I am much more comfortable in non-Windows format (JSON, Java, Python)

      * Where COM, DDL are more in your comfort area.

    Wednesday, October 17, 2018 7:39 PM
  • Sorry it's taken me so long to respond. I've been in binge coding mode for a couple of days.

    I think I've pretty much milked MoneyLink for all it's worth. Conclusions:

    1) From Access/Excel 32-bit VBA, you can do pretty much everything from the USMNY DLL directly that you can do via the MoneyLink.XLA Excel add-in. I didn't test the dataset query scoping parameters like from/to dates and single account/security retrievals. This would be a simple test at this point. I didn't test its password/WindowsID or password/recent file caching because I didn't care or have testcases available. Anybody who has a password on a Money file--especially at this point--is solving some problem the wrong way.

    2) Even reduced to the most basic 10 or 15 lines of code to fetch query data, the USMNY DLL will not play outside Access/Excel. (I tried it from Word VBA.) If you really want to get the data MoneyLink can provide without the Excel Add-in, it's about 10 or 15 lines of code to get it in the simplest possible way--add more code to do whatever you want with it.



    3) The retrieval methods exposed by the DLL do not appear to offer any additional capability over that exposed via the MoneyLink.XLA Excel add-in. The two limitations I've always had with MoneyLink, inability to retrieve the top transaction in a split (for its Memo), and inability to retrieve Class1/Class2 data, run clear through the MoneyLink tool. Sad then. Still sad now.

    4) The MoneyLink DLL is dependent on the MnyCore files. I suspect that MoneyLink uses a private interface with Money and Money-side code does 100% of the heavy lifting. This is not surprising since MoneyLink started out as a Microsoft development and was somehow passed on by Microsoft to Ultrasoft.

    5) It's probably a complete dead-end in my quest to get MoneyLink like functionality in a post- 32-bit Excel world.

    If anybody is interested, let me know and I'll post the Access with all my test code on my MSMstuff website and pass along the URL. (There's the "simplest" case demo, and a set of over-fancy tools to test the interface, dump column and type information based on what comes out of the queries, use that same information to create tables, and, finally to populate tables from retrieved data. These reflect the stages I went through building it and that I kept each previous stage workable, even if re-crafted, as I built the next stage. Or I can just pass along an exported .bas file with the "simplest" query code in it.

    Now, back to the original three post-MS Money problems.

    1) extracting my data from Money for its own sake. Done. Jackcess/sunriise export to MDB (or other forms) does this for me. This capability is probably survivable past even Windows.
    2) replacing Money as my PFM tool of choice. Still no solution in sight. Sooner or later MS will break the 32-bit Windows/mshtml/msisam interfaces the Money app depends on. It will be a dark day. VMs seem the only solution here, now.
    3) MoneyLink functional replacement usable with non- 32-bit Excel/Access data sinks.

    On #3, I agree we are "close" to such a capability. You've got a JSON -centric toolset on the back end; I've played around a very little bit with a JSON to VBA tool. My use of this tool was limited to getting one interface to one JSON data source on the web working for my purposes. So I can't say as I really know what all it does under the covers or how it does it. I had to just look at the code to even remember what it does/what I did with it. Basically, I use MSXML2.XMLHTTP to fetch a blob of JSON .responsetext from a web URL I construct with parameters, and then call the JSON converter code I found on the web with the .responsetext; it returns a Scripting.Dictionary.

    I don't know what it means to say "we can also construct a web service that perhaps VB (and or .NET, COM codes) can access". Can a "web service" be something that, say, MSXML2 can query locally on a given machine? If so, it's probably not a big leap to a "front end" in the Access/Excel (MS Office VBA) environment that can construct an MS Money data centric request ("table", specific filters for date range, account, payee, security, category...) for a JSON-centric sunriise/Jackcess back end, accept back a JSON-ified blob from that back end, and then break that blob back and make its contents available in Excel--even in a VERY MoneyLink -like way.

    (Or, better, MoneyLink fixed for its weaknesses and extended to support more queries than just the six supported in MoneyLink. MoneyLink can return Acct, Lot, Price, Sec (the "Positions" retrieval in MoneyLink Excel), Tran, and TotalBalance. Why not Payee? Category? Class1/2? FI? Bill? Note? "Attachment"? Address? Why not fix Tran for the limitations mentioned above? Etc.)

    With the MDB export, I've created workable queries for all that and more, so I understood the schema well enough to get that far. (Not sure I've ever tried to create/validate either balance([account][,asofdate]) or value([security][,account][,asofdate])queries against the MDB export. I'll have to go look in my MoneyReverseEngineering db to see if I've ever gone down those alleyways.)

    Thinking about it just now, IIRC, you have (somewhere) provided JSON-ified output sample files from Sample.mny. I'll go look for those and try running them through the converter code to see what I get back. It may be a couple of days. My MoneyLink hacking binge put me behind on other (probably more important but less amusing) things.

    For a number of reasons, we should probably take this discussion to some other venue. Do you have my email address?

    Tuesday, October 23, 2018 12:23 AM
    Moderator
  • I just looked (with File Explorer and Notepad, not software tools) at your JSON samples from Sample.mny.

    One immediate question: is there some structural reason you dump Account and Transactions on a per-account basis rather than Accounts (all of them) and Transactions (all of them including an Account element)?

    One immediate comment: any functional replacement for MoneyLink would have to resolve the foreign keys in its outputs as well as do things like weed out the fake bills transactions from real account register transactions. I.e, Transactions spit out with just the foreign key hPay, or hSec, or hCat(s), or hTran(s) of what MoneyLink calls the "farside accounts" (Transfer source/destination; includes the cash account side of investment transactions) would be a pain for mortal users to deal with. I'm not sure how best to do that on-the-fly and be scale-able, I just know it's sooner or later a requirement. The raw table data is just way too database-y for the vast majority of potential end users.

    Tuesday, October 23, 2018 1:32 AM
    Moderator
  • you dump Account and Transactions on a per-account basis

    I created a simple UI to test the extract code and the UI code influence somewhat the dump out format: just feel natural on the UI side on things to show list of account and the transaction associated with it. There was no true thought-out.

    The current codes does make an attempt to "weed out the fake bills transactions" and sort out the "foreign key" so that there is a more object-oriented linkage of account, transaction, security, payee, categories ...

    Be happy to take this offline in email

    Email: hleofxquotes@gmail.com

    Tuesday, October 23, 2018 2:49 AM
  • Hi both,

    I'm very interested in what you bring here.
    Where I come from: I have a 26 years old money file… some knowledge in IT (this is supposed to be my job but getting old and rusty). I've discovered the sunriise stuff long ago. I'm stuck today with a new laptop coming with Excel 64 bits, and a crashing MoneyLink.

    now 2 things:

    1) @Dick: why do you see "MoneyLink functional replacement usable with non- 32-bit Excel/Access data" sinking? stupid question:why fixing the error message on "Declare Function IsDBCSLeadByte Lib "kernel32" " wouldn't be sufficient? can't the DLL afford 64 bits?

    2) on my laptop, export of my 50 Gb money file to .MDB takes 10 seconds or so. What about having a Python add-on (xlwings) replacing Moneylink features?

    Christophe

    Tuesday, October 15, 2019 2:21 PM
  • Hi Christophe,

    I will defer the DDL question to Dick.

    > replacing Moneylink features

    I see a couple of options

    Tuesday, October 15, 2019 4:53 PM
  • thank you for your quick reply!

    I observed that you Indeed provide a REST API with sunriise-spring.0.0.2, which I successfully tested.

    It misses documentation as far as I could see, and the "verbs" you're providing here (/mny/accounts /mny/account/#) are not documented either.

    is there a way/place to get a (simple) doc about all the REST API you've kindly implemented ?

    Tuesday, October 15, 2019 6:59 PM
    • Unzip sunriise-spring-0.0.2-SNAPSHOT.zip to get directory sunriise-spring-0.0.2-SNAPSHOT. Go into that directory.
    • Put a copy of a *.mny here. 
    • OR to specify the location of the *.mny and/or password
    • java -jar sunriise-spring-0.0.2-SNAPSHOT-exec.jar  --file=sample.mny --password=qwerty1234

    Currently the available resources are (they are more as a demo of capability, than actual useful resouces). Also, response is in JSON but for Excel it probably will make more sense to return as CSV

    • List of accounts - http://localhost:8080/api/msmoney/accounts
    • Once you have the list of ids, to query an account detail id==2 - http://localhost:8080/api/msmoney/account/2
    • Transactions for a given account id==2 - http://localhost:8080/api/msmoney/account/2/transactions
    • Then detail for a given transaction id==660 -  http://localhost:8080/api/msmoney/account/2/transaction/660


    Tuesday, October 15, 2019 7:42 PM
  • I did another update 

    Thursday, October 17, 2019 11:30 PM