Importing bank statements .csv template?

mr_nicknames · August 16, 2014, 8:07am

Is there a .csv template I can follow to import bank transactions from paper statements?

I want to enter the data in excel then import into Manager.

I have closed the accounts to this bank statement, so no longer have online access.

lubos · August 17, 2014, 7:57pm

There is limited support for importing .csv bank statement.

When importing CSV file, Manager will look for following columns in CSV file

Date
Payee
Reference
Description
Amount

niknah · May 4, 2015, 6:01am

I’m trying to import using those headers.
But it says…
“Input string was not in the correct format”

Example .csv file…

lubos · May 6, 2015, 2:16am

Make sure the date format is dd/mm/yyyy (e.g. 03/05/2015)

Yours is dd/mmm/yyyy (03/May/2015)

Wornout · May 7, 2015, 6:45pm

Hi I found it easier to use http://www.csvconverter.biz and convert all my CSV files its free to join.

niknah · May 8, 2015, 11:28am

Using dd/mm/yyyy just gives a “File you are trying to upload has ambiguous date format.” error.

Thanks for the csvconverter tip.

lubos · May 9, 2015, 3:22am

Make sure to import more transactions. If you import single transaction as at 03/05/2015, it won’t be obvious to Manager whether it should be March 05 or May 03.

Importing more transactions avoids this issue as Manager will be able to determine whether date format is dd/mm/yyyy or mm/dd/yyyy

raj_nann · May 11, 2015, 3:56am

Can we create excel .CSV file for expenses from cash account and upload?
Bcz in a day there will be too many expenses which will take lot of time to create them in server edition.

lubos · May 11, 2015, 10:28am

I guess I could add support for this in future. However, you don’t have to enter each transaction under Cash accounts. You can just enter one transaction per day summarizing all transactions.

ghairaj · September 5, 2015, 8:08pm

Would like to import bank statement from excel / csv, how the debit and credit entries can be recognised?
Is it pssible by assigning the - sign for debits?

lubos · September 7, 2015, 9:38am

For bank statements, terms “Debit” and “Credit” are extremely confusing. That’s because what is from your point of view a “Credit” is from bank’s point of view “Debit”.

So if importing from Excel / CSV, there is a column called Amount. Negative amount will be understood as a payment out of bank account.

ghairaj · September 7, 2015, 2:02pm

Thanks for your support.

luiz · October 11, 2015, 7:57am

Hi . I have tried looking for the answer to my problem but I don’t seem to find it here.
I and trying to import my bank stat to my manager acc books but cant get it right . i don’t have excel on my computer but when down loading the bank state i saved it as “transactionHistory (2).csv” using note pad but when i try to export is it says “Input string was not in a correct format” . What am i doing wrong ? pls help.

PS thanks for the great service.
Luiz.

lubos · October 12, 2015, 7:07pm

The issue could be a date format. What is your date/number format set under Preferences tab? And what date format is in your CSV file?

ebunders · November 29, 2015, 9:01pm

Hi
I don’t know if this helps, but I spent quite some time trying to use csv import support in Manager, but in my view it is broken on account of what Lubos says here:
“Make sure to import more transactions. If you import single transaction
as at 03/05/2015, it won’t be obvious to Manager whether it should be
March 05 or May 03.”

Eventually I created a script to convert my csv files to qbo format, and that works for me. I attach it here. It is a linux bash script.

I had some discussion about this with Lubos, and I stil think it would be nice to change the current situation, either by making the expected date format static, or by allowing users to supply a date format pattern. At the moment the software tries to guess the format, and, alas, guesses wrong

#!/bin/bash

# example statement line
# 1 14-08-2014     date
# 2 0918829518     acc from
# 3 0426800109     acc to
# 4 g j  elshof rook    name to
# 5
# 6
# 7
# 8 EUR            currency
# 9 9237.59        total
# 10 EUR            currency
# 11 -453.75        sighned amount
# 12 14-08-2014     date
# 13 14-08-2014     date
# 14 9720           prev total
# 15 OVB            ?
# 16 80437195
# 17
# 18 'aan gj elshof rook cj'     description

clear
export LOG_FILE=/home/ernst/tmp/log.txt
exec 3>&1 1>>${LOG_FILE} 2>&1
echo
export dt="`date` :"

awkscript='
function trim(str) {
    sub(/^[ \t\n\f\r]+/,"",str);  # remove leading whitespaces
    sub(/[ \t\n\f\r]+$/,"",str);  # remove trailing whitespaces
    return str;
}

function strip(string){
  return substr(string, 2, length(string)-2)
}

