Skip to content

Adapting your own spreadsheet to export data

Jump to bottom
OneClickToQif edited this page Feb 29, 2016 · 3 revisions

If you already have a spreadsheet and you want to export it to QIF, you have two options:

  • Move the data to the sample spreadsheet templates provided, by copying and pasting in the appropriate columns.

or:

  • Adapt your spreadsheet to use the project macros. This section details this option

You only need to do this once; then you can export your data to QIF with just one click from your spreadsheet each time you insert new transactions.

Note: You do not need any knowledge of macros to adapt your own spreadsheet. However, you need a minimum knowledge of Excel. If you need specific help adapting a spreadsheet, refer to the "How to get help" section.

First, you must determine if what you have is a spreadsheet with single or split transactions.

Simple vs. Split Transactions

If you do not know what split transactions are, you likely have simple transactions. Go to the section for simple transactions. If you want to be sure, continue reading.

Every transaction has at least two splits, but a transaction can have more than two splits.

A transaction with only two splits is called a simple transaction, since it only involves the current account and a single remote account. An extract from the bank account or credit card uses simple transactions, because it only involves the (bank or credit card) account and the remote account to which the money goes.

A transaction with three or more accounts is called split transaction. Such transactions are used for movements carrying amounts to different accounts, such as payrolls.

Gnucash documentation explanins these types of transactions here:

http://www.gnucash.org/docs/v2.6/C/gnucash-guide/txns-registers-txntypes.html

How to adapt a spreadsheet with simple transactions

Insert a new row under the header, which should be row # 2 (*). Tag this row of the spreadsheet with the following labels (without quotes):

  • “(Memo)” column: el comentario del movimiento
  • “(Category)” column: the category. In Gnucash this corresponds to the target account. Based on this value, the transaction will be imported into the appropriate account. In the sample templates this field is validated from a list.
  • “(Exported)” column: this field indicates whether the transactions have previously been exported. Only transactions with "N" value will be exported. The export macro will automatically set the value to "Y".
  • “(Amount)” column: transaction value
  • “(Date)” column: date of the transaction

The next step is to import the macros:

  1. Download these files:

    • Module_export.bas
    • Module_format.bas
    • Module_util.bas

    from the project at: https://github.com/OneClickToQif/OneClickToQif

  2. Press “Alt+F11” to open Visual Basic editor.

  3. Click right button on the VBAProject, and select “Import File”.

  4. Import the three files above

To begin to export you have two options:

  • Press “Alt+F8”, then select “ExportAllToQIF” macro

or:

  • Add a button to run the macro: At “Programmer” menu tab, “Insert” button, Form controls, click the button item to create a button in the spreadsheet. Then assign the “ExportAllToQIF” macro to the new button.

(*)The position of the labels row can not be changed for the simple transactions spreadsheet. If you need it, make a request at the address in the “How to get help” section.

How to adapt a spreadsheet with split transactions

If you have a spreadsheet with split transactions:

  1. Insert a new row. It can be at any position (any row number) on the top of the data.
  2. In this new row:
    • Label the first cell (column #1) as “QIF”.
    • Label the cell over the date column in the former row as “Date”
    • Label the cell of the export column as “Exported”. This column indicates which transactions have already been exported.
    • Label the cell over the column with comments as “Memo”
    • Label the cell over the columnn with the amount going to the actual account as “Main”
    • Label the cells over other columns for amounts going to other accounts, with the name of those accounts. If you want to invert the amount when exporting (from positive to negative and viceversa), append “(neg)” to the label.

The next step is to import the macros:

  1. Download these files:
  2. Press “Alt+F11” to open Visual Basic editor.
  3. Click right button on the VBAProject, and select “Import File”.
  4. Import the two files above

To begin to export you have two options:

  • Press “Alt+F8”, then select “ExportAllToQIF” macro

or:

  • Add a button to run the macro: At “Programmer” menu tab, “Insert” button, Form controls, click the button item to create a button in the spreadsheet. Then assign the “ExportAllToQIF” macro to the new button.

Note: The payroll sample in this project is made from the point of view of accounting of an employee. The example in the GNUCash documentation is made from the point of view of a company accounts to pay salaries.

Clone this wiki locally