Difference between revisions of "Using GnuCash"

From GnuCash
Jump to: navigation, search
m (Reports Included in GnuCash: indicated which items are menu headings)
m (Added link to Zero-Based Budgeting page)
 
(156 intermediate revisions by 20 users not shown)
Line 1: Line 1:
 +
[[Category:Usage]] [[Category:Help]]
 +
{| class="wikitable" style="margin: auto;"
 +
! scope="row"|Languages
 +
| [[{{PAGENAME}}|English]]
 +
| [[He/{{PAGENAME:שימוש}}|עִברִית]]
 +
|}
 
==Introduction==
 
==Introduction==
 
GnuCash is a complex piece of software with many features and possibilities. It requires some learning and experience to work fluently and effectively with it.
 
GnuCash is a complex piece of software with many features and possibilities. It requires some learning and experience to work fluently and effectively with it.
  
To get you started, it comes with a good [http://www.gnucash.org/docs/v2.0/C/gnucash-guide/ concepts guide], that will introduce you to some basic accounting concepts and explains how GnuCash works with those principles.
+
To get you started, it comes with a good [{{URL:docs:guide}}index.html Concept Guide], which will introduce you to some basic accounting concepts and explain how GnuCash works with those principles.
  
A second helpful source of information is the [http://www.gnucash.org/docs/v2.0/C/gnucash-help/help.html GnuCash manual], which is sort of a reference to all the menu options and dialogs in GnuCash. At the time of this writing, it is in need of an update as some new features in GnuCash aren't documented yet.
+
A second helpful source of information is the [{{URL:docs:manual}}index.html GnuCash Manual], which is a reference to the menu options and dialogs in GnuCash.
  
These two documents cover only a part of what GnuCash can do. This is to be expected as there are as many use cases as there are users of the tool. It would be impossible to contain all of these use cases in a manageable static document.
+
These two documents cover only a part of what GnuCash can do. This is to be expected, as there are practically as many use cases as there are users of the tool. It would be impossible to contain all of these use cases in a manageable static document. Instead, this page and the ones linked to below will show ways to deal with specific problems. The examples and solutions provided reflect solutions found by users of GnuCash for real world scenarios.  
  
Instead, this page and the ones linked to below will show ways to deal with specific problems. The examples and solutions are provided by users of GnuCash, most of them use (or have used) these solutions in real world scenarios.  
+
If you know a solution to a particular problem while using GnuCash, you are welcome to add it to these pages as well.
 +
 
 +
Alternatively you can send it to the [mailto:gnucash-user@gnucash.org GnuCash user mailing list]:
 +
# You need to be [{{URL:mail info}} subscribed] before you can post to this list without moderation.
 +
# Use a <tt>Subject</tt> in the lines of "GnuCash tip" or something similar, so it is easily recognized.
 +
:More details in [[Mailing Lists]].
 +
 
 +
== General Advice ==
 +
* [[GnuCash Quick Start Guide For Business Users]]. This is a quick start guide with some focus on the needs of business users.
 +
* [[Documentation|List to external international documentation]] and the official list of [{{URL:www}}docs.phtml available online documentation]
 +
 
 +
Here is a section of helpful tips on the use and functionality of GnuCash.
 +
*[[Keyboard Shortcuts]]
 +
* [[GnuCash on the Command Line]]
 +
* Using [[Scheduled Transactions]]
 +
* Online Banking: [[Setting up OFXDirectConnect]] and [[AqBanking]] (FinTS/HBCI)
 +
* [[Trading Accounts]]
 +
* [[Void Transactions]]
 +
* [[:Category:Cookbooks & Examples]]
 +
* Some examples can be written in other [[:Category:Lingua|langages]] or [[:Category:Region|region]] specific.
 +
* Some users have published their in [[Python_Bindings#Example_Usages|Python]] written tools.
 +
* [[Zero-Based Budgeting]]
 +
 
 +
== Register Tips ==
 +
 
 +
===Moving Multiple Transactions===
 +
There is no direct way to move a large number of transactions from one account to another in GnuCash.
 +
 
 +
If you are trying to move a small subset of an account's transactions, your best approach is to edit each transaction manually. You can speed this up somewhat by changing your register view to Transaction Journal mode, which will show all split lines at once. You may also find that copying the destination account and pasting it into the split line may help.
 +
 
 +
If you are trying move ''all'' (or MOST) of an account's transactions, you can get GnuCash to re-designate all your transactions at once by asking GnuCash to DELETE the account in question. For accounts that have transactions in them, GnuCash will ask whether you want to completely delete the transactions, or move them to a different account. Select the account you want all your transactions to go, and they will be moved. (To delete an account, open the Accounts window, select the account you want to delete, and click the Delete button).
 +
 
 +
===Move Transactions to Another GnuCash File===
 +
'''Note:''' Ensure backups are current then work with a temporary copy of original files and confirm the changes before overwriting original files.
 +
 
 +
Although accounts can have the same name in different files the GUIDs are unique, so they are different accounts. Moving transactions is a manual process to export transactions by account to csv and import them into another file (see [[CSV Import/Export]]). The first page of the export wizard gives guidance on which settings to select.
  
If you know a solution to a particular problem while using GnuCash, you are welcome to add it to these pages as well.
+
It might be easier to export the account(s) containing more transactions than need to be moved then edit the csv file in a spreadsheet or text editor.
  
Alternatively you can send it to the [mailto:gnucash-user@gnucash.org GnuCash user mailing list] ('''Note:''' you need to be [https://lists.gnucash.org/mailman/listinfo/gnucash-user subscribed] before you can post to this list). Use a subject in the lines of "GnuCash tip" or something similar, so it is easily recognized.
+
After confirming the transactions have been moved they can be deleted manually from the first file then confirmed again before overwriting original files.
  
To keep some overview, the examples and solutions are grouped per context in GnuCash.
+
===Import transactions from another program===
 +
You should have read [{{URL:docs:manual}}trans-import.html Help:Importing Transactions from Files].
 +
:;See also: [[CSV Import/Export]]
 +
Easy way to import a large amount of transactions from another your program (import from bank isn't covered by this tip)
  
==Reports Included in GnuCash==
+
If you use another program that may produce accounting data, tell your programmer to use QIF format to easy import into GnuCash.
 +
QIF format is simple and other programs can easy produce it. GnuCash's QIF importer is also easy to use and comes with self explaining wizard incorporated.
  
This section (currently in progress; based on GnuCash 2.2.9) could include a small amount of information about the standard reports in GnuCash, including when they might be used and some of the options included.
+
You can find QIF format description on [{{URL:wp}}Quicken_Interchange_Format Wikipedia] or ask uncle [https://www.google.com/search?&q=qif+format Google].
  
Account Summary
+
''--Provided by Pietro B.''
Assets & Liabilities -->
 
  Advanced Portfolio
 
  Asset Barchart
 
  Asset Piechart
 
  Average Balance
 
  Balance Sheet
 
  General Journal
 
  General Ledger
 
  Investment Portfolio
 
  Liability Barchart
 
  Liability Piechart
 
  Net Worth Barchart
 
  Price Scatterplot
 
Business -->
 
  Customer Report
 
  Easy Invoice
 
  Employee Report
 
  Fancy Invoice
 
  Payable Aging
 
  Printable Invoice
 
  Receivable Aging
 
  Vendor Report
 
Income & Expense -->
 
  Budget Report
 
  Cash Flow
 
  Equity Statement
 
  Expense Barchart
 
  Expense Piechart
 
  Expenses vs. Day of Week
 
  Income Barchart
 
  Income & Expense Chart
 
  Income Piechart
 
  Income Statement
 
  Income vs. Day of Week
 
  Trial Balance
 
Sample & Custom -->
 
  Sample Multicolumn Report
 
  Sample Report with Examples
 
  Welcome Sample Report
 
Tax Report & TXF Export
 
Transaction Report
 
Account Report
 
Account Transaction Report
 
  
 
==Reporting Tips==
 
==Reporting Tips==
 +
 +
As of GnuCash 2.6.15, the Tutorial & Concepts Guide contains a [{{URL:docs:guide}}ch_reports.html Reports chapter]. Please refer to the Guide for details about the reports included with GnuCash.
  
 
====A single report that shows summary amounts for multiple months for multiple accounts====
 
====A single report that shows summary amounts for multiple months for multiple accounts====
To create a single report that shows summary amounts for multiple months for multiple accounts (for example, monthly totals for expense accounts over the course of a year), you can accomplish this with a little creative thinking. The trick is to use Gnucash's Budgeting features.
+
To create a single report that shows summary amounts for multiple months for multiple accounts (for example, monthly totals for expense accounts over the course of a year), you can use the Transaction Report
  
:#First, create a Budget that includes the accounts upon which you ultimately want to base your report and a date range that is useful to you. Save this budget.
+
:#Choose your Accounts as the Accounts source in the Accounts tab, e.g. highlight "Expenses" and click "Select children"
:#Next, create a budget report, and in the options for this report, deselect the "Show Budget Amounts" check box. The resulting report will list monthly transaction totals for each account in the budget.
+
:#Set sortkeys in the Sorting tab: Primary key is Account Name, Primary subtotal is enabled. Secondary key is Date, secondary subtotal for date key is Monthly
 
+
:#Enable Subtotal table in the Display tab
This solution is not perfect (you must, for example, edit the budget to cover the date range you want, and then open the report), but it does give a spreadsheet-like summary of a subset of accounts by month.
 
 
 
''--Provided by David T.''
 
  
 
====Quicken-like "Overview" of your accounts====
 
====Quicken-like "Overview" of your accounts====
Line 102: Line 103:
 
For a simple transaction like this:
 
For a simple transaction like this:
  
<date> <ref no> "Cheques Received"
+
{| class="wikitable"
  <income account> "multiple payers"     <credit> £157.60
+
!colspan=4|
  <bank account>                         <debit>  £157.60
+
!debit
 +
!credit
 +
|-
 +
|<trans-date>||<ref-no>||"Cheques Received"
 +
|colspan=4|
 +
|-
 +
|colspan=2|
 +
|"multiple payers"||<income account>|| ||£157.60
 +
|-
 +
|colspan=3|
 +
|<bank account>||£157.60 ||
 +
|}
  
 
then on a Transaction Report for the bank account the details appear
 
then on a Transaction Report for the bank account the details appear
Line 113: Line 125:
 
transaction might well be entered like this:
 
transaction might well be entered like this:
  
<date> <ref no> "Cheques Received"
+
{| class="wikitable"
  <income account>  <first payer name>   <credit> £100.00
+
!colspan=4|
  <income account>  <second payer name> <credit> £ 57.60
+
!debit
  <bank account>                         <debit>  £157.60
+
!credit
 +
|-
 +
|<trans-date>||<ref-no>||"Cheques Received"
 +
|colspan=4|
 +
|-
 +
|colspan=2|
 +
|<first payer name>||<income account>|| ||£100.00
 +
|-
 +
|colspan=2|
 +
|<second payer name>||<income account>|| ||£57.60
 +
|-
 +
|colspan=3|
 +
|<bank account>||£157.60 ||
 +
|}
  
 
When this transaction is printed on the bank account's Transaction
 
When this transaction is printed on the bank account's Transaction
Line 123: Line 148:
  
 
To display the additional split detail, you need to set
 
To display the additional split detail, you need to set
General -> Style = Multi-Line and then you need to turn on Display ->
+
Display -> Detail Level = Multi-Line and then you need to turn on Display ->
Account Name.  Do NOT turn on Display -> Other Account Name.
+
Account Name.  Display -> Other Account Name is automatically disabled.
  
 
You can choose whether or not to turn off the full account name using
 
You can choose whether or not to turn off the full account name using
 
Display -> Use Full Account Name?   
 
Display -> Use Full Account Name?   
  
The Use Full Other Account Name? has no effect in this
+
''--Provided by Derek Atkins (in response to Colin Scott)''
configuration.
+
 
 +
====Monthly Income/Expense Reports====
 +
 
 +
You can export a GnuCash transaction report to HTML and then open the HTML file in Excel.
 +
You then have a ready-made data source from which you can create a pivot table with the
 +
transaction data grouped both by account and by period, with very few steps involved.
 +
 
 +
Two enhancements to the data to try are:
 +
  (1) grouping the dates by month; and
 +
  (2) parsing multilevel account names into columns labeled "Account1",
 +
  "Account2", etc. using the ":" delimiter.
 +
 
 +
This gives you a report that is very close to the monthly/quarterly/yearly income/expense report that MS Money and Quicken provide.
 +
 
 +
''--Provided by Martin Cunningham on gnucash-users''
 +
 
 +
====Reporting in single file resp. in landscape format====
 +
Currently, this isn't supported. Via HTML export, however, both options are possible, with the help of <tt>htmldoc</tt>. Sample call for landscape in european A4 format, which also puts alle single html files into one compound pdf:
 +
 
 +
<pre>
 +
htmldoc  -t pdf14 --webpage --no-links --linkstyle plain --size 297x210mm --headfootsize 9
 +
--header fff -f report.pdf *.html
 +
</pre>
 +
 
 +
====Exporting a report to OpenOffice Calc====
 +
Sometimes a report needs to be laid out slightly differently than is possible in GnuCash. One way to do this is to export the report to OpenOffice Calc. GnuCash can't export directly into that format, but it can export to html. OpenOffice Calc can import such a html file. Here's what to do:
 +
# Create your report in GnuCash
 +
# With the report open in front of you, select <tt>File -> Export -> Export Report</tt> to save the report somewhere in html. Note: there is also a tool bar button that does exactly the same thing.
 +
# Open OpenOffice Calc
 +
# Select <tt>Insert -> Link to external data</tt>
 +
# In the popup window, use the "..." button to find your exported report and below, choose "html all", then click ok.
 +
 
 +
This should load report in a Calc sheet. From here you can make tweaks as you like.
 +
 
 +
====Printing a Rental Report====
 +
 
 +
GnuCash does not have a special Rental Statement report. You need to decide if it is easier to do this in GnuCash or use the data from existing GnuCash reports to prepare the report in other software. This process describes preparing a rental statement in a spreadsheet for a cash accounting system assuming tenant pays into a bank account.
 +
 
 +
Example:
 +
A lease is commenced with T Smith on 1 Mar 2020 with rent of $250 per week payable each 4 weeks. Tenant must also pay water usage.
 +
 
 +
Bank transactions:
 +
<Syntaxhighlight lang="Console">
 +
Bank Statement
 +
 
 +
Date          Description  Withdrawal  Deposit
 +
06/03/2020  T Smith                    750.00
 +
28/04/2020  T Smith                  2,000.00
 +
01/05/2020  WaterCo          35.00         
 +
</Syntaxhighlight>
 +
 
 +
<div class="mw-collapsible mw-collapsed" data-expandtext="show more" data-collapsetext="hide me">
 +
In GnuCash the Water transaction is split into Expense:Water:Supply $20 and Expense:Water:Usage $15
 +
<div class="mw-collapsible-content">
 +
 
 +
<Syntaxhighlight lang="Console">
 +
Transaction Report
 +
 
 +
From 01/01/2020 to 15/05/2020
 +
 
 +
Date        Description Memo/Notes Transfer from/to                Debit    Credit
 +
 
 +
Assets:Current Assets:Checking Account
 +
06/03/2020 T Smith Rent          Income:Rent                      $750.00
 +
28/04/2020 T Smith Rent          Income:Rent                    $2,000.00
 +
01/05/2020 WaterCo                Split Transaction                          $35.00
 +
Total                                                            $2,715.00
 +
 
 +
Expenses:Utilities:Water:Supply
 +
01/05/2020 WaterCo                Split Transaction                $20.00
 +
Total                                                                $20.00
 +
 
 +
Expenses:Utilities:Water:Usage
 +
01/05/2020 WaterCo    T Smith    Split Transaction                $15.00
 +
Total                                                                $15.00
  
''--Provided by Derek Atkins (in response to Colin Scott)''
+
Income:Rent
 +
06/03/2020 T Smith Rent          Assets:Current Assets:Checking            $750.00
 +
28/04/2020 T Smith Rent          Assets:Current Assets:Checking          $2,000.00
 +
Total                                                                      $2,750.00
 +
 
 +
Grand Total                                                                    $0.00
 +
</Syntaxhighlight>
 +
 
 +
</div>
 +
</div>
 +
 
 +
Sample Spreadsheet Rental Statement:
 +
<Syntaxhighlight lang="Console">
 +
Rent Statement - T Smith Unit 83/2849 High Street MyCity 9999
 +
Payments are provisional upon tenant providing proof of payment
 +
 
 +
Date          Due        Payment    Balance Owing  Notes
 +
 
 +
01/03/2020  1,000.00                  1,000.00
 +
06/03/2020                750.00          250.00
 +
03/04/2020  1,000.00                  1,250.00
 +
01/05/2020  1,000.00                  2,250.00
 +
28/04/2020              2,000.00          250.00
 +
01/05/2020      15.00                    265.00  Water usage
 +
 
 +
                      15 May 2020                      1 of 1
 +
</Syntaxhighlight>
 +
 
 +
Process:
 +
# Load bank transactions into GnuCash
 +
# Print transaction report
 +
# Open transaction report in spreadsheet
 +
# Copy/Paste values transactions with tenants bills and payments. Take more than needed and delete extra lines.
 +
# Append rent due transactions
 +
# Copy Date, Due and Payment – use copy/paste special values if rent due calculated with formula
 +
# Sort by Date Ascending and Payment descending
 +
# Format amounts and ensure blank line after heading
 +
# Calculate Balance Owing
 +
 
 +
Note: These steps can be done in a working sheet and saved to a statement template
 +
 
 +
''--Content provided by flywire and transferred from FAQ''
 +
 
 +
== Stocks and Commodities ==
 +
 
 +
* [[stocks/add_stock|Add stock or mutual fund to portfolio]]
 +
* Get current [[Online Quotes]]
 +
* [[stocks/get_prices|Add historic prices to the price database]]
 +
* [[Importing fund or stock prices from an OFX file]]
 +
 
 +
== GnuCash on Holidays with your friend ==
 +
Yes you can use GnuCash while on holidays with your friend.
 +
 
 +
Here's what you need:
 +
- a computer + gnucash
 +
- a freshly created gnucash file
 +
- two people on holiday wanting to share costs.
 +
 
 +
Setup:
 +
1. Account schema:
 +
- Group Account "Cash", with one account named Me, and one named MyFriend
 +
- Group Account "Expenses", with accounts like Lunch, Dinner, AfterEight, Hostels, TravelCosts etc.
 +
- Group Account "Equity", with one account named Me, and one named MyFriend
 +
 
 +
2. Initial deposit:
 +
Before taking off, both you and your friend empty your wallets, and count the money inside. No need to create a third wallet. Gnucash will take care of that.
 +
Now say your friend has 15.50 in her wallet. The booking in GnuCash is : Cash:YourFriend +15.50  Equity:YourFriend -15.50
 +
See how easy that is?
 +
 
 +
3. After the holidays are over:
 +
Again count the money inside both of your wallets,
 +
then create the exact opposite booking for the remainder in cash.
 +
So let us say you paid for everything and your friend paid nothing,
 +
then the booking after the holidays would be: Cash:YourFriend -15.50  Equity:YourFriend +15.50
 +
 
 +
So now you see that Equity is really what you put into the holidays. In your friend's case: -15.50 + 15.50 = 0
 +
IMPORTANT: the Equity:Me and Equity:MyFriend show how much you put into the holiday each.
 +
Calculation to make 50-50: subtract the lower amount from the higher amount, then divide this subtracted amount by two. The result is what the owner of the lower amount should pay the owner of the higher amount.
 +
Example: You spent 150 dollars, your friend spend nada. Now 150-0=150 . 150 : 2 = 75. So your friend needs to pay you 75 dollars.
 +
 
 +
4. While on holidays, adding cash money:
 +
From whatever funding you add to your wallet (Cash:Me) it is a deposit
 +
so you can book the same as with (2. Initial deposit)
 +
 
 +
5. While on holidays, spending money:
 +
Now here is the interesting part. What was the money spent on?
 +
Whatever the answer, one thing is clear: Expenses:XXX go up, and some other account goes down.
 +
 
 +
5a. Option#1: While on holidays, your friend spends money to buy two beers, one for you, one for her (beer costs 2.20 each):
 +
This is where Expenses:XXX is debited (plus amount) and the Cash:YYY or Equity:ZZZ is credited (minus amount).
 +
- With cash payment, it is Cash:YourFriend -4.40  Expenses:AfterEight +4.40
 +
- However, if she paid with creditcard, it is Equity:YourFriend -4.40  Expenses:AfterEight +4.40
 +
 
 +
5b. Option#2: While on holidays, you spend money from your own cash on private affairs (private affairs cost 9.70).
 +
This is where Equity:Me is debited and Cash:Me is credited (less cash)
 +
- With cash payment, it is Cash:Me -9.70  Equity:Me  +9.70
 +
- If you used your own creditcard, it is Equity:Me -9.70  Equity:Me +9.70
 +
 
 +
This last line looks a little stupid for the laymen bookkeeping,
 +
so best use your own creditcard and not tell GnuCash about private affairs.
 +
 
 +
Enjoy !
 +
Best regards, Ron
 +
 
 +
==Tracking Reimbursable Expenses==
 +
 
 +
It is not uncommon for people to incur expenses that are later reimbursed.
 +
Let's assume travel expenses are going to be reimbursed by your
 +
employer. In effect, you're making a loan to your employer, which
 +
creates an account receivable. This is an asset account, not an expense,
 +
so it is kept completely separate from your own expenses.
 +
 
 +
The simplest solution is to set up an account receivable,
 +
'''Assets:Accounts Receivable:Employer''' or similar.
 +
(If you don't have any other accounts receivable, you can skip the
 +
intermediate level and just create '''Assets:Due from Employer'''.)
 +
Then when you buy let's say airline tickets for an employer-required trip:
 +
    Debit: Due from Employer
 +
    Credit:(whichever credit card you used)
 +
When your employer reimburses you, it's:
 +
    Debit: Cash and Checking Accounts
 +
    Credit:Due from Employer
 +
 
 +
Your employer probably has some sort of form that you fill out for
 +
reimbursement. While you ''could'' complicate the above scheme to generate
 +
reports for that form, in my opinion, it's less work overall to just
 +
write the expenses on your employer's form when they happen, and
 +
separately record them in GnuCash.
 +
 
 +
Where the simple scheme above will definitely help is at income tax
 +
time. You'll be able to show that ''X'' amount of money received from your
 +
employer was a non-taxable reimbursement for expenses, not a taxable
 +
payment of salary.
 +
 
 +
Original discussion at {{URL:mail archive}}2022-November/103477.html
 +
==Getting started with GnuCash' business features==
 +
 
 +
A prerequisite for using the Business features of GnuCash is that you have
 +
some business related accounts setup (Accounts Receivable/Accounts Payable).
 +
The easiest way to do this is to create a new file and add Business
 +
Accounts in addition to the Common Accounts.
 +
 
 +
To get to your invoice you could take these steps:
 +
# If you want GnuCash to deal with taxes properly you should first set up the proper tax tables for your country
 +
#* Select Business-Tax Table Editor
 +
#* Create a new tax table and set the proper tax amounts (for example, here in Belgium we have 21% VAT, so I have a tax table with one entry for 21%)
 +
# Customer
 +
#* Select Business->Customer->New Customer
 +
#* Enter the required fields
 +
#* Optionally select a tax table
 +
#* Hit ok
 +
# For your project, you can create a job
 +
#* Select Business->Customer->New Job
 +
#* Enter the information you need
 +
#* Note: in 2.2.x you have to use the "Select" button to enter a customer, the text field is read-only
 +
#* Hit ok
 +
# Now you can create your invoice
 +
#* Select Business->Customer->New Invoice
 +
#* Select a customer and job and set the other fields to your likings
 +
#* Hit OK
 +
#* The invoice entry window should appear now as a new tab in the main window
 +
#* Enter all the lines you wish to appear on your invoice in this window. The entry method is similar to the normal GnuCash register. Enter here for example your work, the items you sell to the customer and additional charges such as freight and insurance.
 +
#* Note that GnuCash will calculate the taxes and totals automatically. You find them at the bottom of the window.
 +
# When you are satisfied with the invoice entries, you have to post the invoice:
 +
#* Select Edit->Post Invoice
 +
#* Choose a post date and hit OK
 +
 
 +
That should be it. You can now print your invoice:
 +
* You can use either
 +
*# Reports->Business->Easy Invoice
 +
*# Reports->Business->Fancy Invoice
 +
*# Reports->Business->Printable Invoice
 +
*: I'm not exactly sure what the difference is. Just take the one you like best
 +
* Choose one of the three reports above
 +
* It will open with an empty window. Click on the options button (second button from the right in the toolbar) to configure your report.
 +
* The most important information is the invoice number (use the Select button)
 +
* Other than that, you can play a little with the layout of your invoice, by changing the other options.
 +
* When finished, hit ok and the invoice will display onscreen. You can choose to print it from there.
 +
 
 +
''-- Provided by Geert Janssens''
 +
 
 +
==Using third party software for data exploration and visualization==
 +
 
 +
You can use third party software like Excel, Tableau and Qlik Sense to explore and visualize GnuCash data through an ODBC connection as long as you save your data in sqlite format.
 +
 
 +
Here are instructions for Qlik Sense in Windows 10 (I chose Qlik Sense because it is a free download).
 +
 
 +
I wanted to visualize GnuCash transaction data without having to write templates and compile stuff, so I turned to QlikSense as they have a free personal edition (although similar steps would work for Tableau and other software that accepts ODBC connections). The trick is fairly simple:
 +
 
 +
# Download sqlite3 drivers (for windows: install the 64bit version from http://www.ch-werner.de/sqliteodbc/)
 +
# Export your GnuCash data as a sqlite file
 +
# Configure the ODBC providers in your operating system to point to the Gnucash export
 +
## Open Control Panel >> Administrative Tools >> Data sources (ODBC)
 +
## Click on System DSN, then on Add
 +
## Select Sqlite3 ODBC Driver, then click Create
 +
## Provide a name for the connection (GnuCash)
 +
## Under Database Name, click on Browse and select the file you exported from GnuCash
 +
## Click Ok, then Ok again to close the previous dialog
 +
# Open QlikSense, create a new app, load data via ODBC, select System DNS and the source you created earlier
 +
# In the Data Editor, open or create a section and write some SQL. This is the trickiest part. See an example below.
 +
# The results of the queries are automatically joined through columns of the same name; go into your app overview and start editing a sheet. The charts and tables you add will be tied together automatically!
 +
 
 +
Sample SQL Queries:
 +
 
 +
<pre>
 +
LIB CONNECT TO [GnuCash];
 +
 
 +
//*
 +
[expense_accounts]:
 +
SQL SELECT
 +
accounts.name AS parent_account,
 +
    p_acc.name AS account,
 +
    p_acc.hidden AS account_is_hidden,
 +
    p_acc.placeholder AS account_is_placeholder
 +
FROM accounts
 +
INNER JOIN accounts AS p_acc ON p_acc.parent_guid = accounts.guid
 +
WHERE accounts.account_type = 'EXPENSE'
 +
AND accounts.hidden = 0
 +
    AND p_acc.hidden = 0;
 +
//*/
 +
</pre>
 +
 
 +
User G2010a has a [{{URL:GH}}g2010a/gnucash-queries repository] with some more GnuCash-related queries.

Latest revision as of 01:15, 3 September 2024

Languages English עִברִית

Introduction

GnuCash is a complex piece of software with many features and possibilities. It requires some learning and experience to work fluently and effectively with it.

To get you started, it comes with a good Concept Guide, which will introduce you to some basic accounting concepts and explain how GnuCash works with those principles.

A second helpful source of information is the GnuCash Manual, which is a reference to the menu options and dialogs in GnuCash.

These two documents cover only a part of what GnuCash can do. This is to be expected, as there are practically as many use cases as there are users of the tool. It would be impossible to contain all of these use cases in a manageable static document. Instead, this page and the ones linked to below will show ways to deal with specific problems. The examples and solutions provided reflect solutions found by users of GnuCash for real world scenarios.

If you know a solution to a particular problem while using GnuCash, you are welcome to add it to these pages as well.

Alternatively you can send it to the GnuCash user mailing list:

  1. You need to be subscribed before you can post to this list without moderation.
  2. Use a Subject in the lines of "GnuCash tip" or something similar, so it is easily recognized.
More details in Mailing Lists.

General Advice

Here is a section of helpful tips on the use and functionality of GnuCash.

Register Tips

Moving Multiple Transactions

There is no direct way to move a large number of transactions from one account to another in GnuCash.

If you are trying to move a small subset of an account's transactions, your best approach is to edit each transaction manually. You can speed this up somewhat by changing your register view to Transaction Journal mode, which will show all split lines at once. You may also find that copying the destination account and pasting it into the split line may help.

If you are trying move all (or MOST) of an account's transactions, you can get GnuCash to re-designate all your transactions at once by asking GnuCash to DELETE the account in question. For accounts that have transactions in them, GnuCash will ask whether you want to completely delete the transactions, or move them to a different account. Select the account you want all your transactions to go, and they will be moved. (To delete an account, open the Accounts window, select the account you want to delete, and click the Delete button).

Move Transactions to Another GnuCash File

Note: Ensure backups are current then work with a temporary copy of original files and confirm the changes before overwriting original files.

Although accounts can have the same name in different files the GUIDs are unique, so they are different accounts. Moving transactions is a manual process to export transactions by account to csv and import them into another file (see CSV Import/Export). The first page of the export wizard gives guidance on which settings to select.

It might be easier to export the account(s) containing more transactions than need to be moved then edit the csv file in a spreadsheet or text editor.

After confirming the transactions have been moved they can be deleted manually from the first file then confirmed again before overwriting original files.

Import transactions from another program

You should have read Help:Importing Transactions from Files.

See also
CSV Import/Export

Easy way to import a large amount of transactions from another your program (import from bank isn't covered by this tip)

If you use another program that may produce accounting data, tell your programmer to use QIF format to easy import into GnuCash. QIF format is simple and other programs can easy produce it. GnuCash's QIF importer is also easy to use and comes with self explaining wizard incorporated.

You can find QIF format description on Wikipedia or ask uncle Google.

--Provided by Pietro B.

Reporting Tips

As of GnuCash 2.6.15, the Tutorial & Concepts Guide contains a Reports chapter. Please refer to the Guide for details about the reports included with GnuCash.

A single report that shows summary amounts for multiple months for multiple accounts

To create a single report that shows summary amounts for multiple months for multiple accounts (for example, monthly totals for expense accounts over the course of a year), you can use the Transaction Report

  1. Choose your Accounts as the Accounts source in the Accounts tab, e.g. highlight "Expenses" and click "Select children"
  2. Set sortkeys in the Sorting tab: Primary key is Account Name, Primary subtotal is enabled. Secondary key is Date, secondary subtotal for date key is Monthly
  3. Enable Subtotal table in the Display tab

Quicken-like "Overview" of your accounts

Quicken provides an "overview" of your accounts -- a list of today's balances for checking, savings, credit cards, major assets and liabilities.

For something similar in GnuCash, run the Balance Sheet report, then go to Options to pick the accounts that you want to see in the overview. Then just leave that tab open, and every time you start GnuCash you'll have the overview. Click "reload" if you put in transactions and want to see how things have changed.

--Provided by Anthony Dardis

Alternatively just keep the "Accounts" tab open. It shows the current balances of all the accounts too, without having to run a report. However, I don't use the business functions, or scheduled transactions, so that might perhaps make a difference.

--Provided by Mike Leone


Displaying Split Account Details in Transaction Report

For a simple transaction like this:

debit credit
<trans-date> <ref-no> "Cheques Received"
"multiple payers" <income account> £157.60
<bank account> £157.60

then on a Transaction Report for the bank account the details appear pretty much as above.

For clarity of the accounts, you might enter transactions with multiple splits referring to the same account. For example, the above transaction might well be entered like this:

debit credit
<trans-date> <ref-no> "Cheques Received"
<first payer name> <income account> £100.00
<second payer name> <income account> £57.60
<bank account> £157.60

When this transaction is printed on the bank account's Transaction Report, then under "other account" it simply prints "split" instead of the account name.

To display the additional split detail, you need to set Display -> Detail Level = Multi-Line and then you need to turn on Display -> Account Name. Display -> Other Account Name is automatically disabled.

You can choose whether or not to turn off the full account name using Display -> Use Full Account Name?

--Provided by Derek Atkins (in response to Colin Scott)

Monthly Income/Expense Reports

You can export a GnuCash transaction report to HTML and then open the HTML file in Excel. You then have a ready-made data source from which you can create a pivot table with the transaction data grouped both by account and by period, with very few steps involved.

Two enhancements to the data to try are:
 (1) grouping the dates by month; and 
 (2) parsing multilevel account names into columns labeled "Account1", 
 "Account2", etc. using the ":" delimiter. 

This gives you a report that is very close to the monthly/quarterly/yearly income/expense report that MS Money and Quicken provide.

--Provided by Martin Cunningham on gnucash-users

Reporting in single file resp. in landscape format

Currently, this isn't supported. Via HTML export, however, both options are possible, with the help of htmldoc. Sample call for landscape in european A4 format, which also puts alle single html files into one compound pdf:

htmldoc  -t pdf14 --webpage --no-links --linkstyle plain --size 297x210mm --headfootsize 9
--header fff -f report.pdf *.html

Exporting a report to OpenOffice Calc

Sometimes a report needs to be laid out slightly differently than is possible in GnuCash. One way to do this is to export the report to OpenOffice Calc. GnuCash can't export directly into that format, but it can export to html. OpenOffice Calc can import such a html file. Here's what to do:

  1. Create your report in GnuCash
  2. With the report open in front of you, select File -> Export -> Export Report to save the report somewhere in html. Note: there is also a tool bar button that does exactly the same thing.
  3. Open OpenOffice Calc
  4. Select Insert -> Link to external data
  5. In the popup window, use the "..." button to find your exported report and below, choose "html all", then click ok.

This should load report in a Calc sheet. From here you can make tweaks as you like.

Printing a Rental Report

GnuCash does not have a special Rental Statement report. You need to decide if it is easier to do this in GnuCash or use the data from existing GnuCash reports to prepare the report in other software. This process describes preparing a rental statement in a spreadsheet for a cash accounting system assuming tenant pays into a bank account.

Example: A lease is commenced with T Smith on 1 Mar 2020 with rent of $250 per week payable each 4 weeks. Tenant must also pay water usage.

Bank transactions:

Bank Statement

Date          Description  Withdrawal   Deposit
 06/03/2020   T Smith                    750.00
 28/04/2020   T Smith                  2,000.00
 01/05/2020   WaterCo           35.00

In GnuCash the Water transaction is split into Expense:Water:Supply $20 and Expense:Water:Usage $15

Transaction Report

From 01/01/2020 to 15/05/2020

Date        Description Memo/Notes Transfer from/to                Debit    Credit

Assets:Current Assets:Checking Account
 06/03/2020 T Smith Rent           Income:Rent                      $750.00
 28/04/2020 T Smith Rent           Income:Rent                    $2,000.00
 01/05/2020 WaterCo                Split Transaction                          $35.00
Total                                                             $2,715.00

Expenses:Utilities:Water:Supply
 01/05/2020 WaterCo                Split Transaction                 $20.00
Total                                                                $20.00

Expenses:Utilities:Water:Usage
 01/05/2020 WaterCo     T Smith    Split Transaction                 $15.00
Total                                                                $15.00

Income:Rent
 06/03/2020 T Smith Rent           Assets:Current Assets:Checking            $750.00
 28/04/2020 T Smith Rent           Assets:Current Assets:Checking          $2,000.00
Total                                                                      $2,750.00

Grand Total                                                                    $0.00

Sample Spreadsheet Rental Statement:

Rent Statement - T Smith Unit 83/2849 High Street MyCity 9999
Payments are provisional upon tenant providing proof of payment

Date          Due        Payment    Balance Owing  Notes

 01/03/2020   1,000.00                   1,000.00
 06/03/2020                750.00          250.00
 03/04/2020   1,000.00                   1,250.00
 01/05/2020   1,000.00                   2,250.00
 28/04/2020              2,000.00          250.00
 01/05/2020      15.00                     265.00  Water usage

                      15 May 2020                       1 of 1

Process:

  1. Load bank transactions into GnuCash
  2. Print transaction report
  3. Open transaction report in spreadsheet
  4. Copy/Paste values transactions with tenants bills and payments. Take more than needed and delete extra lines.
  5. Append rent due transactions
  6. Copy Date, Due and Payment – use copy/paste special values if rent due calculated with formula
  7. Sort by Date Ascending and Payment descending
  8. Format amounts and ensure blank line after heading
  9. Calculate Balance Owing

Note: These steps can be done in a working sheet and saved to a statement template

--Content provided by flywire and transferred from FAQ

Stocks and Commodities

GnuCash on Holidays with your friend

Yes you can use GnuCash while on holidays with your friend.

Here's what you need:

- a computer + gnucash
- a freshly created gnucash file
- two people on holiday wanting to share costs.

Setup:

1. Account schema:
- Group Account "Cash", with one account named Me, and one named MyFriend
- Group Account "Expenses", with accounts like Lunch, Dinner, AfterEight, Hostels, TravelCosts etc.
- Group Account "Equity", with one account named Me, and one named MyFriend
2. Initial deposit:

Before taking off, both you and your friend empty your wallets, and count the money inside. No need to create a third wallet. Gnucash will take care of that. Now say your friend has 15.50 in her wallet. The booking in GnuCash is : Cash:YourFriend +15.50 Equity:YourFriend -15.50 See how easy that is?

3. After the holidays are over:

Again count the money inside both of your wallets, then create the exact opposite booking for the remainder in cash. So let us say you paid for everything and your friend paid nothing, then the booking after the holidays would be: Cash:YourFriend -15.50 Equity:YourFriend +15.50

So now you see that Equity is really what you put into the holidays. In your friend's case: -15.50 + 15.50 = 0 IMPORTANT: the Equity:Me and Equity:MyFriend show how much you put into the holiday each. Calculation to make 50-50: subtract the lower amount from the higher amount, then divide this subtracted amount by two. The result is what the owner of the lower amount should pay the owner of the higher amount. Example: You spent 150 dollars, your friend spend nada. Now 150-0=150 . 150 : 2 = 75. So your friend needs to pay you 75 dollars.

4. While on holidays, adding cash money:

From whatever funding you add to your wallet (Cash:Me) it is a deposit so you can book the same as with (2. Initial deposit)

5. While on holidays, spending money:

Now here is the interesting part. What was the money spent on? Whatever the answer, one thing is clear: Expenses:XXX go up, and some other account goes down.

5a. Option#1: While on holidays, your friend spends money to buy two beers, one for you, one for her (beer costs 2.20 each): This is where Expenses:XXX is debited (plus amount) and the Cash:YYY or Equity:ZZZ is credited (minus amount).

- With cash payment, it is Cash:YourFriend -4.40   Expenses:AfterEight +4.40
- However, if she paid with creditcard, it is Equity:YourFriend -4.40  Expenses:AfterEight +4.40

5b. Option#2: While on holidays, you spend money from your own cash on private affairs (private affairs cost 9.70). This is where Equity:Me is debited and Cash:Me is credited (less cash)

- With cash payment, it is Cash:Me -9.70  Equity:Me  +9.70
- If you used your own creditcard, it is Equity:Me -9.70  Equity:Me +9.70

This last line looks a little stupid for the laymen bookkeeping, so best use your own creditcard and not tell GnuCash about private affairs.

Enjoy ! Best regards, Ron

Tracking Reimbursable Expenses

It is not uncommon for people to incur expenses that are later reimbursed. Let's assume travel expenses are going to be reimbursed by your employer. In effect, you're making a loan to your employer, which creates an account receivable. This is an asset account, not an expense, so it is kept completely separate from your own expenses.

The simplest solution is to set up an account receivable, Assets:Accounts Receivable:Employer or similar. (If you don't have any other accounts receivable, you can skip the intermediate level and just create Assets:Due from Employer.) Then when you buy let's say airline tickets for an employer-required trip:

   Debit: Due from Employer
   Credit:(whichever credit card you used)

When your employer reimburses you, it's:

   Debit: Cash and Checking Accounts
   Credit:Due from Employer

Your employer probably has some sort of form that you fill out for reimbursement. While you could complicate the above scheme to generate reports for that form, in my opinion, it's less work overall to just write the expenses on your employer's form when they happen, and separately record them in GnuCash.

Where the simple scheme above will definitely help is at income tax time. You'll be able to show that X amount of money received from your employer was a non-taxable reimbursement for expenses, not a taxable payment of salary.

Original discussion at https://lists.gnucash.org/pipermail/gnucash-user/2022-November/103477.html

Getting started with GnuCash' business features

A prerequisite for using the Business features of GnuCash is that you have some business related accounts setup (Accounts Receivable/Accounts Payable). The easiest way to do this is to create a new file and add Business Accounts in addition to the Common Accounts.

To get to your invoice you could take these steps:

  1. If you want GnuCash to deal with taxes properly you should first set up the proper tax tables for your country
    • Select Business-Tax Table Editor
    • Create a new tax table and set the proper tax amounts (for example, here in Belgium we have 21% VAT, so I have a tax table with one entry for 21%)
  2. Customer
    • Select Business->Customer->New Customer
    • Enter the required fields
    • Optionally select a tax table
    • Hit ok
  3. For your project, you can create a job
    • Select Business->Customer->New Job
    • Enter the information you need
    • Note: in 2.2.x you have to use the "Select" button to enter a customer, the text field is read-only
    • Hit ok
  4. Now you can create your invoice
    • Select Business->Customer->New Invoice
    • Select a customer and job and set the other fields to your likings
    • Hit OK
    • The invoice entry window should appear now as a new tab in the main window
    • Enter all the lines you wish to appear on your invoice in this window. The entry method is similar to the normal GnuCash register. Enter here for example your work, the items you sell to the customer and additional charges such as freight and insurance.
    • Note that GnuCash will calculate the taxes and totals automatically. You find them at the bottom of the window.
  5. When you are satisfied with the invoice entries, you have to post the invoice:
    • Select Edit->Post Invoice
    • Choose a post date and hit OK

That should be it. You can now print your invoice:

  • You can use either
    1. Reports->Business->Easy Invoice
    2. Reports->Business->Fancy Invoice
    3. Reports->Business->Printable Invoice
    I'm not exactly sure what the difference is. Just take the one you like best
  • Choose one of the three reports above
  • It will open with an empty window. Click on the options button (second button from the right in the toolbar) to configure your report.
  • The most important information is the invoice number (use the Select button)
  • Other than that, you can play a little with the layout of your invoice, by changing the other options.
  • When finished, hit ok and the invoice will display onscreen. You can choose to print it from there.

-- Provided by Geert Janssens

Using third party software for data exploration and visualization

You can use third party software like Excel, Tableau and Qlik Sense to explore and visualize GnuCash data through an ODBC connection as long as you save your data in sqlite format.

Here are instructions for Qlik Sense in Windows 10 (I chose Qlik Sense because it is a free download).

I wanted to visualize GnuCash transaction data without having to write templates and compile stuff, so I turned to QlikSense as they have a free personal edition (although similar steps would work for Tableau and other software that accepts ODBC connections). The trick is fairly simple:

  1. Download sqlite3 drivers (for windows: install the 64bit version from http://www.ch-werner.de/sqliteodbc/)
  2. Export your GnuCash data as a sqlite file
  3. Configure the ODBC providers in your operating system to point to the Gnucash export
    1. Open Control Panel >> Administrative Tools >> Data sources (ODBC)
    2. Click on System DSN, then on Add
    3. Select Sqlite3 ODBC Driver, then click Create
    4. Provide a name for the connection (GnuCash)
    5. Under Database Name, click on Browse and select the file you exported from GnuCash
    6. Click Ok, then Ok again to close the previous dialog
  4. Open QlikSense, create a new app, load data via ODBC, select System DNS and the source you created earlier
  5. In the Data Editor, open or create a section and write some SQL. This is the trickiest part. See an example below.
  6. The results of the queries are automatically joined through columns of the same name; go into your app overview and start editing a sheet. The charts and tables you add will be tied together automatically!

Sample SQL Queries:

LIB CONNECT TO [GnuCash];

//*
[expense_accounts]:
SQL SELECT
	accounts.name AS parent_account,
    p_acc.name AS account,
    p_acc.hidden AS account_is_hidden,
    p_acc.placeholder AS account_is_placeholder
FROM accounts
INNER JOIN accounts AS p_acc ON p_acc.parent_guid = accounts.guid
	WHERE accounts.account_type = 'EXPENSE'
	AND accounts.hidden = 0
    AND p_acc.hidden = 0;
//*/

User G2010a has a repository with some more GnuCash-related queries.