How to code Excel cashbook to import into software

How do I code up an analysed Excel cashbook with a code to import into my accounts software for MTD?

Didn't find your answer?

With just over a year to go before Making Tax Digital VAT submissions are [probably] required, I am starting to think how I will deal with my Excel clients, like the ones that use a typical analysed cash book.

We will have columns for transaction date, description of transaction, gross, VAT and several columns to analyse out the net amounts.

I can then very easily import this type of spreadsheet, with a little bit of editing, into my account software e.g. Sage 50 or Sage Essentials, and submit a MTD VAT return in due course.

I know that I could get them to buy software or start using the cloud.  However, I still like the simplicity of Excel for both myself and my clients. 

I have had a play with Sage One Start, which costs £6 pm, and tried to import a bank statement (from an Excel sheet) or using a bank feed. It is a very slow and time consuming task.

I still find, for my small clients, the ability to receive an Excel spreadsheet, and for my own bookkeeping service, the ability to add columns, move amounts around etc is extremely powerful and flexible.

I would like to add an extra column to the Excel cash book that will insert a nominal ledger into the column based on if an amount is entered into one of the net amount analysis columns.  The code would be based on the column heading.

This must be a very simple task for Excel and for anyone who has the know-how for tables and look up functions etc.

 

I’ve got an answer from a web company who provide 24X7 access to a quick and reliable way to get unstuck in Excel. Just visit www.excelchat.co enter a problem and get your solution in just 20 minutes. Normal cost is US$9.99 first question free.

Assuming that you want the nominal ledger code in column C (of row 10), and the cash book net (of VAT) amounts are analysed in columns F to K (of row 10) with the header code in row 7 then use the formulae

=INDEX($7:$7,SUMPRODUCT(($F10:$K10<>"")*COLUMN($F10:$K10)))

Copy the formulae down to as many rows as you have in your Excel cash book. Insert extra columns if you want between F and K

Replies (7)

Please login or register to join the discussion.

By ireallyshouldknowthisbut
14th Feb 2018 15:03

Excel is MTD "approved" and there are supposed to be functionality to go from excel to filing if a third party developer creates this app. I imagine it would work just as VT does now.

Remember only the TOTAL gets sent per box number (as now), so if you do end up using SAGE etc, you could simply post a journal with the quarterly totals in SAGE from any other system (excel, paper etc), and file that way, so you don't need to recreate it all in cloud software.

Thanks (0)
avatar
By SJRUK
14th Feb 2018 15:06

I don't see how you can consider any options yet until the software companies have a finished product and the relevant API Link.
It certainly sounds like book-keeping on Excel spreadsheets will be an easyish option anyhow.

Thanks (1)
d
By puzzel
14th Feb 2018 15:10

Beat me to it :(

Thanks (0)
avatar
By djtax
06th Mar 2018 15:12

I fully agree with the sentiments expressed here. My concern is when (or if?) HMRC are going to actually agree with any software providers to allow issue to us of a viable end product to somehow provide the missing electronic link between continuing to use Excel spreadsheets but then comply with MTD for VAT filing. No such software is yet available despite the MTD for VAT pilot staring in 4 weeks time. Are all those businesses (and their accountants) who are relying on HMRC to keep their word on Excel spreadsheets now being effectively excluded from the pilot? In which case what happens when we reach April 2019?

Thanks (1)
paddle steamer
By DJKL
06th Mar 2018 16:53

Do you not still have the issue that the data within the excel sheet itself still needs to comply with the legislation.

For example will say current excel sheets laid out as a cashbook that has a one line entry per bank payment, gross, vat, standard, zero, exempt, re multiple purchase invoices from one supplier paid en masse, will such a transaction line, not directly linking the input vat claimed per invoice to the net values on the invoice, be compliant under MTD?

The simplest excel business entities will maybe link through so individual transactions are detailed within the "digital platforms used" but for a fair few retailers I think I will need to move them on to a PDB/PL approach.

Of course maybe I am misunderstanding the MTD requirements (quite likely)

Edit, link to the SI

http://www.legislation.gov.uk/uksi/2018/261/regulation/7/made

Thanks (1)
avatar
By Practicaledd
09th Mar 2018 13:01

Personally I'd have a separate table (named so it can expand) with my chart of accounts on and use Index Match to link it to the input table.

One thing to watch out for is the import process. MTD won't allow you to copy and paste. Any link between software (except for journal adjustments) has to be done programmatically.

An import tool in your MTD software that drags the data straight out of Excel would work fine.

As others have said, there's a good chance that someone will make an addin for Excel that would avoid any of those sorts of problems (fingers crossed!).

I agree with SJRUK though, HMRC haven't got the final spec out to the software companies yet (as far as I know) so who knows what kind of solutions will be available.

Thanks (1)
avatar
By Cantona1
09th Mar 2018 19:57

I have been importing sales invoices in a CSV format in to Sage 200. At the end of process, Sage shows an error. When I looked at the details of the error, there is an "Exception caused by some code written in C#.
How a software company could get away with this type of error is beyond me? Exceptions should never appear or shown to end user. It is only used for production.

Thanks (0)