Excel Script - Capital On Tap

Are there any Excel experts on here?
When I download my statement in CSV i have to edit the figure in each transaction to remove the “-” from credits and add “-” for debits for it to work properly in QF banking. Is there any way I can have this done automatically to save some time?


Easiest way is to hold ctrl and select each credit and place in a new column then use find and replace to remove the minus figure.

When you upload the csv tick the box that says my spreadsheet has two columns for debits and credits and map the correct ones before uploading.

If you open the file in excel you could put a formula in a spare column saying


(or whatever the amount cell is on this line) to generate the value with the opposite sign. Drag the corner handle to fill down the whole spreadsheet, then save it back to CSV.

1 Like

In addition to Paul and Ian, you could record a macro in Excel/OpenOffice/LibreOffice the first time you change your csv file. Each time you want to change the next csv file from your bank you only need to run the macro by clicking a button or typing a 2 or 3 key combination.

1 Like

Why not just change the column headers, or map the columns when importing?

I read the original post as saying the bank CSV has one column for the amount with plus and minus numbers, but the sign is reversed from what QuickFile expects.

My Capital On Tap CSV’s have 2 columns. Never actually imported one though, just used them for reconciling.

Hi @Jsmith9114

Have any of the suggestions made by the other users helped or are you still having problems?

This topic was automatically closed after 4 days. New replies are no longer allowed.