A client used Scottish Pacific for invoice factoring, and I created these support notes for them to help them download the data, and import it into Xero.

If you are using another online accounting solution these support notes may help too. I hope you find them useful. Please follow the steps below. There may be updates, which I am unaware of, and I am unlikely to be able to help you with

Import bank statements data from Scottish Pacific site into Xero

This downloads into an excel spreadsheet.

Delete rows that you don’t need.

Delete column E.

Now enter a header in column E called “Amount”

Click in cell E2 then +D2-C2 press enter.

Double click on the cross to drag the formula down.

Copy column and paste back as values.

This is now what you excel spreadsheet looks like:

Every day I believe STAFF at Scottish Pacific sends you an email that splits down the Customer Receipt figure? To quickly identify the Customer Receipt details use the Filter Sort.

To apply the Filter Sort

  1. Click in the top corner cell to select all cells.
  2. Click Sort and Filter
  3. Click Filter

Once you have done this tiny downward arrows appear at the top of the row.

  1. Click on the tiny arrow to the right of the description.
  2. Click on Select All, so there is nothing there.
  3. Select Customer Receipts – now you are just seeing Customer receipts.

It should look something like this:

Now go into your emails and check the details for each one of them. I expect that for many of the customer receipts they represent a split of numerous transactions. Gather all the emails – it might be easy to print them out.

Before the next stage, we need to get rid of the sort that you just applied.

  1. Click on the little filter icon beside the word description.
  2. Click select all. We can now see the full listing.

Let’s pretend that 49289.25 is made up of the following:

10 000

20 000

19289.25

49289.25

Three lines now need to be inserted into the excel spreadsheet.

  1. Select three rows just below the first Customer Receipts line.
  2. Right-click to bring up a context-sensitive menu and click on the word Insert. Three blank rows will now be inserted.

Now in the enter column enter the amounts.

Copy the date and description down.

Be careful the date remains accurate. I have coloured green what is now the new data. The original summary line which has now been replaced with the details should be deleted.

You will now repeat this for every day. It will make reconciling much easier.

Once you are done here, you can save the file.

File > Save As > DESKTOP > save it as a CSV type

Keep clicking Yes, then close then say Don’t save.

This is now what you