How To Open Transaction Files In Excel

^{🕒 2024-06-07}

You may have transactions downloaded as QBO, QFX, OFX, QIF, PDF, MT940. Online banking sites, online credit card sites or investment brokers provide “accounting downloads”. Excel cannot open these formats. Can I open these files as text in Notepad or Excel? Yes, but they are easy to edit or read.

Use the ProperConvert app to convert transaction files to CSV or Excel format.

Open a transactions file in Excel

Transaction files in QBO, QFX, OFX, QIF, MT940/SAT format are great for processing, but not so great for review like CSV or Excel files. Use the ProperConvert app to open, review such files and convert them to a conventional table format like CSV or Excel.

Download transaction files and convert to CSV/Excel using these steps:

Step 1: log in into your bank or credit card or broker website and look for an accounting or transaction download
Step 2: download and install the ProperConvert app
Step 3: Browse for a file with transactions such as a PDF, QFX, QBO, OFX, QIF, CSV, MT940 file
Step 4: set the output target (all columns, Xero, Quickbooks Online, CSV Mint, etc.) and create a CSV file (copy to clipboard option to paste directly into a spreadsheet is available as well)

Supported Account types to convert

Bank (Checking/Savings/Credit Line) (PDF, QFX, QBO, OFX, QIF, CSV, MT940)
Credit Card (PDF, QFX, QBO, OFX, QIF, CSV)
Investment (QFX, QBO, OFX, QIF)

Choose the CSV target

The "CSV target" feature allows to to create the most suitable CSV file from transaction files:

Regular CSV or Excel XLSX or XLS (all details)
Excel XLS (all details are included)
Quickbooks Online CSV (date, description, amount)
Xero CSV (date, description, amount, payee, reference, check number)
Quicken for Mac (CSV Mint) (date, amount, description)
Clipboard (all details are included, no CSV file created, copy and paste directly in Excel)

Open QBO/QFX/OFX Format in Excel

QBO/QFX/OFX files are offered in online banking to download transaction details. OFX files provide the date, amount, payee/payer, memo, check number, investment details for investment transactions. OFX files are also called "Microsoft Money" files.

Open QIF in Excel

QIF files are Quicken Interchange Format files containing details about transactions:

Downloaded from your bank, credit card company, investment broker
Created as an export file by Quicken
Created as an export file by some personal finance software

Excel or other spreadsheet software like Google Sheets cannot open QIF files directly. Use the converter app to convert to QIF to CSV/Excel first.

MT940/SAT Format

These files are provided by many banks to download transactions for your accounts. Files are not readable when are opened in Notepad or directly in Excel.

Excel and other spreadsheet applications, such as Google Sheets, are unable to directly open QIF files. To access the data in these files, use a conversion app to first convert the QIF files to CSV or Excel format.

Extract transactions from PDF Bank Statements files to Excel

PDF files are great to view and print documents, but hard to copy transactions from a statement. The ProperConvert app extracts transactions from such files. Other 'noise' details as small prints, legal paragraphs are left out.

To convert bank statements to Excel, use the ProperConvert app to extract transactions and save them as CSV/Excel file.

Extracting transactions from OFX, QFX, QBO, QIF, MT940 files

The financial formats are great to store transactions and import into accounting software. Many accounting products prefer to work with the financial formats. Such files provide only one way to store transaction details, so there are fewer errors in parsing these files.

For human beings, these formats are not good to view, read or work with. The files in these formats are better when converted to a spreadsheet format like CSV.

Extract from multicolumn CSV files

Some CSV files like PayPal transaction downloads or Stripe transaction export have too many columns to work with.

Usually only details like date, amount, description, check number are needed. Accounting software like Xero or Quickbooks Online/SelfEmployed or Quicken for Mac may support CSV import, but may refuse to work with multi-column CSV files.

Use the ProperConvert app to extract specific transaction details. Once details are extracted, create simplified CSV file that are ready to import.

Access MS Money MNY files and convert to CSV or Excel

Look for the Microsoft Money Sunset Edition (on Windows). It should open .mny files. Once you have them opened, export to the QIF format and then use the ProperConvert app to convert CSV or Excel.

Extract data from a Quicken QDF file

You need to get a Quicken installed on your computer and then use it to open a QDF file. Once you have a QDF file opened, export data as a QIF file and use the ProperConvert app to export your data to CSV or Excel/XLS/XLSX format.