Merge Transactions & Overwrite Existing Memo

retail's Avatar

retail

28 Aug, 2021 04:11 PM

Hello, I've written an Excel macro that automatically processes Amazon order data and matches credit card charges to Amazon order ID.

The card charges appear in my MD register first as a result of MD syncing with the bank. I then run my Excel macro and import the resulting data (these steps MUST be in that order). Now I have a the transaction in the MD register (from the bank), and also in a separate file from Excel that includes the Order ID. I now have to merge the data.

Option 1 - I can generate a QIF file and import the QIF file. Each transaction comes in a a duplicate to the existing bank transaction (without MD trying to merge data), and I then have to go through the register and delete the original bank downloaded transaction, keeping the new version from Excel that has the Order ID.

Option 2 - I can use the CSV Importer extension and import the data as an Online Transaction. MD will then merge the new data to the existing transaction in the register. That works, but in the process, MD discards the Memo field from the new data (that contains the Order ID), which is the new information I want.

How can I import a list of transactions that contain additional information, and force MD to not just Merge the transactions, but keep the memo field from the incoming data and add that to the existing data in the register?

  1. 1 Posted by Stuart Beesley ... on 28 Aug, 2021 04:31 PM

    Stuart Beesley (Mr Toolbox)'s Avatar

    How about creating an ofx instead of a qif with the id so that the merge works?

    Or… as you can write macros, just knock up a Python script?

  2. 2 Posted by retail on 28 Aug, 2021 10:27 PM

    retail's Avatar

    Would an OFX file work any differently that the CSV Importer using the "Online Transaction" setting? The latter seems to function the same way with MD matching transactions and letting me merge them, it just discards the memo info from the new transaction.

    I've done some Python but I have not even scraped the surface of the MoneyDance API and I am, to be frank, a bit intimidated. I've looked at some of the extension code available (including yours), but know it will take many hours to parse through and figure it out. I'm hoping to get an import to work as an easier option.

  3. 3 Posted by Stuart Beesley ... on 30 Aug, 2021 04:55 AM

    Stuart Beesley (Mr Toolbox)'s Avatar

    The Python API looks daunting but it isn’t really. For example acct.getAccountName() for account name or txn.getDescription() for a txn’s description. The harder bit (not much) is probably a) parsing your file, b) finding the right txns etc. If you are interested, I can guide you.

    However, if you can leverage import then that’s probably easier for now.

    My idea about ofx was that if you supply a unique FITID number, then I believe that MD match will use that to match/merge a txn.

  4. 4 Posted by Stuart Beesley ... on 30 Aug, 2021 05:53 AM

    Stuart Beesley (Mr Toolbox)'s Avatar

    You might have to play with it, and also MD auto merge options, but seems to work and yes it matches on the which you could maintain yourself...

    You just need to repeat the section per txn..

    Example attached. So fairly easy to format the file....

  5. 5 Posted by retail on 30 Aug, 2021 01:10 PM

    retail's Avatar

    Thanks - going to be traveling for the next several days - will try this out when I get back.

  6. System closed this discussion on 29 Nov, 2021 01:20 PM.

  7. retail re-opened this discussion on 23 Jan, 2022 01:49 AM

  8. 6 Posted by retail on 23 Jan, 2022 01:49 AM

    retail's Avatar

    @Stuart

    I've decided to bite the bullet. I'm writing a script to match up the order id back to the payment transaction in Moneydance. Probably taking more time than figuring out the ofx, but I figure once I get the hang of it, maybe it can be of use for other things.

    My question. Does every transaction in Moneydance have a unique identifier? The list of payments with order id was originally exported from Moneydance to begin with. If each transaction has a unique identifier, then I could export that too and it would make matching the processed list to the original transaction simple. My current method is to iterate through both lists by date, looping to find a date/value pair that matches.

    Thanks.

  9. 7 Posted by Stuart Beesley ... on 23 Jan, 2022 05:26 PM

    Stuart Beesley (Mr Toolbox)'s Avatar

    Yes. It’s called a/the UUID. There is an API method on all objects. Something like obj.getUUID()

    

  10. System closed this discussion on 24 Apr, 2022 05:30 PM.

Comments are currently closed for this discussion. You can start a new one.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac