Financial formats for accounting software

propersoft
propersoft

A review of financial file formats used by accounting and personal finance software

There are many accounting software packages available, both desktop and online. Software products like Quickbooks, NetSuite, Xero, Sage, Wave, Quicken come to mind right away. There are many different accounting tools available because of a wide range of accounting and bookkeeping needs companies and businesses have. As for financial formats, there are not so many to transfer data from one place to another (for example, from your bank to your accounting software). This is a good thing. You should not a have an issue every time you get a file with transactions from your bank or your client and the accounting software cannot import it. 

Financial formats 

The following formats are the most used/common ones: 

  • OFX - also known as "Web Connect" format to get transactions from a financial institution, widely supported by almost all accounting software. The OFX format allows extended variants of itself. Extended OFX variants should be compatible and provide additional details. Quickbooks has its QBO variant and Quicken has it own QFX variant, and PeachTree/Sage has its own ASO variant as 
  •  QIF - Quicken Interchange Format, supported by many accounting software 
  • IIF - Intuit Interchange Format, supported by Quickbooks and many software packages exporting data for accounting systems 
  • QBJ - General Journal Entry file format, supported by Quickbooks 
  • CSV/Excel - as "free-form" format, it requires additional handling by the accounting software, so it is only partially supported, but it is the easiest format to work with for accountants and bookkeepers 
  • PDF - the easiest format to view, share, print, archive financial statements, but mostly not supported by accounting systems regarding parsing and importing transactions inside such statements. However, there is several independent software packages available offering conversion from PDF to CSV or other financial formats. 
Less common formats are: 

  • MT940 - mostly used in European countries 
  • BAI/BAI2 - used by large organizations and financial institutions 

OFX/QFX/QBO format 

The OFX/QFX/QBO (Web Connect) format is the most frequently used format as the majority of bookkeeping is bank and credit card-related activities. Many banks allow downloading QBO files from the online banking. Some banks only provide last 90 days for QBO files, and CSV or PDF files for months past 90 days. Accounting software imports transactions as "statements" into accounting software for further review and categorization, as the banks are not aware of specific accounting rules of their clients. For example, Quickbooks imports QBO files into the Bank Feeds Center, where you match "downloaded as" payee names or transaction descriptions to vendor records and assign expense/income accounts ("categorize") before adding the transactions to the register. Quickbooks learns your matches and uses them for next QBO import or direct bank download. 

QIF format 

The QIF format one of the oldest widely supported formats to export and import transactions. Many accounting systems support this format. It provides many features missing in OFX/QFX/QBO format like categories and tags. The format has some challenges as it was extended to different accounting systems over time and now the QIF format has few dialects that make it incompatible with certain systems. For example, to make a QIF file importable by NetSuite, it must have details that make that QIF file non-importable into Quicken. 

IIF format

The IIF format was designed by Intuit for its QuickBooks products but widely adopted by many software packages as a format to export and import financial data like transactions, sale receipts, bills, invoices, etc. As IIF is considered a "system-level" format where data are imported directly into accounting system data file, you should backup the company file before importing any IIF file (the same applies to other formats and any bulk operations - do a backup before you import). 

QBJ format

The QBJ format if the relatively new format, but it is a great option to import transactions from the General Journal Entry files. You will get full audit records for each General Journal Entry. The only challenge is to enter such entries as Quickbooks interface is not as fast as Excel. Compared to QBO format, QBJ format is much more advanced - you create multiple debit and credit lines, assign accounts, classes, and there is no limit to just bank and credit card accounts as with QBO files. You do not have to match vendor records and assign accounts as QBJ format carries all this already. 

CSV/Excel Format

The CSV format supported by few accounting products like QuickBooks Online. It is a great choice if you have your transactions entered into a spreadsheet or downloaded from your bank as a CSV file. Unlike QBO, IIF, QBJ formats that are defined formats where data elements can be interpreted only one way, CSV format is a "free-form" format. You may have two date columns, columns not relevant to details that an accounting software can import. The date format could be different (DAY/MONTH VS MONTH/DAY). The amount format could use different decimal or thousands separator. You may have transactions taking one line or many lines. A CSV file may have the first line as the column header, or the first few lines are not transactions, but statement details. In these cases, you may have to reformat the CSV file (open in a spreadsheet software, edit, and then save as a new CSV file) before importing into Quickbooks. 

PDF format

The PDF format was designed to print documents, so they can be printed the same way they are viewed on a computer screen. As the PDF format is a widely adopted document format, it is often used to archive financial statements that hard to process later when it comes to accounting and bookkeeping. Usually, a human being (you) needs to read PDF files and enter data as CSV file or directly into an accounting system. The accounting software packages usually stay away from supporting PDF format. Separate software packages solve this problem by converting to a compatible financial format. It is a good idea to check available formats when choosing a financial institution and supported formats when choosing accounting software. It is also a good idea to have conversion tools available when you have an accounting practice with a wide range of clients. 

ProperSoft creates accounting friendly software at https://www.propersoft.net/ 



Recommended Stories