How To Build Your Import (UK)

You can import both UK and Irish clients using this spreadsheet, so you can use both guides to understand what data needs to be added.

This article will explain what you can achieve adding different levels of data, which columns are compulsory and what the purpose of each column is.

Levels of Data

Once you've imported your clients you can then add additional data manually at a later date. This may be time consuming though as each client would have to be updated individually so we recommend that you add as much as you are able to during the initial import.

Level 1 - Just get your clients in and portal registered

Fill in columns:

A, C, F, I, N, O, R, X

(This will add just a Main Contact, you have the option to add Secondary Contact Details too)

Level 2 - Get your clients portal registered and Task List set up

Fill in columns:

Add CH Accounts, CT600, Confirmation Statements, Self Assessment Accounts and Returns:

A, C, F, I, N, O, R, X, A, AA, AB

(This will add just a Main Contact, you have the option to add Secondary Contact Details too)

Add VAT Tasks

CG, CH.

Add PAYE Tasks:

CL, CM, CO

Add P11D Tasks:

Add CIS Tasks:

Add Auto Enrolment Tasks:

AV, CZ

Add Bookkeeping Tasks in line with other services:

SA800 Accounts and Returns:

EL, EN

Level 3 - Get your clients portal registered, Task List set up make sure your client’s missing Data is accurate

Complete Level 2 then:

For Agent Authorisations:

AC, AU, BL, CC, CI, CT, CU

Client has provided valid ID:

AD, AV, BM, CD

Tax Reference Numbers (including NI and PSS numbers:

J, W, Y, AO, AQ, BF, BH, BW BY, CE, CJ, CK, EN

Authorisation Codes:

Level 4 - All of the above and everything else

Column Breakdown

This section of the article explains what each column on the spreadsheet does

Staff & Partner Users

Column A - Staff User Responsible - Compulsory Column

This will be the Staff Member named as the Client Manager, this user must have a login for the import to work correctly. If the user hasn't been added or the cell is blank then the client manager will be the user performing the import. The Client Manager will receive Notifications relating to that client.

Column B - Partner Name - Reference Only

This will be the Staff Member named as the Client Partner, this user must have a login to be added as the Partner. This field is for reference, you can filter your Task List by Partner.

Business Details

Column C - Client Name - Compulsory Column

This is the name of the Limited Company, Partnership, Self Assessment Client etc. If the client is a Self Assessment client this should still be entered. There should be no blank cells in this column.

Column D - Trading As - Reference Only

This field is used to record the Trading As name, and this name will be displayed alongside the Client Name on the Client List. On the client file for a Limited Company, Other or LLP this name will be stored under Company Details > Company Trading As. On a Partnership, Trust or Self Assessment client file it will be stored under Business Details > Trading As and on a Charity Client File it will be stored under Charity Details > Company Trading As. This field should only be used if the Trading As and Client Name differ.

Column E - Nature of Business - Reference Only

This field is for reference, you could enter something like Estate Agent, Window Cleaner etc. This is stored on the client file for a Limited Company, Other or LLP this will be stored under Company Details > Nature of Business. On a Partnership, Trust or Self Assessment client file it will be stored under Business Details > Nature of Business and on a Charity Client File it will be stored under Charity Details > Nature of Business.

Column F - Client Type - Compulsory Column

The options are: Private Limited Company or LTD, Irish Limited Company or Irish LTD, Public Limited Company, Limited Liability Partnership or LLP, Self Assessment or SA, Irish Self Assessment or Irish SA, Trust, Charity, Other, Irish Partnership and Irish Other. If the client is a Sole Trader they should be marked as SA and an Accounts Period End should be recorded under Column Z (Sole Trader Accounts Period End).

For UK Limited Companies the following tasks will be generated automatically:

Accounts Preparation

Companies House Submission

CT600 Submission

Confirmation Statement Submission

Column G - Date of Trading - Reference Only

This field is used to record the day the client first started trading. This is stored on the client file for a Limited Company, Other or LLP this will be stored under Company Details > Date of Trading. On a Partnership, Trust or Self Assessment client file it will be stored under Business Details > Date of Trading and on a Charity Client File it will be stored under Charity Details > Date of Trading.

Accounting System

Column H - Accounts Production Software - Reference Only

This is stored on the client file under Other Details > Accounting System. The standard options are Cashbook, Assisted, Sage, Xero, QuickBooks, FreeAgent, VT Transaction+, Excel, Paper and Other.

You can add other options prior to import by navigating to Settings > Account Settings > Client Settings > Accounting System Options.

Company Details

Column I - Company Number - Compulsory Column

If a UK company number is entered here then this will populate the CH Year End, HMRC Year End, CH Accounts Next Due date, CT600 Due date, Confirmation Statement Date, Confirmation Statement Due date, Company Status, Incorporation Date, Registered Address and SIC Code. This will only work for UK Limited Companies.

Column J - Company UTR - Missing Information Trigger

To be entered for the Client Types Limited Companies & LLP. If left blank a request for this information will be sent to your client via the Registration or Missing Information email.

Column K - Companies House Authorisation Code - Missing Information Trigger

To be entered for the UK Limited Company Client Types. If left blank a request for this information will be sent to your client via the Registration or Missing Information email.

Column L - HMRC Year End Date - Task Related

Used as the Period End for CT600 Return Tasks - This only needs to be filled in if for some reason this date differs from the CH Year End Date. Format dd/mm/yyyy.

Column M - CT Online (Y/N) - Missing Information Trigger

This information will be stored on the client file under Agent Authorisation. Indicates whether you have a 64-8 for Corporation Tax filing. If left blank a request for this information will be sent to your client via the Registration or Missing Information email.

Main Contact / Director

Columns N & O - Contact 1 First Name & Last Name - Compulsory Columns

If a cell in either of these columns is blank, the entire row will fail to upload.

You must add a Main Contact First Name for each client.

Column P - Terms of Engagement Signed Date - Terms

If a date is added it will populate Terms Signed under Main Contact. If this field is populated the client will not need to sign Registration Terms if you have set these up, they will still need to sign an LoE. Format dd/mm/yyyy.

Column Q - Date of Birth - Reference Only

This information is stored under Main Contact > Date of Birth. The format is dd/mm/yyyy.

Column R - Email 1 - Portal Registration

Field populated is under Main Contact > Email.

This email address will be used to Portal Register the contact.

Column S - Email 2 - Reference Only

Field populated is under Main Contact > Email. This second address will be added with a comma after any address added to Column R.

Column T - Postal Address - Reference Only

The address will be stored under Main Contact > Address in the same format it is entered into this cell.

Column U - Telephone Number - Reference Only

The number will be stored for reference under Main Contact > Telephone.

Column V - Mobile Number - Used for SMS

The number will be stored under Main Contact > Mobile. SMS can only be sent to UK and ROI numbers.

Column W - UK - NI Number - Missing Information Trigger

The number will be stored under Main Contact > NI Number. If left blank a request for this information will be sent to your client via the Registration or Missing Information email.

Column X - Self Assessment Client (Y/N) - Creates SA Client File

If the contact appears more than once on the import a Y must only be entered for them once or multiple SA client files will be created for them.

Add a Y to create a Self Assessment client file for this contact.

If the contact appears more than once on the import a Y must only be entered for them once or multiple SA client files will be created for them.

Column Y - Personal UTR No. - Missing Information Trigger

The number will be stored under Main Contact > Personal UTR. If left blank a request for this information will be sent to your client via the Registration or Missing Information email.

Column Z - Sole Trader Accounts Period End - Drives Tasks

If a date is added this will turn on the Accounts Service for this client. Format dd/mm/yyyy.

Column AA - SA Tax Year - Drives Tasks

This should always be entered if you perform a Self Assessment for this contact, the format is yyyy/yy (eg 2019/20).

Where no period end has been entered for the Self Assessment, the period end will be assumed as the 5th April for the Tax Year entered.

Column AB - Next SA Return Due - Drives Tasks

The format for Next SA Return Due is dd/mm/yyyy. This deadline will be applied to the SA task for this client.

Column AC - SA Online (Y/N) - Missing Information Trigger

This information will be stored on the client file under Agent Authorisation. Add a Y if you have a current 64-8 for this client’s Self Assessment. If N is entered or the cell is left blank, a request for this information will be sent to your client via the Registration or Missing Information email.

Column AD - ID Received (Y/N) - Missing Information Trigger

This information will be stored under Main Contact > ID Verified & Address Verified. Add a Y if you have received ID and proof of address from your client. If N is entered or the cell is left blank, a request for this information will be sent to your client via the Registration or Missing Information email.

Second Contact / Director

If you carry out a Self Assessment for a Secondary Contact, on their Self Assessment client file their details will appear under Main Contact.

Column AF & AG - Contact 2 First Name & Last Name

If you are adding a secondary contact both name cells need to be filled in.

Column AH - Terms of Engagement Signed Date - Terms

If a date is added it will populate Terms Signed under Secondary Contact. If this field is populated the client will not need to sign Registration Terms if you have set these up, they will still need to sign an LoE. Format dd/mm/yyyy.

Column AI - Date of Birth - Reference Only

This information is stored for reference under Secondary Contact > Date of Birth. The format is dd/mm/yyyy.

Column AJ - Email 1 - Required for Client Portal Registration

Field populated is under Main Contact > Email.

This email address will be used to Portal Register the contact.

Column AK - Email 2 - Reference Only

Field populated is under Secondary Contact > Email. This second address will be added with a comma after any address added to Column AJ.

Column AL - Postal Address - Reference Only

The address will appear under Secondary Contact > Address in the same format it is entered into this cell.

Column AM - Telephone Number - Reference Only

The number will be stored for reference under Secondary Contact > Telephone.

Column AN - Mobile Number - Used for SMS

The number will be stored under Secondary Contact > Mobile. SMS can only be sent to UK and ROI numbers.

Column AO - NI Number - Missing Information Trigger

The number will be stored under Secondary Contact > NI Number. If left blank a request for this information will be sent to your client via the Registration or Missing Information email.

Column AP - Self Assessment Client (Y/N) - Creates SA Client File

If the contact appears more than once on the import a Y must only be entered for them once or multiple SA client files will be created for them.

Add a Y to create a Self Assessment client file for this contact.

If the contact appears more than once on the import a Y must only be entered for them once or multiple SA client files will be created for them.

Column AQ - Personal UTR No. - Missing Information Trigger

The number will be stored under Secondary Contact > Personal UTR. If left blank a request for this information will be sent to your client via the Registration or Missing Information email.

Column AR - Sole Trader Accounts Period End - Drives Tasks

If a date is added this will turn on the Accounts Service for this client. Format dd/mm/yyyy.

Column AS - SA Tax Year - Drives Tasks

This should always be entered if you perform a Self Assessment for this contact, the format is yyyy/yy (eg 2019/20).

Where no period end has been entered for the Self Assessment, the period end will be assumed as the 5th April for the Tax Year entered.

Column AT - Next SA Return Due - Drives Tasks

The format for Next SA Return Due is dd/mm/yyyy. This deadline will be applied to the SA task for this client.

Column AU - SA Online (Y/N) - Missing Information Trigger

Column AV - ID Received (Y/N) - Missing Information Trigger

This information will be stored under Secondary Contact > ID Verified & Address Verified. Add a Y if you have received ID and proof of address from your client. If N is entered or the cell is left blank, a request for this information will be sent to your client via the Registration or Missing Information email.

Third Contact / Director & Forth Contact / Director

Columns AW-CD - Third and Fourth Contacts

The same rules apply for entering personal details for third and forth contacts, these will just be added as links to the client file for the entity specified in Column C - Client Name. If N is entered for Self Assessment Y/N, then a client file will be created with the Client Type SA, but no services will be turned on for this client.

VAT Details

Column CE - VAT Number - Reference Only

This information will be stored for reference on the Client File under VAT Details > VAT Number.

Column CF - VAT Scheme - Reference Only

This information will be stored for reference on the Client File under VAT Details.

Column CG - VAT Quarter End - Drives Tasks

This information will be stored on the client file under VAT Details > VAT Period End. The format for this is dd/mm/yyyy and this will be used as the period end for the VAT Tasks.

Column CH - VAT Next Return Due - Drives Tasks

This information will be stored on the client file under VAT Details > VAT Next Return Due.The format for this is dd/mm/yyyy and this is the deadline used for the VAT tasks.

Column CI - VAT Online - Missing Information Trigger

This information will be stored on the client file under Agent Authorisation. Add a Y if you have a current 64-8 for this client’s VAT. If N is entered or the cell is left blank, a request for this information will be sent to your client via the Registration or Missing Information email.

PAYE & CIS

Column CJ - PAYE Employers Reference - Missing Information Trigger

This information will be stored on the client file under PAYE Details > Employers Reference. If left blank a request for this information will be sent to your client via the Registration or Missing Information email.

Column CK - PAYE Accounts Office Reference - Missing Information Trigger

This information will be stored on the client file under PAYE Details > Accounts Office Reference. If left blank a request for this information will be sent to your client via the Registration or Missing Information email.

Column CL - PAYE Year Required - Reference Only

This information will be stored for reference on the client file under PAYE Details > Year Required.

Column CM - PAYE Payslip Frequency - Drives Tasks

This information will be stored on the client file under PAYE Details > PAYE Frequency. Your Payroll Task will be created in line with this frequency.

Column CN - PAYE First Pay Date - Reference Only

This information will be stored for reference on the client file under PAYE Details > First Pay Date. Format dd/mm/yyyy.

Column CO - PAYE RTI Deadline Due - Drives Tasks

This information will be stored on the client file under PAYE Details > RTI Deadline. This is the deadline used for your Payroll Task. Format dd/mm/yyyy.

Column CP - Next P11D Return Due - Drives Tasks

This information will be stored on the client file under PAYE Details > Next P11D Return Due. This is the deadline used for your P11D Task. Format dd/mm/yyyy.

Column CQ - CIS Contractor (Y/N) - Reference Only

This information will be stored for reference on the client file under CIS Details > CIS Contractor.

Column CR - CIS Subcontractor (Y/N) - Reference Only

This information will be stored for reference on the client file under CIS Details > CIS Subcontractor.

Column CS - CIS Deadline Due - Drives Tasks

This information will be stored on the client file under CIS Details > CIS Deadline. This is the deadline used for your CIS Task. Format dd/mm/yyyy.

Column CT - PAYE Online Y/N - Missing Information Trigger

This information will be stored on the client file under Agent Authorisation. Add a Y if you have a current 64-8 for this client’s PAYE. If N is entered or the cell is left blank, a request for this information will be sent to your client via the Registration or Missing Information email.

Column CU - CIS Online Y/N - Missing Information Trigger

This information will be stored on the client file under Agent Authorisation. Add a Y if you have a current 64-8 for this client’s CIS. If N is entered or the cell is left blank, a request for this information will be sent to your client via the Registration or Missing Information email.

Auto Enrolment

Column CV - Auto-Enrolment Staging Date - Reference Only

This information will be stored on the client file under PAYE Details > Auto Enrolment Staging. Format dd/mm/yyyy.

Column CW - Postponement Date - Reference Only

This information will be stored on the client file under PAYE Details > Postponement Date. Format dd/mm/yyyy.

Column CX - Pension Provider - Reference Only

This information will be stored for reference on the client file under PAYE Details > Pension Provider.

Column CY - Pension ID - Reference Only

This information will be stored for reference on the client file under PAYE Details > Pension ID.

Column CZ - Declaration of Compliance Due Date - Drives Tasks

This information will be stored on the client file under PAYE Details > Declaration of Compliance Due. This will be used as the deadline for your Auto Enrolment Task. Format dd/mm/yyyy.

Column DA - Pension Deadline Due Date

Staff Tasks

Columns DC - DO - Staff Tasks

The Staff Member entered here will need to have a user account for the import to assign the task correctly. Enter a Staff name for Bookkeeping to turn on this service. Other services are turned on when deadlines are entered on other sections of the import.

Client Notes

Column DP - Notes - Reference Only

This information is stored on the client file under Internal > Notes.

Additional Information

Column DR - Previous Accountant - Reference Only

This information is stored on the client file under Previous Accountant > Accountant Details.

Column DS - Profession - Reference Only

This information is stored on the client file under Other Details > Profession.

Column DT - Referred By - Reference Only

This information is stored on the client file under Other Details > Referred By. This dropdown can be edited by navigating to Settings > Account Settings > Client Settings > Referred by Options.

Column DU - Initial Contact (Date) - Reference Only

This information is stored on the client file under Other Details > Initial Contact, the format for this field is dd/mm/yyyy.

Column DV - Quote Email Sent (Date) - Reference Only

This information is stored on the client file under Other Details > Proposal Email Sent, the format for this field is dd/mm/yyyy.

Column DW - Internal Reference - Reference Only

This information is stored on the client file under Internal > Internal Reference.

Column DX - Registration Fee Paid (Y/N) - Defines if the Client is a Prospect

If you add an N then the client will be imported to your Prospect List. If you leave the cell black or enter a Y they will be imported to your Client List.

Column DY - 64-8 Registration - Reference Only

This information is stored on the client file under Registration > 64-8 Registration. Format dd/mm/yyyy.

Column DZ - Company Turnover - Reference Only

This is stored on the client file for a Limited Company, Other or LLP this will be stored under Company Details > Turnover. On a Partnership, Trust or Self Assessment client file it will be stored under Business Details > Turnover and on a Charity Client File it will be stored under Charity Details > Turnover.

Column EA - IR 35 Notes - Reference Only

This information is stored on the client file under Income Details > IR35 Notes.

Column EB - Allocated Office - Reference Only

This information is stored on the client file under Internal Details > Allocated Office.

Column EC - Client Links - Reference Only

These will be automatically added where contacts relate to more than one client file. You can add additional links here for where companies are linked but not by a mutual contact. This information is stored on the client file under the Client Links tab.

Services

Columns ED - EI - Services (add note about ow Confirmation Statement is pointless)

Add a Y here to turn on some standard services that do not generate Tasks, for example, Investigation Insurance (this is actually Fee Protection Service). Registered Address, Bill Payment, Advice (Consultation/Advice).

If you leave the cell blank the import will take no action, so you do not need to add an N for clients you don’t provide these services to.

When you import a Limited Company the service Confirmation Statement is turned on as standard. You only need to add a Y to Column EH for Limited Liability Partnerships and Charities.

Column EJ - Monthly Charge (Fee Amount) - Reference and Fees

This amount will be added on the client file under Services Required > Monthly Charge. This will appear in Proposal Emails and under Tools > Fee Projections.

Column EK - Annual Charge (Fee Amount) - Reference and Fees

This amount will be added on the client file under Services Required > Annual Charge. This will appear in Proposal Emails and under Tools > Fee Projections.

Partnership Details

Column EL - Next Return Due (Partnership) - Drives Tasks

Adding this date will generate the SA800 Task. The format for this is dd/mm/yyyy and this is the deadline used for the SA800 Tasks.

Column EM - Accounts Period End (Partnership) - Drives Tasks

This date will be used as the period end for the SA800 Accounts Task. The format for this is dd/mm/yyyy.

Column EN - Partnership UTR - Missing Information Trigger

If left blank a request for this information will be sent to your client via the Registration or Missing Information email.

Search

Welcome to our Support