New Report. Suitable for VAT/GST returns.

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

New Report. Suitable for VAT/GST returns.

Christopher Lam
Hi All

After many years of trying to hack scheme, I've finally been able to create
a custom report building on previous efforts by Doug Doughty. Report is
accessible in Report > Business > BAS/VAT Report.

I attach .scm file to be loaded into ~/.gnucash/config.user as usual, and
an example report output. I feel this is the missing report for periodic
(eg quarterly) tax reporting as required in some authorities e.g. UK and
Australia. Formulas are modifiable with some effort.

I hope this is useful to some. The learning curve was painful! Repo at
https://github.com/christopherlam/bas-report

C

_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

bas1.html (5K) Download Attachment
bas-vat-report.scm (102K) Download Attachment
test.gnucash (4K) Download Attachment
config.user (90 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: New Report. Suitable for VAT/GST returns.

Christopher Lam
Report is refined to show multiple tax accounts. This will be useful for
e.g. UKVAT normal and EC sales.
Tax column names are copied from the Account names.
Terminology changed from VAT/GST to generic 'tax' and TaxTables must use
'Input'/'Output' names.
Disabled zero-amount cells.
Technically it will count multiple currencies however this is likely to be
meaningless.
I think this is as far as this needs to go. Please let me know any bugs
prior to submitting for inclusion.
See sample output report attached, source in repository.
https://github.com/christopherlam/bas-report

On 31 July 2017 at 20:22, Christopher Lam <[hidden email]> wrote:

> Hi All
>
> After many years of trying to hack scheme, I've finally been able to
> create a custom report building on previous efforts by Doug Doughty. Report
> is accessible in Report > Business > BAS/VAT Report.
>
> I attach .scm file to be loaded into ~/.gnucash/config.user as usual, and
> an example report output. I feel this is the missing report for periodic
> (eg quarterly) tax reporting as required in some authorities e.g. UK and
> Australia. Formulas are modifiable with some effort.
>
> I hope this is useful to some. The learning curve was painful! Repo at
> https://github.com/christopherlam/bas-report
>
> C
>

_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

bas1.html (5K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: New Report. Suitable for VAT/GST returns.

Spandan888
In reply to this post by Christopher Lam
Good to see your work on a report for GST returns. In India, we have just
changed our Tax System to GST since 1 July 2017.
We need to file monthly returns.
Your test file and the reports show as expected.
Thanks.
I haven't yet quite figured out how to set up the accounts for GST.
Could you point to any resources?

Regards,
Spandan Sinha
--
Using Opera's mail client: http://www.opera.com/mail/
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: New Report. Suitable for VAT/GST returns.

Christopher Lam
https://forum.manager.io/t/indian-gst-tax-code-tax-summary/10315

This seems to show exactly what my report produced :-)

And the attached chart of accounts seems to fit the bill.

Just make sure you assign the correct tax account per multisplit
transaction.

In my updated report you can merge all input tax and output tax columns
into a 'total tax collected' 'total tax paid' column. See Display tab.

Don't forget to double check your bookkeeping with a licensed accountant.

HTH



On 9 August 2017 at 19:23, Spandan Sinha <[hidden email]> wrote:

> Good to see your work on a report for GST returns. In India, we have just
> changed our Tax System to GST since 1 July 2017.
> We need to file monthly returns.
> Your test file and the reports show as expected.
> Thanks.
> I haven't yet quite figured out how to set up the accounts for GST.
> Could you point to any resources?
>
> Regards,
> Spandan Sinha
> --
> Using Opera's mail client: http://www.opera.com/mail/
> _______________________________________________
> gnucash-user mailing list
> [hidden email]
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
>
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: New Report. Suitable for VAT/GST returns.

Spandan888
In reply to this post by Spandan888
We have mutiple slabs - 0%, 5%, 12%, 18% and 28%.
So i have to make all these tax tables...
excluding the 0%, there would be 4 tables for each slab - let's take 18%  
as an example - the slabs would be 9% INPUT, 9% OUTPUT, 18% INPUT and 18%  
OUTPUT.

here the 18% is divided between the Centre (CGST) and the State (SGST) for  
sales and purchases within the state,
and 18% is charged as Integrated GST (IGST) for inter-state transactions.

that comes to 16 tax tables.
Now most businesses would have only one or two OUTPUT rates, but there are  
bound to be multiple INPUT rates, as evident in the examples given:

just have a look at
https://cleartax.in/s/accounting-entries-under-gst


now it would be more convenient if there was a provision that your report  
would take in account the tax tables that have the words Input and Output  
as part of the name.. What i mean to say is that "Input 12" or "Output 9"  
also be considered for the report.

I tried to install manager.io to see what it has to offer, but i need to  
first upgrade my Internet Explorer....it doesnt work on systems with IE 9  
or below.

Following the project on Github, again thanks for all the hard work.

Regards,
Spandan Sinha
http://about.me/spandan
--
Using Opera's mail client: http://www.opera.com/mail/
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: New Report. Suitable for VAT/GST returns.

Spandan888
Or could you tell me how to modify the .scm file so that tax table rates  
"Input 18", "Output 18", "Input 28", "Output 28" etc are also looked up by  
name?

tried adding the names with the operator "and", but it just gives errors.

Regards,
Spandan Sinha
http://about.me/spandan


On Thu, 10 Aug 2017 16:09:50 +0530, Spandan Sinha <[hidden email]>  
wrote:

> We have mutiple slabs - 0%, 5%, 12%, 18% and 28%.
> So i have to make all these tax tables...
> excluding the 0%, there would be 4 tables for each slab - let's take 18%  
> as an example - the slabs would be 9% INPUT, 9% OUTPUT, 18% INPUT and  
> 18% OUTPUT.
>
> here the 18% is divided between the Centre (CGST) and the State (SGST)  
> for sales and purchases within the state,
> and 18% is charged as Integrated GST (IGST) for inter-state transactions.
>
> that comes to 16 tax tables.
> Now most businesses would have only one or two OUTPUT rates, but there  
> are bound to be multiple INPUT rates, as evident in the examples given:
>
> just have a look at
> https://cleartax.in/s/accounting-entries-under-gst
>
>
> now it would be more convenient if there was a provision that your  
> report would take in account the tax tables that have the words Input  
> and Output as part of the name.. What i mean to say is that "Input 12"  
> or "Output 9" also be considered for the report.
>
> I tried to install manager.io to see what it has to offer, but i need to  
> first upgrade my Internet Explorer....it doesnt work on systems with IE  
> 9 or below.
>
> Following the project on Github, again thanks for all the hard work.
>
> Regards,
> Spandan Sinha
> http://about.me/spandan


--
Using Opera's mail client: http://www.opera.com/mail/
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: New Report. Suitable for VAT/GST returns.

Frank H. Ellenberger-3
In reply to this post by Christopher Lam
Hi, Christopher,

Am 02.08.2017 um 17:39 schrieb Christopher Lam:
> Terminology changed from VAT/GST to generic 'tax' and TaxTables must use
> 'Input'/'Output' names.

I liked the old terminology more. AFAIK in most countries the businesses
or it's owners have to pay one or more income based taxes, too.

I had not the time to study the deails of your reports, but a few
questions raise.


1. from
https://github.com/christopherlam/bas-report/blob/master/business-tax-report.scm:

> ;; Original report by Robert Merkel <[hidden email]>
:

From which report is it derived?

2. Do you make use of
https://www.gnucash.org/docs/v2.6/C/gnucash-help/set-tax-options.html ?

https://github.com/Gnucash/gnucash/tree/maint/src/report/locale-specific
shows the current use for US Income Tax
and with prefix "de" german VAT

Regards
Frank
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: New Report. Suitable for VAT/GST returns.

Christopher Lam
In reply to this post by Spandan888
A dirty hack, but you'll probably want to change
(gncTaxTableGetEntries (gncTaxTableLookupByName (gnc-get-current-book) "
Input"))
to
(append
(gncTaxTableGetEntries (gncTaxTableLookupByName (gnc-get-current-book) "Input
18"))
(gncTaxTableGetEntries (gncTaxTableLookupByName (gnc-get-current-book) "Input
28")))

Perhaps try model on a spreadsheet first how to divvy up the amounts among
different accounts.
You can always change the formulas in line 1092 onwards and make additional
calculations.
Good luck!

On 11 August 2017 at 00:30, Spandan Sinha <[hidden email]> wrote:

> Or could you tell me how to modify the .scm file so that tax table rates
> "Input 18", "Output 18", "Input 28", "Output 28" etc are also looked up by
> name?
>
> tried adding the names with the operator "and", but it just gives errors.
>
> Regards,
> Spandan Sinha
> http://about.me/spandan
>
>
> On Thu, 10 Aug 2017 16:09:50 +0530, Spandan Sinha <[hidden email]>
> wrote:
>
> We have mutiple slabs - 0%, 5%, 12%, 18% and 28%.
>> So i have to make all these tax tables...
>> excluding the 0%, there would be 4 tables for each slab - let's take 18%
>> as an example - the slabs would be 9% INPUT, 9% OUTPUT, 18% INPUT and 18%
>> OUTPUT.
>>
>> here the 18% is divided between the Centre (CGST) and the State (SGST)
>> for sales and purchases within the state,
>> and 18% is charged as Integrated GST (IGST) for inter-state transactions.
>>
>> that comes to 16 tax tables.
>> Now most businesses would have only one or two OUTPUT rates, but there
>> are bound to be multiple INPUT rates, as evident in the examples given:
>>
>> just have a look at
>> https://cleartax.in/s/accounting-entries-under-gst
>>
>>
>> now it would be more convenient if there was a provision that your report
>> would take in account the tax tables that have the words Input and Output
>> as part of the name.. What i mean to say is that "Input 12" or "Output 9"
>> also be considered for the report.
>>
>> I tried to install manager.io to see what it has to offer, but i need to
>> first upgrade my Internet Explorer....it doesnt work on systems with IE 9
>> or below.
>>
>> Following the project on Github, again thanks for all the hard work.
>>
>> Regards,
>> Spandan Sinha
>> http://about.me/spandan
>>
>
>
> --
> Using Opera's mail client: http://www.opera.com/mail/
> _______________________________________________
> gnucash-user mailing list
> [hidden email]
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
>
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: New Report. Suitable for VAT/GST returns.

Christopher Lam
In reply to this post by Frank H. Ellenberger-3
Hi Frank

1. it comes from the original transaction.scm which has sat unchanged for
years.

2. I figured these tax options mainly relate to Income tax reporting with
numerous categories for various allowable deductions and are rather hard to
set up for each country, and hard to keep up to date.
 I preferred to reuse the existing Business>Sales Tax UI to select GST/VAT
accounts for reporting, which seems appropriate because the business
features will offer to populate these tax accounts.
 So this is not an income tax report, but rather a business activity (and
input/output tax) report.

3. VAT/GST also seem more appropriate but rather wordy. We can change if it
fits the bill better.

Chris

On 11 August 2017 at 01:08, Frank H. Ellenberger <
[hidden email]> wrote:

> Hi, Christopher,
>
> Am 02.08.2017 um 17:39 schrieb Christopher Lam:
> > Terminology changed from VAT/GST to generic 'tax' and TaxTables must use
> > 'Input'/'Output' names.
>
> I liked the old terminology more. AFAIK in most countries the businesses
> or it's owners have to pay one or more income based taxes, too.
>
> I had not the time to study the deails of your reports, but a few
> questions raise.
>
>
> 1. from
> https://github.com/christopherlam/bas-report/blob/master/business-tax-
> report.scm:
>
> > ;; Original report by Robert Merkel <[hidden email]>
> :
>
> From which report is it derived?
>
> 2. Do you make use of
> https://www.gnucash.org/docs/v2.6/C/gnucash-help/set-tax-options.html ?
>
> https://github.com/Gnucash/gnucash/tree/maint/src/report/locale-specific
> shows the current use for US Income Tax
> and with prefix "de" german VAT
>
> Regards
> Frank
>
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: New Report. Suitable for VAT/GST returns.

Spandan888
In reply to this post by Christopher Lam

Chris,
That definitely works!

Thanks to you, I have my very first project on GitHub.
the fork branch is available at
https://github.com/spandan888/bas-report/tree/GST-india-edits

I still need to create the 6 totals columns, which would come before the  
bank remittance column
Total Output CGST Total Output SGST Total Output IGST Total Input  
CGST Total Input SGST Total Input IGST


                                       


these accounts are under Liabilities:GST:


I  have attached a file named gstreport_demo02 .html with this mail
and you can see the tax tables also in the image file.

There are also two spreadsheets in github, one a final tax payable  
calculator, another a list of tax tables and accounts.
The calculation of the final amount is complex, and would leave that to  
the spreadsheet for now.
  Regards,
Spandan Sinha
http://about.me/spandan





--
Using Opera's mail client: http://www.opera.com/mail/
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

supported-taxtable-names.png (67K) Download Attachment
gstreport_demo02.html (5K) Download Attachment
Loading...