VAT report by currency

I am likely being a bit dumb here, but am prepared to risk it.

I have looked at custom reports and read some of the help info thereto, but am no wiser. Here is what I want to do:

I need to do 2 different VAT reports.

1 showing output VAT including Invoice number, date, vat amount, total and the description (top one not the item one). So far so good. But I need the amounts to be in the currency of the transaction.

2 the same thing for input vat

So far I have been copying and pasting bits and pieces to excel then weeding and rearranging columns. It is time consuming and not elegant at all. Maybe there is a better way?

Have you tried writing a report transformation.
I think that would be the best solution

You might find some useful information in this topic, where I was asking about pretty much the same thing. And this one.

For VAT reports I found it useful to create a new tax code for USD transactions, so I have VAT 14.5% for all ZWL (base currency) transactions and VAT 14.5% (USD) for all USD transactions. This separates them out in the built-in tax reports, but it shows USD amounts converted to the base currency. Thankfully when I thought of this I didn’t have too many past transactions to change, but if you have hundreds or thousands it could be a real pain to try to implement this retroactively. There might be ways to do a batch update, but I didn’t think I find one when I briefly looked into it.

If you do come up with a good workflow please do update us, as I’m sure others like myself could benefit too.

I think for my last VAT report I also made extensive use of a spreadsheet application for some of the calculations. I had all my USD transactions copied from the built-in Tax Transactions report, and the exchange rates copied from Exchange Rates in Settings, and I matched up the transactions to the relevant exchange rates to calculate the USD amounts. Certainly not ideal, and I hope we’ll find a way to do all this in Manager. Maybe when my next VAT return is due I’ll make the time to properly investigate the report transformations.

Hi Patch

Transform reports looks like you have to do code. Not an area where I can function, unless you know better?

Hi Graham

If you export from the vat liability on the balance sheet you will get all the transactions in the currency of transaction. Then sort input from output. Then cut paste USD so they have a column of their own. Then remove usd symbol since excel does not see it as what it is. Strangely the ZWL is no problem. Date range is a bit hit and miss since you are copy pasting an .io page at a time. The if issues reconciling a day at a time if there is disagreement with z report

Ah, yes. This could be helpful. However, it shows line items, not invoice totals. This wouldn’t be a problem if you only ever have one item on an invoice, but I usually have multiple. It also doesn’t show the cost of the item, just the VAT amount (though the cost could be calculated).

My separate tax codes for ZWL and USD mean that I see them separately in the Balance Sheet, so I don’t have to do this step.

As of a few weeks ago Manager has the option to display 50, 100, 250, or all the records in a list. So you could sort by date, view as many as you need to cover your range, click Copy to clipboard, paste it in your spreadsheet application, and trim from there.

It enables searching for particular data, doing calculations, and displaying the results. Similar to the formulas and procedures you are doing in excel, but within manager, so making future reports trivial.

However I agree they are still in development and doing unusual things requires writing in liquid so maybe easier to leave for awhile or employ a programmer

I am playing around with the report transformations now, and I think we’ll be able to get what we need fairly easily once I figure out a few of the operations. There aren’t really a lot of calculations necessary – I think I just need to figure out the syntax and data structures to access the right information. The totals seem to be the easy part. It’s the transaction-level details I’m struggling with, and none of the other localisations I’ve looked at for reference seem to include any. If I get something to work I’ll share it here and hopefully get it to the point where I can submit it for review to be included as a localisation for Zim. Perhaps @Stewart you could help me test them when they’re ready for that?

After a lot of reading, exploring, and trial and error, and some help from @Patch, I have had moderate success in creating a report transformation. So far I’ve just done one for ZWL input claims, but I can take what I’ve learned with this and apply it fairly easily to USD input claims and the output section for both currencies. Some of it is fairly crude, and I’m hoping that I can refine it. Some improvements I’d like to make before trying to submit it for review as a localisation are:

  1. Make it pick up transaction currencies automatically so that it’s not relying on extra custom tax codes like I’ve used. This should make it easier for others to load it into their businesses when it’s ready, I hope.
  2. Make it automatically iterate through all the currencies used, so that if you don’t have any USD transactions, for example, it won’t create that section, and if you use other currencies too, like ZAR, it will automatically include those in their own sections.
  3. Make all tax calculations reference the appropriate tax code directly. I’ve calculated the tax exclusive amount and tax amount manually with hard-coded values for the tax rates, which will give incorrect results if the tax rate is changed.
  4. Improve readability, variable naming, etc.
  5. I see that numbers generated from my calculations don’t use the number format set in my preferences, so it would be nice to change that, though I’m not sure if it’s possible in the report definition.

@Stewart, does this look like it will meet your needs?:

And the definition: