This is a preliminary (spreadsheet) process to add GST splits to a bank
statement and use a multisplit csv file to import them to GnuCash.
1. Start with Bank Statement based on the Guide Checking Account -
see GST_Demo_Statement.csv
2. Accounts should exist for the transactions -
see GST_Demo_Accounts.csv and the Note for GST accounts
3. Unique account strings are required to identify Accounts -
see GST_Demo_AccountKeywords.csv
4. Each of the above csv files can be copied to a sheet in a spreadsheet
to lookup the Account and GST fields - see GST_Demo_Statement_Accounted.csv
5. A process is run to create the GST split in a multisplit csv file -
see GST_Demo_Statement_Multisplit.csv
Let's create the GnuCash GST_Demo file:
*Note:* GnuCash V5.1 has a bug preventing the multisplit file from loading
amounts. Either load GnuCash V4 and translate all the labels or run V5.1
anyway assigning value to an empty column to see the process without values.
Run GnuCash
Create the accounts:
File, New file, Cancel
File, Import, Import accounts from CSV, Next, Choose file to import:
GST_Demo_Accounts.csv, Import account preview: 1 row in header, comma
separated, Next, Import accounts now: Apply, New book options: Close, A
message should display 21 accounts created: Close.
You can examine the accounts created.
Import the Checking Account transactions:
File, Import, Import transactions from CSV, Next, Choose file to import:
GST_Demo_Statement_Multisplit.csv, Import preview: File format:
Separators, Comma, Multi-split, Miscellaneous: Date format - d-m-y, Leading
lines to skip - 1, Select columns - Date, Number, Description, Amount
(negated), Amount, Price (assigned to empty column to workaround bug),
Account, Next, Match import and GnuCash accounts: Next, Transaction
information: Next, Match transactions: Apply, Import summary: Close
In the Accounts tab double click on Bank:Checking to open the account,
View, Transaction journal.
Regards
>
Date,No,Description,Debit,Credit,Balance
01/03/2006,,Opening Balance,,1000.00,1000.00
05/03/2006,101,GG25j1546 Groceries wtf 15:57 061124,45.21,,954.79
05/03/2006,,EFTPOS 5632185,75.00,,879.79
06/03/2006,,Transfer to J&J Doe Savings Acc 5765-8397 589654259587,,100.00,979.79
06/03/2006,106,EFTPOS 98416,100.00,,879.79
14/03/2006,,Direct Credit Salary from Employers R Us,,670.00,1549.79
24/03/2006,,Transfer to J&J Doe Savings Acc 5765-8397 589654259587,,500.00,2049.79
28/03/2006,,Mmvoin515b Internet Company bg??,20.00,,2029.79
28/03/2006,102,Light Company Big City Branch 9g8k863,78.00,,1951.79
28/03/2006,103,Phone Company Name Autodebit 595642583,45.00,,1906.79
28/03/2006,104,April Rent 5 Short Road,350.00,,1556.79
28/04/2006,105,May Rent 5 Short Road,350.00,,1206.79
Type,Full Account Name,Account Name,Account Code,Description,Account Colour,Notes,Symbol,Namespace,Hidden,Tax Info,Placeholder
ASSET,Assets,Assets,,,,,AUD,CURRENCY,F,F,T
ASSET,Assets:GST,GST,,,,,AUD,CURRENCY,F,F,T
ASSET,Assets:GST:GST on Purchases,GST on Purchases,,,,,AUD,CURRENCY,F,F,F
BANK,Bank,Bank,,,,,AUD,CURRENCY,F,F,T
BANK,Bank:Checking,Checking,,,,,AUD,CURRENCY,F,F,F
BANK,Bank:Saving,Saving,,,,,AUD,CURRENCY,F,F,F
EQUITY,Equity,Equity,,,,,AUD,CURRENCY,F,F,T
EQUITY,Equity:Opening Balance,Opening Balance,,,,,AUD,CURRENCY,F,F,F
EXPENSE,Expense,Expense,,,,,AUD,CURRENCY,F,F,T
EXPENSE,Expense:Electricity,Electricity,,,,GST,AUD,CURRENCY,F,F,F
EXPENSE,Expense:Groceries,Groceries,,,,,AUD,CURRENCY,F,F,F
EXPENSE,Expense:Hardware,Hardware,,,,GST,AUD,CURRENCY,F,F,F
EXPENSE,Expense:Internet,Internet,,,,GST,AUD,CURRENCY,F,F,F
EXPENSE,Expense:Phone,Phone,,,,GST,AUD,CURRENCY,F,F,F
EXPENSE,Expense:Rent,Rent,,,,,AUD,CURRENCY,F,F,F
EXPENSE,Expense:Unallocated,Unallocated,,,,,AUD,CURRENCY,F,F,F
INCOME,Income,Income,,,,,AUD,CURRENCY,F,F,T
INCOME,Income:Salary,Salary,,,,,AUD,CURRENCY,F,F,F
LIABILITY,Liability,Liability,,,,,AUD,CURRENCY,F,F,T
LIABILITY,Liability:GST,GST,,,,,AUD,CURRENCY,F,F,T
LIABILITY,Liability:GST:GST on Sales,GST on Sales,,,,,AUD,CURRENCY,F,F,F
Keyword,Account
98416,Expense:Hardware
R Us,Income:Salary
Groceries,Expense:Groceries
Internet,Expense:Internet
Light,Expense:Electricity
Opening,Equity:Opening Balance
Phone,Expense:Phone
Savings Acc,Bank:Saving
Rent,Expense:Rent
Warning: Insert new items above last item to maintain table,
Date,No,Description,Debit,Credit,Balance,Account,Match,GST
01/03/2006,,Opening Balance,,1000.00,1000.00,Equity:Opening Balance,Opening,
05/03/2006,101,GG25j1546 Groceries wtf 15:57 061124,45.21,,954.79,Expense:Groceries,Groceries,
05/03/2006,,EFTPOS 5632185,75.00,,879.79,Expense:Unallocated,#N/A,
06/03/2006,,Transfer to J&J Doe Savings Acc 5765-8397 589654259587,,100.00,979.79,Bank:Saving,Savings Acc,
06/03/2006,106,EFTPOS 98416,100.00,,879.79,Expense:Hardware,98416,GST
14/03/2006,,Direct Credit Salary from Employers R Us,,670.00,1549.79,Income:Salary,R Us,
24/03/2006,,Transfer to J&J Doe Savings Acc 5765-8397 589654259587,,500.00,2049.79,Bank:Saving,Savings Acc,
28/03/2006,,Mmvoin515b Internet Company bg??,20.00,,2029.79,Expense:Internet,Internet,GST
28/03/2006,102,Light Company Big City Branch 9g8k863,78.00,,1951.79,Expense:Electricity,Light,GST
28/03/2006,103,Phone Company Name Autodebit 595642583,45.00,,1906.79,Expense:Phone,Phone,GST
28/03/2006,104,April Rent 5 Short Road,350.00,,1556.79,Expense:Rent,Rent,
28/04/2006,105,May Rent 5 Short Road,350.00,,1206.79,Expense:Rent,Rent,
Date,No,Description,Debit,Credit,,Account
01/03/06,,Opening Balance,1000.00,,,Bank:Checking
,,,,1000.00,,Equity:Opening Balance
05/03/06,101,GG25j1546 Groceries wtf 15:57 061124,,45.21,,Bank:Checking
,,,45.21,,,Expense:Groceries
05/03/06,,EFTPOS 5632185,,75.00,,Bank:Checking
,,,75.00,,,Expense:Unallocated
06/03/06,,Transfer to J&J Doe Savings Acc 5765-8397 589654259587,,100.00,,Bank:Checking
,,,100.00,,,Bank:Saving
06/03/06,106,EFTPOS 98416,,100.00,,Bank:Checking
,,,90.91,,,Expense:Hardware
,,,9.09,,,Assets:GST:GST on Purchases
14/03/06,,Direct Credit Salary from Employers R Us,670.00,,,Bank:Checking
,,,,670.00,,Income:Salary
24/03/06,,Transfer to J&J Doe Savings Acc 5765-8397 589654259587,,500.00,,Bank:Checking
,,,500.00,,,Bank:Saving
28/03/06,,Mmvoin515b Internet Company bg??,,20.00,,Bank:Checking
,,,18.18,,,Expense:Internet
,,,1.82,,,Assets:GST:GST on Purchases
28/03/06,102,Light Company Big City Branch 9g8k863,,78.00,,Bank:Checking
,,,70.91,,,Expense:Electricity
,,,7.09,,,Assets:GST:GST on Purchases
28/03/06,103,Phone Company Name Autodebit 595642583,,45.00,,Bank:Checking
,,,40.91,,,Expense:Phone
,,,4.09,,,Assets:GST:GST on Purchases
28/03/06,104,April Rent 5 Short Road,,350.00,,Bank:Checking
,,,350.00,,,Expense:Rent
28/04/06,105,May Rent 5 Short Road,,350.00,,Bank:Checking
,,,350.00,,,Expense:Rent
_______________________________________________
gnucash-user mailing list
gnucash-user@gnucash.org
To update your subscription preferences or to unsubscribe:
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.
