Exporting Data from Spreadsheets as a CSV File (5:30)

We are very grateful to our clients for their comments and suggestions, without which these guides would not be possible.
If you have a suggestion to improve this guide or its associated video, please email tracey@ftax.co.uk

Exporting Data from Spreadsheets as a CSV File - Step by Step Instructions

This guide covers how to import VAT data into Ftax MTD forms from your own spreadsheets, or from Sage or other accountancy packages by importing data in CSV format which is the file format commonly used in the banking and financial services industry. Most accountancy packages and bank software can output files in CSV format; the files having the extension .csv (compared with .xls for a spreadsheet or .doc for a document).

This guide is broken down into the following steps:

1. Create a new worksheet in your workbook
2. Link the relevant fields in your VAT records to the new sheet
3. Make sure you have formatted the sheet properly and that all calculations are correct
4. Save the new sheet in CSV format and in a known location
5. Your data is now ready to be imported into the Ftax MTD VAT form
6. Troubleshooting and generating a Nil Return

1. Create a new worksheet in your workbook

A workbook is a single file containing several different types of related information. Data is often held in separate sections known as worksheets. Data from one worksheet can be used to make calculations in another sheet. As there is no real limit to the number of sheets, a company's accounts may be held across a number of sheets, all of which can be held in the same workbook. 

To create a new worksheet in your company's workbook, click the new sheet icon to the right of the tabs at the bottom of your most recent worksheet. The example below uses an OpenOffice Calc sheet, but it can apply to other providers such as Excel, Google Sheets and LibreOffice:

2. Link the relevant fields in your VAT records to the new sheet

You should now link the relevant fields in your VAT record sheet with the fields in your new worksheet. Go to the field in the new sheet where you want the first value to appear and type an equals sign. Now go to the relevant field in your own spreadsheet and press enter. Go back to the new sheet and you will see that the two sheets are now linked. If you make changes in your VAT record sheet, this will automatically be reflected in the new sheet. Repeat this process with fields 2, 4, 6, 7, 8 and 9. If you know that the value of a field will always be nil, you can leave it blank. Your new sheet could look like this:

Note that the value 4440 at location 1A has been linked to a field in another worksheet, in this case the field is C4 and sheet is called 'Test Spreadsheet02'. The location of the field and the name of the sheet can be seen in the formula bar, highlighted in the above illustration.

Fields 3 and 5 are calculations and must be correct for your form to submit successfully. These fields can be linked from your VAT Record sheet or can be done as calculations within the new sheet itself. To make calculation in the new sheet, note that value three is the sum of values one and two. Value five is the difference between values three and four, including pence.

Please note that you must ensure that there is no data in any field to the RIGHT of the nine values.

To avoid confusion, you may wish to create a series of labels for the nine values which are required for the VAT return. This is entirely optional. If you wish, you can simply link the nine values from the VAT record sheet to a column in a new worksheet. In either case, there should be no data to the right of the values in the new worksheet. If you decide that labels would be useful, these should be placed in a column to the left of the values themselves. The values are defined in the Ftax form, which is based on HMRC guidelines, as:

1. VAT due in the period on sales and other outputs
2. VAT due in the period on acquisitions from other member states of the EU
3. Total VAT due
4. VAT reclaimed in the period on purchases and other inputs (including acquisitions from the EU)
5. Net VAT to pay to HMRC or reclaim
6. Total value of sales and all other outputs excluding any VAT
7. Total value of purchases and all other inputs excluding any VAT
8. Total value of all supplies of goods and related costs, excluding any VAT, to other EU member states
9. Total value of all acquisitions of goods and related costs, excluding any VAT, from other EU member states

Having created any labels, titles and dates which you feel are necessary, follow the procedure at the beginning of this section: go to the cell where you wish the data for box one to appear, type an equals sign and go to the relevant field in your VAT sheet. Press enter and go back to the new VAT sheet where you will now see that the relevant cells have been linked in both sheets. You can put any wording, data or calculations to the LEFT of the nine values, but nothing to the RIGHT. Below is a sheet with labels and dates. Note that all fields to the right of the nine values are empty:

3. Make sure you have formatted the sheet properly and that all calculations are correct

Check the sheet to ensure that the formatting and calculations are correct. Boxes 6 to 9 do not need to include pence but the Ftax form will remove them if they are present. Ensure that there is no data to the right of any of the VAT report values.

4. Save the new sheet in CSV format and in a known location

Finally, you should save your sheet in CSV format. Click 'Save As'  from the File menu and choose CSV from the list of options:

Save the file in a known location. It is now ready to be imported into the Ftax MTD form. Please note that only the page you have been working on will be saved in CSV format. The remainder of your workbook, including the VAT page which provided the source of your data, will not be saved and made available for export.

IMPORTANT: Please make sure the file is closed before you continue

5. Import the file into the Ftax MTD VAT form

To import data into the Ftax MTD VAT form, click the 'Import VAT Report' and select the 'Import a .csv file' option button in the Ftax form:

Select CSV format and browse to the known location of the file. Further information on this can be seen in the videos Ftax VAT Overview for Businesses and Ftax VAT Overview for Agents

6. Troubleshooting and creating a Nil Return

1. If your file fails to import, please ensure that the file has  been saved and closed before use
    
2. Do not use pound signs in your spreadsheet, format the data as 'number'
    
3. Some spreadsheet packages will not allow the importation of a single sheet from a workbook. If your import fails, create an independent worksheet containing nothing but a column with the nine values you need and save this as a CSV.
    
4. If you need to create a Nil Return, create a ticket for Ftax Support and we will send you an xfdf file with the appropriate values and formatting

We are very grateful to our clients for their comments and suggestions, without which these guides would not be possible.
If you have a suggestion to improve this guide or its associated video, please email tracey@ftax.co.uk