Importing an excel file from bank

Philip J. McGee's Avatar

Philip J. McGee

18 Nov, 2017 11:47 PM

When I uploaded an excel spreadsheet from my bank the columns were inserted in the wrong spaces. How can I edit them so that I can tell deposits from withdrawals?

  1. 1 Posted by -Kevin N. on 19 Nov, 2017 12:59 AM

    -Kevin N.'s Avatar

    Hi Philip,

    Using Excel, it unfortunately looks as though your bank's CSV file is poorly formatted.

    The transaction amount values (column E) are all positive values. There is a 'DR' or 'CR' designation in the adjacent column (column F) which I believe distinguishes 'Payments' from 'Deposits'.

    You'll need to edit the file in Excel (or your spreadsheet of choice) before importing it.

    An easy solution is to enter the following formula into Cell G2.
    =IF(F2="DR",-E2,E2)
    Drag it (auto-fill the formula) down the length of Column G equal to the number of transaction values.

    Doing so, will create positive and negative values down the length of Column G dependent on the text "DR" in Column F.

    Exit the spreadsheet saving the changes. Due to the restrictions of CSV files, the formula will be lost but the positive and negative values should remain.

    You should then use the Text Importer extension to import the file. Use the newly created positive and negative values (Column G) to populate the 'Amount' field of the Text Importer. If you need help with the Text Importer extension, post back and I'll try to lend a hand.

    -Kevin N. (not a member of MD support)

  2. 2 Posted by PJ McGee on 19 Nov, 2017 03:15 AM

    PJ McGee's Avatar

    Thanks Kevin.

    Sent from my iPhone

  3. 3 Posted by dwg on 19 Nov, 2017 08:13 PM

    dwg's Avatar

    I would look to see if your bank offers any other file formats. CSV is often not the easiest format to deal with as it is so generic. Formats like OFX, QFX, OFC, QIF are much easier to deal with as they are intended for financial information and Moneydance handles their import.

Reply to this discussion

Internal reply

Formatting help / Preview (switch to plain text) No formatting (switch to Markdown)

Attaching KB article:

»

Already uploaded files

  • PeaGlad_statements_1116-1117.csv 2.77 KB

Attached Files

You can attach files up to 10MB

If you don't have an account yet, we need to confirm you're human and not a machine trying to post spam.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac

Recent Discussions

17 Dec, 2017 03:37 PM
17 Dec, 2017 03:21 PM
17 Dec, 2017 03:05 PM
17 Dec, 2017 02:58 PM
17 Dec, 2017 02:55 PM