2.5. Storing your financial data

2.5.1. Overview

GnuCash offers several formats for storing your financial data. The default file storage format is XML, while SQL storage is available in SQLite, MySQL, and PostgreSQL formats. Users can choose a file format for new files from FileSave and for existing files from FileSave As... dialogs.

The XML storage format is a text file that by default is compressed, which is a preference that is set at EditPreferences General Compress files. SQLite storage is also available, and stores your data in a single file on your system, like the XML format. However, internally, an SQLite file is managed as a database. The MySQL and PostgreSQL storage options require access to a MySQL or PostgreSQL database server and the installation of additional database drivers on your machine.

Tip

Users can change the format at any time by using FileSave As.... This will create a copy of the data file in the selected format.

2.5.2. Storage Comparison and Recommendations

Each storage format has benefits and shortcomings that users should consider for their needs and abilities. See the Storage Comparison below for further details.

The XML format is the most stable and established, and for this reason, it is recommended for most users. SQL storage was added for the 2.4 release and has become an increasingly popular choice for users. The SQLite format allows users to realize the benefits of SQL storage without the overhead of installing or managing a full DBMS. MySQL and PostgreSQL require the installation of MySQL and PostgreSQL DBMS, respectively, and are best maintained only by experienced database administrators.

Note

Use of a SQL back end for storage implies to many that GnuCash has fully implemented DBMS features, including multi-user and incremental data manipulation. However, GnuCash does not currently implement these features, although it is a long term goal of the development team.

2.5.3. Storage Comparison Table

Table 2.1. Storage Comparison

XMLSQLiteMySQLPostgreSQL
Availability Built-in Depends on packaging[a]
File extension gnucash N/A[b]
Additional software None MySQL PostgreSQL
Additional expertise None Database Administrator
Compression gzip N/A
File Save On command On commit
Multi-user No No No No

[a] SQLite relies on an additional package and driver (called libdbi and libdbd-sqlite3, respectively), which are installed by default on Mac OS and Windows. Linux users may need to manually install these for SQLite.

MySQL and PostgreSQL may require the installation of additional software drivers (libdbd-mysql and libdbd-pgsql).

[b] MySQL and PostgreSQL place data within their own storage system.


2.5.4. Creating a file

To create a new GnuCash file do the following:

  1. From the GnuCash Menubar, choose FileNew File. The New Account Hierarchy setup assistant will start.

    Note

    If you are running GnuCash for the first time, you will be presented with the Welcome to GnuCash! screen. This screen is described in detail in the GnuCash manual.

  2. Set your preferences in the assistant and move through the screens with the Forward, Cancel and Previous buttons.

2.5.5. Saving data

Follow these steps to save the file under your preferred name:

  1. Choose FileSave As... from the Menubar or select the Save Toolbar button. GnuCash will bring up the save window.

  2. Select the Data Format of the file you are saving from the drop down list. The default selection is XML but if you have set up a database back end you can change to that format.

    Depending on the selected Data Format the window can change as described in the following.

    • If you selected XML or sqlite3 you will see a screen like this:

      Figure 2.2. Save screen when XML or sqlite3 is selected.

      The Save screen

      This image shows the Save screen when the selected Data Format is XML or sqlite3.


      Type your chosen filename in the Name field. It is not necessary to specify an extension when you write the file name. GnuCash will automatically add the extension .gnucash to the file.

      Note

      The .gnucash extension was introduced in the 2.3 series of GnuCash. For already existing files, the extension will never be changed. So if you open an existing file named Myoldfile, that name won’t be changed if the file is saved. You might use the Save As... command and give the file a new name in order to have it saved with the extension .gnucash.

      Select the path where the file will be saved by browsing the tree in the lower panes.

      Tip

      Click on the Create Folder button to create a new folder with a custom name in the selected path.

    • If you selected mysql or postgres Data Format you will see a screen like this:

      Figure 2.3. Save screen when mysql or postgres is selected.

      The Save screen

      This image shows the Save screen when the selected Data Format is mysql or postgres.


      Enter in this window the Database Connection information: Host, Database, Username and Password.

      Warning

      Saving to mysql or postgres requires the proper permissions in that database, that is you need to have the permissions to create a new database with the given database name, or you need to have write access to an existing database with the given database name.

  3. Click the Save As button to save the file.

If you are keeping track of finances for a single household, you need only one file. But if you are also tracking business finances or want to keep data separate for some reason, then you will need more than one file.

Before ending each GnuCash session, be sure to save your data changes using FileSave or the Save Toolbar button.

Note

As it is very important to save your data frequently to avoid losing them for whatever reason, GnuCash is able to automatically save the opened file every a certain amount of time. This interval can be set in the General tab under EditPreferences (GnuCashPreferences on MacOS). Keep in mind that this option is relevant only if you are saving in XML format. If you are working with a database, the Save button and the Save menu entry will be grayed out because changes are stored right away.

2.5.6. Opening data

To open an existing file or database, select FileOpen from the menu. In the window that will open, select the Data Format. If you selected File choose the file you want to open by browsing the folders in the lower panes. Else, enter the required Database Connection information.

Tip

GnuCash keeps a list of the recently opened files. Open the File menu and you will see listed the names of recently opened files. Click on the one you want to load to open it.

2.5.7. Duplicating an Account Hierarchy

In some cases, it might be useful to duplicate the structure of an existing data file in a new file. For example, you might want to try out new accounting techniques without corrupting your actual accounting data, or you might need to follow accounting guidelines that require you to close your books at the end of the year and begin each year with a fresh set of books.

GnuCash allows you to create an empty copy of your Chart of Accounts simply by selecting FileExportExport Accounts. When you select this command, you are asked to provide the name for the new empty file, and GnuCash creates a new data file that contains only your account hierarchy (that is, there is no transaction data). Once saved, the new file can be opened like any other GnuCash data file as described above.