Mac Numbers - UK HMRC "Making Tax Digital" bridgeware

Question

Level 1

10 points

Mac Numbers - UK HMRC "Making Tax Digital" bridgeware

The UK HMRC has introduced some rules called "Making Tax Digital" (MTD) about digital record keeping, and submission of VAT returns. From April 2018, a business cannot submit VAT returns by hand. They must be submitted by approved software using a secure interface to HMRC, and the returns must be derived from automated digital records.

All the accounting packages (Sage, Xero, Quickbooks.... etc.) have introduced the automated submission.

But not all businesses use accounting packages - many use spreadsheets to give more flexibility. HMRC has (reluctantly) allowed the use of spreadsheets, but has insisted never-the-less that the data from the spreadsheets be submitted by approved "bridgeware". A few approved "bridgeware" products are under development, but all of them work solely with Excel.

I am a book-keeper who uses Mac Numbers exclusively. I dont know what I am going to do. I need bridgeware for MTD in Mac Numbers.

Is this something Apple are considering for their UK customers?

Does anyone know of any third party MTD products for Numbers being developed.

This is a bit of a disaster waiting to happen.

Hoping someone is looking into this.

Gordon Jones

Posted on Dec 21, 2018 11:46 AM

Reply

Answer 1

Top-ranking reply

gajaj Author

Level 1

10 points

Feb 25, 2019 1:55 PM in response to VicJano

I started this discussion and its probably time I gave an update.

I have successfully submitted a quarterly VAT return to the HMRC MTD interface using the intermediary www.vat.direct.

HMRC does NOT accept csv files. HMRC will take JSNON or XML data through an API from "approved" clients.

Some "approved client" software suppliers will take extracts from spreadsheets and submit the VAT data from those extracts. I picked www.vat.direct precisely because their interface accepts a simple csv extract, so is not restricted to EXCEL as some of the other suppliers are.

I did not use the script kindly supplied by SG, instead I use the csv export from mac numbers and trashed the files I didn't need.

If you chose the somewhat clunky, but effective interface from www.vat.direct then there are a number of videos describing the format of the file that they accept.

Good luck to all those who still have to do this.

Gordon Jones

Reply

Answer 2

gajaj Author

Level 1

10 points

Jan 3, 2019 3:39 PM in response to Tautvydas Girenas

I had similar thoughts about trying to hack it myself, but when I found vat.direct, and found that they are offering a lot of free hand holding during the HMRC pilots, and they anticipate a charge of £10 or less per 3 monthly submission once its all up and running, and they accept just an exported Comma Separated Values file with just the the 9 VAT box values, I decided life is too short to roll my own.

I think it is outrageous that HMRC are forcing people into using commercial software. I don't understand why they are not offering a default interface. But £10 every three months seems good value.

Reply

Answer 3

gajaj Author

Level 1

10 points

Jan 3, 2019 9:20 AM in response to -dotty-

Hello Dotty,

What a wonderful handle for a finance/book keeper.

I have chosen this company www.vat.direct to interface between my spreadsheet accounts and HMRC. The company is, for the moment, a bit of a one man band, and the web site is a bit clunky, but they are approved by HMRC, and being small they are offering excellent support in getting to grips with the process.

To submit a VAT return, you drop a csv file onto one of their web pages, and they pass it onto HMRC. There is quite a lot of set up to get to that point, but it is relatively straight forward once the concepts have been grasped.

I have not looked in detail at other bridgeware suppliers, but I know there are some. If anyone has info about them please add details.

Producing the csv file has to be automatic - I believe that to satisfy the rules imposed by HMRC, copy paste is frowned on.

Numbers does not make it easy to produce the csv file for a single table (for 1 return) without using copy paste. When you ask numbers to export to csv, it generates for every table in your spreadsheet. You cant just ask for 1 table. And of course with numbers you cannot link between files (remind me, why do I use numbers?)

A bit more help from Apple would be good - at the low end, a simple change to allow export of a single table - at the high end, the apple cloud offering the interface to HMRC. The latter would give Apple an edge over office.

Hope that helps. Gordon Jones

Reply

Answer 4

gajaj Author

Level 1

10 points

Feb 26, 2019 4:34 AM in response to VicJano

