Latest revision as of 20:27, 7 August 2020

Online Banking in Gnucash - OFXDirectConnect

GnuCash can import financial data from several types of files. But you can also connect directly from within your account registers to your financial institutions to download transaction data directly to your registers.

• In Germany where most banks offer FinTS, formerly called HBCI, you can even initiate bank transactions from within GnuCash.
• In the US and a few other countries some banks offer direct transfer via one of two OFX protocols, OFX DirectConnect and OFX WebConnect.

GnuCash is able to connect with OFX DirectConnect if the bank makes its URL public.

OFX WebConnect uses a browser-based authentication scheme that is proprietary to Intuit, the makers of Quicken, and GnuCash isn't able to connect that way.

At present, OFXDirectConnect can be used to download transaction data from credit card and bank accounts denominated in national currencies. GnuCash does not support transfers in non-currency commodities.

AqBanking

GnuCash uses the AqBanking library to handle connections to financial institutions. AQBanking is included in the Linux Flatpak, macOS, and Microsoft Windows application bundles. For other Linux, BSD, or MacPorts it may or may not be automatically installed along with GnuCash; consult your distribution's package manager.

Important

AqBanking between 5.99.0 and 6.1.4 doesn't work with OFX DirectConnect. Please inform the AqBanking package maintainer of your distribution, if they ship that versions.

You can use Flatpak until they get it fixed.

The Online Banking Set-up Wizard

1. From the GnuCash main window, choose the menu item: Tools>Online Banking Setup...
2. Click the Next button in the Initial Online Banking Setup window that appears.
3. In the following window, click the "Start AqBanking Wizard" button which appears in the center of the right-hand panel.

You'll be presented with the new-user dialog. The first screen is informational, just click "Next".

Since we're setting up an OFX Direct Connect user, select that from the radio-group and click "Next".

On the third page, select "Run" to proceed to the second part of the assisant.

Defining a User in AqBanking

The first OFX screen is for the bank details. Press the "Select" button.

And start typing your bank's name. AQBanking will search the OFXHome database. Most banks that support OFX direct-connect are included in the database. Once your bank's name appears in the list you can select it and click "OK".

The next page collects a name for the user; for AQBanking to use. You can put whatever you like here. The next field is your user id at the bank, and the third is "Client UID". That's a user-specific "Two Factor Authentication" number that ensures that only one program installation can connect to your bank. Not all banks use it, but if your's does then you'll need to insert one. It's a UUID. There are several generators on the web like this one.

Just like the note says. The actual values are software and header versions, the pull-down list just sets those based on (unfortunately older) versions of various Intuit products. You may need to set the versions by hand.

Clicking Special Settings brings up...

This screen, but it's not widely needed so just click "OK" on the 4th page.

Using AqBanking to set up accounts

Once a User is defined, you could click on the Accounts tab and define the account(s) associated with the previously define User(s). But it is usually easier to let AqBanking retrieve an account list for each user. If successful, you don't have to worry about the Accounts tab at all.

In the OFX tab of the User Configuration, Click the "Supports Account List Downloads" check box, and Click the "Get Accounts" button

AqBanking should ask you for your password/PIN Enter the password and click OK

On the first connection, AqBanking will ask if you want to accept the SSL Certificate the server is reporting.

You must accept it (Once or Permanently) or the connection attempt will abort.

For the very security minded, you could try to verify the certificate independently, but I don't know how to do that.

I just accept the certificates permanently -- I'm only downloading data, not initiating transactions.

If the connection was successful, you should see something like:

(If it only displays the last line, "Finished. You may close this window", you most likely have an incorrect setting. Try changing the HTTP Version to 1.1 [from 1.0], or make sure your FID and ORG settings are correct, the URL is correct, etc. Additionally, if it does connect but says "service not enabled" in red, make sure you signed up for the Quicken service (and not necessarily Microsoft Money). For example, you have to tell (California and non-California) Bank of America, by calling 1-800-792-0808, that you need Quicken access and there might be a $9.95 monthly fee, which may be waived if your accounts satisfy certain conditions, such as monthly direct deposit.)

At this point, you should have one or more automatically generated accounts in the AqBanking files on your machine. The account(s) information you downloaded won't be visible in the AqBanking setup wizard until the next time you open the wizard (the Accounts tab does not refresh automatically), but the data is on your machine and available to GnuCash's HBCI setup.

