<?xml version="1.0" encoding="UTF-8"?>
<chapter id="details.impexp">
<chapterinfo>
<authorgroup>
<author> &Ace.Jones; &Ace.Jones.mail; </author>
</authorgroup>
<date>2011-07-3</date>
<releaseinfo>4.6</releaseinfo>
</chapterinfo>
<title>Importing and Exporting</title>
<sect1 id="details.impexp.gnucash">
<sect1info>
<author>&Tony.Bloomfield; &Tony.Bloomfield.mail;</author>
</sect1info>
<title>GnuCash Importer</title>
<sect2>
<title>GnuCash Files</title>
<para>
The &kappname; GnuCash importer handles direct reading of standard (&XML;)
files as produced by GnuCash versions 1.8 and 2.0. The following are not
supported:
</para>
<itemizedlist>
<listitem><para>import of database (Postgres) data</para></listitem>
<listitem><para>import of 'multi-book' files</para></listitem>
<listitem><para>import into an existing &kappname; file</para></listitem>
<listitem><para>import of small-business specific features (Employees,
Invoices, etc.)</para></listitem>
<listitem><para>export to GnuCash files.</para></listitem>
</itemizedlist>
<para>
The import will probably only work correctly if presented with a valid
file. It is recommended that the GnuCash <guimenuitem>Check & Repair All</guimenuitem> function (in
the <guimenu>Actions</guimenu> menu) be run before attempting to import.
</para>
<para>
Files can be opened by specifying the filename on the command line
(<command>kmymoney <path to file></command>), or by means of the &kappname;
<menuchoice>
<shortcut><keycombo>&Ctrl;<keycap>O</keycap></keycombo></shortcut>
<guimenu>File</guimenu><guimenuitem>Open</guimenuitem>
</menuchoice> or
<menuchoice>
<guimenu>File</guimenu><guimenuitem>Import</guimenuitem>
</menuchoice> menu items.
</para>
<para>
The similarity between the two products means that much day-to-day data can be
imported in a straightforward fashion. However, there are some areas where
differences arise, and various options are provided to deal with these. The
following sections will describe some of these differences; understanding them
should lead to a smoother importation.
</para>
</sect2>
<sect2>
<title>Similarities, Differences, and Terminology</title>
<sect3>
<title>Small Business Usage</title>
<para>
It should be noted that &kappname; is a <emphasis>personal</emphasis> finance
manager, and as such, does not directly support any of the business features
of GnuCash, such as tax tables, payroll, and tracking of lots. Any Accounts
Payable or Receivable accounts found in a file will be imported as Liability
or Asset accounts respectively.
</para>
</sect3>
<sect3>
<title>Accounts</title>
<sect4>
<title>Account types</title>
<para>
For both products, the highest level of structure in the file is the
account. &kappname; supports 5 main types of account: Asset, Liability,
Income, Expense and Equity, each of which may have various subtypes, ⪚,
Checking, Credit Card, &etc;. &kappname; includes a 'standard' account for
each of these five types, and all other accounts are held subordinate to one
of these. &kappname; enforces more consistency (or less flexibility, depending
on your point of view) between account types than does GnuCash, and the
importer will correct any inconsistencies it detects. This may result in a
slightly different account structure, though this can, within reason, be
amended after the import is complete.
</para>
</sect4>
<sect4>
<title>Categories</title>
<para>
&kappname; uses the term Category to denote an account of an Income or Expense
type. Unlike GnuCash, these are not considered as 'ledger' accounts, and entry
of transactions folder into categories is not supported; allocations are made
during transaction entry into other account types.
</para>
</sect4>
<sect4>
<title>Structure and Placeholders</title>
<para>
GnuCash supports the use of Placeholder accounts. In effect, these are just
read-only accounts into which no transactions can be entered, but which
function in an analogous fashion to folders in a folder structure, as a
holder for other accounts. Though &kappname; does not support this feature as
such, it does provide a parent/child account relationship, so the importer
simulates placeholders by creating empty accounts.
</para>
</sect4>
<sect4>
<title>Account Type map</title>
<informaltable frame='all'>
<tgroup cols='2' align='left' colsep='1' rowsep='1'>
<thead>
<row>
<entry>GnuCash type</entry><entry>&kappname; type</entry>
</row>
</thead>
<tbody>
<row>
<entry>BANK</entry><entry>Checking</entry>
</row>
<row>
<entry>CHECKING</entry><entry>Checking</entry>
</row>
<row>
<entry>SAVINGS</entry><entry>Savings</entry>
</row>
<row>
<entry>ASSET</entry><entry>Asset</entry>
</row>
<row>
<entry>CASH</entry><entry>Cash</entry>
</row>
<row>
<entry>CURRENCY</entry><entry>Cash</entry>
</row>
<row>
<entry>MONEYMRKT</entry><entry>MoneyMarket</entry>
</row>
<row>
<entry>STOCK</entry><entry>Stock</entry>
</row>
<row>
<entry>MUTUAL</entry><entry>Stock</entry>
</row>
<row>
<entry>EQUITY</entry><entry>Equity</entry>
</row>
<row>
<entry>LIABILITY</entry><entry>Liability</entry>
</row>
<row>
<entry>CREDIT</entry><entry>CreditCard</entry>
</row>
<row>
<entry>INCOME</entry><entry>Income</entry>
</row>
<row>
<entry>EXPENSE</entry><entry>Expense</entry>
</row>
<row>
<entry>RECEIVABLE</entry><entry>Asset</entry>
</row>
<row>
<entry>PAYABLE</entry><entry>Liability</entry>
</row>
</tbody>
</tgroup>
</informaltable>
</sect4>
</sect3>
<sect3>
<title>Transactions and Splits</title>
<sect4>
<title>Balanced transactions</title>
<para>
As with GnuCash, data is entered in the form of transactions, each generally
consisting of 2 or more split entries. In fact, valid GnuCash transactions
will always contain at least 2 splits, and to conform to GnuCash's
double-entry bookkeeping standard, these must be in monetary balance (i.e.,
they must balance out to zero). &kappname; encourages, but does not enforce,
this standard, but any imported transaction which is not balanced will be
marked in the ledger view as having a problem.
</para>
</sect4>
<sect4>
<title>Payees</title>
<para>
&kappname; prefers that all transactions have a Payee (a generic term that
encompasses both payees and payers), and unlike GnuCash, a list of these
payees is maintained. Payee names are generated by the importer from the
GnuCash transaction's Description field.
</para>
</sect4>
<sect4>
<title>Transfers</title>
<para>
&kappname; uses the term Transfer to describe a transaction which does not
involve a Category, but only transfers money between Asset and/or Liability
accounts.
</para>
</sect4>
<sect4>
<title>Reconcile</title>
<para>
&kappname; provides an account reconciliation function similar to that of
GnuCash, and the corresponding transaction status will be imported.
</para>
</sect4>
</sect3>
<sect3>
<title>Commodities</title>
<para>
GnuCash uses the term Commodity to cover both currencies and non-currency
assets. These are treated separately in &kappname;.
</para>
<sect4>
<title>Currencies</title>
<para>
&kappname; has built-in support for all foreign
<link linkend="details.currencies">currency</link> types. &kappname; also
requires that the user specify a base currency, this being the default
currency for new accounts. The importer will attempt to determine the most
likely base currency, though this choice may be rejected in favor of an
alternative.
</para>
<para>
(NOTE: &kappname; does not currently support accounts denominated in 'defunct'
currencies (except those replaced by the Euro). At present, it will be
necessary to remove any such accounts from your GnuCash file before
importing. We hope to improve on this situation in a future release.)
</para>
</sect4>
<sect4 id="gncsecurities">
<title>Securities and Investments</title>
<para>
Non-currency assets (normally stocks and bonds) are called Securities by
&kappname;, and represent the main difference between the two products, in
that &kappname; requires any account denominated in a security to be
subordinate to an Investment Account. This is described in more detail in the
chapter on <link linkend="details.investments">Investments</link>. Though
users may have implemented such a relationship, GnuCash imposes no defined
structure on it, so the importer is unable to detect it and perform an
automatic conversion. Three options are therefore made available:
</para>
<itemizedlist>
<listitem>
<para>Create a separate Investment account for each security, with the same
name as the security</para>
</listitem>
<listitem>
<para>Create a single Investment account which will act as 'parent' for all
security accounts</para>
</listitem>
<listitem>
<para>Create several Investment accounts, and assign securities to them as
directed by the user.</para>
</listitem>
</itemizedlist>
<para>
It depends entirely on user requirements which of these options is relevant in
each situation, and in some cases, manual restructuring of accounts after
importation may be necessary.
</para>
</sect4>
<sect4>
<title>Prices and currency rates</title>
<para>
Security prices and currency exchange rates as displayed in the GnuCash Price
Editor will be imported. In addition, price and rate entries will be generated
from all transactions involving securities and multiple currencies.
</para>
</sect4>
<sect4 id="details.impexp.gncquotes">
<title>Online Quotes</title>
<para>
For obtaining online price and currency rate quotations, GnuCash uses a
package called Finance::Quote. Recent versions of &kappname; contain support
for this package for obtaining stock quotes, and this will be used by default
when importing data. You may however select to convert to the native method
used by &kappname; which is covered in more detail in
<link linkend="details.investments.onlinequotes">online quotes</link>.
</para>
<para>
If you choose to do so, the following dialog will allow selection of a
'native' &kappname; price source, or a user-defined source, for each account
for which online quotes are required. However, the stock (ticker) symbol will
be imported unchanged. Since this symbol will almost certainly be different in
the two packages, it will need to be manually edited after completion of the
import process. Future currency rate updates will not use Finance::Quote, and
will always use the native retrieval method.
</para>
<para>
<screenshot>
<mediaobject>
<imageobject>
<imagedata fileref="gnucash-select_price_source.png" format="PNG" />
</imageobject>
</mediaobject>
</screenshot>
</para>
</sect4>
</sect3>
<sect3 id="gncschedules">
<title>Scheduled Transactions</title>
<para>
&kappname; does not retain the separation made in GnuCash between template
transactions and their frequency of occurrence. Transaction data will be
duplicated if the same template is used in different schedules, but this is
not likely to be of great significance.
</para>
<sect4>
<title>Schedule types</title>
<para>
&kappname; classifies all schedules as one of three types, Bills, Deposits, or
Transfers. Since GnuCash does not make such a distinction, the importer
attempts to determine the classification from the accounts and direction of
money movements. It may be that in some cases incorrect assumptions are made,
and these will need manual correction.
</para>
</sect4>
<sect4>
<title>Suspect Schedules</title>
<para>
Some features of GnuCash scheduled transactions are not available in
&kappname;, so the importer tries in each case to reach a reasonable
compromise in converting the data. These transactions will be flagged as
suspect, and the user will be given the option of editing them directly during
the import process. Examples of situations which may cause this are:
</para>
<itemizedlist>
<listitem>
<para>some frequency intervals supported in GnuCash are not currently
available in &kappname;</para>
</listitem>
<listitem>
<para>&kappname; does not support the use of formulae and variables in
amount fields</para>
</listitem>
<listitem>
<para>complex cases which have not yet been identified for import.</para>
</listitem>
</itemizedlist>
<para>
Despite best efforts, it is possible that, due to the many options involved, a
scheduled transaction may cause a fatal error within &kappname;. If this sort
of problem seems to be occurring, the importer offers the option to drop all
suspect schedules.
</para>
</sect4>
</sect3>
<sect3>
<title>Reports</title>
<para>
&kappname; provides a comprehensive selection of configurable reports,
described in more detail in <link linkend="details.reports">Reports.</link>
These will not necessarily, however, match precisely those reports available
in GnuCash.
</para>
</sect3>
</sect2>
<sect2>
<title>Selecting Importer Options</title>
<para id="details.impexp.gncoptions">
<screenshot>
<mediaobject>
<imageobject>
<imagedata fileref="gnucash-import_options.png" format="PNG" />
</imageobject>
</mediaobject>
</screenshot>
</para>
<sect3>
<title>Investment Handling</title>
<para>
See <link linkend="gncsecurities">"Securities and Investments"</link> above.
</para>
</sect3>
<sect3>
<title>Online Quotes</title>
<para>
Turn this off if you wish to use the native method for future online price
quotes.
</para>
<para>
See <link linkend="details.impexp.gncquotes">"Online Quotes"</link> above.
</para>
</sect3>
<sect3>
<title>Scheduled Transactions</title>
<para>
See <link linkend="gncschedules">"Scheduled Transactions"</link> above.
</para>
</sect3>
<sect3>
<title>Decoding Options</title>
<para>
If your native language is written in letters or symbols which are different
from those used in the 'Latin' languages (i.e., generally Western European),
these are represented in a special fashion ('encoded') in your GnuCash file.
If these letters are not displayed correctly on your screen, then they must be
decoded. Currently, it is often not possible to detect accurately which form
of decoding must be used, so you may need to set this option and select an
entry from the list. In general, the first item in the list will be that
which is considered appropriate for your locale (i.e., the country and
language which was selected as native when your operating system was
installed), so this should be tried first. Since the import process does not
overwrite your GnuCash file, you are free to experiment with any of these
selections.
</para>
</sect3>
<sect3>
<title>Transaction Notes option</title>
<para>
Under some usage conditions, non-split GnuCash transactions may contain
residual, often incorrect, memo data which is not normally visible to the
user. When imported into &kappname; however, due to display differences, this
data can become visible. Often, these transactions will have a Notes field
describing the real purpose of the transaction. If this option is selected,
these notes, if present, will be used to override the extraneous memo data.
</para>
</sect3>
<sect3>
<title>Debug Options</title>
<para>
These need only be used in the event of import problems. If you have such
problems, you should also report them to the &kappname; developer list
&devlist;. Note that the traces produced by these options may contain data of
a confidential nature, and the Anonymize option should be used if they are to
be made publicly available.
</para>
</sect3>
</sect2>
<sect2>
<title>Import Report</title>
<para>
At the end of processing, the importer produces a report showing the number of
different entities processed, and any errors or anomalies encountered. This
report will be displayed on screen, and may be saved to a file for later
review. A full report may contain the following sections:
</para>
<itemizedlist>
<listitem>
<para>Record counts</para>
</listitem>
<listitem>
<para>Inconsistencies in account types and actions taken</para>
</listitem>
<listitem>
<para>Details of suspect schedules</para>
</listitem>
</itemizedlist>
<para>
<screenshot>
<mediaobject>
<imageobject>
<imagedata fileref="gnucash-report.png" format="PNG" />
</imageobject>
</mediaobject>
</screenshot>
</para>
</sect2>
</sect1>
<sect1 id="details.impexp.qifimp">
<sect1info>
<author>&Thomas.Baumgart; &Thomas.Baumgart.mail;</author>
</sect1info>
<title>QIF Importer</title>
<sect2>
<title>QIF format considered harmful</title>
<para>
Generally speaking, the QIF format should be avoided wherever possible. It is
a poor choice for transporting financial data. Among other things, QIF suffers
from these problems:
</para>
<itemizedlist>
<listitem>
<para>Lack of standardized format: Different versions of the same program
will impart different meanings to the same element.</para>
</listitem>
<listitem>
<para>Lack of transaction identifier: Because there is no ID number
associated with each transaction, matching duplicate transactions is
haphazard at best.</para>
</listitem>
<listitem>
<para>Lack of expressiveness: The grammar is really simple, and cannot
portray the depth of financial information found in today's financial
environment.</para>
</listitem>
</itemizedlist>
<para>
This is generally why Intuit stopped supporting QIF input at all with Quicken
2005. If you have the option of getting data some other way, like OFX, always
choose that option.
</para>
</sect2>
<sect2>
<title>How to import a QIF file</title>
<para>
To import a QIF file, first ensure you have a valid &kappname; file open.
Then select <menuchoice><guimenuitem>Import</guimenuitem>
<guimenuitem>QIF...</guimenuitem> </menuchoice> from
the <guimenu>File</guimenu> menu.
</para>
<para>
<screenshot>
<mediaobject>
<imageobject>
<imagedata fileref="qifopen.png" format="PNG" />
</imageobject>
</mediaobject>
</screenshot>
</para>
<para>
The resulting dialog prompts for the QIF filename allowing you to locate the
file by clicking on the <guibutton>Browse</guibutton> button.
</para>
<para>
Also, &kappname; differentiates between the import of a bank statement file
and historic data exported from another application. The default is to import
a bank statement file. In case you are importing data from your previous personal
finance manager application select the appropriate option.
</para>
<para>
In general the default QIF profile should work with your QIF data. In some
cases it might become necessary to use a modified QIF profile. See
the <link linkend="details.impexp.qifimp.profile">next section</link> for more
details on that subject.
</para>
<para>
Click on <guibutton>Import</guibutton> to import the QIF file.
</para>
<para>
&kappname; will start scanning the file to determine the formats used to
represent dates and numbers. In case it cannot determine a date format
unambiguously, &kappname; will ask the user to select one from the list of
possible date formats.
</para>
<para>
Next, &kappname; imports the data and creates all necessary objects, such as
payee information, accounts and category records, and stock price information.
Wherever possible, existing transactions will be matched against the imported
information. A progress bar is shown and updated during the import process.
</para>
<para>
In case &kappname; could not detect the name of the account to be imported,
the user will be asked to select the account into which the data should be
imported. If the account does not already exist in your file, a new account
can be created by clicking on <guibutton>Create</guibutton>.
</para>
<para>
At the end of the import, &kappname; shows a statement import statistics
window.
</para>
<para>
<screenshot>
<screeninfo>Statement statistics</screeninfo>
<mediaobject>
<imageobject>
<imagedata fileref="qif_report.png" format="PNG" />
</imageobject>
<textobject>
<phrase>Statement statistics</phrase>
</textobject>
</mediaobject>
</screenshot>
</para>
<para>
After importing, all of the imported transactions will be shown with a yellow
background in the ledger view. In case &kappname; was able to match an
imported transaction with an already existing transaction, the background is
shown in light green.
</para>
<para>
The next step is to verify the imported data and accept it. This is a general
process and also applies to imports from other sources. It is outlined in a
separate section of this document.
</para>
<note>
<para>
The colors used to mark imported and matched transactions are customizable and
may be different in your environment.
</para>
</note>
</sect2>
<!--
<sect2>
<title>Accepting the imported transactions</title>
<para>
When &kappname; has finished importing the QIF transactions the account will be shown with the imported transactions listed in Yellow.
</para>
<para>
<screenshot>
<screeninfo>Imported transactions</screeninfo>
<mediaobject>
<imageobject>
<imagedata fileref="qifimportverify.png" format="PNG" />
</imageobject>
<textobject>
<phrase>Imported transactions</phrase>
</textobject>
</mediaobject>
</screenshot>
</para>
<para>
Some of your transactions may be flashing red in the ledger.
This is because they need to be assigned a category.
The importer was not able to automatically assign a category based on your past transaction history.
</para>
<para>
Transaction data can be edited or even deleted if needed. To edit a transaction simply double click on the entry or hit enter when the entry is highlighted. Once finished click on <guibutton>OK</guibutton> to accept the imported transactions or <guibutton>Cancel</guibutton> to remove the imported transactions.
</para>
</sect2>
<sect2><title>Importing Investments</title>
<para>
Please note that if you are importing a file with investment transactions, those investments must first exist in your &kappname; file.
The trading symbol is used to match, so please ensure that the symbol in &kappname; is exactly the same as the one in the file you're importing.
</para>
</sect2>
-->
<sect2 id="details.impexp.qifimp.profile">
<title>Setting up a QIF profile</title>
<para>
Because there is no universally standard format for a QIF file, different
vendors have taken liberties with the format, and introduced their own
nuances. The QIF Profile allows &kappname; to know about the peculiarities of
your file. To edit an existing QIF Profile, or to create a new one, press the
<guibutton>New</guibutton> button on the QIF Import dialog, near the profile selector.
</para>
<para>
<screenshot>
<mediaobject>
<imageobject>
<imagedata fileref="qifimport-qifprofileeditor.png" format="PNG" />
</imageobject>
<textobject>
<phrase>QIF Profile Editor</phrase>
</textobject>
</mediaobject>
</screenshot>
</para>
<note>
<para>
Previous versions of &kappname; used to have a tab for date and amount
specifications. &kappname; now determines those settings by scanning the
file. If it cannot figure out all settings, it will interrogate the user
during import.
</para>
</note>
<!--
<para>
The most commonly changed thing between QIF implementations is the date format.
So if this is the first time you're importing a QIF file, spend a few moments to figure out what format the dates are in, and set the QIF Profile accordingly.
See the discussion below on apostrophe format for more details.
</para>
<para>
<screenshot>
<mediaobject>
<imageobject>
<imagedata fileref="qifimport-qifprofiledate.png" format="PNG" />
</imageobject>
<textobject>
<phrase>QIF Profile Date</phrase>
</textobject>
</mediaobject>
</screenshot>
</para>
</sect2>
<sect2><title>Apostrophe format</title>
<para>
Many common QIF writers use a 2-digit representation for the year.
This is ambiguous, because the importer cannot know which century the date belongs in.
To make things even more complicated, QIF files will often used an apostrophe as a year separator to indicate that the date belongs in the OTHER century from the default.
</para>
<para>
For example, if the default century is 1900-1999, the date 12/31/95 would mean 1995. The date 12/31'05 would mean 2005.
</para>
<para>
Because the QIF format is not standardized, it's impossible to know which century is desired.
This is why you have to explicitly state it in the QIF profile.
You do this by specifying which century is intended when an apostrophe is found.
In the example above, you would set the Apostrophe Format to "2000-2099", so dates with an apostrophe will be interpreted as being > year 2000.
In this case, dates without an apostrophe will be treated as being in the 1900's.
</para>
-->
</sect2>
<sect2><title>Transaction matching</title>
<para>
As noted previously, one of the major drawbacks of the QIF format is the lack
of a unique identifier for each transaction. Thus, if you import a QIF file
and some of the transactions are already in your ledger, you may get
duplicates. &kappname; attempts to get around this by looking for
transactions that look similar to those you already have. If it finds
something that looks like the same transaction, it will match the apparent
duplicate.
</para>
<para>
This can be a problem if you have transactions that look too similar but are
actually different. In this case, you can unmatch those transactions later in
the ledger view.
</para>
</sect2>
<sect2>
<title>Writing an import filter</title>
<para>
Sometimes you may have data in a custom format, like comma-separated-values
(CSV) or something else unique to your situation. As of version 4.6,
&kappname; includes a CSV Importer Plugin, but you can still import other
types of files into &kappname; using a QIF Import Filter. A filter is a
custom program you write which takes your special file as input, and produces
a QIF file as output. This can be a shell script, a perl script, a compiled
program written in C/C++, or anything else you can dream of, as long as the
system can run it.
</para>
<para>
To use it, edit your favorite QIF Profile, and select the Filter tab. Enter
the location of your filter program where prompted. Then, whenever you do a
QIF import using this profile, the file you select for importing will be run
through your filter first.
</para>
<para>
A common problem is to convert a list of comma-separated-values into a QIF
file. This is a textbook case for the awk tool. Create a script called
csv2qif.awk, with the following two lines as contents:
</para>
<programlisting>
BEGIN { FS=","; print "!Type:Bank" }
{ print "D"$1; print "T"$2; print "N"$3; print "P"$4; print "M"$5; print "^" }
</programlisting>
<para>
Then, change the QIF keys (D,T,N,P,M) to match the order of your csv data.
Set the input filter to <userinput>awk -f csv2qif.awk</userinput>.
</para>
<para>
Another problem sometimes arises in the encoding of QIF files. &kappname;
expects files to be UTF8 encoded. If your file is encoded in something else,
it can be useful to convert it to UTF8. For example to convert it from
iso-8859-1, you would set the input filter to <userinput>recode
iso-8859-1..utf-8</userinput>.
</para>
</sect2>
<sect2>
<title>Special &kappname; QIF extensions</title>
<para>
As already mentioned, one of the major drawbacks of the QIF format is the lack
of a unique identifier for each transaction. If you are writing your own QIF
file creator (or filter, as described above), you can overcome this problem.
&kappname; supports the '#' field. The importer will interpret this as a
unique transaction ID, and disregard the record if the transaction is already
in the system.
</para>
</sect2>
</sect1>
<sect1 id="details.impexp.qifexp">
<title>QIF Exporter</title>
<para>
To export one of your accounts to a QIF file, choose
the <menuchoice><guimenuitem>Export</guimenuitem> <guimenuitem>QIF</guimenuitem></menuchoice> from
the <guimenu>File</guimenu> menu. You will be prompted for which single
account to export, what file to export it to, and what QIF Profile to use.
</para>
<note><para>
At the moment, QIF Exporter does not handle export of investments.
</para></note>
<para>
<screenshot>
<mediaobject>
<imageobject>
<imagedata fileref="qifimport-export.png" format="PNG" />
</imageobject>
<textobject>
<phrase>QIF Export</phrase>
</textobject>
</mediaobject>
</screenshot>
</para>
</sect1>
<sect1 id="details.impexp.ofx">
<sect1info>
<author>&Ace.Jones; &Ace.Jones.mail;</author>
<author>&Thomas.Baumgart; &Thomas.Baumgart.mail;</author>
</sect1info>
<title>OFX Importer Plugin</title>
<sect2>
<title>Getting the plugin</title>
<para>
&kappname; will import OFX files painlessly. However, this functionality is
not built into the core program. You must obtain and install the OFX Importer
Plugin. Once that is installed, the command to import OFX files will
automatically show up under the <menuchoice><guimenu>File</guimenu>
<guimenuitem>Import</guimenuitem></menuchoice> menu.
</para>
<para>
Note that many prepackaged versions of &kappname; were built with the OFX
importer already included or available as a separate package. If the OFX
importer does not seem to be installed in your version, the first place to
check is in the same place you got your base &kappname; package.
</para>
<para>
If you have installed from RPM, the OFX Importer Plugin is contained within
the kmymoney-ofx RPM. It should be available from whatever source you got the
base &kappname; RPM. If you have built from sources, all you need to do is
have preferably the libOFX 0.9 development headers and libraries installed on
your system. The &kappname; build process will detect these and compile the
plugin. At the time of release of &kappname; 4.6, the latest libofx version
was 0.9.4, which is also the minimum required version.
</para>
<para>
Should you run into trouble trying to compile &kappname;, and you are certain
you have the correct version of libOFX installed, please contact the
developers list &devlist; for assistance. Include a copy of your config.log
file, compressed first via gzip.
</para>
</sect2>
<sect2>
<title>Importing an OFX file</title>
<para>
The most basic way to import an OFX file is to choose the importer from the
menu bar. From the <guimenu>File</guimenu> menu,
choose <guimenuitem>Import</guimenuitem>, and
then <guimenuitem>OFX...</guimenuitem>. If OFX does not show up under Import,
you do not have the OFX Importer Plugin installed correctly. Please see the
previous section.
</para>
<para>
The first thing the importer will do is ask you into which account to import
the transactions. If there are transactions from multiple accounts in your
file, you will be asked this question multiple times.
</para>
<para>
After importing, some of your transactions may be shown with an exclamation
mark on a yellow triangle in the ledger. This is because they need to be
assigned a category. The importer was not able to automatically assign a
category based on your past transaction history. You can edit each
transaction in the ledger to assign a category, and the mark will be removed.
</para>
<para>
Please note that this section describes the <quote>native</quote> OFX
importer. OFX files may also be imported using the AqBanking Importer Plugin
if you have installed that. Note that the two importers do behave slightly
differently, and they are written and supported by two different developers.
</para>
</sect2>
<sect2>
<title>Importing Investments</title>
<para>
Please note that if you are importing a file with investment transactions,
those investments must first exist in your &kappname; file. The trading
symbol is used to match, so please ensure that the symbol in &kappname; is
exactly the same as the one in the file you're importing.
</para>
</sect2>
<sect2 id="details.impexp.webconnect">
<title>Web Connect</title>
<para>
The easiest way to import an OFX file is to set up Web Connect. Visit your
bank's web site, and click on a link to download an OFX file. Your browser
should ask you what program you would like to use to open the program. Point
your browser to &kappname;. It will then import the downloaded OFX file into
the &kappname; file you most recently had open. You can also change the file
associations of your desktop environment, and have &kappname; open the OFX
file automatically for you.
</para>
<para>
If you need to import the OFX file into some other &kappname; file, load up
that file in &kappname; first, and then visit your bank's web site.
</para>
</sect2>
<sect2 id="details.impexp.ofxdirectconnect">
<title>Direct Connect</title>
<para>
OFX Direct Connect is now supported in &kappname;. This gives you the ability
to contact your bank directly to obtain statements. In the future, there will
be more help written, and this will be moved to its own section.
</para>
<para>
To enable this feature, you must compile &kappname; with the
--enable-ofxbanking switch (now the default).
</para>
<para>
Please be warned: Many banks require a separate signup, will give you a
separate password or PIN, and may even charge you a separate fee for this
service. No bank directly supports &kappname;. You will have to tell them
you want to bank directly from MS Money or Quicken.
</para>
<para>
The first step is to configure each account for which you wish to download
statements. Go to the Accounts view, right click on the account you wish to
configure, and choose <guimenuitem>Map to online account...</guimenuitem>.
In case more than one online banking plugin is installed on your system
you will be asked which one to use. For the internal OFX method select
<guimenuitem>KMyMoney OFX</guimenuitem>. A list of banks will be downloaded
from the Internet and a wizard will guide you through choosing a bank,
entering your username and password, and selecting an account. Should you
find that your bank is not listed, then it may still be possible to use the
manual option. Your bank may be able to provide the required parameters, or
you may have to do some research to find them.
</para>
<para>
Once you have an account set up with online banking, go to the ledger for that
account. Then from the <guimenu>Account</guimenu> menu, choose <guimenuitem>Update
account...</guimenuitem>. This will connect to your bank, and download a statement
for the last 60 days.
</para>
<note>
<para>
In version of &kappname; prior to 4.6, the payee name was always taken
from the PAYEEID field. As of version 4.6, the payee name can be based on
either the PAYEEID, NAME, or MEMO field in the OFX transaction. You can
configure this feature and some other OFX direct connect settings by
selecting the appropriate tab in
the <link linkend="details.accounts.edit">Edit account</link> dialog.
</para>
</note>
</sect2>
<sect2>
<title>Exporting an OFX file</title>
<para>
It is not possible to export your data as an OFX file currently. If you are
interested to contribute in this area, please contact the libofx development
team for details.
</para>
</sect2>
</sect1>
<sect1 id="details.impexp.csv">
<sect1info>
<author> &Allan.Anderson; &Allan.Anderson.mail; </author>
</sect1info>
<title>CSV Importer Plugin</title>
<sect2>
<title>Reasons for importing CSV Files</title>
<para>
In general, it is preferable to import OFX. However, not all institutions
provide data in that format. CSV (comma separated value) files are almost
always available (sometimes described as Excel or spreadsheet.) Also, they
can often be created fairly easily by capturing the data you want to import,
such as in a text file, and manually editing it.
</para>
<para>
The primary focus of the plugin is on importing data from bank statements, but
there is also a capability to import some investment statements. In addition,
it retains its initial ability to produce QIF files from CSV.
</para>
</sect2>
<sect2>
<title>Getting the plugin</title>
<para>
&kappname; will import CSV (comma separated values) files. However, this
functionality is not built into the core program, although the source is now
provided as part of the &kappname; tarball, which needs to be installed. Once
that is done, the command to import CSV files will automatically show up under
the
<menuchoice><guimenu>File</guimenu><guimenuitem>Import</guimenuitem></menuchoice>
menu.
</para>
<para>
The CSV importer plugin is much newer than the OFX plugin, so it may take some
time until many prepackaged versions of &kappname; are built with the CSV
importer already included or available as a separate package. Ensure that it
is enabled within &kappname;. Check the
<menuchoice><guimenu>Settings</guimenu><guimenuitem>Configure
KMyMoney</guimenuitem><guimenuitem>Plugins</guimenuitem></menuchoice> menu.
If the CSV importer does not seem to be installed in your version, the first
place to check is in the same place you got your base &kappname; package.
</para>
<para>
If you have installed from RPM, the CSV Importer Plugin may be contained
within the kmymoney-csv RPM. It should be available from whatever source you
got the base &kappname; RPM. If you have built from sources, there should be
no additional requirements. The &kappname; build process should detect the
plugin source and compile the plugin.
</para>
<para>
Should you run into trouble trying to compile &kappname;, and you are certain
you have the plugin source available, please contact the developers list
&devlist; for assistance. Include a copy of your config.log file, compressed
first via gzip.
</para>
</sect2>
<sect2>
<title>Importing a CSV file</title>
<para>
To import a CSV file, choose the importer from the menu bar:
<menuchoice><guimenu>File</guimenu><guimenuitem>Import</guimenuitem><guimenuitem>CSV...</guimenuitem></menuchoice>.
If CSV does not show up under Import, you do not have the CSV Importer Plugin
installed correctly. Please see the previous section.
</para>
<para>
The CSV Importer window has three main sections.
</para>
<itemizedlist>
<listitem>
<para>
The upper left area has three tabs, Banking, Investment, and Settings.
Because of differences in processing banking and investment data, you need to
indicate which type of data you are importing before you select the file. You
do this by clicking on either the <guilabel>Banking</guilabel> or
<guilabel>Investment</guilabel> tab. The selected tab is indicated by a "*"
after the label, as a reminder in case you accidentally click the wrong tab.
If later you select the other tab, the plugin warns you that you will lose
your current settings, and gives you an option to cancel the switch. All
three tabs are described in more detail below.
</para>
</listitem>
<listitem>
<para>
The lower section of the window displays the contents of the csv file
currently being imported.
</para>
</listitem>
<listitem>
<para>
The upper right area contains some buttons for controlling the import process.
</para>
<formalpara><title>Open File</title>
<para>
This is used to select the file to import. As noted above, the
file will be imported as either banking or investment data, as
indicated by which tab is marked with an asterisk ("*").
</para>
</formalpara>
<formalpara><title>Import</title>
<para>
This tells the plugin to actually import the data from the file, based on
the choices you have made on the <guilabel>Banking</guilabel> or
<guilabel>Investment</guilabel> tab and on the <guilabel>Settings</guilabel>
tab, all as described below. &kappname; will prompt you for the correct
account into which to import the data.
</para>
</formalpara>
<formalpara><title>Save as QIF</title>
<para>
This button gives you the ability, after the import has been completed, to
save the data from the csv file as a qif file, should you require one for
any reason.
</para>
</formalpara>
<formalpara><title>Quit</title>
<para>
Closes the plugin, after saving your settings.
</para>
</formalpara>
<formalpara><title>Clear selections</title>
<para>
Clear all the columns and settings you have chosen or adjusted.
</para>
</formalpara>
</listitem>
</itemizedlist>
<para>
Importing a CSV file is a multi-step process. Not all steps will always be
necessary, and the exact order of these steps depends on the specifics of the
data being imported.
</para>
<orderedlist>
<listitem>
<para>
Choose whether you are importing banking or investment data.
</para>
</listitem>
<listitem>
<para>Open the file containing the data.</para>
</listitem>
<listitem>
<para>
Confirm the field delimiter, and possibly the text delimiter, and set the
starting and ending lines to be imported.
</para>
</listitem>
<listitem>
<para>
Assign which fields or columns contain particular types of data relevant
to the import type.
</para>
</listitem>
<listitem>
<para>
Confirm or adjust settings such as date format and decimal.
</para>
</listitem>
<listitem>
<para>Confirm the import.</para>
</listitem>
</orderedlist>
<para>
Once you have selected the <guilabel>Banking</guilabel> or
<guilabel>Investment</guilabel> tab, click the <guibutton>Open
File</guibutton> button, and select your input csv file. Before actually
proceeding with the import of the file, you need to give &kappname; some
details about the layout of the file, which differs depending on whether the
file contains banking or investment data. First, however, you may need to
adjust some settings common to both file types.
</para>
<sect3>
<title>CSV Importer Plugin Settings</title>
<para>
Select the Settings tab on the importer window. Here you configure a number
of fields that allows the plugin to correctly interpret your input file. In
general, this is done after you indicate whether you are importing banking or
investment data and you have opened the file, but you may need first to
correct the field delimiter if the display is obviously incorrect. Then assign
specific fields to columns. Note that this information is saved, so you will
only need to set or confirm it once, unless you are importing a csv file
created with different settings. In addition, some of the settings may
already be correct, based on your current locale setting.
</para>
<formalpara><title>Field Delimiter</title>
<para>
Even though the file is still called comma delimited, the character used to
separate values in the file may be a comma, semicolon, or tab. Once this
is set correctly, your data should appear correctly split into fields.
</para>
</formalpara>
<formalpara><title>Text Delimiter</title>
<para>
This will usually be a single or double quote character. It is important in
case the field delimiter character appears within a column, such as the memo
field.
</para>
</formalpara>
<!-- I am not sure whether the following should be a note, tip, caution,
warning, or mportant. I did remove several guilabel tags, because they
looked excessive within the emphasis of the note tag. -->
<note>
<para>
Once the fields are correctly displayed, you have to tell the plugin about
the column layout of the input file, which you do on the Banking or
Investment tab, as appropriate. Note that you can switch between the data
type tab and the settings tab without losing any data. On the Banking or
Investment tab, if you make a mistake when entering column numbers, just
re-enter the correct column number. Alternatively, click <guibutton>Clear
selections</guibutton> to clear all the selections and try again.
</para>
</note>
<formalpara><title>Decimal Symbol</title>
<para>
For each import, after the fields have been selected, the decimal symbol
should now be selected, as it triggers a validation process on your monetary
column choices in the <guilabel>Banking</guilabel> or
<guilabel>Investment</guilabel> tab. Set the Decimal Symbol to match those
in your file, not your locale. If your locale setting has a different
value, conversion will be seen to take place. In the display of the file in
the lower part of the window, numeric fields are highlighted to show in
green if this setting produces valid results, otherwise in red. The
highlighting also reflects the start and end lines settings (see below).
There could be warnings if any of the selected cells appear not to contain
the selected symbol.
</para>
</formalpara>
<formalpara><title>Thousands Symbol</title>
<para>
This does not need to be selected, as it is set automatically based on the
<guilabel>Decimal Symbol</guilabel>. It is provided purely as a guide.
</para>
</formalpara>
<formalpara><title>Date format</title>
<para>
This needs to be set according to the order of year, month, and day in any
dates in the file. If the plugin finds data incompatible with this setting,
it will complain when you try to import. However, if the setting is wrong,
but doesn't produce invalid results (such as data with no days higher than
12, so month and day could be confused) you will simply get incorrect data,
because the plugin cannot know you made a mistake, but the error will be
obvious in the ledger after import.
</para>
</formalpara>
<formalpara><title>Start line</title>
<para>
Set this so the importer skips any header lines in the file.
</para>
</formalpara>
<formalpara><title>End line</title>
<para>
The importer will automatically set this to the last line in the file. You
will only need to adjust it if there are footer lines in the file the
importer should ignore. Otherwise, you are likely to get a data error
warning.
</para>
</formalpara>
</sect3>
<sect3>
<title>Importing banking data</title>
<para>
Importing banking data is fairly straightforward, you just need to select
the appropriate column numbers, which is done on the
<guilabel>Banking</guilabel> tab.
</para>
<itemizedlist>
<listitem>
<para>
If your file has just a single column for the amount, click the
<guilabel>Amount column</guilabel> radio button, and enter the appropriate
column number in the <guilabel>Amount</guilabel> dropdown.
</para>
</listitem>
<listitem>
<para>
If there are two columns - debit and credit - click the <guilabel>Debit /
credit columns</guilabel> radio button, and enter the appropriate column
numbers in the <guilabel>Debits</guilabel> and <guilabel>Credits</guilabel>
dropdowns.
</para>
</listitem>
<listitem>
<para>
If you wish to save the values from more than one column in the memo field,
just select those columns sequentially. An asterisk appears against the
selected choices, as a reminder.
</para>
</listitem>
<listitem>
<para>
The plugin detects attempts to select the same column for two different
fields. Because it cannot know which one is correct, it will output an error
message and clear both selections.
</para>
</listitem>
</itemizedlist>
<para>
Once you are happy with the settings and column selection, you can import
the file, as described further below.
</para>
</sect3>
<sect3>
<title>Importing investment data</title>
<para>
To import investment data, click the <guilabel>Investment</guilabel> tab.
The procedure is similar to the above.
</para>
<itemizedlist>
<listitem>
<para>
Select the column which contains the <guilabel>Date</guilabel> of
the transaction.
</para>
</listitem>
<listitem>
<para>
Select the column which contains the <guilabel>Price</guilabel>.
The <guilabel>Price Fraction</guilabel> setting is for matching the
imported pricing units with the existing pricing, where for instance
one is in $ and the other in cents, or £ versus pence, etc. For
example, if your &kappname; data file pricing is in dollars, and so
is the CSV file, then set Price Fraction to 1.0. If however, the
CSV file pricing is in cents, set the fraction to 0.01.
</para>
</listitem>
<listitem>
<para>
The <guilabel>Type/Action</guilabel> column is where the activity is
described: buy, sell, reinvest, etc.
</para>
</listitem>
<listitem>
<para>
Select the column which contains the <guilabel>Quantity</guilabel>
or number of shares of the transaction.
</para>
</listitem>
<listitem>
<para>
Assign a <guilabel>Fee Column</guilabel> according to whether any
fee is involved, and click the <guilabel>Fee is
Percentage</guilabel> box if the imported fee is a percentage rather
than a value. (Just a warning here. It may be that the fee has
been taken into account in the unit price. If so, don't select any
fee column, although any fee shown may be retained by selecting the
fee column as another memo column.)
<!-- This is the only field to explicitly include "column" in the label, in
case someone tries to enter the fee instead of the column number. -->
</para>
</listitem>
<listitem>
<para>
As with banking data, the <guilabel>Memo</guilabel> field can be used to
select more than one column (sequentially) to include multiple values in
the memo field of the imported transaction.
</para>
</listitem>
<listitem>
<para>
Select the column which contains the <guilabel>Amount</guilabel>.
</para>
</listitem>
<listitem>
<para>
Enter the name of the security in the <guilabel>Security Name</guilabel>
field, ensuring it matches exactly the existing security as specified in
&kappname;. If the security name appears in the imported file, double click
on it to select it, then copy and paste/edit to match, taking care if you have
used a variation or abbreviation within &kappname;. As you enter security
names in this field, they are retained in the resource file (see <link
linkend="details.impexp.csv.config">below</link> for more details on this
file.) This means they will be available in this dropdown when you run the
plugin the next time. If you wish, you can also edit the resource file to add
a complete list of securities you expect to encounter in your import files.
If you have entered a name incorrectly, click the <guilabel>Hide
Security</guilabel> button. This just removes the name from the plugin list
and has no effect on your data in &kappname;.
</para>
<para>
Because of the lack of standardization of formats, the current version of the
plugin is restricted to importing data for one security in one account at a
time. If your file contains data for more than one security, you can import
it in stages, using the start line and end line to identify only one at a
time.
</para>
</listitem>
</itemizedlist>
</sect3>
<sect3>
<title>Completing the import</title>
<para>
For either banking or investment data, once you have selected all the
appropriate columns, click <guibutton>Import</guibutton>. &kappname; will now
display a selector dialog box for you to choose the correct account into which
to import the data. It is possible at this stage to save to a qif file,
should you require one for any reason, by clicking <guibutton>Save as
QIF</guibutton>.
</para>
<para>
For investment data, if any transaction involves another account, ⪚, a
checking or brokerage account for a received dividend or for making a
payment, a message box will pop up for the account name to be entered for
the transfer. If the investment account allows for, say, writing checks,
you may enter an existing checking/brokerage account name. Similarly enter
the column number containing the payee, if requested. If a mistake is made
when entering the account name, the import will go ahead, but &kappname;
will not recognize it, and will flag those transactions as missing a
category assignment. If the required account name is rather long, just
enter a few characters. The import will proceed but the transactions will
be flagged by &kappname; as missing a category assignment, and the correct
transfer account will need to be selected.
</para>
<para>
If, during import of a transaction, the plugin finds no valid transaction
type, it will display the offending transaction, and the user may select a
valid type to substitute, depending on the combination of quantity, price, and
amount values. For every transaction, the plugin will validate the column
contents to ensure they match the action type. For instance, if a quantity
appears but no price or amount, it is assumed that the transaction can be only
an add or remove shares. Or, if there is an amount but no quantity or price,
then a Dividend is assumed, etc..
</para>
<para>
If you find that your investment statements keep including activity types
that are not recognized, just add them to the section in the resource
file. (See <link linkend="details.impexp.csv.config">below</link> for
more details on this file. For instance, in the [InvestmentSettings]
section of the file, the BuyParam field includes entries for Purchase,
Buy, New Inv, and Switch In. If you find a different one, add it to the
correct list and restart the plugin. You may notice that there are
similarities in the entries in different fields, and you may find that
the wrong activity type is being selected. The plugin checks these lists
in the following order: Shrsin, DivX, Reinvdiv, Brokerage, Buy, Sell,
Remove. Re-ordering the lists to suit this does not work as might be
expected, since the entries in the resource file get sorted into
alphabetical order. If the offending parameter is one you don't need,
just delete it from the file. If that is not possible, you may need to
edit your file before input.
</para>
<caution>
<para>
Note that it may appear that the displayed entries in the lower
section of the plugin window may be edited, and in fact they
may, but the edits are not kept. The display is purely for display,
not for editing. The input file is never altered by the plugin, and
the data actually imported comes from the input file, not from the
display.
</para>
</caution>
</sect3>
<sect3 id="details.impexp.csv.config">
<title>Configuration of CSV importer plugin</title>
<para>
A well-known drawback of QIF format is that it is a fairly loose format.
With CSV files, there is this same problem, only more so, in that there
is no agreed standard at all. With investment files, in particular,
there is much more scope for variation in specifying the different types
of activities represented in the data. The plugin handles this by
listing these activity types in a resource file, called csvimporterrc.
The location of this file depends on your distribution. It will usually
be located in ~/.kde4/share/config/, but may be in ~/.kde/... instead.
Using this resource file allows the user to add an activity type that the
developer had not encountered. If the file does not exist when the
importer first runs, the plugin will create a default version, containing a
few of the more obvious descriptions.
</para>
<para>
A number of sample csv files are provided (in the
kmymoney/contrib/csvimporter/ folder in the source tree) in the hope that
they may help. For example, in the investment sample, an activity type is
"ReInvestorContract Buy : ReInvested Units". In the validation process, the
first successful match is on the ReInv in ReInvestorContract Buy, so the
transaction therefore gets classed as Reinvdiv, even though Buy also is
mentioned. Another example which has been observed is an activity type of
Reinvest even though the transaction included neither price nor amount, but
only a quantity, so that needed to be treated as Add Shares, or Shrsin.
</para>
<para>
When this plugin was created, only a few investment formats had been seen as
examples, and it may well be that you will encounter one which cannot be
handled correctly. If you find such a file, please send a suitable example
(edited to remove or replace personal information) to the &kappname; user list
&userlist; or developer list &devlist;, the developer will do his best to
modify the plugin to handle it.
</para>
</sect3>
</sect2>
</sect1>
<sect1 id="details.impexp.plugins">
<title>Writing Importer Plugins</title>
<para>
&kappname; contains explicit support for importer plugins. If you have a
custom format, and you would like to write an importer plugin, we would value
your contribution. To do so, you'll need to compile the program from source.
Then use the OFX Importer Plugin as an example.
</para>
</sect1>
</chapter>
