Ablegatio Ftax Support

<< Back to Video Guides

Exporting data from spreadsheets as a CSV file

rule line

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.ukrule line

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. There are two ways to do this:

  • you can use the Ftax MTD VAT Link spreadsheet tool; or
  • you can import a file in CSV format.

For further information on the VAT Link too, please click here to view the video How to Use the Ftax MTD VAT Link tool.

This guide looks at 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

rule line

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:

New sheet tab in OpenOffice Calc

rule line

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:

    No labels but formula bar highlighted

    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.

    No labels and formuli needed for boxes 3 and 5

     

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

    No lables with emply field

     

    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:

    Labels with empty fields to the right of values

    rule line

    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.

    rule line

    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 as

    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.

    rule line

    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:

    Import button on VAT 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

    rule line

    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

    rule line

     

    << Back to Video Guides