How to create a link / automatically link bank and credit card accounts?

jimrh's Avatar

jimrh

10 Mar, 2019 01:38 PM

Greetings!
I have purchased, installed, and am using Moneydance 2019.1 and I am moving my transaction data over from Quicken 2015 to this program.

So far there have been a few "bumps in the road", but it's not any worse than upgrading from one version of Quicken to another.

Here's what's up:

  • I have a checking account and three credit card accounts.
  • As per the published instructions, I exported all the account information as one or more QIF files and imported them into Moneydance.
  • I now have four "registers" in Moneydance, one for the checking account and one for each of the three credit-card accounts.  This is similar to the way Quicken was organized - four registers, one for each account.
    • As far as I can tell, none of the accounts are subsidiary to any of the others - that is, it appears that each of the four accounts exists as an individual, stand-alone, account.
  • Each account contains transactions up to a particular point in time.
  • Each of the three credit-card accounts contain particular transactions, (payments and/or credits), that should link back to the checking account.
    • There are no transactions that occur between the individual credit-card accounts.  Any transaction in any of the credit-card accounts is either a purchase/credit to the card itself, or a payment/credit back to the checking account.
  • Because of the way the four individual banks process and post transactions, there can be an offset, (plus or minus), of a few days between the posting dates on "each side" of the transaction.

Under "normal" circumstances within Quicken, (if anything about Quicken can be called normal!), it would notice that there's a transaction on this date here that (appears to) correspond to a similar transaction in this account over here on this particular (nearby) date - and ask, "are these transactions linked somehow or other?"  Answering "yes" then creates a correspondence between the two transactions - a debit from one and a credit to the other.  (i.e. Such-and-so transaction is a payment from my checking account to one of the three credit-card accounts.)

At present, this correspondence does not exist between any of the transactions in any of the four accounts.  I am sure that in order for this to happen, I have to - somehow - create this initial correspondence between these four accounts.

This leaves two questions:

  • How do I set up this initial correspondence between the four accounts?  (In detail, please)
  • How do I set things up so that as additional transactions are downloaded, these transactions can, (hopefully), automagically understand that there is supposed to be a correspondence between them?

I have searched and read several prior postings that lead me to believe that this can be done, however I am not exactly sure exactly how to make this happen.

Any help would be gratefully appreciated as it will save me weeks of work trying to match these transactions with each other.

Thanks!

