Cash Based Reports

[munga]

Hi,
I have create a simply sql script that gives me the info I need to fill in
my BAS. This method will only work if you are storing your gnucash data in a
mysql database.
I have a very small business that is GST registered and I use the invoice
feature of Gnucash.
Here is the output of the report.
+----------------+-----------+
| Field          | Amount    |
+----------------+-----------+
| G1 Total Sales | xxx.00 |
+----------------+-----------+
+---------------------------+----------+
| Field                     | Amount   |
+---------------------------+----------+
| G11 Non-capital purchases | xxx.00 |
+---------------------------+----------+
+---------------------+--------+
| Field               | Amount |
+---------------------+--------+
| 1B GST on purchases | xxx.00 |
+---------------------+--------+
+-----------------------------------+----------+
| Field                             | Amount   |
+-----------------------------------+----------+
| 1A GST on sales or GST instalment | xxx.00 |
+-----------------------------------+----------+
+-------------------------------------------+-----------+
| Field                                     | Amount    |
+-------------------------------------------+-----------+
| W1 Total salary, wages and other payments | xxx.00 |
+-------------------------------------------+-----------+
+----------------------------------------------+-----------+
| Field                                        | Amount    |
+----------------------------------------------+-----------+
| W2 Amount withheld from payments shown at W1 | xxx.00 |
+----------------------------------------------+-----------+

The process may differ depending on how you've setup your accounts. When I
post and invoice the GST amount goes into an account called GST Invoiced
when an invoice is paid I have to manually transfer the GST amount from GST
Invoiced to another account called GST Collected.

You will need to get some information from the database, so if your not
familiar with mysql this may not be a solution for you.

Anyway here is the command line I use to execute the script. You will need
to change the username/password and required dates.
mysql -u <user> -p<password> rjg -e "set @start_date:='2013-10-01'; set
@end_date:='2013-12-31'; source BAS_report.sql;"

Here is the contains of BAS_report.sql you'll have to adjust the account
guid numbers to suit.
-- Cash income
select 'G1 Total Sales' as Field, format(sum(s.quantity_num/100)/1.1,2) as
Amount
from splits s, transactions t, accounts a
where s.tx_guid = t.guid
and s.account_guid = a.guid
and s.action = 'Payment'
and a.account_type = 'BANK'
and DATE_ADD(t.post_date, INTERVAL 10 HOUR) between @start_date and
@end_date;


-- Exspenses excluding wages and PAYG withheld.
select 'G11 Non-capital purchases' as Field,
format(sum(s.quantity_num/100),2) as Amount
from splits s, transactions t, accounts a
where s.tx_guid = t.guid
and s.account_guid = a.guid
and s.account_guid in (select sa.guid from accounts sa where sa.account_type
= 'EXPENSE' and (sa.guid <> '67ab0eb692c27206fc740594f9204199' and sa.guid
<> '01f127e87bd1196777f38c6769d66d29'))
and DATE_ADD(t.post_date, INTERVAL 10 HOUR) between @start_date and
@end_date;

-- GST paid
select '1B GST on purchases' as Field, format(sum(s.quantity_num/100),2) as
Amount
from splits s, transactions t, accounts a
where s.tx_guid = t.guid
and s.account_guid = a.guid
and s.account_guid in (select sa.guid from accounts sa where sa.name = 'GST
Paid')
and  s.quantity_num > 0
and DATE_ADD(t.post_date, INTERVAL 10 HOUR) between @start_date and
@end_date;


-- GST Collected
select '1A GST on sales or GST instalment' as Field,
format(sum(s.quantity_num/100)*-1,2) as Amount
from splits s, transactions t, accounts a
where s.tx_guid = t.guid
and s.account_guid = a.guid
and s.account_guid in (select sa.guid from accounts sa where sa.name = 'GST
Collected')
and  s.quantity_num < 0
and DATE_ADD(t.post_date, INTERVAL 10 HOUR) between @start_date and
@end_date;


-- Wages including PAYG withheld, modify
'67ab0eb692c27206fc740594f9204199','01f127e87bd1196777f38c6769d66d29' to
include your accounts.
select 'W1 Total salary, wages and other payments' as Field,
format(sum(s.quantity_num/100),2) as Amount
from splits s, transactions t, accounts a
where s.tx_guid = t.guid
and s.account_guid = a.guid
and s.account_guid in
('67ab0eb692c27206fc740594f9204199','01f127e87bd1196777f38c6769d66d29')
and  s.quantity_num > 0
and DATE_ADD(t.post_date, INTERVAL 10 HOUR) between @start_date and
@end_date;

-- PAYG withheld
select  'W2 Amount withheld from payments shown at W1' as Field,
format(sum(s.quantity_num/100),2) as Amount
from splits s, transactions t, accounts a
where s.tx_guid = t.guid
and s.account_guid = a.guid
and s.account_guid in ('01f127e87bd1196777f38c6769d66d29')
and  s.quantity_num > 0
and DATE_ADD(t.post_date, INTERVAL 10 HOUR) between @start_date and
@end_date;

I hope this helps someone else in Australia.



--
View this message in context: http://gnucash.1415818.n4.nabble.com/Cash-Based-Reports-tp4655948p4667537.html
Sent from the GnuCash - User mailing list archive at Nabble.com.