vat.direct are very forgiving in the format of the values they accept. They certainly accept the quoted currency format (with the "£" currency symbol, and thousands comma that numbers outputs if your cell is formatted that way. FYI below is the (redacted) file I exported from my accounts spreadsheet and then submitted successfully through vat.direct. The key information that they look for in the file are rows starting "BOXN" followed somewhere on the row by a field that looks like a value. They do some validity checks on values and vat numbers, and will probably enhance their checks to include period start and end dates.

There are other suppliers of MTD spreadsheet bridgeware who will have different conventions.

2018,,Qtr 4

BOX1,Vat due on sales,"£xx,xxx.xx"

BOX2,VAT due on EEC purchases,£0.00

BOX3,Sum of 1&3,"£xx,xxx.xx"

BOX4,VAT Reclaimed,"£xx,xxx.xx"

BOX5,Adjusted VAT due,"£xx,xxx.xx"

BOX6,Total of (net) sales,"£xx,xxx.xx"

BOX7,Total of (net) purchases,"£xx,xxx.xx"

BOX8,Total supplies to EEC,£0.00

BOX9,Total purchases from EEC,£0.00

VRNVERIFY,vatnumber,

Reply

Answer 5

gajaj Author

Level 1

10 points

Dec 23, 2018 1:51 AM in response to Badunit

I have done some more digging, and the situation is not quite as dire as I first suggested. This is a quote from one review of MTD bridging software:

"Some bridging solutions will be plugins that can connect Excel to HMRC's systems. But most are cloud based offerings where spreadsheets or csv files will be uploaded and imported."

I think that means that it will be possible to upload a csv file generated from Numbers to one of the commercial cloud based HMRC interfaces.

Reply

Answer 6

SGIII

Level 8

37,562 points

Jan 5, 2019 2:55 AM in response to gajaj

Paste the script below in Script Editor (in Applications > Utilities)
The first time make sure Script Editor.app is listed and checked at Security & Privacy > Privacy > Accessibility.
Click in the table you want to export.
Click the 'run' button in Script Editor.

That's it! You should be up and running in a minute or less.

Notes:

If you have to export frequently and want to run the script from the menu or via a keyboard shortcut then you can place it in an Automator Service. Post if you need that.
The script assumes the table does not have blank columns and rows that you don't want to export. It can be modified if you do.
If you have a particular location other than the Desktop that you always use then the script can be modified to export to that location.
Also note that the script exports values as they are displayed. If you have more precise values in the cells (such as extra decimal places that are "hidden" by your formatting)nand you need to retain that precision in the exported CSV, then just take out the word 'formatted' where it appears in the script.

SG


# place clicked Numbers table values into AppleScript list of lists
tell application "Numbers"
	tell front document's active sheet
		tell (first table whose selection range's class is range)
			set tableData to rows's cells's formatted value
		end tell
	end tell
end tell

# convert to csv format
set text item delimiters to ","
set tableCSV to ""
repeat with r from 1 to (count tableData)
	set tableCSV to tableCSV & (tableData's item r as text) & return
end repeat

# save to a file
set oFile to (choose file name with prompt "Save As File" default name "My File.csv" default location path to desktop) as text
set f to open for access oFile with write permission
set eof f to 0
write tableCSV to f as «class utf8»
close access f

Reply

Answer 7

SGIII

Level 8

37,562 points

Dec 21, 2018 2:20 PM in response to gajaj

I suggest you communicate directly with Apple on this. One way is to go to Numbers > Provide Numbers Feedback in your menu. But since this is urgent for your business you might also try finding an appropriate support number to call.

SG

Reply

Answer 8

Jan 3, 2019 1:52 PM in response to gajaj

Gordon,

You are not alone! There are at least two of us! I too exclusively use numbers for all of my bookkeeping and VAT returns.

I'm still in the early stages of trying to get my head around it all (about a week), but it seems in a nutshell to be that:

1) The relevant data is extracted from a spreadsheet and loaded into an xml format file using iXBRL markup.

2) Through a secure api this file is then 'submitted' to hmrc and a response received.

The extraction of data and file creation could be done with a script then transmitted through the secure api. I've yet to work out the 'secure api' part.

To do live testing with HMRC one has to register as a developer. Then submit various test files to cover scenarios such as nil returns, errors in submitted returns, duplicated returns etc. The files and responses then need to be emailed to HMRC for approval.

I'll keep everyone posted, if I make any progress on this.

Peter

Reply

Answer 9

SGIII

Level 8

37,562 points

Jan 3, 2019 3:45 PM in response to gajaj

Numbers does not make it easy to produce the csv file for a single table (for 1 return) without using copy paste. When you ask numbers to export to csv, it generates for every table in
 your spreadsheet. You cant just ask for 1 table.

Exporting one table can be done with two clicks using an AppleScript. If that's of interest post what the Numbers table looks like and what the required format of the csv file is (comma-separated or tab-separated, etc).

SG

Reply

Answer 10

Jan 12, 2019 7:29 AM in response to gajaj

I am in the same situation. I do my VAT accounts on Apple's Numbers. I object to having to pay a monthly charge to file my tax return. I have found this. It means a change to Excel, & I've not tested it yet, but it is free!

https://www.accountingweb.co.uk/tech/accounting-software/mtd-avalara-bridges-the-spreadsheet-gap

Reply

Answer 11

Feb 26, 2019 4:59 AM in response to ihatekenburns

Not sure about avalara after all.

Has anyone tried www.123efiling.co.uk ? Free at present if you are just a single business submitting 4 returns a year, but you will have to export to Excel first

Reply

Answer 12

gajaj Author

Level 1

10 points

Dec 22, 2018 6:10 AM in response to SGIII

Thank you SG. Indeed I have raised this with support, and via feedback, but I thought I would mention it here to get some feedback as to how widespread the problem is.

I can't believe I am the only UK user who uses numbers to calculate their VAT returns, but I might be!

Gordon

Reply

Answer 13

gajaj Author

Level 1

10 points

Jan 5, 2019 1:11 AM in response to SGIII

Hello SG,

Two clicks would be brilliant.

The format is comma separated values, and the export that numbers does now generates the right data from the values in the cells. The only trouble is it generates a file for every other table in my spreadsheet as well. So these have to be deleted. Not a big deal, but annoying.

I am not familiar with AppleScript, but if it can restrict the export to the one table then great.

Gordon Jones

Reply

Answer 14

gajaj Author

Level 1

10 points

Jan 6, 2019 11:59 AM in response to SGIII

Hello SG,

Thank you for your suggestion. Very close, but the script doesn't quite generate the cell values correctly.

There are cells with the value "missing value"
When a cell is formatted as currency with comma separators at thousands, the script does not escape the value with quotes - Numbers export does add the quotes.

I wondered if a slightly different approach might be preferable so that the script uses the numbers csv export. I can clearly do this approach manually - and its what I am doing while testing, but be nice to get the script to do it.

Create a new document,
copy the currently selected table/sheet from the old document to the new,
export the new document as a file

I have been trying to implement this, but I am unfamiliar with applescript syntax, so not got very far. Do you think this approach might work?

Thanks again for your help.

Oh incidentally. This csv generation is something that is only done once every 3 months when a business is submitting its VAT return to the government.

Gordon Jones

Reply

Answer 15

SGIII

Level 8

37,562 points

Jan 6, 2019 3:57 PM in response to gajaj

Since you haven't posted what your table actually looks like I've had to guess.

-- "missing value" simply means you have blank cells in the table. See my second note in the original post.

-- the extra comma comes from using 'formatted value' in the script

Based on your comments the modified script below should be close to what you want. If not, could you post a screenshot of your table and describe the behavior you don't want?

SG

# place clicked Numbers table values into AppleScript list of lists
tell application "Numbers"
	tell front document's active sheet
		tell (first table whose selection range's class is range)
			set tableData to rows's cells's value
		end tell
	end tell
end tell

# convert to csv format
set text item delimiters to ","
set tableCSV to ""
repeat with r from 1 to (count tableData)
	set tableCSV to tableCSV & (tableData's item r as text) & return
end repeat

#remove missing values
set text item delimiters to "missing value"
set tableCSV to tableCSV's text items
set text item delimiters to ""
set tableCSV to tableCSV as string

# save to a file
set oFile to (choose file name with prompt "Save As File" default name "My File.csv" default location path to desktop) as text
set f to open for access oFile with write permission
set eof f to 0
write tableCSV to f as «class utf8»
close access f

Reply