|
Vettrasoft
Z Directory > SUPPORT > convert-to QIF example
|
Z Directory: about the QIF file generator program
This web page provides some background and history about a program that reads data from a file that uses a custom format, and generates a QIF file suitable for import into Gnu's GnuCash accounting program. A "QIF file" is a file containing accounting transaction data in Quicken Interchange Format.
This program is available for downloading at www.vettrasoft.com. It is a small program, and it was designed, written, built, debugged, tested, and production run all inside of 1 day: Sunday March 06, 2011. This point emphasizes that software can be created quickly with the Z Directory. The reason for the program: usually (at least for home or small business use) transactions are entered into an accounting program manually, by hand. Some data is voluminous, and it would be much preferable to load it into the accounting program via some automated means (the basic premise of most computer data processing).
Telephone Call Detail Records (CDRs) is a case in point. Call data can be and usually is voluminous, perhaps too much so for manual data entry. A quicker solution could be to take a file full of such information, apply a program to it to generate another file in an appropriate format, then take that file as input to the accounting program. In this example the data comes from such transactions maintained in an excel spreadsheet file. Data from an excel file can be easily transformed to fixed column fields in a text file, but to convert to QIF transactions requires applying a program to the data.
We will follow the process of taking this data to such a program, then taking the results to GnuCash. Data processing in large accounting systems often involves transforming transactional data in files from one format to another - this is no exception. Given CDR data in an excel spreadsheet:
In this example, Gnucash was first used with a start date of Dec 01, 2010. All starting balances are entered on this date. In the picture above, the yellow block indicates phone calls that are to be entered as individual transactions in GnuCash. All call records prior to this date will be ignored, as they occured before "day 0" of the accounting system. If we examine the highlighted row in this file, we find the following information:
- the call was made on Dec 23, 2010 (time of call is unknown).
- the "reference number" field contains the telephone number called to (DNIS). In this case, the number is (858) 279-5919.
- In the description field, we see that the number is in San Diego, CA.
- the call duration was 8 minutes 25 seconds, at a cost of 4.5 cents / minute, for a net cost of $0.41.
Our end goal is to get the data into Gnucash, so we will end up with a journal entry like so:
| Date | Account | DR | CR |
| 12/23/2010 | telephone calls expense | 0.41 | |
| 12/23/2010 | prepaid telephone | 0.41 |
(Note: the files referred to In the following discussion can be found in the downloadable zip file in the SAMPLES area of the web site.)
The data that we are interested in, the yellow block of the excel spreadsheet ("PHONE_BILL.xls") is cut and pasted (manually, or perhaps via some program) into a text file ("phonecalls-fixed_columns.txt"). This data will be the input for the program:
We need to know what the account name is, to be applied to each transaction. In GnuCash, the name is in the Chart of Accounts (the COA):
Here we see that the expense account is "phone calls". In GnuCash syntax, the full 'path' of this account is "Expenses:phone:phone calls".
Next, the program is run, in a Microsoft command window ("cmd"), with this line:
qif_maker -in phonecalls-fixed_columns.txt -out cdr.qif -account "Expenses:phone:phone calls"
The program creates an output file ("cdr.qif"), which looks like so:
Inside the accounting program.
The final procedure is to guide this file through the 'import' procedure in GnuCash, with the help of their wizard ("druid"?). In this section, we do a quick walk-through of the steps required to complete the phone calls account:
- enter a starting balance;
- import the call records;
- create a recurring transaction ("scheduled transaction", in GnuCash terminology) for the monthly service charge;
- add any additional transactions - in this case, deposit of $25 on Feb 23rd.
Step [1] is done by double-clicking on the "prepaid phone" account (under Assets > Current Assets, in the COA), which opens the transaction journal for this account. The amount entered, $47.82, is the balance in the prepaid phone account as of 12/01/2010:
For step [2], go File > Import > Import QIF.., which brings up a wizard. Many pages are simply informational, requiring just clicking on the FOrward button. In the second window, drill down to the file to import ("cdr.qif"):
The window for setting the default account name is important. It is the account that these transactions all belong to ("prepaid phone"). Enter its 'full path' name, "Assets:Current Assets:prepaid phone", then press Confirm:
GnuCash does a lot of checking and confirming. In the window titled "QIF files you have loaded", it should show the file. Since this is the only file of interest, just press Forward. Do likewise (eg, skip) for "Accounts and stock holdings" window. In the "Match QIF accounts with GnuCash accounts" window, they show off their ability to guess which account you meant for the default account. Make sure it matches up and press Forward. Skip (eg press Forward) the "Income and Expense Categories" window. In "Match QIF categories with GnuCash accounts", there should be the singleton entry "Expenses:phone:phone calls". Skip forward. Also skip forward the "file currency" window. In Update your GnuCash accounts", press Apply. This will populate the transactions in GnuCash:
Note that the ending balance, $28.92, does not reflect the service charges or final deposit:
$28.92 - $15.00 {3 $5 service charges} + $25.00 {deposit} = $38.92
$38.92 is the ending balance that can be found in the original excel file. The final deposit can be entered directly in the the "prepaid phone" journal here. Note that in the general ledger, we can see our detailed descriptions:
The final step is to set up a recurring transaction. The details of how to do this is outside the scope of this discussion. Briefly, go Actions > Scheduled Transactions > Scheduled Transactions Editor, press "New" in the toolbar, assign it a name (eg, "monthly phone charge"). Make sure "Enabled" and "Create automatically" are checked-selected. In ther Frequency tab, set the "Start Date" to 12/01/2010. Leave the 'Frequency' to Monthly (the default); Every [1] months; 'On the' [17th]. In the "Template Transaction" tab, match the form to reflect this:
| memo | Account | Debit Formula | Credit Formula |
| monthly service charge | |||
| Expenses:phone:service charge | 5.00 | ||
| Assets:Current Assets:prepaid phone | 5.00 |
You may need to exit out of GnuCash and re-open it to see the resultant transactions.
The program need not restrict itself to telephone call data. It can be used for multiple transactions for any 2 accounts (the debit side and the credit side). This program, along with the GnuCash data, input and output files, can be downloaded from the samples page .
This program, as provided here, was not intended to be the final program for doing this task. It was provided as a "quick and dirty" example of using the Z Directory in financial applications. The column values for processing the input file are hardcoded in the program. The QIF transaction type - "Cash" is also hardcoded. There are several other [unexplored] QIF types available. There may be better ways to accomplish the task. However, the program met the specs - all in well under a days' work.