Excel to OFX Converter: Spreadsheet to Bank File

An Excel to OFX converter turns rows of bank transactions in a spreadsheet into an OFX bank file. It needs one row per transaction, a single date format, one signed amount column and a description. A running balance column lets it write the closing balance. Check the output in a text editor, then test it in an empty account.

What an Excel to OFX converter reads from each spreadsheet column

An Excel to OFX converter reads four things from each row: the date, the description, the amount and, if you have one, a reference such as a check number. It also needs a few facts from outside the rows: the account type, the last digits of the account number and the closing balance.

The columns, and what each turns into

The example values are illustrations. The FITID is the one element your sheet never holds. The OFX 2.2 specification says each FITID must be unique within the account, and that its main purpose is to let software detect duplicate downloads. A converter usually builds it from the date plus a sequence number. Ask yours how it does that, and whether converting the same rows twice produces the same IDs.

A before-and-after example sheet

Here is an example of a sheet as it often arrives, copied from a bank website:

And the same rows after tidying, ready for a converter:

The sections below take each change in turn.

Do XLS and XLSX workbooks need saving as CSV first?

Some converters open XLS and XLSX workbooks directly, and others accept only CSV. Saving the sheet as CSV first is the safer habit either way. It strips out formulas, hidden columns, merged cells and number formatting, so the converter sees exactly the values you see, and nothing a workbook does behind the scenes.

To save a clean copy in Excel:

1. Put the transactions on their own sheet, with one header row and nothing above it.
2. Delete totals rows, notes and blank rows at the bottom.
3. Use File, Save As, and choose CSV UTF-8.
4. Excel warns that only the active sheet will be saved. That is what you want.
5. Open the CSV in a text editor to see the raw values the converter will get.

Step 5 catches most surprises. An amount that showed as 1,500.00 in Excel may be written with a comma inside quotes, and a date formatted to look like 05/06/26 may be stored as something else entirely. If the CSV looks right, the conversion usually does too.

If the converter you have only makes QuickBooks files, the same CSV goes into a CSV to QBO converter instead.

One date format down the whole Excel date column

Every cell in the date column needs the same format, written the same way, before conversion. A sheet that mixes "May 4, 2026" with "05/06/26" can produce an OFX file where some lines land in the wrong month, or where the converter skips rows it cannot read. Settle on year-month-day.

Why mixed dates appear

Mixed dates usually come from copying a bank's web page, from two downloads pasted together, or from a colleague typing a few missing lines by hand. Excel may store some cells as real dates and others as text that only looks like a date. A converter treats the two kinds differently.

Fixing them

• Select the column and look at the alignment. Real dates align right by default; text dates align left.
• Convert text dates with DATEVALUE, or with Data, then Text to Columns, choosing the date order the text was written in.
• Format the finished column as yyyy-mm-dd. That form cannot be misread as day-first or month-first.
• Look for a jump of a year or more when sorted. A two-digit year such as 26 can be read as 1926 by some tools.

The OFX file itself will hold dates as eight digits, year first. The converter does that rewrite, but only if it understands what it is given.

Signed amounts versus separate debit and credit columns in Excel

An OFX file holds one amount per transaction, negative for money out and positive for money in. Many bank spreadsheets have two columns instead, one for withdrawals and one for deposits, both written as positive numbers. Merge them into one signed column before converting, unless the converter clearly offers a two-column setting.

A formula does the merge. If withdrawals are in column C and deposits in column D, put this in a new column and fill it down:

=D2-C2

A deposit of 1,500.00 gives 1500. A withdrawal of 58.20 gives -58.2. Then copy the column and paste it back as values, so the CSV holds numbers and not formulas.

Watch these

• Text amounts. Numbers pasted from a web page sometimes arrive as text, with a currency symbol or a trailing space. Excel will not subtract them. Clean them with VALUE, or by removing the symbol.
• Brackets for negatives. Some statements show (58.20) for money out. Replace the brackets with a minus sign.
• Credit cards. On a card account, a purchase is still money out and should be negative. The OFX specification signs each transaction by its effect on the account, so keep that rule even though the card balance itself grows with purchases.

Why does the OFX file want the spreadsheet's running balance?

The OFX file has a ledger balance element, LEDGERBAL, which states the account balance on a given date, usually the last day the file covers. Many converters take it from the last row of a running balance column. If the sheet has no balance column, you type the closing balance from the statement instead.

That one number does more than fill a field. It is your proof that the sheet is complete:

1. Find the opening balance, the balance before the first row.
2. Add the sum of the Amount column to it.
3. The result must equal the closing balance.

In the example sheet, suppose the opening balance before May 4 was 4,960.31. Adding 1,500.00 and subtracting 58.20 gives 6,402.11, which matches the last Balance cell. If it did not, a row would be missing, doubled or carrying the wrong sign.

Do the check before converting. It is much easier to find a missing row in Excel than in an OFX file.

Reading the OFX file an Excel converter produced

Open the finished OFX in a plain text editor and read it before importing. You are checking three things: that the header names the right account type, that the number of transaction blocks equals the number of rows in the sheet, and that a few dates and amounts match the spreadsheet exactly.

What to look for

• The header. The first lines say which OFX version the file follows. Some programs are fussy about the older and newer styles, so note which one you got.
• The account block. A bank account file has BANKACCTFROM with an account type such as CHECKING. A card file has CCACCTFROM.
• The transactions. Each row should appear as one STMTTRN block. Use the editor's find feature to count them.
• The IDs. Every FITID should be different. Two rows with the same date and amount should still get two different IDs.
• The balance. The LEDGERBAL amount should match your closing balance.

Test before the real import

Import the file into a new, empty account first. If the receiving program is QuickBooks Online, Intuit's page on uploading transactions manually to QuickBooks Online lists .ofx among the transaction list types and sets a limit of 350 KB per file. Split a long year into smaller files if you are near that size.

If your bank exports plain text rather than a spreadsheet, the same checks apply once you have rebuilt it in rows, as shown in converting a TXT bank export to OFX.

Excel sheets that should never become an OFX bank file

Some spreadsheets are the wrong source for an OFX file, however carefully they are tidied. An OFX file claims to be what the bank recorded. A sheet that is a budget, a ledger export, a summary or a mix of accounts would put entries into a bank register that the bank never made, and reconciliation would fail later.

Do not convert:

• Budgets and forecasts. Planned amounts are not transactions.
• Ledger or general journal exports. These already reflect your categories and may include entries that never touched the bank, such as depreciation.
• Sheets with more than one account. Split checking, savings and cards into their own sheets and their own files.
• Monthly totals. One line per month cannot be matched to bank lines.
• A sheet that does not balance. Fix the gap first.

Better sources to start from

If the bank offers an OFX or QBO download for the period, use it, because it carries the bank's own IDs. If your bank supplies MT940 statements instead, converting MT940 to OFX is a shorter route than going through Excel.

Many people end up in Excel because their only copy is a PDF statement. In that case it is worth skipping the spreadsheet stage. SimpleBankConvert is being built to turn a PDF statement into a CSV or QBO file with dates and signs already fixed, and converting a PDF to QBO on a Mac shows the steps from the statement side.