Introduction

This guide provides information on how to compare RBS bank and Xero transactions to try to make sure that, for each bank account,  the balance in Xero (Speakeasy’s accounting package) match with the balance in RBS. This is vitally important for year end accounting purposes, but should also be performed regularly throughout the year to fix and differences as soon as possible (as details of recent transactions are more easily remembered by staff!).

Instructions

There are four steps to performing the comparison of RBS bank and Xero transactions:

1. Export current account transactions from the RBS web site
2. Export current current transactions from the Xero web site
3. Execute the PowerShell script to compare the two sources of data
4. Review the output of the script and make corrections (usually to reconciliations) in Xero

Steps 2-4 can be repeated after performing any corrections in Xero, until balances match. Unlike Xero where we are making adjustments, the bank transactions don’t change – so we only need to perform step 1 when we have a lot of new transactions to check. It is recommended that Step 1 is only be re-performed occasionally, e.g. once or twice per month.

The whole process should be repeated for the Speakeasy reserve account, to make sure its balance in Xero matches that in RBS.

Export Account Transactions from RBS

1. Login to the RBS website from a laptop. Note: for obvious reasons, access to RBS is severely restricted. Gill Pearl has the login details so she may need to perform the authentication process on a user’s behalf, before that user can follow all the other steps.
2. Once logged in, navigate to “Statements and Transactions”
3. Click “View Transactions”
4. In the “Select an account”, make sure the current account (Business current) is selected
5. Click the “Search” link. Modify the “time frame” dropdown list to display “Date range” and specify the from and to dates using the controls provided. Typically set the range to 1st of Jan of the current year and yesterday’s date – don’t use the current day as more transactions may yet arrive.
6. Click the “Search” button. Transactions are listed newest first – don’t change the order before downloading!!
7. Click the “Download” link. In the “choose a format” box, select “Excel & Text (CSV)” then click the “Download” button.
8. A .csv file will be downloaded to the user’s “Downloads” folder. The name will comprise he account number and the current date. It is recommended that this file be copied or moved to the following Teams folder:
   Bury Speakeasy\Speakeasy – Operational Administration – Operational Administration\Finance\Bank Statements\{currentYear}
   and the name changed so that it is easier to understand – e.g use CurrentAccountRBSTransactionsYTD.csv      (YTD = year to date)
9. Try not to leave multiple copies of transaction files in your downloads directory as these represent sensitive information. Having copied the file to the folder specified above, delete any other copies from the downloads folder.

To download the reserve account transactions, simply repeat the above process but in step 4, select the reserve account instead of the current account. Also, in step 8, specify the file name as “ReserveAccountRBSTransactionsYTD.csv”

Export Account Transactions from Xero

1. A user should login to Xero from a laptop using their Xero login details – these are available in BitWarden. The user may need their mobile phone to authenticate the login.
2. From Xero, navigate to “Accounting…Reports…Trial Balance”.
3. In the date field, specify “Custom Date”, then specify the from date using the controls provided. Typically set the start date to 1st of Jan of the current year.
4. Click the “Update” button. then click on the Current Account
5. Locate the line named “Current Account” and click on the link named “Debit – Year to date”.
6.  Modify the Date range in the screen that appears so that the end date is yesterday’s date (to match the RBS export performed earlier). Click “Update”. Scroll through the transactions to confirm that they cover the required date range.
7. Click the Export button towards the bottom right-hand side of the screen, then select “Excel”.
8. An Excel  file (of type .xlsx) will be downloaded to the user’s “Downloads” folder. It is recommended that this file be immediately copied or moved to the following Teams folder:
   Bury Speakeasy\Speakeasy – Operational Administration – Operational Administration\Finance\Bank Statements\{currentYear}
   and the name changed so that it is easier to understand – e.g use CurrentAccountXeroTransactionsYTD.xlsx      (YTD = year to date)
9. Try not to leave multiple copies of transaction files in your downloads directory as these represent sensitive information. Having copied the file to the folder specified above, delete any other copies from the downloads folder.

To download the reserve account transactions, simply repeat the above process but in step 5, select the line named “Reserve Account” instead of “Current Account”. Also, in step 8, specify the file name as “ReserveAccountXeroTransactionsYTD.xlsx”

Executing the PowerShell Script

To launch the PowerShell script, double-click on the XXX icon on the desktop. If this cannot be found, ask the Speakeasy IT administrator to provide an icon.

The script first prompts the user for a CSV file containing RBS account transactions which have been exported from the RBS account software. Specify the relevant CSV file, e.g.:

• if the comparing the current account balances , use: CurrentAccountRBSTransactionsYTD.csv
  
• if the comparing reserve account, use balances, use: ReserveAccountRBSTransactionsYTD.csv

The script then prompts for an Excel spreadsheet containing Xero account transactions which have been exported from the Xero accountancy package. Specify the relevant .xlsx file, e.g.:

• if the comparing the current account balances , use: CurrentAccountRBSTransactionsYTD.xlsx
  
• if the comparing reserve account, use balances, use: ReserveAccountRBSTransactionsYTD.xlsx

The script file will then load the contents of these two files into a database and perform the comparison. It will then display an Excel spreadsheet with the results of that comparison.

Reviewing the Output of the PowerShell Script

The excel spreadsheet contains four sheets which are described below:

1. Transaction Differences – this sheet shows the days on which the either the number of transactions or the total value of credit transactions or the total value of debit transactions differs between RBS and Xero. Any difference can usually be attributed to one or two transactions that have been incorrectly reconciled in Xero. By comparing the data in sheets 2 and 3 for each date listed on this sheet, it should be possible to identify the specific different Xero transactions.
2. Differing Bank Transactions – this sheet shows the bank transactions that took place on the days which had differences – a user can apply a date filter to look at a specific day
3. Differing Xero Transactions – this sheet shows the Xero transactions that were found on the days which had differences – a user can apply a date filter to look at a specific day
4. Daily Transaction Summary – this sheet has a similar output to sheet 1, but lists the transactions on every day, not just those where transactions differ. It can be used to see how the Xero balance gradually drifts away from the bank balance and the days on which there is no drift