Let's say that you have expenses saved in your smartphone in a spreadsheet application. You would like to import these transactions into GnuCash.
- Prerequisites: A smartphone with a spreadsheet application. You have saved a few cash transactions in the spreadsheet application.
- Connect your phone to your PC and upload the spreadsheet file. You can upload to any popular spreadsheet of your choice such as OpenOffice.org4Calc or Excel2 or Google Docs3.
- Open the spreadsheet in your PC and save the data in a CSV (Comma Separated Values) format. OpenOffice.org Calc and Excel will you give you an option in the Save As dialog to choose a type. Google Docs has a Download As CSV option for its spreadsheet.
- When you have completed the preceding step, you should have a file named
expenses.csvin your PC with the saved data. - Prerequisites: Make a backup of your GnuCash accounts data file first. Please refer to the Backing up section in Chapter 10, Adapting Gnucash for Non-profits and Personalizing.
- Prerequisites: From the menu, select the Edit | Preferences, General tab, Files pane. Set Auto-save time interval to 0 (Do not save automatically). This will give you the option of closing GnuCash without saving, if the data import runs into any problems, and start over.
- In GnuCash from the menu, select File | Import | Import CSV/Fixed Width. In the Select an CSV/Fixed Width file to import dialog, find the folder where you have saved the
expenses.csvfile, select it, and click Import. - The Import CSV/Fixed Width File dialog will open. In the date format, select m-d-y. Below that, you should see the Select the type of each column below pane showing your data. Click on None in the header for the first column. A drop-down list will appear. If that is your date column, select Date. For the amount, select Withdrawal because these are all expenses. Similarly, select the type of each column, as shown in the following screenshot:
- Once you have completed selecting the type for all the columns, click OK. The Select Account dialog will open to let you select an account for the source of funds. Select the Cash account under Current Assets in Assets and click OK. The Generic import transaction matcher dialog will open, as shown in the following screenshot:
- All the lines are yellow to show that none of them have been matched with an account. Double-click on the first row. The Select Account dialog will open again to let you select a "use of funds account" for that line. Select the Office Supplies account under Expenses, as shown in the following screenshot.
- Click on OK. That line will turn green and the Info column will be updated to show the selected account, as shown in the following screenshot:
- Continue double-clicking on each yellow row that has the text "need account to transfer" to select the matching use of funds account. Once all rows have turned green, click OK to complete the import.
- What data should you enter into the mobile phone spreadsheet application? Not a lot. As you saw, GnuCash can only import four columns. So, this is all you need:
- Date
- Description
- Amount
- Receipt number
Tip
Leave the safety door open
Before importing, we recommended turning off the automatic save. If you run into any problems while importing, you can exit without saving and restart from where you were before the import. Now that you have completed the import successfully, don't forget to turn Auto-save back on.
As you saw in the tutorial, while you can map the use of funds to different accounts for each transaction, the source of the funds account is a single one. What if you are paying cash, by check, as well as by credit card, for different expenses? In that case, you need to create separate files for cash, check, and credit card so that each will go into its matching account.
There are two ways of saving expense data in your mobile phone and uploading to your PC.
If you have a smartphone with an application that can edit a spreadsheet, you can enter and save the data in the spreadsheet and sync with your PC or upload to your PC. Then you can import that into GnuCash, as you saw in the preceding tutorials.
However, if you don't have a spreadsheet application, you can use a note-taking application. If you use a note-taking application, the data you enter should look like the following:
10/8/2010,Ink cartridge,209237, $44.50
10/8/2010,Gas,1239, $27.65
10/12/2010,Trash collection,8095, $50.00
When you use a note taking application, please follow these guidelines:
- As you see in the preceding example, each field should be separated by a comma, a semicolon, or a colon. Whichever punctuation you select as the separator, just be consistent throughout that file.
- Make sure you click the Enter key at the end of each transaction so that the next transaction starts on a new line.
- The
$sign is optional. If it is convenient for you, enter it. Otherwise feel free to leave it out. - Do not use any commas, or other separator such as semicolon or colon that you chose, within the description.
- Once you have successfully uploaded the file to your PC, make sure to change the filename extension to CSV before attempting to import into GnuCash.
- How do you make sure that transactions paid in cash, paid by check, and paid by credit card are imported into their matching accounts?
a. Select the appropriate matching account in the Select Account dialog.
b. Create separate files for each type of payment.
c. GnuCash will automatically match them by payment type.
d. Flag each payment as cash, check, or credit.