How to format your files for an AlphaVAT calculation

DATA FILE REQUIREMENTS

FILES

File type

Supported:

• Excel files in file formats of csv, xls or xlsx that stores tabular data.

• Xls and xlsx files can contain multiple tabs – each tab will be treated as a separate upload

• The additional csv file delimiters that are supported are:

• Claret ^

• Pipe |

• Semi colon ;

• Tab

• Tilda ~

• A csv file must contain a single table of data.

• Xls or xlsx files can contain multiple tables of data.

• The first row of the file containing data must contain the column headings used within the file; this is required for the mapping process.

• Blank columns and rows will be removed if followed by subsequent data.

• All subsequent cells must be data records and not further headings, sub-totals or totals.

• Each record must be on a separate row.

• All data, including white space, is considered part of the value, and could cause warnings and or errors within the calculation.

Not supported:

• Other Excel types (i.e., .xlsm, .xlsb etc.), Google sheets, Apple Numbers etc.

HEADERS

Mandatory fields

• Net amount

• VAT amount

Not currently supported

A VAT Code used in conjunction with either Net amount or VAT amount to calculate the Net or VAT value. We will be providing the ability to derive new Net or VAT amounts in the future.

Optional fields

• Date

• Description

• Tax code

• Gross amount

• Recovery rate override

• Grouping (PESM - Allocation)

• Amount (PESM - custom table)

• Currency Code

• Exchange rate

• Net Amount (Non-GBP)

• VAT Amount (Non-GBP)

• Counterparty

• Account code

• Nominal code

• Cost centre

• Invoice number

• Reference

• Counterparty address

• Counterparty VAT number

• Country to

• Country from

• Other: This can be used to map a field that has not been included in the mandatory and optional field lists.

Duplicate headers

Can be filtered and then mapped independently:

• If there are duplicate headers, we will append each with an incrementing number e.g. VAT, VAT(1), VAT(2) etc.

• If the column data needs summing together then this can be achieved by using multiple filters and column mappings

DATA

Header data

Header character limit = 260 per header.

NOTE: Special characters are handled. There may be some presentation issues with special characters but these will be addressed.

Column data

255 characters. Additional characters after this will be removed following upload in to AlphaVAT

Logical data formats throughout a column:

• For instance, all numbers in a column that is to be used in the calculation.

• Dashes will be treated as 0. Currency symbols, brackets and minus signs in front of numbers are accepted as standard.

• Calculation may produce 0 if a column that is meant to be summed is a mix of numbers and text, for example if the VAT column said "zero" rather than "-" or "0". The column would be treated as text and therefore not sum. This will produce a warning upon the upload of the data, providing the opportunity to make amendments.

Columns with opposite signage to what is expected, are not currently supported:

For example, the VAT Return requires positive values for sales. If the signage is negative the sum will currently take the negative total. This will be addressed in a near future release by allowing the column signage to be treated in the correct manner for the VAT Return preparation.

General

These formatting related items are not supported once uploaded. Therefore, these will be stripped out and/or changed and treated as text items:

• Accounting/text/number etc. are all set to general when converted to csv

• V-lookups

• Page breaks

• Comments on cells

• Bullet/lists

• Fonts are all set to a standard when converted to csv

• Bold/underline/italics/highlighting

• Links to other documents

These formatting related items will be converted to standard column/row format and/or standard formatting when uploaded:

• Wrapped cells

• Merged cells

• Filters

• Embedded tables

• Hidden rows

• Hidden columns

OTHER

Files

• Files with the same name will be appended with an incrementing number e.g. Sales, Sales(1), Sales(2) etc.

• File name can contain any special characters that can be used in Windows. Where a file name is uploaded and contains special characters that are not supported, AlphaVAT will replace these with an underscore.

• There are no file path limits with direct upload to AlphaVAT or via AlphaLink.

• Password protection is not supported by csv so will not cause an issue.

• Windows does not support the following characters within file names: \ / : * ? " < > |

Currency

You can upload files with foreign currency transactions. AlphaVAT contains a module to convert currencies using either HMRC rates (pulled directly from HMRC) or a column of source data conversion rates.

Where source data conversion rates are used, there must be a rate per transaction (i.e., there should be a column containing the rate of exchange in each row relating to a foreign currency transaction).