when using Import bank statement, all my transactions go into amount paid, nothing into amount received and no + or - on any amount and no red numbers. All my bank rules work fine, but i have to go into each sale and change it to Received.
Not sure if i doing some setup wrongly or is this what i have to do ?
any advice appreciated.
This could be due to the convention your bank is using for reporting transactions. What format are you importing? Also, are all these transactions receipts (in the real world), or are they mixed? Have you used a text editor to examine the bank statement file? What do its contents look like?
If you are using CSV-formatted exports and your bank offers any other choice, use it. There is no standard for CSV bank statement exports, so banks basically put anything they want, in whatever position they want, in whatever format they want.
using qif. All transactions are mixed, payments and received.
Just looked in ms word - example
D24/09/20
T-364.11
N000000000000
PHiTech gas L6006958507 JOHON
values are nothing for + and - for payments
Well, the excerpt you showed should be interpreted as a payment, subject to some items you did not display. So that particular bit of information was not helpful in determining why receipts also show up as payments.
The QIF specification from Intuit was last updated in 1997. Their own products after 2005 no longer even support it. It’s better than CSV, but only just barely. If your bank offers other formats, try them.
Meanwhile, it is better to examine monetary exchange files with a text editor rather than Word, as Word may impose its own interpretations on the file. Can you post a screen shot from a text editor of a portion of the file that includes both payments and receipts? (The transcription you included is missing some characters that could explain the problem.)
bank only offers csv or qif. If notepad is a text editor, then it shows exactly the same as I posted earlier.
I was thinking that i may have changed some setting somewhere ( maybe hoping )
Please post a screenshot from Notepad showing including transactions of both types. There is no setting you can change. But if the bank’s formatting is wrong, I may be able to tell you how to edit the export file to fix it.
!Type:Bank
D30/09/20
T0.09
N000000000000
PINTEREST
^
D30/09/20
T151.59
N000000000000
PSQUARE AU PTY LT John xxxxxx
^
D29/09/20
T75.00
N000000000000
PE PICKETT 177- 72 minnamurra E
^
D29/09/20
T428.54
N000000000000
PSQUARE AU PTY LT John xxxxxx
^
D29/09/20
T-528.00
N000000000000
PV6021 28/09 xxxxxxx AUSTRALIA CAMPBELLTO 74940520272
^
small sample of what you asked for. Hope it helps
Thanks. That is what I needed.
Technically, the format matches the 1997 QIF specification. Unfortunately, that specification does not actually tell whether the lines beginning with T, which are amounts, should include positive or negative signs. Nor does it tell which would be payments and which receipts. (Remember, we are dealing with a format its developer has considered obsolete and ignored for 16 years, one which was never intended for use by outsiders in the first place.)
However, the published writings of various practitioners who use the QIF format all agree that a (-) sign after the T denotes a payment. Some say receipts should have a (+) after the T, while others say the (+) is optional. Probably, the truth depends on the software reading the file.
So I would try manually editing this excerpt of the file by inserting (+) signs in the receipt entries, like this:
T+0.09 or T+151.59
Don’t make any other changes, don’t insert any spaces, and especially do not remove the ^ symbols.
If that imports correctly, it will mean that Manager’s QIF parser expects the (+) symbol for receipts. If you want to continue using bank imports, you will have to edit the files first.
I had wondered about that. Just gave it a go. Very carefully inputted + 's where they needed to go. Saved the file. Imported, " The file you are trying to import is invalid ". there goes that theory.
thanks for assistance. looks like I do it the slow way.
That’s a strange result. Are you sure, when you saved the edited file, you saved it with its .qif extension? It would be normal for a text editor to convert the file to its own extension.
If that was not the issue, it’s worth trying CSV. Follow the instructions in this Guide: Import bank statements | Manager. Be sure to include the column headings. And be sure to save the file as a .csv file, not a spreadsheet. Most spreadsheet software will open CSV files, but upon saving will convert to their own formats and extensions. the problem with CSV files is that you will have to manually edit the payee fields.
you were right. i looked at the .qif and thought it was saving correctly, but saved as xxx.qif.txt
am about to download another statement shortly. will test again
I edited the qif file and put the +'s in the correct places. Imported the file, which did import, but the outcome was the same. All entries went into the Amount Paid column still.
I have finished now, my head is frayed - done over a 1000 entries today. not thinking straight anymore.
thanks again. will reread tomorrow
@gavinshad, here is a notepad copy of a qif file which imports correctly.
The only differences between yours and mine is that the amount is on line 3, not line 2 and it doesn’t have those N000000000 entries. No appearance of a “+”.
The order of Date, Description, Amount makes sense as it aligns with the Manager screen order.
Which gives this output
So I suggest, trying your notebook file with some transactions resorted and see the results.
The QIF standard allows detail lines to be in any order, so that should not matter. The parser figures out what they are based on the leading letter. Lines not included for a particular transaction (there are 33 possible line types) are simply assumed to be blank.
Lines beginning with N indicate a cheque number, or can include such information as DRAFT, ATM, etc. They are informational.
There are two other differences in content that might matter:
- @gavinshad’s example includes P lines, which are for Payee or Description. @Brucanna’s example uses M lines instead. M lines are designated as Memo lines for similar information. The standard does not specify what should be on either type very clearly. M lines are used less frequently, but apparently Manager’s parser can decipher them.
- @Brucanna’s example includes L lines. L signifies Category. Again, the standard is silent on what this is supposed to be used for, especially on whether it might affect the positive or negative aspect of the amount on the T line.
These differences illustrate that two banks have implemented the QIF standard differently for their own purposes, based on their own internal software needs. Yet both are compliant to the standard.
@gavinshad, it would be interesting to edit a QIF file from your bank, first by changing your P lines to M lines, next by adding L lines. The results of those experiments would shed light on the capabilities and expectations of Manager’s QIF parser.
Thanks very much guys. I will try editing/adding the Lcredit Lpayment line in notepad next time I’m importing from bank. Still very tedious when doing hundreds, if it works. will try to let u know the outcome.
For your experiment, edit just a few. Or create a truncated file. (Manager should detect any duplicates and refuse to import them.) And try this first in a test business. If you figure out something that works, you could then work on a script in your spreadsheet program to do the conversion automatically.