Accounting formats for financial software
Accounting software comes in two forms: desktop and cloud-based. Desktop software tends to be easier to install and manage but requires a lot of maintenance. Cloud-based software allows users to access their accounts remotely, and can also sync files between multiple devices. Both types of software offer similar features, although they may differ in how much control the user has over the system. For example, if you're working with a business partner who uses a different accounting program, you might find yourself having to manually enter information into each account. With cloud-based software, however, you could easily share documents and information across platforms, allowing your accountant to view your books without ever needing to log onto your computer. All formats listed below are supported by the ProperConvert app (the app can reads and write to these formats, see more details on the app description page).
There are several common types of files:
- OFX/QFX/QBO - also known as the WebConnect format to get transactions from financial institutions, is widely supported by almost every accounting software. The OFX format allows extended variants like QFX or QBO. Extended variants should be compatible and include additional details. QuickBooks has its QBO variant and Quicken has its own QFX variant. If a software imports the OFX format, it should import QFX and QBO formats as well.
- QIF - Quicken Interchange Format, supported most commonly by accounting software Intuit Interchange Format (IIF) — supported by QuickBooks and many software packages exporting their data for accounting systems
- IIF - Used by Quickbooks and other accounting systems to exchange data
- QBJ - QuickBooks Journal Entries - new IIF variant supported by Quickbooks
- CSV/Excel - free form table formats that is easy to edit and read by accountants and bookkeepers, but usually hardly supported by accounting software packages during its "free form" as columns and their meaning is not clearly defined
- PDF files are easy to read, share, print, and archive. But they're not well-supported by accounting systems when it comes to parsing and importing transactions within them. There are however several independent software packages available for converting PDFs into CSV or other financial formats (e.g., XLS).
- MT940 - used by banks to send messages and sometimes provided to customers and supported by some accounting services
The OFX/QFX/QBO file format is the most commonly used format for bookkeeping purposes because most financial institutions support the format. Most banks allow downloading QBO, QFX or OFX files from the online banking interface. Some banks only provide access to data within the last 90 days, and others do not provide any access to historical data. Accounting software imports transactions into accounting software for further analysis and categorization. For example, QuickBooks imports QBO statements into the Bank Feeds Centre, where you match downloaded payees or transaction descriptions to vendor records and assign expense/income account numbers (‘category’). Quickbooks learns your matches and uses them for future QBO imports or direct bank downloads. Quicken imports QFX files. Most accounting software packages import OFX files.
QBO/QFX/OFX files have the following transaction details:
- check number
- unique transaction ID
QBO format is a QuickBooks Web Connect format
The QBO format is the format to import transactions into QuickBooks. ".QBO" does not mean "QuickBooks online".
QuickBooks Desktop has to be not older than three years to import ".QBO" files. For the older Quickbooks versions, convert to the IIF format.
IIF format is the format to use for older QuickBooks versions. Look for converters converting to the QBO format.
QuickBooks Desktop (PC or Mac) imports QBO files through "File", then "Utilities", then "Import", then "Web Connect".
QuickBooks Online imports QBO files as well and the QBO format is the best format it imports. QuickBooks online also imports OFX and QFX files (also a good choice to import). OFX and QFX formats are not importable by QuickBooks desktop.
QuickBooks desktop imports QBO (Web Connect) files into the Bank Feeds Center. Once the transactions are imported, you review them and match them to vendor records and assign expense/income accounts. After that, the transactions are added to the register.
The QIF format is among the oldest widely supported formats for exporting and importing transactions. Its popularity stems from its ability to store information about accounts, such as account names, balances, transaction amounts, dates, and so forth. However, it also supports many features that aren’t found in any other popular exchange format, including categories and tags. Because the format has undergone various extensions over the years, it can be difficult to convert between them. One problem that often arises is that the QIF files exported from older software may not be compatible with newer software. In order to ensure compatibility, some companies may require that users upgrade their software before they can view data stored in QIF files.
The IIF format has been developed by Intuit for their Quickbooks product line but is now used by many different applications. Before importing an IIF file, back up the company file first so that you don't lose any data if something goes wrong. As IIF is considered a "system-level" format where data are imported directly into a Quickbooks data file.
For transactions, the IIF format supplies the following details:
- vendor or customer[:job]
- main account
- expense/income account
The QBJ format is the newer format, but it is also a great option to import your transactions from the General Journal Entries. You will get full audits for each General Journal Entry so you can see exactly what was entered into QuickBooks. The only downside is that entering these entries through the QuickBooks interface is not as quick as entering them through an excel spreadsheet. Compared to QBO formats, the QBJ format is very advanced. You can create multiple debit and credit accounts, assign accounts, and classes and there is no limit on how many accounts you can add.
Normally, you would need the Accounting edition of Quickbooks to create such files (as an accountant or bookkeeper) and send them to your clients to import. The ProperConvert app allows you to create a QBJ file from an Excel spreadsheet.
Each Quickbooks journal entry contains two or lines (debit and credit) that must balance within the entry. Each line carries an account, optional or mandatory name (depending on the account), class, description, and document number.
The QBJ format requires account numbers to be activated in your Quickbooks chart of accounts.
The CSV format supported my few accounting products like Quickbooks Online. It is a good choice if you have your transaction entered into a spreadsheet or downloaded from your bank as a ".CSV" file. Unlike QBO, MT940, IIF, and QBJ formats, which are defined formats where data can be interpreted only one way, the CSV format is a "free-form" format. You may have 2 date columns, columns not related to details that an accounting program can import. The date formatting could be different (day/month vs month/day), and the amount format could use different decimals or thousand separators. You may have transactions take one line or multiple lines. A CSV file might have the first line as a header, or the first couple of lines aren't transactions, but statement details, so you may have to reformat the CSV file (open it in spreadsheet software, change it, and then save it as another CSV file) before importing it into Quickbooks.
The PDF format was developed to be a universal method of storing electronic documents. In order to make them viewable on any device, they must be converted to a suitable format before viewing. Most commonly, this involves converting them to HTML5, CSS3, or plain text. However, if you wish to convert your PDFs to another format such as Microsoft Excel, then you'll need to purchase additional software.
You should always check for available formats when choosing a bank and supported formats when choosing an accounting system. Also, having conversion tools available is important if you have a large client base.
The MT940 format is a file format used by the SWIFT network to send and receive end-of-day bank account statements. It is provided to bank clients as well by certain banks and is supported by some accounting software packages and can be converted to other formats or converted from other formats to this format.