Revision as of 19:18, 25 January 2019

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 Europe, for banks using HBCI, you can even initiate bank transactions from within GnuCash. Those of us in the U.S., living in a Quicken and MSMoney dominated environment, generally must use OFX based data for online banking. See if your institution is supported at ofxhome.

At present, OFXDirectConnect can be used to download transaction data from credit card and bank accounts. Investment transactions should still be imported from downloaded OFX files (for example, ofx.py) via GnuCash's File>Import>Import OFX/QFX... menu option.

You can look at the OFX Direct Connect site for additional support: [1] .

AqBanking

GnuCash now uses AqBanking as a means to handle connections to financial institutions. GnuCash must be configured using --enable-hbci and --enable-ofx in order for OFXDirectConnect to be available. Note: --enable-hbci is really two things at once. It means --enable-aqbanking and --enable-hbci. Although there isn't an --enable-aqbanking per se. But you need AqBanking for DirectConnect, which is why you need to --enable-hbci.

AqBanking has its own setup wizard for the purpose of setting up account identification and user login ID for your online connections. This page is intended to document how to use this wizard to enable you to download data directly from your bank or credit card company into the appropriate GnuCash register.

You must have Libofx 0.8.2 or higher and AqBanking 2.0.0 or higher to user OFXDirectConnect in Gnucash

The AqBanking Set-up Wizard

1. From the GnuCash main window, choose the menu item: Tools>Online Banking Setup...
2. Click the Forward button in the Initial HBCI Setup window that appears.
3. In the following window, click the "Start AqBanking Wizard" button which appears in the upper left
4. The following lines are valid only for the older aqbanking-2.x versions. In newer versions, there is no extra "Enable Backends" step necessary and you can directly proceed to the next step below, defining a user.
5. (Only for old aqbanking-2.x) In the Configuration window which appears, click on the Backends tab,
   1. (Only for old aqbanking-2.x) Click on the line containing "aqofxconnect" to select the AqBanking backend that handles OFXDirect Connect,
   2. (Only for old aqbanking-2.x) And click the Enable button

Your installation of GnuCash/AqBanking may have several additional backends listed. For OFXDirectConnect, only the aqofxconnect backend needs to be enabled.

After enabling the backend, you are ready to define "Users" -- one for each of the financial institutions that can send you OFX data.

Defining a User in AqBanking

In the same Configuration window that is already open,

1. Click on the Users tab, and
2. Click on the New button
3. At this point, the Select Backend window appears. Use the Backend popup menu to select "aqofxconnect - OFX-DirectConnect backend" and Click OK
4. The User Configuration window appears with the Intro tab chosen. Click on the General tab, and you are ready to start entering information that AqBanking will need to login to one of your accounts.

In the General tab:

• User Name = anything you'd like. It is the local name you want to associate with a given logon ID. It should be unique.
• User ID = the login name the bank associates with your account. If you have web access to your data, this ID is usually the same one you use to login to the bank/credit card web site. Some banks use your SSN as the default User ID, particularly if the bank thinks you are using Quicken to connect. The User ID field is the one AqBanking sends as part of its OFX login request
• Customer ID = ? (I don't know. I just set it equal to the User ID, and I haven't had problems.)
• Country = whichever country your financial institution is in -- usually United States of America for OFXDirectConnect
• Bank ID = RTN (Routing Transit Number, sometimes referred to as ABA number) for checking/savings. For credit cards (I usually set this equal to the OFX <FID> tag discussed under the OFX tab of the User Configuration window

In the OFX tab: On this page you need some data that may be hard to find. Financial institution help-line staff will have no idea what you're talking about if you ask them. Their supervisors will probably deny that any such information exists. So you'll have to find it yourself or check the existing list of OFX information.

• FID = the value of the OFX tag <FID> usually a 4 or 5 digit number, but the OFX spec does not limit it to numerical values
• ORG = another OFX tag... <ORG>, sometimes referred to as FIORG. This one can be weird. Chase's credit card <ORG> is B1 because they are now using the servers acquired along with the rest of BankOne, and they haven't changed the front end
• Broker Id = another OFX tag
  • usually blank for banks and credit cards
  • If the data you are looking at has "Yodlee" in the broker ID, you need to find another OFX tag source
  • Yodlee is a branding agent that stands between you and your data. You will need to find the next server down the link list before you will be able to log in via GnuCash/AqBanking. It is possible to use the trial version of Quicken in order to find the correct server information.
• Server URL = the exact, complete server URL for the OFX data server that has your account data

NOTE: Click OK to save all configuration data BEFORE attempting to download an Account List, as described below. (Refer to bug #637499)

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 Forward to go to the "Match HBCI accounts with GnuCash accounts" window

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.

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 creats an account configuration file where this "selected" part is already removed. When you assign the account manually, it doesn't remove it for somereason, adn 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. 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.

For Windows users, 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).