Importing Investment Data from a CSV File
You can transfer your investment data into StockMarketEye via Comma-separated values (CSV) files. You can create CSV files via most spreadsheet applications such as Microsoft Excel or OpenOffice.org Calc. StockMarketEye can also create a CSV file in this format. See the “Exporting to CSV” section.
CSV Import Format
StockMarketEye reads your investment data from the CSV file, one transaction per line. The data contained in each line of the CSV file must meet the specifications described in the sections below.
- All columns must be present in each line of the CSV file.
- Column values are mandatory for the columns in bold in the “Column Order” table below.
- Column values for the other columns are optionally required, depending on the type of transaction being recorded. See the “Column Descriptions” table below for details.
- The order of the columns in the CSV file must be the same as that specified in the “Column Order” table below. See the example file if you are unsure.
- You may include a “header” line, but it is not required. If the header line is included, the first cell must contain the word “date”.
Column Order
| Transaction Date | Transaction Type | Symbol | Shares | Price/Share | Costs | Fees | Total | Distribution Amount | Shares Affected | Currency | Exchange Rate | Cash Affected | Name | Comment | BrokeageId | Taxes | Credits | ExchangeRateCurrency | ACB |
You can download an example CSV investment transactions file here.
In your spreadsheet application, the file to import will look something like this:
Column Descriptions
The following table describes the columns of the CSV file in greater detail.
| Column | Usage | Description |
|---|---|---|
| Transaction Date | Value is mandatory | The date of the transaction. Available date formats are: MM/DD/YY or MM/DD/YYYY (ex. 07/4/2010 for July 4th, 2010) YYYY-MM-DD (ex. 2010-07-04) MMM D, YYYY (ex. July 4, 2010) This column is mandatory and must always have a value specified. |
| Transaction Type | Value is mandatory | The type of the transaction. Available transaction types are: BUY SELL SELL SHORT COVER SHORT CASH IN CASH OUT DIVIDEND DIVIDEND REINVEST INTEREST IN INTEREST OUT SHARES IN SHARES OUT SPLIT CAPGAIN CAPGAIN REINVEST RETURN OF CAPITAL OTHER IN OTHER OUT FEES This column is mandatory and must always have a value specified. |
| Symbol | Value is mandatory | The symbol connected with this transaction. You must specify a symbol for every transaction unless the transaction type is cash related (CASH IN, CASH OUT, INTEREST IN, INTEREST OUT, OTHER IN, OTHER OUT, FEES). If the transaction type is cash related, this column can be empty or can contain the word “Cash”. |
| Shares | Value is optional | The number of shares associated with this transaction. Whole and decimal numbers are accepted. This column is not used when the transaction type is cash related or for DIVIDEND, CAPGAIN or RETURN OF CAPITAL transactions. For SPLIT transactions, this column must contain the split ratio as a decimal number (i.e 2-for-1 split is 2.0). |
| Price/Share | Value is optional | The price per share of this transaction. This column is not used when the transaction type is cash related or for DIVIDEND, CAPGAIN, RETURN OF CAPITAL or SPLIT transactions. If this column is empty, StockMarketEye will lookup the price of the stock on the transaction date and use the closing price from that date as the price/share. |
| Costs | Value is optional | This column specifies any costs or commissions associated with the transaction. If this column is empty, the costs for this transaction are set to 0. |
| Fees | Value is optional | This column specifies any fees associated with the transaction. If this column is empty, the fees for this transaction are set to 0. |
| Total | Value is mandatory for cash transactions. | The total amount of the transaction, including costs and fees. A value in this column is mandatory for CASH IN, CASH OUT, DIVIDEND, RETURN OF CAPITAL, CAPGAIN, INTEREST IN, INTEREST OUT, OTHER IN, OTHER OUT, and FEES transactions. This column is not used when the transaction type is SPLIT. For all other transactions, if no value is specified, the value will be computed from the price/share, shares, costs and fees columns. |
| Distribution Amount | Value is optional | The amount of the distribution per share. This column is only used for distribution transactions such as DIVIDEND, CAPGAIN, RETURN OF CAPITAL and their re-investment equivalents. It is for informational purposes and can be left blank. |
| Shares Affected | Value is optional | The number of shares affected by a distribution or SPLIT transaction. This column is for informational purposes and can be left blank. |
| Currency | Value is optional | The currency of the transaction. If this field is left blank, the import will determin the currency based on the symbol (if this is a symbol specific transaction) or take the currency of the portfolio (if this is a cash transaction). |
| Exchange Rate | Value is optional | The exchange rate between the currency of this transaction and the Portfolio’s currency on the date of the transaction. If your transactions and Portfolio are in the same currency, this column can be left blank. Defaults to 1 if left blank. |
| Cash Affected | Value is optional | Were the proceeds of this transaction added/deducted from the Portfolio’s cash balance? Can be specified with ‘yes’ or ‘no’ (or ‘true’ / ‘false’). If this field is empty, the import function will determine the proper value. |
| Name | Value is optional | The full name of the company whose stock this transaction concerns. If this field is empty, the import function will search for a name. |
| Comment | Value is optional | Any comments you want to remember about this particular transaction, up-to a limit of 2048 characters. This column is optional and can be left blank. |
| Brokerage Id | Value is optional | The ID of this transaction at the brokerage. |
| Taxes | Value is optional | The taxes associated with this transaction. |
| Credits | Value is optional | The credits associated with this transaction. |
| Exchange rate currency | Value is optional | The currency to which the exchange rate converts. Typically this is the currency of the portfolio. |
| ACB | Value is optional | The Average Cost Basis value of this transaction. Use for sell transacitons. |
Old CSV Format
Prior to StockMarketEye 3, a slightly different CSV format was produced and imported by StockMarketEye. The old CSV format can still be imported, but StockMarketEye will no longer create CSV files in this format. You are encouraged to update any of your old StockMarketEye CSV files to the current format.
Importing a CSV Investment Transaction File
Once you have created a CSV file containing your investment transactions, you can import it into StockMarketEye with the following steps.
- Open StockMarketEye and use the menu: File -> Import Portfolio -> Import from CSV…
- In the “Import CSV Investment Transactions” window, click on the “Browse” button to help select the CSV file containing the investment data that you want to import. Once you have found the file, select it and click OK. The file’s name should now be in the “CSV Investment Transactions File” field. CSV Columns Format – You can also select the format of the CSV file you are importing. The default “StockMarketEye format” is the format described above. You can also import from the Investoscope 3 CSV format by selecting it from the format dropdown. Date Format – Normally you should leave the date format on “Auto” and StockMarketEye will detect the format automatically. However, there are some formats that StockMarketEye can not detect automatically (such as European-style date formats that conflict with US-style formats). If the dates in your CSV file have one of these formats, select it from the dropdown, otherwise leave the dropdown on “Auto”. It is also possible to change the advanced CSV options, such as which record separator character to use. Some CSV files use a semicolon as a separator rather than the default comma. This can be selected with the “Advanced CSV Options” button. Once you have entered an existing CSV investment data file, click on the “Next” button to move to the next step.
- Map the symbols found in the import file to symbols known to StockMarketEye. Stock (or ticker) symbols often differ between applications. In this step you can convert the stock symbols that were found in the import file into symbols known to StockMarketEye. Before moving to the next step, you must accept each symbol mapping by checking its “Accepted” box. If the box is initially checked, it has been mapped automatically by StockMarketEye. If the box is initially unchecked, there was no direct match to a StockMarketEye symbol. You can modify the mapped, StockMarketEye symbol by clicking on the “Modify” button. This will open the Symbol Search window where you can search for the correct symbol. Clicking “Ok” in the Symbol Search search window will use the selected “Search Result” as the mapped, StockMarketEye symbol. If you click “Ok”, but have not selected one of the “Search Results”, the search query itself will be used as the mapped, StockMarketEye symbol. This allows you to enter and use unrecognized ticker symbols. Note that you don’t need to have a valid ticker symbol for each entry, but you do need to accept all of the mappings before continuing. Unrecognized symbols (ex. obsolete or non-existent ticker symbols) are OK to include in the mapping and are often useful for including items in your portfolio which do not have a ticker symbol (ex. bonds, preferred stock, etc). Note, however, that StockMarketEye will not be able to get historical or current quotes for these symbols which can affect performance calculations and the Portfolio’s historical value chart. You can reset the mapped, StockMarketEye symbol to its original, unmapped symbol using the “Reset” button. This will uncheck the symbol’s “Accepted” button and reset the StockMarketEye symbol to the symbol from import. When all of the symbols have been mapped and accepted, click on the “Next” button to move to the final step.
- Finish the import of the investment data by selecting a portfolio into which the data will be added. You can either select an existing portfolio, or create a new one. Click on the “Finish” button to complete the import.
- The investment data has now been imported into the selected portfolio.
