Windward Software Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Bank Reconciliation ======


===== Diagnosing Bank Reconciliation Import Problems =====

==== Import does not work, No message ====

The bank rec import converts an OFX file (open financial exchange) to an XML file.  If you locate your .OFX file you should see a corresponding .XML file.  Simply double click on this file to open in Internet explorer.  Scroll down to the bottom of the file to see if you have an error message or a problem in the XML document.  Often the problem may be resolved by editing the OFX file and fixing the problem.\\
== Common problems ==
   * an "&" (ampersand) in the description text.  You can change this to "&amp;" or "and".  This problem has been resolved in version 5.35.8.74
   * System Five supports OFX version 2.  Some foreign banks still use OFX version 1.  There are ofx1 to ofx2 converters available.
   * .QIF files (Quicken Interchange Format) are only supported in 5.41  This format should be avoided unless it is your only available format.
   * ATB Financial, Alberta OFX files are not formatted correctly.  A fix for this import is available in 5.41
 
==== Import does not work, no records found for this account ====
== Common problems ==
  * You have imported this file already and no new transactions were found.
  * The bank reconciliation "Account" field name does not match the file you are importing. For example you are importing "Chequing" into "Savings". When you import a file for the first time, it will list the available account(s) in the OFX file. The account name will usually include the account type and account number. Select the account name you wish to import. The account name will be stored in the bank reconciliation "account" field.  The next time you attempt to import from your bank, the system will import the same account.  If your account name has changed, or you have the wrong account name, you can fix this by turning on Supervisor mode, then blanking out the "account" field. When you re-import, it will ask you to select the account name again from the available accounts in the OFX file.




===== Bank Export File Formats =====
  * Quicken files are .qfx - these are simply .OFX files with a different file extension.
  * Microsoft Money files are .OFX files
  * Quick Book files are .qbo files. these are simply .OFX files with a different file extension.
  * QIF files (Quicken Interchange Format) .QIF are only supported in 5.41  This format should be avoided unless it is your only available format as it does contain all of the required information.
  * XML files, can be imported. OFX files are converted to XML for the import. 
  * CSV (Comma Separated Values). This import format is not supported.
  * Most other formats are simply OFX files with a different extention. You can try renaming to .ofx and importing.

==== OFX file Format ====
The ofx file format is similar to below. The file can be viewed with notepad to determin if you have an OFX file.
<code>
    OFXHEADER:100
    DATA:OFXSGML
    VERSION:102
    SECURITY:TYPE1
    ENCODING:USASCII
    CHARSET:1252
    COMPRESSION:NONE
    OLDFILEUID:NONE
    NEWFILEUID:NONE
    
    <OFX>
    <SIGNONMSGSRSV1>
    <SONRS>
    <STATUS>
    <CODE>0
    <SEVERITY>INFO
    <MESSAGE>OK
    </STATUS>
    <DTSERVER>20060404160753[-5:EST]
    <USERKEY>--NoUserKey--
    <LANGUAGE>ENG
    <INTU.BID>00002
    </SONRS>
    </SIGNONMSGSRSV1>
    <BANKMSGSRSV1>
    <STMTTRNRS>
    <TRNUID>QWEB - 200604041607537951
    <STATUS>
    <CODE>0
    <SEVERITY>INFO
    <MESSAGE>OK''
</code>
  * Here is a sample line out of the QFX file form CIBC
    * Notice the DTPOSTED is for 2015-05-29
    * Notice the TRNAMT is -1.82 meaning an charge of 1.82 was applied (TRNTYPE = SRVCHG)
    * The memo lets us know it was for overdraft interest. 
<code>
<STMTTRN><TRNTYPE>SRVCHG<DTPOSTED>20150529172447.000[-5:EST]<TRNAMT>-1.82<FITID>15149153052514253474570000<MEMO>OVERDRAFT INTEREST CHARGE;Branch Transaction</STMTTRN>
</code>

=== OFX files missing line breaks ===
Some exports are missing the line breaks at the end of each tag. It looks like 
<code><OFX><SIGNONMSGSRSV1><SONRS><STATUS><CODE>0<SEVERITY>INFO<MESSAGE>OK</STATUS></code>
instead of
<code>
  <OFX>
    <SIGNONMSGSRSV1>
    <SONRS>
    <STATUS>
    <CODE>0
    <SEVERITY>INFO
    <MESSAGE>OK
    </STATUS>
</code>    
Here is how they can fix the file format. They can use the qfx for ofx file format. 

  * Download notepad++ from https://notepad-plus-plus.org/   They must use notepad++ as most other search and replace editors do not have the regular expression option.
  * Open the file in netpad++
  * Select Search, Replace.
  * Find <
  * Replace with \n<
  * Search mode = Extended 
  * Replace All,
  * Save with a new file name and then import into System Five.