CSV Importer Plugin

Allan Anderson

Reasons for importing CSV Files

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.

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.

Getting the plugin

KMyMoney 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 KMyMoney tarball, which needs to be installed. Once that is done, the command to import CSV files will automatically show up under the FileImport menu.

The CSV importer plugin is much newer than the OFX plugin, so it may take some time until many prepackaged versions of KMyMoney are built with the CSV importer already included or available as a separate package. Ensure that it is enabled within KMyMoney. Check the SettingsConfigure KMyMoneyPlugins 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 KMyMoney package.

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 KMyMoney RPM. If you have built from sources, there should be no additional requirements. The KMyMoney build process should detect the plugin source and compile the plugin.

Should you run into trouble trying to compile KMyMoney, and you are certain you have the plugin source available, please contact the developers list for assistance. Include a copy of your config.log file, compressed first via gzip.

Importing a CSV file

To import a CSV file, choose the importer from the menu bar: FileImportCSV.... If CSV does not show up under Import, you do not have the CSV Importer Plugin installed correctly. Please see the previous section.

The CSV Importer window has three main sections.

  • 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 Banking or Investment 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.

  • The lower section of the window displays the contents of the csv file currently being imported.

  • The upper right area contains some buttons for controlling the import process.

    Open File.  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 ("*").

    Import.  This tells the plugin to actually import the data from the file, based on the choices you have made on the Banking or Investment tab and on the Settings tab, all as described below. KMyMoney will prompt you for the correct account into which to import the data.

    Save as QIF.  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.

    Quit.  Closes the plugin, after saving your settings.

    Clear selections.  Clear all the columns and settings you have chosen or adjusted.

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.

  1. Choose whether you are importing banking or investment data.

  2. Open the file containing the data.

  3. Confirm the field delimiter, and possibly the text delimiter, and set the starting and ending lines to be imported.

  4. Assign which fields or columns contain particular types of data relevant to the import type.

  5. Confirm or adjust settings such as date format and decimal.

  6. Confirm the import.

Once you have selected the Banking or Investment tab, click the Open File button, and select your input csv file. Before actually proceeding with the import of the file, you need to give KMyMoney 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.

CSV Importer Plugin Settings

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.

Field Delimiter.  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.

Text Delimiter.  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.

Note

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 Clear selections to clear all the selections and try again.

Decimal Symbol.  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 Banking or Investment 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.

Thousands Symbol.  This does not need to be selected, as it is set automatically based on the Decimal Symbol. It is provided purely as a guide.

Date format.  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.

Start line.  Set this so the importer skips any header lines in the file.

End line.  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.

Importing banking data

Importing banking data is fairly straightforward, you just need to select the appropriate column numbers, which is done on the Banking tab.

  • If your file has just a single column for the amount, click the Amount column radio button, and enter the appropriate column number in the Amount dropdown.

  • If there are two columns - debit and credit - click the Debit / credit columns radio button, and enter the appropriate column numbers in the Debits and Credits dropdowns.

  • 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.

  • 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.

Once you are happy with the settings and column selection, you can import the file, as described further below.

Importing investment data

To import investment data, click the Investment tab. The procedure is similar to the above.

  • Select the column which contains the Date of the transaction.

  • Select the column which contains the Price. The Price Fraction 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 KMyMoney 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.

  • The Type/Action column is where the activity is described: buy, sell, reinvest, etc.

  • Select the column which contains the Quantity or number of shares of the transaction.

  • Assign a Fee Column according to whether any fee is involved, and click the Fee is Percentage 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.)

  • As with banking data, the Memo field can be used to select more than one column (sequentially) to include multiple values in the memo field of the imported transaction.

  • Select the column which contains the Amount.

  • Enter the name of the security in the Security Name field, ensuring it matches exactly the existing security as specified in KMyMoney. 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 KMyMoney. As you enter security names in this field, they are retained in the resource file (see below 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 Hide Security button. This just removes the name from the plugin list and has no effect on your data in KMyMoney.

    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.

Completing the import

For either banking or investment data, once you have selected all the appropriate columns, click Import. KMyMoney 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 Save as QIF.

For investment data, if any transaction involves another account, e.g., 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 KMyMoney 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 KMyMoney as missing a category assignment, and the correct transfer account will need to be selected.

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..

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 below 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.

Caution

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.

Configuration of CSV importer plugin

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.

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.

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 KMyMoney user list or developer list , the developer will do his best to modify the plugin to handle it.