Setting up OFXDirectConnect in GnuCash 2

From GnuCash
Jump to: navigation, search

Contents

Online Banking in Gnucash 2 - OFXDirectConnect

GnuCash 2 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 (I use ofx.py) via GnuCash's File>Import>Import OFX/QFX... menu option.

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

AqB-Backends.png

If aqofxconnect is not listed, you may need to install a package for your particular operating system

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

AqB-UserGeneral.png

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

AqB-UserOFX.png

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:

AqB-getaccountsuccess.png

(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

Ubuntu

Staring with Ubuntu 8.10 Intrepid, the ubuntu gnucash package includes online banking support. A backport of gnucash 2.2.6 is available for hardy at https://launchpad.net/~gnucash/+archive.

Versions of Ubuntu before Hardy need to build gnucash from source. See the Debian guide page for building instructions.

Windows XP --GNUCash 2.2.1

Before selecting "Supports Account List Download". Select Ok after entering all other data in General and OFX tab. Then edit the account you just made and select "Supports Account List Download" and "Get Accounts".

"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.

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/

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

If your bank (Bank of America does this) indicates that the application or version is not supported when using GnuCash on MacOS X 10.6.x (Snow Leopard,) a quick fix is to modify the libofx.3.dylib (inside GnuCash package, Contents:Resources:lib) with a hex editor. Search for the string "1200" which is just before the string APPVER. Modify it to "1800". (Did this myself following above instructions for Windows XP by copying libofx.3.dylib to Windows running in a VirtualBox VM--as I didn't have an Mac hex editor handy--edited with UltraEdit32 editor in hex, copied back to GnuCash package on Mac. Restarted GnuCash and got no more complaints from BofA.)