MacOS Quicken - OFX Import - Match Using BANKTRNS

bash
bash Quicken Mac Subscription Member

I am transferring data from YNAB to Quicken. I wrote a script to transfer the older data. However, matching is not happening.

Before I go down the rabbit hole, can someone confirm if BANKACCTTO in the OFX file in each STMTTRN would work to match it? or is that not a thing?

Answers

  • Chris_QPW
    Chris_QPW Quicken Windows Subscription Member ✭✭✭✭

    I'm not a Mac user so I don't know if this is 100% true for Mac, but it should be. Quicken's implementation of QFX (which is supposed to be OFX with some more fields) is not using the current OFX standard version specifications. And that basically lines up with the financial institutions that have not done any updating. I have seen QFX files that are at the 1.01 OFX version.

    What this means to your question is, no Quicken doesn't use the BANKACCTTO to match accounts for things like transfers. It either "guesses" based on if there as a deposit and withdraw transactions that are for the same amount around the same time, or it depends on matching an existing transaction that was put from a scheduled reminder or manual entry.

    BTW if you think about it, the name in Quicken for a given account might not bear any resemblance to the one the financial institution is using so even if it was using BANKACCTTO it would have ask the user which account was meant by this at least the first time (or if either the Quicken or online account name ever changed).

    Note that as far as matching the "account to import into", Quicken does look at the financial institution and account information in the QFX file and records that as "linking" the account in Quicken to the online one (it asks on the first import what account to link it to). When another data file with the same information is imported it that same Quicken account.

    I will also note that Quicken will not import a straight OFX file, it has to have the extra QFX entries in it, like these: The FID and INTU.BID numbers are the first number in the line of the financial institution in this file:

    https://ofx-prod-filist.intuit.com/qm2400/data/fidir.txt

    Signature:
    This is my website: http://www.quicknperlwiz.com/
  • bash
    bash Quicken Mac Subscription Member

    This is great information—thank you! I appreciate the insights. While I was aware of some of this, I wasn’t sure which fields Quicken would accept and whether I could simply add them into an OFX file.

    Just to clarify, I’m not importing OFX files directly from my banks. Instead, I’m working with CSV files containing Mint-style data and manually mapping BANKIDs and ACCTTYPE values. Since this is my own script, and I’m importing historical data up to December 31, 2024, I created my own BANKIDs, ACCTTYPE mappings, and even generated my own FIDs. For transfers to credit cards or credit lines, I’ve been using CCACCTTO.

    My plan is to import all historical transactions, close those legacy accounts, and then sync data moving forward using “new” accounts that will pull transactions starting January 1, 2025.

    I actually considered using your software, but since it requires a Windows machine, I decided to take a DIY approach. Plus, I find it interesting to learn how everything works under the hood.

    You mentioned that “QFX files are at the 1.01 OFX version”—do you happen to know where I can find the schema documentation for this version? I’ve been referencing the OFX - Open Financial Exchange XML Schema Documentation and currently generate OFX 1.00 files, though I can easily adapt to newer versions if needed.

  • bash
    bash Quicken Mac Subscription Member

    I am going over your ImportQIF Setup Documentation - QuicknPerlWiz right now. You don't have the PERL code on GitHub or something do you?

  • Chris_QPW
    Chris_QPW Quicken Windows Subscription Member ✭✭✭✭

    The OFX standard is "backwards compatible", a fact that the financial institution have used to the point where they never update it from what I can see once they get it implemented. But even more to the point, at this point not only are then not updating to the latest version, at least some of the major ones are switching over to FDX (which is an "aggregator only" close format) and dropping OFX/QFX altogether.

    But of course, something like this very helpful in situations like you describe. Note that when Intuit owned Quicken, they activity tried to prevent any kind of importing from "non partners", and even with Quicken Inc, it is a very "thin line".

    Whereas in the EU they mandated the financial institutions to adopt one or both of standards (one being OFX) no such luck in the US or Canada, and as such they have fragmented into "aggregators" which is a fancy way to say "hackers with some agreements".

    Here is a link to the people that maintain the standard, note that they are the same ones for FDX.

    OFX Work Group - About

    For FDX (which isn't very useful for this use case, but you can see where they think the future is):

    https://financialdataexchange.org/

    But I will say one thing about the spec, it is big and complicated and for the most part not really needed to be fully understood or used.

    One of the best ways to do it if possible is just look at QFX file from a financial institution that support QFX exports. Doing it for banking and checking accounts is much easier than doing it for investment accounts.

    Signature:
    This is my website: http://www.quicknperlwiz.com/
  • bash
    bash Quicken Mac Subscription Member

    I have about 30 accounts, and all my data is stored in YNAB, which includes its own categories, memos, and notes. I’ve successfully converted this data into Mint-style CSV files and have another script that can convert the data into OFX files. Both sets of file are able to bring in the transactions successfully.

    However, I’m running into a problem with matching transfers. If I manually enter Transfer[ACCOUNTNAME] in the category field, Quicken detects it correctly and links the transactions, which works great. But that is one at a time.

    The issue arises when I try to bulk edit transfers. I use a unique tag to track transfers (e.g., from ACCOUNT_A to ACCOUNT_B), so I filter all related transactions to speed up the process. But when I apply the category change in bulk, Quicken creates duplicate entries on the other side instead of properly linking them.

    With over 25,000 transactions, manually fixing each transfer isn’t feasible. I need a way to automate this, whether through OFX, CSV, QFX, or QIF. Has anyone found a reliable solution for handling bulk transfer matching?

    I actually just checked several of my canadian bank accounts. None offer QFX export interesting. All do quicken. Some do Sage 50, MS Money, or even QBO. But not QFX. I will read the spec from what you linked about and try to resolve it. Unless you know of a solution to match using CSV or the OFX files, and I can just add the tag that allows it to match.

  • Chris_QPW
    Chris_QPW Quicken Windows Subscription Member ✭✭✭✭

    Transfer matching has always been a sore spot in Quicken. A Quicken Mac user will have to answer on automatic transfer matching while importing. Quicken Windows has an option for it, but it is poor, nothing that would really work for bulk importing. I don't use Quicken Mac, just follow along sometimes to their threads. I got the impression that it also has some kind of automatic transfer matching, but I don't know much about it.

    In Quicken Windows the category field is actual used for transfers where you would have [Other Account] as the category, and that would create a linked transfer to the other account. And that is the same format in a QIF file. For Quicken Windows you can import QIF files for transactions, but from what I understand you can only import a QIF file for creating accounts in Quicken Mac… Which I guess actually does apply in this case.

    For Quicken Windows it doesn't handle these properly and link them back up. But maybe Quicken Mac's will work right, I don't know.

    The more I think of it the QIF format is probably your best bet. QFX has no categories, Mint didn't have linked transfers.

    If all else fails, can do what I did for testing trying to recover a data file by using a QIF export/import when Quicken Windows will always mess up the transfers, and that is to go through the QIF file and just change transfers to a regular category. Accounting wise, this is still "double book" and is "correct".

    Here is the "longer explanation" of that trek into trying an export/import of Quicken Windows:

    ChangeTransfers changes transfers in a QIF file to categories. - QuicknPerlWiz

    When they did the rewrite of Quicken Mac at first, they didn't have linked transfers and people would just put in Transfer for that category (I think it is even a reserved token for Quicken Mac to this day).

    Signature:
    This is my website: http://www.quicknperlwiz.com/
  • Chris_QPW
    Chris_QPW Quicken Windows Subscription Member ✭✭✭✭

    P.S. As stated in on that webpage I didn't change the category to Transfer, I changed it to XfrACCOUNT_NAME, because that gives somewhat of a chance to maybe do a bulk edit in Quicken to fix it up, but that was very "iffy" for me in Quicken Windows. It worked for some, and not for others.

    Signature:
    This is my website: http://www.quicknperlwiz.com/