BEGIN{
    print "<OFX>\n\t<BANKMSGSRSV1>\n\t\t<STMTTRNRS>\n\t\t\t<STMTRS>\n\t\t\t\t<CURDEF>EUR\n\t\t\t\t<BANKTRANLIST>"
}
{
    if(NR > 0) {
      #date: "09-05-2014" => 20140509
      split(trim($1), d, "-")
      date = d[3] d[2] d[1]

      # remove single quotes on description
      description =  trim(strip(trim($18)))

      #Amount (signed)
      amount = trim($11)

      #Payee
      payee =  trim($3)
      if(trim($4) != "") payee = trim($4) ": " payee


      # del = ", "
      print "\t\t\t\t<STMTTRN>"
      print "\t\t\t\t\t<DTPOSTED>" date
      print "\t\t\t\t\t<TRNAMT>" amount
      # print "\t\t\t\t\t<NAME>" payee
      print "\t\t\t\t\t<MEMO>" description
      print "\t\t\t\t</STMTTRN>"
    }

}
END{
  print "\t\t\t\t</BANKTRANLIST>\n\t\t\t</STMTRS>\n\t\t</STMTTRNRS>\n\t</BANKMSGSRSV1>\n</OFX>"
}
'



echo ">>"
echo "${dt}convert ing manger script running"

echo "${dt}evaluationg arguments: $@"
for FILE in "$@"
do
   echo "${dt}evaluation file: ${FILE}"
   if [ -f "$FILE" ]
        then

        #create new file name
        extension=${FILE##*.}
        fname="${FILE%.*}-manager.qbo"
        echo "${dt}filename: ${fname}"

        echo "${dt}handling file ${FILE}"
        rm "$fname"
        cat "$FILE" | awk -F, "$awkscript" >> "$fname"
   fi
done

Andro · December 5, 2015, 9:52am

What is “Reference” thing?
i thought that it’s intended to “reference” transaction to another account (as in double entry).
Already tried that, but bank transaction can not automatically linked to expense. Or that’s what expected from importing?

lubos · December 7, 2015, 12:22am

Reference is just a transaction number (if applicable). For example, if you are using cheques, then bank will put cheque number as a reference.

Maerel · January 8, 2016, 10:26pm

It took me quite some time to achieve a successfull import. Unfortuneately I don´t have the final Excell source I once made anymore, but the tips below might help.

(upfront, if you see typos, the Dutch spell checker keeps overruling my English here… ).

Tips

1.1) system settings should be: comma is decimal point. Manager needs decimal point in the currency value, rather than comma (Netherlands). As the comma is the value separator (CSV), during import the intended currency value might otherwise be interpreted as two separate values.

1.2) within Manager, set the date etc settings too to UK or US.

don´t forget - as suggested earlier - to have the field names in row one, cell one (A1), separated by comma’s. Too, see 5), last line.
A value of € 92,23 (or any European currency, notated with a comma…) should be visible as 92.23 (with a decimal point and too without the currency sign. For that matter, check 1.1 and 1.2: temporarily reset Manager as well as system settings to UK or US and then (re-)start using Excell to compose the data (either by import or entry). Too see Lubos´ response a few messages up w/t to negative amounts (prefix payments with a minus sign in front of the value, e.g. -92,23).
consequently, as a result of 1.1 and 1.2 the date format of the imported or pasted input data might be wrong to start with too. Do some Excell magic to get the YYYYMMDD format for the date values. Paste this result ´as values´ into another column. And then delete the columns used for this manipulation.
if you were using multiple columns to compose the data, merge them all into a new column (Excell formula:“=A2&”,“&B2&”,“&C2&”,“&D2&”,"&E2). Select and paste the result column into the next column AS VALUES.
Move or copy the header values in cell A1 (containing the coma separated field names) into the header of this column. NOTE: empty fields would show up as two commas.
save the file as an Excell file (xls or xlsx), as you might need it to recompile things if import into Manager fails.
delete all columns preceeding the result of 5) and save the file as CSV file (next to the Excell xls or xlsx version of 6).

8.1) prior to 7), DON´T have hidden or additional rows or columns.
8.2) don´t have multiple worksheets too, only one will work.

after saving the Excell as CSV file, check the contents by means of a regular text editor, like Notepad on Windows and TextEdit on Mac. This might show possible irregularities, like wrong date and currency formats. Correct things by means of the xls or xlsx file, generated in step 6). And proces with 8) onwards.
Too, count the number of coma´s in any line. This always should be one less the number of fields in the header: 4 (with 5 fields, if I recall correctly). If more commas, than see bullets 1) and 2) and restart by correcting, followed by 6) onwards.

Success & best regards !

Artem · June 22, 2016, 7:19am

Hello.

Im still struggling with uploading bank statement in CVS file. I always get a message that CSV file is missing “Date” column. I had set up Date column format as dd/mm/yyyy and still get the same mistake.

Appreciate your help and feedback.

lubos · June 22, 2016, 9:46am

Can you paste here the first line in your CSV file? The first line should contain headers. One of the headers should be Date

Topic		Replies	Views
.csv files seem difficult to import in Manager. What's up?	13	3663	January 18, 2023
CSV statements import issue	11	3131	January 18, 2023
Bank statement import issues	13	1013	January 18, 2023
.csv importing bank statements	4	814	January 18, 2023
CSV file is missing "Date" column	27	6221	January 18, 2023

Importing bank statements .csv template?

Related topics