Click Close on the "Requesting account list" communication progress window

Click OK in the AqBanking Configuration window, and you return to the GnuCash HBCI Setup window.

Click Next to go to the "Match HBCI accounts with GnuCash accounts" window. If Next is disabled see the next section Manual AQBanking Account Creation.

Click on an account name on the left (the account defined in the AqBanking setup wizard configuration), and select the GnuCash account that should be associated with it

Click the Forward button

In the next window, click the Apply button

You're now ready to use OFXDirectConnect from your GnuCash register.

Manual AQBanking Account Creation

It sometimes happens that downloading accounts from your bank doesn't populate the AQBanking accounts list. When this happens the Next button on the Online Account Setup Wizard will stay disabled after you quit the AQBanking Setup Wizard. The first thing to try is to create a dummy account and retry the account retrieval:

1. Click Start AqBanking Wizard
2. Select the Accounts tab and click Create Account
3. Ensure that OFX is the selected account type and click OK.
4. Enter a made-up number in the Account Number and Bank Code fields. Select a user from the drop down at the bottom.
5. Click OK.
6. Switch to the Users tab, select the user you created, and click the Edit User button.
7. Select the Bank Settings tab. Make a note of the FID. Now click the Retrieve Account List button.
8. Make a note of the Account numbers in the log window just in case.
9. Quit the AQBanking Setup Wizard and click Next on the Online Banking Setup Assistant. If the downloaded accounts are there proceed to match them with their GnuCash equivalents. If not, click the Back button and return to the AQBanking Setup Wizard. The Accounts tab will show your dummy account and presumably no others.
10. Run the Create Account wizard as before, once for each account. Enter the account number noted from the log window into the Account Number box and the FID noted from the Bank Setting tab of the User Setup dialog into the Bank Code entry. Select the appropriate user from the drop down at the bottom. You may want to enter an Account Name, Owner Name, and Bank name to make each account easier to identify in the AqBanking Setup Wizard Account list; they won't appear anywhere else.
11. Once you've completed entering all of your accounts close the AqBanking Setup Wizard and click Next on the Online Banking Setup Assistant to associate your online accounts with GnuCash accounts.

Using Gnucash to download transactions directly to an account register

After you have successfully run the AqBanking setup wizard

1. Open the register for the account that is to connect to the financial institution
2. Choose the menu item: Actions>Online Actions>Get Transactions...
3. Enter your password in the pop-up window and Click OK
4. GnuCash will connect to your account and download transactions
5. Any new transactions will appear in GnuCash generic import matcher
   1. Click the A or R boxes as appropriate (Add new or Reconcile)
   2. Select a split account if the importer shows the line in yellow
   3. Click OK, and Gnucash adds or reconciles transactions in your register. You're done.

Where to find connection info

See OFX Direct Connect Bank Settings

Source of 4000+ OFX connections: OFX Blog

Distribution-specific Information

Debian

Since Debian Lenny, the debian package of gnucash includes online banking support. A backport for the current stable release Squeeze is available in squeeze-backports: http://packages.debian.org/squeeze-backports/gnucash

"Wrong Account Type" Error

This error may appear on non-Checking accounts depending on your bank's OFX server implementation. Savings, Credit Lines, etc. may not download correctly.

How to fix this problem:

• aqbanking-2.2.6 or greater
• libofx-0.8.2 or libofx-0.8.3
• the following patch for libofx (from Christian Lupien):

--- libofx-0.8.2-old/inc/libofx.h 2006-11-26 12:54:59.000000000 -0500
+++ libofx-0.8.2/inc/libofx.h 2006-11-26 13:00:20.000000000 -0500
@@ -705,7 +705,12 @@
OFX_BANK_ACCOUNT,
OFX_INVEST_ACCOUNT,
OFX_CREDITCARD_ACCOUNT,
- OFX_INVALID_ACCOUNT
+ OFX_INVALID_ACCOUNT,
+ OFX_CHECKING_ACCOUNT,
+ OFX_SAVINGS_ACCOUNT,
+ OFX_MONEYMRKT_ACCOUNT,
+ OFX_CREDITLINE_ACCOUNT,
+ OFX_CMA_ACCOUNT
} AccountType;