Jim  "JR"

  1. 1 Posted by TonyRI on 10 Mar, 2019 02:22 PM

    TonyRI's Avatar

    Hi Jim,

    I'll offer a couple of things that may help to answer your questions. Others may provide more detail. If you need more help, don't hesitate (I know that you won't , lol ;)) to post back.

    To enter (e.g.) a transfer from your Checking to Credit Account, you would Open your checking account register, select 'new transaction' and then in the category field you would select the credit card account that you'd like to pay. The amount will show on both sides, the checking debit, and the credit card payment.

    http://help.infinitekind.com/kb/register-and-transactions/transfers...

    When you download transactions from the various accounts you will perform a Confirmation process, where you can select the appropriate categories. If you have already manually entered a downloaded transaction, you will begin to get merge options:

    http://help.infinitekind.com/kb/online-banking-and-bill-pay/confirm...

    Not sure it this is exactly what you are looking for, but it may be a good starting point.

    Take care,

    Tony

  2. 2 Posted by jimrh on 10 Mar, 2019 02:32 PM

    jimrh's Avatar

    What happens if the two corresponding transactions already exist?  (i.e. A payment debit in the checking account on date "X", and a payment credit in the credit-card account on day "X" or on day "Y" nearby?)

    I also notice that all my account balances, including credit cards, appear to be positive balances.  I had to add a huge positive offset to my checking account to get the "ending balance" to correspond with reality.  I'll probably have to enter a huge negative offset to my credit card accounts to get them to reflect reality too.

    Jim  "JR"

  3. 3 Posted by jimrh on 10 Mar, 2019 02:40 PM

    jimrh's Avatar

    Tony,

    OK, I think I see what you mean.

    On date "X" my checking account has a payment to my credit card and on the corresponding date in my credit card account, there's a transaction that has my bank account as the category.

    In Quicken, if you selected a transaction that was part of a linked transaction to another account, it offered you the ability to see the transaction "on the other side" - that it believed this was linked to.  This was important because, sometimes, Quicken would bring over both transactions, create a new transaction to correspond with one of the old ones, and leave one as a duplicate.

  4. 4 Posted by jimrh on 10 Mar, 2019 02:55 PM

    jimrh's Avatar

    Ahhh!

    I see said the blind man as he picked up his hammer and saw. . . . .

    Silly me, I hadn't entered/downloaded any of the corresponding credit card account transactions!

    Having done so, (and I suspect there will have to be some ex post facto cleanup as well), I did find some duplicate transactions. I'll do some looking on how to merge them - though what I suspect will be that I'll have to decide which one to keep and delete the other.

  5. 5 Posted by TonyRI on 10 Mar, 2019 02:55 PM

    TonyRI's Avatar

    On date "X" my checking account has a payment to my credit card and on the corresponding date in my credit card account, there's a transaction that has my bank account as the category.

    Yes, that is correct

    You can see "other side" transactions, by right-clicking on the transaction and selecting "Show other side".

  6. 6 Posted by TonyRI on 10 Mar, 2019 02:59 PM

    TonyRI's Avatar

    though what I suspect will be that I'll have to decide which one to keep and delete the other.

    I think that you are right. I believe that the merge option will only become available for duplicate downloaded transactions.

  7. 7 Posted by jimrh on 10 Mar, 2019 03:33 PM

    jimrh's Avatar

    Tony,

    You can see "other side" transactions, by right-clicking on the transaction and selecting "Show other side".

    I'm slowly-but-surely working my way through this.  What I believe is happening is that I have transactions that appear to be linked, but really aren't.  (i.e. There's no "show other side" available when I right-click.)

    I suspect - as I continue to learn this muck! - that if these transactions are truly not linked, I will have to manually create, (or edit?), a transaction on one side and categorize it to appear on the other side, and then clean up whatever duplicates may exist.

    Ohhh does this remind me of other data migrations in the past!  If I knew more about this, I'd probably write a Perl or Python script to clean this mess up for me.

    Hey!  That's an idea!!  I'd make a killing selling software that would help migrate data from Quicken to anything else, let alone just Moneydance.

    I wrote a blog post many years ago on why people were abandoning Ubuntu in droves and migrating to distributions like Mint - the B.S. was getting deeper than the hip-boots were tall.  I think we're seeing the same thing here.

    Once I get this done, I'm going to have to dust off my blog and write an article about Moneydance - there are so very many people over in the various Quicken fora CRYING for a reasonable replacement product.  Writing something that people can search-and-find to find a replacement. . . I'd get ten-million hits on the first day alone!

    Thanks for all your help.

    Jim  "JR"

  8. 8 Posted by TonyRI on 10 Mar, 2019 05:01 PM

    TonyRI's Avatar

    Hi Jim,

    Happy to help. You would DEFINITELY make a killing with your Quicken data-migration-made-easy software!

    Happy blogging and good luck with your mission! :

    Best wishes,

    T

  9. 9 Posted by dwg on 10 Mar, 2019 08:52 PM

    dwg's Avatar

    You cannot have mismatched dates between two sides of a transactions.

    Moneydance is based on the rules of double entry accounting and having mismatched dates is saying the money goes missing for those x days. An auditor would have a fit. Any software based on Accounting rules should not allow it.

    The only real way of duplicating bank processing times is to have something like a Transits account and pass the funds through this on the appropriate days, this may have to be via manual entries though.

  10. 10 Posted by jimrh on 11 Mar, 2019 03:46 AM

    jimrh's Avatar

    Dwg,

    Please pardon my ignorance, but where do you live?

    In the United States, the fact that bank posting rules can create a time-lapse of up to four days is something that accountants and auditors know can, and does, happen.  Ergo, accountants know that books can, and do, "balance" without necessarily being perfectly symmetrical.  All that has to happen is for there to be a corresponding transaction within a "reasonable" time window from the initiating transaction.

    Most, if not all, of the accounting software that is designed to work in the United States allows a delta of up to four days between transactions, (plus or minus), because of this simple reality.

    I even remember seeing something in Moneydance's "options" that allowed you to specify the delta for matching transactions - which defaults to four days.

    Since the dates on corresponding bank statements can differ, and since, legally, the bank's statement is The Word Of God, the software should allow a reasonable "slippage" in the dates between transactions that are transfers between unrelated banks.  Otherwise, the records you have in your software won't reflect what actually happened in the real world.

    Jim  "JR"

  11. 11 Posted by jimrh on 11 Mar, 2019 04:41 AM

    jimrh's Avatar

    Dwg,

    You're right about one thing, Moneydance absolutely stomps its feet and flat-out refuses to allow a correspondence between differing dates.

    OK.  I'll bite.  You guys are the fancy double-entry accountants and I'm just the silly noob trying to get my paperwork and the registers to match.  Silly me, I forgot.  Reality is for wimps!  ;)

    So, given date "X" on my checking account statement and "Y" on the credit card statement, which do I use?  (i.e. Which date do real accountants use?  Is there a particular "rule-of-thumb" I should follow?)  Given that I have to use one of the two, (and not both), how do I preserve the correspondence between the differing dates?

    Jim  "JR"

  12. 12 Posted by dwg on 11 Mar, 2019 05:37 AM

    dwg's Avatar

    I'm in Australia.

    I learnt bookeeping and did first year Accountancy (I'm an economist) long before computers, so I'm old school.

    I do not actually use the bank's date at all. The date I use is the date I write the cheque out and post it, or hand it over, or the date I initiate the transaction if done online. This is the date I have committed to the payment, the cheque is a Bill of Exchange. The payment may be debited today, it may not be debited for some days or even weeks if someone is slow to present the cheque to their bank. The bank date just represents the posting date by them to your account - the final step in the process.

  13. 13 Posted by jimrh on 11 Mar, 2019 11:19 AM

    jimrh's Avatar

    Interesting!

    The way things work both in the US and when I am in Russia, I seldom write a check.  Actually make that "over the last three or so years, I've personally written maybe two or three checks".  On occasion my bank's bill-payer service writes checks in my behalf and mails them out.  This is good because it's been a long time since my hands have been twenty and writing, (with pen-and-paper), has become much more difficult.  Even typing is becoming a challenge, but I have to do something.

    On my checking account statement, transactions - especially point-of-sale transactions - often have two dates, the date the transaction actually took place and the date it "settled" at the bank, if different.

    My credit card accounts not only do that, but they also provide a running memo of the actual purchase amount in the native currency, (if it's not USD), the conversion rate used on that date, and the resulting amount in dollars.

    As an aside, many banks in the U.S. are making a big deal about not charging a "conversion fee" for transactions in foreign currencies anymore, like it's a huge benefit.  Lots of advertising, bands playing, balloons, confetti, pretty girls, the works!  What they've actually done is hide the fee by using a totally sucky conversion rate.

    I have recent transactions that came through where they convert at some lower number, like 62 or even 57 rubles per dollar instead of using the retail exchange rate of 65 - 67 rubles per dollar that you can get at the currency exchanges.  It's not obvious unless you're paying attention, but they make even more money while they're working to convince you what a huge favor they're doing for you.

    In any event, the amounts and their posting dates don't always line up. I guess I'll just have to flip a coin and pick one, (:grin:), and use the MEMO field to track the differences in date.

    Thanks!

    Jim "JR"

  14. 14 Posted by dwg on 11 Mar, 2019 08:56 PM

    dwg's Avatar

    As well as having worked in the Computer Industry, I have also had the pleasure (or otherwise :) ) of working for a bank so banking systems present no mystery. Foreign exchange transactions can provide a double benefit for the bank, first off they can charge you a fee (it could be a flat fee or a sliding fee) then of course they make money on the currency exchange itself. Don't get me wrong some of the fees were justified, preparing and processing overseas cheques was not cheap, especially then.

    At least I worked for a bank in times when they were a far more respectable organisation and in turn were more respected.

  15. 15 Posted by jimrh on 12 Mar, 2019 01:07 PM

    jimrh's Avatar

    As well as having worked in the Computer Industry, I have also had the pleasure (or otherwise :) ) of working for a bank so banking systems present no mystery.

    Ahhh!  Memories. . . .

    I've done a bit of everything in my day too: Flipping burgers, delivering Pennysavers, roofing, fixing copiers, all the way to military avionics and software QA engineering in a variety of fields, including banking.

    I remember a story that circulated back in the '70's when I worked for Brookhaven National Laboratories that went like this:

    Brookhaven labs bought their mainframe systems from Control Data Corporation - one of IBM's bigger competitors.

    One day, after a bunch of CDC's engineers went out and got drunk, they went back to the lab and decided to have a "who can top this?" contest - and started stacking and wiring core memory modules together.  They kept stacking and wiring, stacking and wiring, until they reached an absolutely unbelievable one megabyte of core memory.  (At that time the best, most expensive, dripping blood bleeding edge systems had 64k of memory and cost more than a battleship.)

    They tested it and it worked, but even they had to admit that they'd gone a bit over the top - and they had absolutely no idea what to do with it.  So, they gave it to Brookhaven Labs - absolutely free of charge - with the express provision that if Brookhaven Labs found a useful application for it, they had to 'fess up and tell CDC what and how they did it.  'natch, Brookhaven labs found a use for it, told CDC all about it, and suggested that they market it as an add-on product.

    CDC thought about it and decided not to market it.  "No way - it's just too big.  It'll never sell.  No one could ever possibly need to have that much memory."

    Here I am, sitting in front of my multi-gigahertz, multi-core laptop with 16 gigabytes of solid state RAM and two terabytes of solid state hard drive, laughing at the memory.

    Jim  "JR"

  16. 16 Posted by dwg on 12 Mar, 2019 08:20 PM

    dwg's Avatar

    You mean like the person who said that a PC would never need more than 1 MB of memory.

  17. 17 Posted by jimrh on 15 Mar, 2019 09:37 AM

    jimrh's Avatar

    Dwg,

    You mean like the person who said that a PC would never need more than 1 MB of memory.

    Laughing!

    The other side of this is that software writers have been cramming more and more (ahem!) "features" into their software until we've gotten to the point where you need the equivalent of a supercomputer to run it.

    I have a mug that I bought - years ago - that looks like line-printer paper on the outside. All around the mug - line by line - are a number of pithy statements printed in the then standard line-printer typeface.

    One of them was "Inside every large program is a small program struggling to get out."

    Right around that time, I was working on increasing manufacturing efficiency for some of the tests an avionics company I worked for were doing. One was for the fuel-quantity display for the A6 Intruder. The test took a minimum of 15 minutes per indicator, if everything was OK, because it was constantly stopping.

    I modified the test to allow it to automatically continue if there were no problems, which reduced the test time to about a minute and a half per indicator. The only kicker was I needed to manually reset the test fixture after each test - AND! - the 5.25" floppy disk was full. There was only one sector, 512 bytes, left on the disk.

    To reset the test fixture, I needed to send three or four bytes to it which triggered a restart.

    How to fit it into 512 bytes when everything is written in Q-Basic? No way. So, I decided to use 8086 assembly language instead and write a teeny-tiny .com file that would push the four bytes I needed.

    I went to the development engineers and asked them if they had an 8086 assembler I could "borrow", (copy, snitch, blatantly steal a copy of, etc.). I explained my problem - I needed to write a tiny patch file.

    Their answer was golden! "What do you care? The system has plenty of memory!"

    :facepalm: (shakes head in wonder. . .)

    Ultimately I got my way and wrote the patch file. 29 bytes!

    Jim "JR"

  18. 18 Posted by dwg on 15 Mar, 2019 09:51 AM

    dwg's Avatar

    Many a time I think today's programmers should be given an under powered machine with limited memory and disk.

  19. 19 Posted by jimrh on 15 Mar, 2019 10:13 AM

    jimrh's Avatar

    (back on topic)

    I'm working with my data and - wonder of wonders! - I'm nearly complete. All the data is in, and right now I am verifying balances and totals to make sure everything lines up and my ending balances match the banks ending balances.

    One of the issues I've run into - and I am slowly but surely weeding them out - is that the absence of any play or articulation in the transaction dates of transfer transactions causes each transfer to appear twice. One transaction for the banks transaction, pushing a transaction over to the associated credit-card account and a second for the credit-card's transaction, (on a different date), pushing a corresponding transaction over to the bank.

    The result is that I have to go through every account, look for duplicated transactions, and delete one - one at a time. Though it's not "difficult", it's time consuming and annoying. One would think that in 2019, transaction based software would know better than that.

    I'm thinking there are three possible reasons:

    • Because this is the first time I'm importing data into Moneydance, the necessary correspondence is not set up, and Moneydance cannot tell that these are potentially duplicate and should be merged. Future imports will try to do this automagically.

    • There is something I'm doing wrong and/or there is a technique I need to know that will make this simpler in the future.

    • Moneydance is a piece of GAGH! in this regard, and it just stinks being me.

    And the answer is. . . .!

    I'm thinking that the answer will ultimately be the first one, because at initial import the bank's credit card payment ended up going to a "dummy" category called "credit card payment" and the payments on the credit-card side went to another "dummy" category. Once I changed the categories to point to the corresponding accounts - I had zillions of duplicates.

    I suspect the reason for this is:

    • The bank's account in Moneydance didn't know about the corresponding credit-card accounts.

    • The credit card accounts themselves didn't know about the credit-card accounts.

      • The first time I imported data it asked me which of my three credit-card accounts, (that had been pre-defined by the initial QIF file import) account number xxxxxxx was associated with.
      • This puzzles me. Doesn't the QIF file include both the account's number and name?

    Thanks!
    Jim "JR"

  20. 20 Posted by jimrh on 15 Mar, 2019 10:17 AM

    jimrh's Avatar

    Many a time I think today's programmers should be given an under powered machine with limited memory and disk.

    Preach it brother! Preach it!

    Give 'em an Android powered desktop with some micro-power Atom processor, and then tell 'em to write Windows 10. I want to be a fly on the wall when they do it!

    Or maybe a Raspberry Pi-3? (grinning!)

  21. 21 Posted by dwg on 15 Mar, 2019 10:24 AM

    dwg's Avatar

    I was going to be a little kinder - an Atom powered Netbook.

  22. 22 Posted by jimrh on 15 Mar, 2019 12:48 PM

    jimrh's Avatar

    Well, (supposedly) Win-10 will actually run on a Pi-3

    I gave up being kind to deliberate idiocy years ago - after the absence of a known registry update caused me to loose mucho gigs of data, not once but several times, until I happened to stumble on an article describing my exact problem.

    It could have been published. It could have been part of a Windows Update. They could have just done it right the first time. . . .

    Jim "JR"

    p.s. what about my multiple entries question above?

  23. 23 Posted by dwg on 15 Mar, 2019 08:42 PM

    dwg's Avatar

    QIF files can include an account name but do not have to, they do not include any account numbers. OFX files include linking information so should know the account in Moneydance the entries are destined for.

    OFX files include transaction ID's which prevent duplicates within an account but not between accounts i.e. you download a transaction for account A that is a transfer to Account B so it posts to both. You then download transactions for Account B the transfer is obviously in that download as well and it also posts - the unique IDs are account centric only.

    QIF files do not have transaction IDs in the first place.

    Moneydance has a confirmation process for downloaded and imported information. Part of this process provides the ability to Merge transactions and this is how you cope with duplicates - including transactions you may have manually entered.

    Details on the confirmation process are in:

    http://help.infinitekind.com/kb/online-banking-and-bill-pay/confirm-downloaded-data

Reply to this discussion

Internal reply

Formatting help / Preview (switch to plain text) No formatting (switch to Markdown)

Attaching KB article:

»

Attached Files

You can attach files up to 10MB

If you don't have an account yet, we need to confirm you're human and not a machine trying to post spam.

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