Is there a way of finding the VAT element of each and every payment Shopify make to our bank account without going into each individual order/sale?'
At the moment we use the Shopify gateway alongside Paypal as this seemed to be the better way of doing it price wise. However Shopify will only pay on a daily basis, sometimes more than once, as opposed to monthly, which leads to big complications when it comes to entering into our Sage accounts package as each payment has to be broken down to find the tax. It seems the only way to do this is to open each individual order and match them with the ones Shopify have advised they've paid for....so time consuming, absolutely completely crazy!
There is no easy answer here - you have to match your sales data to your bank deposits in order to demonstrate that the money you received is a result of sales you made, and to accurately support your VAT calculations. This is also the only way you can reconcile your general ledger to your bank, which is an accounting requirement for any set of books.
So you will have to find an app to download Shopify data to your accounting package - I don't know of one specifically for Sage, but Zapstitch is great for QuickBooks online, and SoftCookies is great for QuickBooks desktop. Contact these guys (through the Shopify app store) they may be able to help.
I wrote some scripting wrapped up in an App for Europeans that have VAT problems in their Shopify lives. It provides a CSV file with each sale, and breaks out the VAT taxes nicely. For any date range the file contains all the line item costs, and taxes broken down by type (for shipping, food, etc).
Shopify provides just enough info in the line items and order details to make sense of each sale (except discounts, Shopify still does not play nice with discounts). This CSV file can then be used to quickly come to pretty accurate conclusions about how much VAT is floating around, especially by the accountants that need to know that stuff.
Yes. It is fun. I have now built four Apps to specifically deal with VAT and sales. Some merchants want to see their net sales, the VAT collected for shipping, the VAT for food, and the VAT for other things as well as the total of discounts, and how each sale was processed, like credit card type, cash etc.
It is complex, and I am learning that a one-size fits all App for this is likely a pipe dream where you smoked some really funny stuff.