/**
--- libofx-0.8.2-old/lib/ofx_request_statement.cpp 2006-11-26 12:54:48.000000000 -0500
+++ libofx-0.8.2/lib/ofx_request_statement.cpp 2006-11-26 13:07:49.000000000 -0500
@@ -61,8 +61,16 @@
OfxAggregate bankacctfromTag("BANKACCTFROM");
bankacctfromTag.Add( "BANKID", m_account.bankid );
bankacctfromTag.Add( "ACCTID", m_account.accountid );
- bankacctfromTag.Add( "ACCTTYPE", "CHECKING" );
- // FIXME "CHECKING" should not be hard-coded
+ if ( m_account.type == OFX_CHECKING_ACCOUNT || m_account.type == OFX_BANK_ACCOUNT )
+ bankacctfromTag.Add( "ACCTTYPE", "CHECKING" );
+ else if ( m_account.type == OFX_SAVINGS_ACCOUNT )
+ bankacctfromTag.Add( "ACCTTYPE", "SAVINGS" );
+ else if ( m_account.type == OFX_MONEYMRKT_ACCOUNT )
+ bankacctfromTag.Add( "ACCTTYPE", "MONEYMRKT" );
+ else if ( m_account.type == OFX_CREDITLINE_ACCOUNT )
+ bankacctfromTag.Add( "ACCTTYPE", "CREDITLINE" );
+ else if ( m_account.type == OFX_CMA_ACCOUNT )
+ bankacctfromTag.Add( "ACCTTYPE", "CMA" );

OfxAggregate inctranTag("INCTRAN");
inctranTag.Add( "DTSTART", time_t_to_ofxdate( m_date_from ) );

• Recompile and install libofx
• make clean and then make, make install aqbanking
• Remove the accounts that were downloaded (under the "Accounts" tab in the AqHBCI Wizard)
• Restart GnuCash
• Start the process over again, instead of creating a new User in the AqHBCI Wizard "Edit" the one you already created

You should now be able to download trasnactions and balance information for Savings and Credit Line accounts

NOTE: once a new release of libofx is out these notes should be changed

"No user assigned to this account. Please check your configuration" Error

This error has at least occurred for me when trying to add an account manually when the account download feature did not work. It occurs when you go to the actions menu and select online actions (at least for Get Transactions and Get Balance).

I am using GnuCash 2.4.10 and Windows 7 64bit, and the error may be specific to this setup. Even after assigning a user to the account with the AqBanking wizard, I still get this. Here is the solution.

Open your main user folder in Windows. Navigate to C:\Users\YOUR_USER_NAME\aqbanking\settings\accounts now find the corresponding account that is giving you this error. These configuration files will open with a simple text editing program such as "Notepad." To open them with notepad, right click and select "open with" and then choose notepad. Somewhere in each file it will say accountNumber="xxxxxxxx". You will need to open the files until you find the file with the correct account number (the one that is giving you the "no user assigned" error).

Once you find the correct account, you will need to look for a line that says selecteduser="xx" (xx will be the number you assigned to the account when you created it) Also, "user" may be capitalized (selectedUser). All you need to do is delete the "selected" part. So when you are finished, you should have user="xx" . Thats it! Go back into GnuCash and try to download some transactions again.

For some reason, if you are able to download your available accounts when you set up your User, the AqBanking wizard creates an account configuration file where this "selected" part is already removed. When you assign the account manually, it doesn't remove it for some reason, and this causes GnuCash to think there is no user assigned. Now you know the solution.

Enabling the OFX Log

A log of the OFX traffic between GnuCash and your financial institution can be created. This can be of use when debugging your OFXDirectConnect configuration.

The file is named ofx.log and is located in your computer's /tmp directory.

Set the AQOFX_LOG_COMM environment variable to 1 to enable the log.

Warning

It is not a good idea to leave this variable set except when you are debugging your configuration, as the userid and password used to connect to your financial institution are contained within the log file.

AQBanking >= 6.1.4

AQOFX_LOG_COMM=1 creates the ofx log file in the current directory, however the name of the file is “1”. Any other value will use the known path "/tmp/ofx.log" regardless of the value given... [1]

Windows

Ensure that there is a C:\tmp directory in existence. Setting environment variables is accomplished by right-clicking on your desktop's "My Computer" icon and selecting "Properties". From there select "Advanced" and then "Environment Variables". Then create a new AQOFX_LOG_COMM User environment variable with a value of 1. It may be necessary to restart your system for this change to take effect. These instructions are for Windows 2000, but other systems are similar.

There is more about debugging in Aqbanking#Debugging.

Known Problems

OFX Downloads Fails - OFX log shows a "TLS Handshake Error"

I found out that my bank only supports the current and prior two years of quicken. When I changed the settings to emulate Quicken 2013, it worked. From https://bugzilla.gnome.org/show_bug.cgi?id=635802#c8

OFX Downloads Fail on Windows - OFX log shows "application or version not supported."

If your bank (National City Bank does this) indicates that the application or version is not supported when using GnuCash on Windows, a quick fix is to modify the libofx-3.dll (in Program Files\GnuCash\bin) with a hex editor (XVI32 works.) Search for the string "1200" which is just after the string APPVER. Modify it to "1800". Found at http://jheslop.com/2008/09/19/online-banking-setup-for-gnucash-under-windows-xp/

Chase "username or password are incorrect"

The current change results from Chase implementing Multi Factor Authentication for DirectConnect sessions by insisting that any Quicken-like software be able to supply a <CLIENTUID> tag as part of the login attempt. Martin supplied the capability in aqbanking by the end of 2008, but Intuit wasn’t providing any public help about how they were implementing it. The FAQ above provides enough of that information to get Gnucash reconnected to Chase accounts.

The key features are that aqbanking has to use “103” as the Header Version for its ofx connections, and it has to send a ClientUID.

The Header Version is on the Application Settings tab available while editing a User definition in an AqBanking Setup session accessed from Gnucash’s Tools>Online Banking Setup… menu.

The Client UID entry box is in the User Settings tab in the same Edit User dialog in banking setup. It has been a long time since I set up a new bank account for aqbanking, but reading some of aqbanking’s git log messages, aqbanking may offer the option of generating a ClientUID while you’re defining the user in the first place. For established accounts, it’s probably easier to find any old UUID generator and paste the results into that box in the Edit User dialog.

Because Intuit specifically says that Quicken sends a 32 character ASCII representation of a hexadecimal number, I’m almost certain that you have to delete the customary hyphens that show up in most uuidgen output. I also made my ClientUID lower case for any of the letters, based on someone else’s observations that their bank was requiring lower case. I have no idea if lower case is required, but it worked for me.

What happens with the connection is that the first time Chase sees an ofx header version 103 connection with a ClientUID that hasn’t been associated with your account, it will let you download transactions, but it fires off the ‘action required’ email to the address associated with your account, telling you to visit the Secure Message Area in your account page on the web. For me that outside email appeared approximately 3 seconds after I had connected. In that secure message, there’s a link that jumps to a verification web page (and Chase has pasted in your one-time authentication PIN) where all you have to do is click Next. There was some kind of successful completion page displayed.

Since completing the authentication process, I have been able to download transactions from my formerly blocked account from both 2.4.15 and 2.6.9 gnucash versions. They both use the same aqbanking user data, so chase just thinks I’ve logged in from the same app multiple times.

If I’m reading Chase’s tea leaves correctly, after February 15, you won’t get any grace period — you’ll have to authenticate before you can access any transaction data. It looks like the authentication PINs will expire in 7 days, now and in the future. If you go beyond 7 days (or maybe if you launch several attempts to log in without authenticating) it looks like Chase’s system will keep generating new PINs for each attempted login. Their mail message mentions you have to be sure to use the most recent PIN if you have received several secure messages regarding authentication.

The FAQ mentions that DirectConnect servers have to be at version 103 in order to implement MFA via ClientUID. In the Quicken realm all versions that haven’t been locked out of DirectConnect for failure to pay Intuit’s upgrade tax already use header version 103. Servers using version 103 are not required to use ClientUID, but 102 and earlier server versions are unable to use UIDs.

If you have already logged into a Chase account with Quicken and authenticated your ID, you might have to call Chase and have them clear your authentication. Intuit suggests that banks allow at least 2 valid ClientUID’s per account. But the banks can do what they want. Intuit also suggests that implementation of ClientUIDs be invisible to the user (#ChaseFail). Quicken stores the ClientUID in the data file, and at least in Quicken 2013 provided no way to see the number. The ClientUID was also redacted from the Quicken ofx logs, at least when I looked. Because the ClientUID is stored in the data file, you don’t have to update your authentication when you upgrade Quicken. The good news there is that GnuCash users might be able to use their authenticated ClientUID essentially forever (at least until Quicken’s potential new owner changes something else).