Difference between revisions of "Setting up OFXDirectConnect"

From GnuCash
Jump to: navigation, search
(Converted to mediawiki list markup.)
(Changed a "click here" hyperlink to be descriptive.)
Line 47: Line 47:
  
 
In the OFX tab:
 
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. See the section "Where to find connection info" for some ways to find this information.
+
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 [[#Where to find connection info|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
 
: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
 
:ORG = another OFX tag... <ORG>, sometimes referred to as FIORG

Revision as of 07:42, 2 February 2010

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.

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. See: Finding OFX Info With Quicken
Server URL = the exact, complete server URL for the OFX data server that has your account data

AqB-UserOFX.png

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

Starting with Debian Lenny, the debian package of gnucash includes online banking support. A backport of gnucash 2.2.6 is available in edgy-backports. http://packages.debian.org/etch-backports/gnucash

Older versions of debian require building gnucash from source. See Installing GnuCash on Debian for instructions.

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

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.

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/