Quicken Windows Help
Quicken Windows Help
Breadcrumbs

Import investment transactions from a .CSV file

You can import investment transactions into Quicken from a comma-separated values (.CSV) file. This is useful when you need to add historical trades, record activity from a brokerage that isn't supported for download, or build a position history by hand.

Banking transactions use a separate import path and a different file format. To import those, see Import transactions from .CSV file.

Investment CSV imports do not check for or match existing transactions. Every transaction in the file is added to the account, even if an identical transaction already exists.

Start from the sample file

Quicken can generate a sample .CSV file that shows the required column order, lists the valid action codes, and includes working example rows. Start there rather than building a file from scratch.

  1. Open your Quicken data file.

  2. Choose File menu > File Import > Investment Transactions > Create Sample File.

  3. Click OK when Quicken confirms the file was created on your desktop.

  4. Open InvestmentTransactionsSample.csv in a plain-text editor or a spreadsheet tool that supports UTF-8 encoding.

  5. Replace the example rows with your own transactions.

Lines that begin with COMMENT are ignored during import. You can leave the explanatory comment lines in place or delete them.

Required file format

The file uses these columns, in this order:

action, date, account, security, optionalSymbol, shares, price, amount, commissionFee, num, payee, memo, clearedStatus, category, tag, transferAccount, basisDate

Every row must supply action, date, and account. Beyond those three, the columns a row requires depend on its action. You can leave an interior column blank, and you can omit trailing columns entirely, as the example rows in the sample file do.

A header row is optional. Quicken reads the columns by position, so a header row does not change how the file is interpreted. If you include one, keep the columns in the order shown.

What each column holds

  • action — The transaction type, using one of the codes listed below.

  • date — Either YYYY-MM-DD or MM/DD/YYYY.

  • account — The investment account that receives the transaction.

  • security — The security name as it appears in Quicken, not the ticker symbol.

  • optionalSymbol — A ticker symbol. If the security name has no exact match in your data file, Quicken tries to match on this symbol instead.

  • shares — The number of shares. For a stock split, put the ratio of old shares to new shares here.

  • price — The per-share price.

  • amount — The total amount. For buy, sell, and reinvestment transactions you can leave this blank, and Quicken calculates it from price, shares, and commission.

  • commissionFee — Commission or fees on the transaction.

  • num — A reference number for the transaction.

  • payee — The payee.

  • memo — The memo.

  • clearedStatus — The cleared status of the transaction.

  • category — The category. Use Category:Subcategory for subcategories.

  • tag — The tag. Use tag1:tag2 for multiple tags.

  • transferAccount — The other account in a transfer action. Required for transfer actions.

  • basisDate — The date the shares were acquired. Use this on an Added transaction to place the shares in a lot dated earlier than the transaction itself.

Valid action codes

Use one of these values in the action column:

Added, Bought, BoughtX, Cash, CGLong, CGLongX, CGMid, CGMidX, CGShort, CGShortX, ContribX, CvrShrt, CvtShrtX, Div, DivX, IntInc, IntIncX, MargInt, MargIntX, MiscExp, MiscExpX, MiscInc, MiscIncX, ReinvDiv, ReinvInt, ReinvLg, ReinvMd, ReinvSh, Reminder, Removed, Reprice, RtrnCap, RtrnCapX, ShtSell, ShtSellX, Sold, SoldX, StkSplit, WithdrwX, XIn, XOut

Additional notes

  • The characters [, /, |, ^, and : are not allowed in account, category, or tag names. Quicken converts them to hyphens during import.

  • If a field contains a comma, enclose the field in double quotes.

  • Enter expense amounts, such as those on a MiscExp transaction, as negative values.

Import the file

After your file is formatted, import it. Quicken walks you through a review of what it found before writing anything to your data file.

  1. Open your Quicken data file.

  2. Choose File menu > File Import > Investment Transactions > From (.CSV) File.

  3. Browse to your file, select it, and click Open.

  4. If Quicken found rows it can't use, review the list under Items in CSV file that cannot be imported. Click Next to import the remaining rows, or click Cancel to stop and correct the file before importing.

  5. Under Account name in CSV file, review each account Quicken found. Click Next.

  6. Under Security name in CSV file, review how Quicken matched each security. Click Next.

  7. If your file contains categories that aren't in your data file, review them under New Categories in CSV file and click Next.

  8. If your file contains tags that aren't in your data file, review them under New Tags in CSV file and click Next.

  9. Under Changes to your Quicken file, review the summary of accounts, securities, categories, and tags to be added or updated. Click Import.

  10. Click Close.

You can click Back on any review screen to revisit an earlier one. Nothing is written to your data file until you click Import.

How Quicken matches securities

Quicken tries to match each security in your file to a security you already hold, first by name, then by the ticker symbol in the optionalSymbol column. A security that matches is marked Existing, and Quicken notes when the match came from the symbol rather than the name. A security with no match is marked New Security and is added to your data file when you import.

Because matching falls back to the symbol, a name that differs from the one in your data file still matches when the symbols agree. A file naming a security Apple matches a holding named Apple Inc when both carry the symbol AAPL.

A security you leave unlinked is created as a separate holding, even when you already hold the same security under a different name. Link it during the import to avoid splitting one position across two securities.

To point an unmatched security at one you already hold:

  1. On the Security name in CSV file screen, click link to existing next to the security.

  2. In Link Security to Quicken, choose the security from the Name in Quicken list.

  3. Click OK.

The security is then marked Linked to, followed by the name you chose. Click Unlink to undo the link and let Quicken create a new security instead.

New securities are created as stocks. The file format has no column for security type, so a mutual fund, bond, or other security type is created as a stock with an unclassified asset class. To correct the type, choose Tools menu > Security List, select the security, click Edit, and change Type.

New categories and tags

Quicken adds any category or tag in your file that doesn't already exist in your data file.

A new category is created as an income category by default. If the category records an expense, click change type next to it on the New Categories in CSV file screen to switch it to an expense category before you import.

When rows can't be imported

Quicken checks the file before importing and lists any rows it can't use, grouped by cause:

  • An invalid value in the action column

  • A missing or invalid date

  • A missing account

  • A missing security name and symbol

  • A missing transferAccount on a transfer action

Quicken skips those rows and imports the rest. The count at the top of the window updates to reflect only the rows it will import.

Which columns a row requires depends on its action. Income actions such as IntInc need a security even when the income isn't tied to one, while MiscExp does not.

This check does not cover the numeric columns. Enter numbers only in shares, price, amount, and commissionFee. A non-numeric value in one of these columns is treated as zero, and the transaction is imported without a warning.

After the import

Quicken reports the number of transactions imported. Review the account afterward to confirm the results.

For a buy or reinvestment with a blank amount, Quicken includes the value in commissionFee in the lot's cost basis.

A StkSplit row adjusts every lot of that security in the account, including shares you held before the import. Quicken restates the share count and per-share price of each affected lot.

Buys and reinvestments draw down the cash balance of the receiving account, and sells add to it. If you import trades without also importing the cash that funds them, the account's cash balance goes negative.

Because investment CSV imports do not match existing transactions, reimporting the same file creates duplicates.