Download Account Transaction Statements

A tester has requested that PalOMoney support downloading account transaction statements. This topic will explore what that might entail. New: This has been implemented in version 1.0.5.

There are several existing methods that use java or python to download statements directly from financial institutions that support ofx (or qfx) direct downloads. By far, this is the best way so far to update your Money account transactions and balances. See the wiki Links to Other Money Sites to discover the solutions that do this today.

The alternative method, for institutions that do not support direct download, or charge a fee for it, is to go to their web site, and download the ofx or qfx file from there, or (awful) scrape the information from a "recent activity" screen or page. Currently, this site is ideologically opposed to the second practice, and takes the position that you should demand that your financial institution support ofx direct downloads.

Note that qfx is just ofx with some added fields designed to meter and restrict access to older versions of Quicken, and that your financial institution may have to pay licensing fees to Intuit (Quicken's parent) to give you your information using their software format. If you think this is unreasonable, you are not alone.

There are two levels of import possible:

• The first gets data from ofx servers at the financial institutions and pushes them to Money, which will import them.
• The second gets data as above, but imports the data into Money statements and accounts directly.

The second method would be much faster than the first method, which is slow and klunky. However, implementing it is fraught with potential issues and risks, so it seems too difficult to implement at this time. We can experiment and turn our attention to the first method.

The ofx file download request contains certain required fields. Fortunately, the Money Jet database has the necessary tables and fields, although the password field seem to be missing. Passwords might have to be stored (encrypted of course) outside of Money, a prospect that is risky. including a password field.

Money contains no available user interface for setting many of these fields. Apparently, the required information was obtained from a MSN server that had entries for each financial institution. This information is readily available on the web, but several dialogs will be are required to enter the info.

Once the information is entered, a scheduler can periodically update the information. We can get just the most recent information using the date of the previous update, or allow a manual override to get a wider range of dates, which would be useful if you had mistakenly deleted some transactions. Because the transaction identifier would be stored with the transactions or statement entries, duplicates could be eliminated before importing a statement.

One issue is that, as transactions roll in during the day, whether a new statement would have to be created for each update, or whether new transactions could be appended to an existing statement.

One of the benefits of this automatic download feature, is that a list of recent transactions could be displayed. If you paid attention to this, or perhaps set alarms, you might catch cyber crime or a profligate spouse in the act, or nearly so.

This feature would go hand in hand with a Statements and perhaps Transactions window like the Portfolio View that could be grouped and sorted in a variety of ways. The view could include the transaction ID, which is hidden in Money.

This feature might help in automatically recognizing transfer transactions, which would have different transaction IDs (one for the source, another for the destination).

This feature could help in redirecting transactions that should not go into an investment account (such as margin adjustments included in some ill-formed downloads), that incorrectly inflates (or reduces) your cash balances, leading to unwarranted joy or, in some cases, despair.



Edited 4 time(s). Last edit at 03/28/2013 09:46PM by dev.

Version 1.0.5 implements OFX Statement downloads. See the Wiki release notes. More on this post later. In the meantime, here are some snapshots:

Statement View

The Statement View window displays downloaded Statement Items, which are are created by importing a statement into Money, and deleted when you accept the item in Money. These are the most recently downloaded transactions. This view does not display all of the most recent transactions: manual account transaction journal entries do not come from statement items, and so are not displayed, nor are downloaded transactions you have accepted. Accordingly, the list displays your most recent transactions that you have not examined or accepted.

Statement view supports configuring online statement downloads via several dialogs. These dialogs configure Money's original online services, which currently no longer work. However, a few Money online functions and services, such as Background Banking, may be persuaded to work with a bit of coaxing. For this reason, PalOMoney is designed to configure online services in a manner compatible with Money 2008's Online Banking.

PalOMoney supports statement direct download and import using a variety of methods.

Because Money used a variety of service providers to implement online banking and statements, Money 2008 Online Banking-compatible configuration is significantly more complicated than that of the current OFX download apps. Basically, four related Money database tables are involved in online configuration: Accounts, Financial Institutions, (Connection) Services, and Provider Logins. Accounts belong to a particular Financial Institution. Provider Logins define a user name and password to a provider server and data transfer between the user and the provider and one or more Financial Institutions. Connection Services link accounts to provider logins, defining the necessary user, account, and financial institution information. PalOMoney can export this information to a .csv file for examination or import into another Money file (see the Import Standard Connection Services and Providers dialog below).

Beyond Money's complicated schema, PalOMoney's complex experimental online configuration dialogs are object lessons on how not to implement a user interface. Even so, they provide the functionality to configure online services. Here are the most important dialogs:

Account Connection Service dialog


The Account Connection Service dialog is the heart of Online Banking configuration. This dialog can modify a few account fields, and can create, edit, and delete Connection Service records. It can also create new Provider records, which can be edited, created, and deleted from the Online Statement Provider Login dialog, below.

Online Statement Provider Login dialog


The Online Statement Provider Login dialog manages Online Statement providers and user accounts on these providers, which may be different from your accounts at your financial institutions. These providers support various capabilities, which enable and disable the ability to perform various functions. The user name and password are stored here. You can have several provider pages with different login information to support multiple account owners and access methods. A provider can support only one financial institution, or multiple institutions. The provider login may be enabled or disabled for downloads.

Import Standard Connection Services and Providers dialog


Automatic configuration will require configuration import. The manual Import function in the Account Connection Service dialog reads a .csv file, compares the contents to the open Money file, and displays a reconciliation report between the import and Money file. You can select which configuration entries to import or delete. The import function can create new Connection Services and Provider Logins. For safety's sake, it does not create new Accounts or Financial Institutions. This must be done by hand using Money's New Account function.



Edited 4 time(s). Last edit at 03/29/2013 12:56PM by dev.