Excel tips: How to amend a CSV file without a direct bank feed

The rapid development of direct bank feeds into cloud-based accounting software has dramatically changed the way accounts can be reconciled these days.

The thought of returning to reconciliations with physical bank statements or matching transactions on a spreadsheet, fills me with horror! 

Unfortunately, I have come across situations recently where a direct bank feed isn’t available. The next best option is therefore to import a statement, but often the raw data that’s available doesn’t match the format required by the software.

Large organisations will find a software solution, but small businesses will make the adjustments manually. The upshot is that some basic excel skills and techniques are required to make suitable adjustments in these situations.

Working with a factoring company

One of my clients uses a factoring company. This means that sales invoices are generated on the clients accounting software and then uploaded to the factoring company’s portal. The client can then draw down funds in advance of customer receipts in order to aid cash flow.

Customers pay invoices in accordance with the client’s terms but payments are made to the factoring company’s bank account rather than the client’s. All of the transactions, including details of various fees, are recorded by the factoring company on a transaction report that can be downloaded from the portal. This is the nearest equivalent of a bank statement available.

In order to account for the transactions that go through the factoring company, a dummy bank account has been created in the accounting software. The transaction report needs tweaking before it can be uploaded and reconciled. This must be done mindfully with the aid of Excel’s functionality, in order to minimise opportunities for human errors. For example, if we overtype numbers or use a formula, we could end up with rounding discrepancies. 

Free Excel webinar

Learn how to present effectively in Excel from expert Deborah Ashby. To view the recorded webinar please register your details below

View webinar

Manually adjusting the raw data

An example of the raw data can be downloaded by clicking here if you would like to have a go at making some adjustments. It will take some time initially, however I have found that, once it is routine, it only takes a few minutes.

Firstly, because the report downloads as a csv file, the columns widths are all the same size regardless of their content;

and need expanding to reveal the full details:

In order to record each statement line, the client’s accounting software’s minimum requirements are the date and amount, which must be in a single column with payments showing as negative figures and income as positive ones. Details of the payee, description and reference are optional.

Step 1 – delete unneeded columns

Delete any unneeded columns, in this case A, D and I.

Step 2 – delete entries for invoices/credit

Step 2 is to delete entries for invoices and credit notes if there are any. This is because whilst the factoring company records these on the report, they are generated in the accounting software so will already be recorded in the sales ledger and must not be duplicated.

The filter function works well at this stage and, as long as you click in a cell in row 1, it will be applied to all the columns containing data:

Once the invoices have been selected, the rows can be highlighted and deleted:

Step 3 – combine the VAT with the net transaction

Combine the VAT with the net transactions it has been charged on, namely services and inter-account transfer fees. This is because direct posts to the VAT account are not possible in the accounting software, so a sensible solution is to combine them at this stage and apply a bank rule once the transactions are imported. The filter can be used to good effect again here as it reduces the chance of transactions being missed through manual error:

It is important to use Excel’s functionality to create the gross figures rather than adjusting them manually. This can be done by dragging the VAT figures into column E (as there are figures in column D that just hidden due to the filter) on the corresponding row.

Note that the pairs of services fees and their corresponding VAT are ordered in reverse. Therefore, be very careful to match up the correct cells, in this case row 2’s VAT is was row 5 and row 3’s VAT is in row 4.

Next, delete the now empty VAT rows then write a formula to calculate the gross amount in F2, and drag it down the column:

Then use copy and paste values to update column C. You will need to do this is in three sections (rows 2-5, 7-9 and 13) because of the hidden rows: 

Note that the figures in column F will alter due to the formula but that’s irrelevant as columns E & F are no longer needed and be deleted. The filter can be removed as well.

Step 4 – combine into one column

The final step is to combine the figures into a single column. As the payments need to be shown as negative figures the easiest way is to use the copy and paste special function. Type -1 into an empty cell and copy it:

Highlight all the figures in column C, then click on the Paste button so it shows the drop down menu and select paste special. This will open the paste special box, where ‘All’ should be selected from the Paste section and ‘Multiply’ from the Operation section:

Selecting OK will result in all the payments showing with minus signs. The -1 can be deleted and the receipts, which are currently in column D, can be dragged into column C.

The final adjustment is to remove the CR from D1 and change the DR in C1 to Amount:

Save the amended CSV file with a new name, so that you still have the original data in case you need to check it in the future, and upload it to the accounting software.

In summary

Technology is transforming accounting and making fundamental tasks, such as reconciliations, much quicker, easier and accurate. That said, it is vital to maintain our knowledge and skills of accounting theory and spreadsheets, as well as learn how accounting software works, so that when an automated function isn’t available a manual solution can be found.

Browse the full range of AAT study support resources here

Gill Myers is a self-employed accounts consultant. She has taught AAT qualifications since 2005 and written numerous articles and e-learning resources.