How do I properly format bank CSV files?

To help automate more data entry for poorly supported banks and credit cards, Seller Ledger makes it possible to upload transaction data in comma-separated file format (CSV.) There are only a few requirements for this formatting, but failure to follow them could result in the file causing an error upon upload.

Column headers

In order for Seller Ledger to know what information is in which column, we require that the first row of the file include a name that describes the data within that column. While you can have names for many columns of data, we care primarily about 3 required ones and 1 optional one:

Required:

• Date - to be used at the top of the column that provides the date of the transaction
• Amount
• Description

Optional:

• Currency

Here's an example of what a simple, correct set of column headers might look like:

Format of data in each column

In addition to making sure that each column has the correct header name, there are some additional guidelines about the data within each column:

Date

All date values must be in mm/dd/yyyy. Yes, this is very US centric, but that's the bulk of the customer base we currently support. Please note, if you open your CSV in Excel, you may see dates shows as mm/dd/yyyy, but that may just be a display option. Click into the individual cell to make sure the year is 4 digits.

Amount

Amounts can be positive or negative, but must not contain any currency information. For example, amounts should not include formatting using $, USD, CAD, etc. Just the number and the sign (positive or negative)

Note: while amounts can be positive or negative, it matters when each sign is used.

For checking and savings accounts, positive amounts are assumed to be deposits, while negative amounts are assumed to be withdrawals.

For credit card accounts, positive amounts are assumed to be payments made to your credit card and negative amounts are assumed to be purchases/charges.

Most banks that we have worked with follow these defaults, but we have seen (at least with credit cards,) examples where the signs on the numbers are reversed. In such a case, simply open your file in a spreadsheet, reverse the signs to match the assumptions above.

Pro Tip: Dealing with banks that don't use negative numbers

There are a number of banks and credit cards who, when providing their transaction history in CSV format, don't use positive and negative numbers. They may use an extra column with values like "Deposit" and "Withdrawal" or "Debit" and "Credit".

How then do you quickly get the data into the format that Seller Ledger expects?

Here's a quick tip for how to do so in a spreadsheet. In this case, we'll use Excel.

Let's say you want to import a checking account file that contains data that looks something like the following:

One quick way to fix this is with an Excel Formula. In Column E, try the following formula:

=IF(C2="Debit",-D2,D2)

This will turn any "Debit" amounts into a negative number while leaving any "Credit" amounts as they are. Then copy that formula down the column.

Now, because your new "amount" column is a bunch of formulas, go ahead and copy and paste those values again as just "values." Then, remove any unneeded columns (and double check that your date column values are formatted properly - Excel is notorious for change date formats without telling you.