Import Specifications

The following sections describe the detailed technical requirements for importing from an Excel workbook, a CSV file or an Excel workbook exported from Access, using the NJT standard formats.



One of the main principles of Making Tax Digital is that any transfer of data must be undertaken digitally. To facilitate businesses that use Excel based Accounting systems, a function will be provided to allow preprared VAT returns to be imported via an Excel workbook that contains a worksheet conforming to a 'Standard Format' as defined by NJT. The pre-requisite being that businesses are able populate the worksheet as per the following example.

Example:
MTD Excel Import Example
Workbook and Worksheet Definition:
  • Workbook: The workbook can be saved as an Excel '.XLS', '.XLSX', '.XLSM' or '.XLSB' format.
  • Worksheet: The name of worksheet containing the VAT Return details should be set to 'MTD-VAT'. This will enable the software to locate the required sheet. Multiple sheets may exist within the workbook.
  • Workbooks Containing Multiple VAT Returns: To cater for workbooks that contain details of multiple VAT Returns, for example, an annual workbook containing the 4 quarterly VAT Returns or the 12 Monthly VAT Returns, you can create up to 12 individual worksheets named 'MTD-VAT1' to 'MTD-VAT12'. The software will inspected each of these worksheets and attempt to match the Period Start and End Dates to the Obligated VAT Return.
Worksheet Cells Definition:
  • Cell B1: contains the Business Name
  • Cell B2: contains the Business VAT Registration Number
  • Cell B3: contains the Period Start Date. Format: DD/MM/YYYY
  • Cell B4: contains the Period End Date. Format: DD/MM/YYYY
  • Cells B5 to B13: contain the values for Boxes 1 to 9. (Box 1 to 5 values should contain no more than 2 decimal places, Box 6 to 9 values should contain no decimal places, i.e. whole numbers only).
Example of a single VAT Return workbook:

Assuming you already use an Excel workbook to calculate your VAT Return. This may include applying manual adjustments etc. This workbook may already contain multiple worksheets that hold your adjustments details. It is assumed that you already have formula based cells that contain your 'Nine Box' totals for the VAT Return.


Steps for making the workbook compatible with the MTD software
  1. Create a new worksheet within the workbook and set the worksheet name to 'MTD-VAT'.
  2. Populate the cells A1 to A13 as defined in the example screenshot. Note: This is for your information only, and is not used by the MTD software.
  3. Either type your 'Business Name' in Cell B1 or enter a formula linking it to your 'Business Name' already present in a cell elsewhere in the workbook.
  4. Either type your 'VAT Registration No.' in Cell B2 or enter a formula linking it to your 'VAT No.' already present in a cell elsewhere in the workbook.
  5. Either type your 'From Date.' in Cell B3 or enter a formula linking it to your 'From Date' already present in a cell elsewhere in the workbook.
  6. Either type your 'To Date.' in Cell B4 or enter a formula linking it to your 'To Date' already present in a cell elsewhere in the workbook.
  7. In cells B5 to B13, enter a formula linking the cells to the existing values already created in the workbook.

You may wish to create an Excel template or an empty workbook containing all the layouts and formulas. Alternatively, after you have completed and saved the current VAT Return workbook, you may choose to 'Save As' the workbook, giving it a different name in preparation for your next VAT Return. The objective being that you only create the formulas once.




One of the main principles of Making Tax Digital is that any transfer of data must be undertaken digitally. To facilitate 3rd party Accounting systems using this software, a function will be provided to allow preprared VAT returns to be imported via a Comma Separated Variable (CSV) file. The pre-requisite being that 3rd party Accounting systems are able to generate a CSV file compliant with the following specification.

Example (MyVATReturn.txt or MyVATReturn.csv):
Definition:

Item 1: always set to "NJT_MTD_VAT" (always enclosed in speech marks)
Item 2: contains the CSV file version number, currently 1.0
Item 3: contains the Import_ID, always set to "StdCSV" (always enclosed in speech marks)
Item 4: contains the Date and Time stamp. I.e. when the CSV file was generated. Format: DD/MM/YYYY HH:MM:SS
Item 5: contains the Business Name (always enclosed in speech marks)
Item 6: contains the VAT Registration Number
Item 7: contains the Period Start Date. Format: DD/MM/YYYY
Item 8: contains the Period End Date. Format: DD/MM/YYYY
Items 9 to 17: contains values for Boxes 1 to 9. (Box 1 to 5 values should be formatted to 2 decimal places, Box 6 to 9 values should be formatted to 0 decimal places and all Box display formatting should be removed E.G. commas and currency symbols).



One of the main principles of Making Tax Digital is that any transfer of data must be undertaken digitally. To facilitate businesses that use Excel based Accounting systems, a function will be provided to allow preprared VAT returns to be imported via an Excel workbook that contains a worksheet conforming to a 'Standard Format' as defined by NJT. The pre-requisite being that businesses are able populate the worksheet as per the following example.

Example:
MTD Access Import Example
Workbook and Worksheet Definition:
  • Workbook: The workbook can be exported from Access as an Excel '.XLS' or '.XLSX' format.
  • Worksheet: The workbook should contain only 1 worksheet, therefore any worksheet name may used.
    Row 1 of the worksheet would normally contain the headings relating to the data in Row 2. However, these headings are not validated by the bridging software and therefore may contain any content.
Worksheet Cells Definition:
  • Cell A2: contains the Business Name
  • Cell B2: contains the Business VAT Registration Number
  • Cell C2: contains the Period Start Date. Format: DD/MM/YYYY
  • Cell D2: contains the Period End Date. Format: DD/MM/YYYY
  • Cells E2 to M2: contain the values for Boxes 1 to 9. (Box 1 to 5 values should contain no more than 2 decimal places, Box 6 to 9 values should contain no decimal places, i.e. whole numbers only).