CSV Importer PluginIn 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 files. 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 initial focus of the CSV Importer was on handling data from bank statements, but the ability to also import some investment statements was added. In addition, it has been further enhanced to import both currency and stock prices. The original code for this was initially created, before becoming a CSV importer, to produce QIF files from CSV files, which could then be imported. This ability is still present, but is likely to be removed, as focus is now on directly importing CSV files. Also, KMyMoney has the native ability to export QIF files, so there is no longer a real reason to produce a QIF file from a CSV file.
The CSV importer is one of the newer features to have been added to KMyMoney in the form of a plugin. However, as with other plugins, the CSV Importer is included with the core KMyMoney source code, so it should be available in all Linux® distribution versions, as well as the versions for other platforms made available through the KMyMoney web site. The menu choice to import CSV files should automatically show up under the → submenu.
To ensure that it is enabled within KMyMoney, check the → menu item, select Plugins on the left pane, and look for the CSV Importer in the displayed list of plugins. 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. See if a later version is available, or if the importer is available as a separate package.
If you have built from source, there should be no additional requirements. The KMyMoney build process should detect the plugin source and compile the plugin. There are configuration settings which can be used to exclude this or other plugins from being built, but these should rarely be needed.
To import a CSV file, choose the importer from the main menu: → → . If CSV does not show up under Import, the CSV Importer Plugin is not installed correctly or is not enabled. Please see the previous section.
The CSV Importer is presented in the form of a wizard, with a separate page for each individual step of the process.
When launched, the CSV Importer displays the Start or File page. On the left is a list of the steps of the import process, with the current one highlighted. The upper right area is where data to be imported will be displayed; before that it shows some brief instructions relevant to the current stage of the wizard. Below that is a set of radiobuttons for selection of the type of data to be imported. That list currently includes Banking, Investment, Currency prices, and Stock prices. Next, there is a profile selector dropdown, which is enabled if once the radio button has been selected for one of the import file types.
Each type of CSV file to be imported requires specification of several details regarding the expected formatting of the data, as described in the following sections. Not only do the details differ for the different types of data files imported, it is possible to need different configurations for the same type of data from different sources. So you do not need to enter all those details each time you import a file, the CSV Importer allows you to specify the details once, and save them in a named profile.
The name of a CSV import profile is completely arbitrary, but will be easier to remember if it reflects both the type of data being imported (bank, credit card, investment, ...) and the source of the data. There is no need to reference the account into which the data is imported, and it is possible to use the same profile for importing into more than one account, such as two checking accounts at the same bank.
The profile selector dropdown includes the names of all CSV Importer profiles you have previously created, and it also lets you type the name of a new profile. Below the profile selector dropdown, there are three buttons. When you click Add, which is always enabled, a new profile will be created with the name entered in the dropdown. Nothing will happen if that field is empty or if it displays the name of an existing profile. As you proceed through the rest of the CSV import wizard, the various configuration settings you enter or modify will be saved in the selected profile.
If you have previously created one or more profiles, the other two buttons will be enabled. If you click Remove, the profile named in the dropdown will be deleted. If you click Rename, then alter the name displayed in the dropdown, and click Rename again, the profile will be renamed accordingly.
Next to the profile selector, there is a Skip setup checkbox. Initially, you should not select this checkbox. Once you have set up a profile and finished the wizard, all the configuration parameters you entered are saved in the resource file under the profile name. The next time you use that same profile, the parameters will be loaded into the UI (User Interface). The wizard would then plod through the following pages of parameters that you won't need to change. So, once you are happy with a profile, it may be helpful to check this box. The wizard will then move directly to the final page, and, assuming no problems are found, you just have to click Import.
At the bottom there is a Select File button. Clicking this will bring up a file selector dialog to let you choose the file to import. At the bottom of the remaining pages of the wizard are buttons to go to the previous step of the wizard, move on to the Next > step, or the import.
To review, on the Start page of the wizard, first select the checkbox for the type of data you are importing. In the profile selector dropdown, you can select the name of a previously created CSV Importer profile, or enter a new name, possibly the name of the account into which you wish to import, and click Add. Finally, click Select File, and a file selector dialog will open, where you select the CSV file you wish to import.
After opening your selected data file, the wizard will advance to the Separators page, and you should now see your data.
Warning
It may appear that the data displayed in the upper section of the plugin window may be edited, and in fact they may, but any edits are not kept. The table is purely for display, not for editing. The input file is never altered by the CSV Importer, and the data actually imported comes from the input file, not from the display. The one exception to this is covered in Securities and Symbols below.
The CSV Importer should have detected the appropriate Encoding, Field Delimiter, and Text Delimiter. You can change the encoding if the program did not choose correctly. If you need to change the Field Delimiter, such as if your data is actually tab delimited, and not a comma delimited, be aware that doing so will reset any field choices you may already have made. Generally the quote (") is the correct text delimiter, but you may change it as necessary. Click to proceed.
You will now be on the Rows or Lines page. Here, you indicate if any lines should be ignored at the beginning or end of the file.
This page allows you to adjust the import to ignore header and footer rows at the beginning or end of the file, which do not actually contain data to be imported.
Start line. Set this so the importer skips any header lines in the file. Your choice will be saved in this profile for future use. The start and end lines interact, and the start line may not be higher than the end line, nor may it be higher then the actual number of lines in the file. If the Start line selector does not respond, check the end line setting.
End line. The importer will automatically set this to the last line in the file, or to the setting last saved. 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 when the plugin attempts to parse incorrect data. Again, if the End line selector does not respond, check the Start line setting.
Above these two fields is a Header contains account name or number checkbox. If checked, the importer will look in the lines above the start line for the name of number of the account to use for the import. If not, you will be prompted for that information.
When you are ready, click Next to proceed to the next step.
You should now be on the Columns page. This is where you tell the importer which columns in the file contain the data which maps to the specific fields needed to create a meaningful record or transaction, depending on the type of data being imported. There is a different version of this page for each type of data, as each requires a different combination of items.
For most fields, you just need to use the dropdown to select the appropriate column. Note that many fields have an “x” button to the right. Clicking it will clear whatever value has been entered or selected in the field to the left. In addition, there are some special considerations.
To the right is an area with two tabs. If your file has a single column for all values, select the Amount tab. However, if there are separate columns for debits and credits, select the Debit/credit tab.
On the Amount tab, start by selecting the column which contains the amount of the transaction. If there is a column which contains “Credit” or “Debit” to indicate which the amount is, select that column as the Debit/Credit Indicator. Separately, if there is a string included in the amount field which indicates the amount is either a credit or a debit, enter that string in the appropriate Indicator field. Finally, if despite setting all the above values correctly, the amount imported is reversed from what it should be, check the Opposite signs checkbox.
On the Debit/credit tab, simply enter the numbers of the columns with those values in the appropriate fields.
It is possible to select more than one column for the Memo field, by making consecutive selections. Memo columns already selected are indicated in the Memo columns: field. If you select multiple columns in this way, their contents will be concatenated in the Memo field.
If you attempt to choose the same column number for two fields, the importer will alert you and clear both selections. However, it is possible, if desired, to use the same column in both the Payee/Description and Memo fields. If you select a column for the Payee/Description field, and then select the same column for the Memo field, you will receive a warning that duplicate columns have been selected, but asking if you wish to proceed. If you do, click , and the column will be used for both fields.
One particular reason to also capture the Payee/Descriptor field in the Memo field is that the incoming Payee/Description field might get completely changed on import when KMyMoney does transaction matching. Selecting that field also as Memo will preserve that data, which would otherwise get lost.
If you notice you have made an incorrect choice, just change that selection. If several changes need to be made, you can click Clear all and start over.
Once columns have been chosen for all the necessary fields, the Next button will be enabled, and clicking it will advance the wizard.
The next page of the wizard still shows the data to be imported on the top, but the lower section provides three dropdowns. The initial values are set by KMyMoney based on examining the actual data.
The Decimal Symbol defaults to “auto,” which usually produces the correct results. If not, you can explicitly specify the “dot” or “comma.”
The Thousands Symbol is set automatically, and the field may be disabled if it will not affect the results.
This needs to be set according to the order of year, month, and day in the 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 it produce invalid results not detected (such as data with no days higher than 12, so month and day could be switched) you will simply get incorrect data, because the plugin cannot know you made a mistake. In this case, the error will be obvious in the ledger after import.
The Date format specifies the order of day, month, and year, and is also set automatically. However, in some cases, such as data with no day higher than 12, you will notice the problem after the import. In such cases, you will need to repeat the import, and choose the actual order of the date components.
This page is similar to the Banking page, although it is somewhat more complex. Most selections are fairly obvious, but there are some items which can seem confusing until you have completed the process once or twice.
As on the Banking page, you may select more than one column for the Memo field
The Type/Action selector is to identify the column which contains the action type, such as Buy, Sell, Dividend, etc.
The Price Fraction selector is to indicate the fraction/multiplier for compatibility between imported and stored prices. For instance, if the import file price is in cents, but your KMyMoney account is priced in dollars, select 0.01. Or, if your KMyMoney data file pricing is in dollars, and so is the CSV file being imported, then set Price Fraction to 1.0.
There are two ways to specify fees for transactions. If your file has a distinct column for fees, use the Fee Column selector. Note that on this page, this is the only field to explicitly include "column" in the label, to emphasize that it is for the fee column, not for any actual fee. Beware, though, that the fee might have been taken into account in the price.
If there is a fee, but it is a percentage figure, rather than a specified value, click the Fee is percentage check box, and enter the percentage rate into the Fee rate field. In addition, if there is a minimum fee, specify it in the Minimal Fee field, which becomes enabled when you enter the Fee rate (with a decimal point.) At this point, clicking will add a column at the right of the table above, and will indicate the calculated fee for each transaction.
If you made a mistake in entering the fee information, clicking will reset all the fee related fields to their initial values.
Below the column selectors are two areas for the security identity. Depending upon your broker or financial institution, your file may contain entries for only one or for several securities.
If the file contains transactions for just a single security, with the name possibly in a header row, the name should be entered into the Security Name box. The name you enter will be added to the dropdown list for future use. You may subsequently wish to remove that name from the list. If so, select it, then click Hide security. This removes it only from this list, and has no effect on your main KMyMoney file.
If the file includes transactions for several securities, each will be identified by its ticker symbol in a column with further detail in another column. Select those columns in the Symbol and Detail selectors. It may be that a security has no official symbol, and in this case a pseudo-symbol may be invented; this is not a problem, as long as it uniquely identifies that security in the import file. Sometimes the actual activity type is embedded in the detail column, possibly prefixed by a standard text. For instance, if the field contains “type: dividend”, go to Filter text box and enter “type: ” including the trailing space.
When all required fields are selected, the Next button will be enabled, and clicking it will advance the wizard.
For an Investment file, after the Columns page has been accepted, you need to assure that each security in the file is matched to the correct security in your KMyMoney file, before import can proceed. At this point, another window will open, showing the securities and symbols contained in the import file. Note that unlike the data display in the main wizard windows, the changes you make on this page are imported.
Completing this page is straightforward, if you consider these items:
Each row represents one transaction, and so it may appear there are duplicate rows. This is OK.
Each security name must match exactly the existing security as specified in KMyMoney. If it does not match, it will be created as a new security, which you probably do not want, unless it represents the purchase of a new security.
A symbol must be shown for each security.
The only information on this page should be the security symbol and name. Any other information initially shown (such as date or activity type) is still in the actual import file, but should not be shown here.
You can edit a symbol or security name by double-clicking the cell. For each security, if necessary, edit the name in one of its rows, If the correct security name appears in the imported file, double-click it to select it, then copy and paste/edit, taking care if you have used a variation or abbreviation within KMyMoney. If you edit a security name, that edit will be applied to all rows with the same symbol.
Any line without a symbol will be treated as a brokerage-type checking item. 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. This will generally be the Brokerage account you chose or created when you created the Investment account. 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 you will need to select the correct transfer account after the import. Click when done. The import process then gets handed over to KMyMoney.
If you have more that one transaction referring to the same security, you can edit all of them at once, using multi-select. For instance, to add a symbol for several lines, press and hold the Ctrl key, and in the symbol column, select each transaction. While still holding the Ctrl key, all those symbol cells should still be selected, so click one and enter the symbol. Click inside the window but outside that column, or hit Enter (not ). Now that those transactions all have the same symbol, double-click one detail entry and edit the security name as you wish. Click elsewhere on the window (or Enter) to accept the edit, which will then change all those entries. The remaining entries will show the symbols picked up from the transactions in the import file.
Now click , then . In the Enter Account box, enter the name of a Brokerage/checking account for funds. If you enter a valid name that account will be used. If you can't be bothered entering a correct but long name, enter a few characters. The import will accept that but the transactions in the ledger after import will need a proper account to be selected. For the Brokerage box, enter the number of the column containing that detail. Now, on the Invalid transaction box you may get a few entries because the activity type does not match the qty/price/amount combination. On each message, click Select Transaction Type, and a dropdown will appear indicating valid activity types for that combination of values.
Now the import has occurred and you're into KMyMoney to select the investment account to use. Then the checking account, if there were any brokerage type transactions.
On reaching the Final page, the plugin automatically validates the values. If the numeric value column/s is/are highlighted in green, then the validation was successful and all that is necessary is to click and control then passes to the main KMyMoney program. However, if the start and/or end lines are incorrectly set, or if the wrong columns were selected, the highlighting will be in red, and an error message will appear indicating where the error lies. The user will then need to click to get to the relevant page to correct the error.
It might also be that if debit and credit columns are in use, one of those columns may legitimately contain no entries. This would mean that that column has no decimal symbol present, and this would result in a warning. If you see that this is the case, you may click either of the buttons to accept ( or .)
Decimal Symbol. Another possible problem might be that the selected decimal symbol is incorrect. Selecting the symbol to match the data should clear that error. Normally, you should not need to change this selection. Note that the Decimal Symbol must be set to match your file, not your locale. If your locale setting has a different value, conversion will be seen to take place. The display of the file in the upper part of the window will show numeric fields highlighted in green if the current setting produces valid results, otherwise in red. The highlighting also reflects the Start line and End line settings. 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. In addition, the selector will be inactive if none of the values to be imported is greater or equal to 1000.
Import CSV. Clicking this button tells the plugin to actually import the data from the file, based on the choices you have made above. KMyMoney will prompt you for the correct account into which to import the data.
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. This was actually the original functionality that drove the creation of this plugin. However, as KMyMoney itself is now able to export a qif file, the capability is now probably of little use and is likely to be removed eventually.
For a Banking import, the plugin has finished, and KMyMoney will prompt you, as stated above, for the correct account into which to import the data. For an Investment import, however, a little more may be required. 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 wish to save your settings, remember to click , and the plugin will then close.
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, and
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.
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
operating system or distribution. On a Linux® system, this will be $KDEHOME/share/config where $KDEHOME is
usually .config within your home folder. If
you are migrating from a version of KMyMoney prior to 5.0, the old location of
$KDEHOME was .kde4, also
within your home folder. 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, it 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
(kmymoney kde.org) or developer list (kmymoney-devel kde.org), the developer will do his best to
modify the plugin to handle it.
Finally, there are a few other items which may be configured in a dialog invoked through the CSV Importer configuration dialog, which can be invoked from the Plugins page of the main KMyMoney configuration dialog. This includes which items will be autodetected by the importer and which profile will be used if you do have the importer export a QIF file, as mentioned above.
To export data to a CSV file, choose the → → menu item. The dialog which appears gives you a number of choices which control the data to be exported.
Please note that an exported CSV file can include data from only one account.
Specify the name of the exported CSV file either by entering the complete path in the File to export to field, or by clicking Browse and navigating to it in the Save File dialog. Choose which account to export with the Account to export dropdown.
The time period of data exported is controlled by the Date Range Start on and End on date fields. The default values represent the complete date range of your entire data file, and they are adjusted when you select an account to export.
By default, the Field Separator is a comma, but you can also choose to use a tab or semicolon.
If you check the Contents to Export Account radio button, then the data exported will be the transactions from the specified account and date range. However, if you select the Categories radio button, then the exported file will contain one row for each category which has been defined. For top level categories, the category name is shown, and an “I” or “E” is shown to indicate whether the Category is Income or Expense. For non top-level categories, the parent category is also shown.
The exporter will output a set of columns appropriate for the type of account being exported.