Import security prices from CSV file

Andy McC

I have some investments from a company that does not allow exports to quicken. I am hoping to be able to bring the stock prices from an excel report into Quicken. I am using Quicken Windows R32.10 Canada. I set up an excel file with the first row as the 'header' with two columns; symbol and price. I then entered the symbol and price information on rows 2 and down. I made sure the symbols were entered into quicken in the security details area under 'symbol'. I then saved the excel file as a comma separated values file (.csv). I then went to 'investing' in quicken and to file/file import/import security prices from csv file. I selected the file and tried the import and got the error message 'no valid prices found to import'.

Any thoughts on what I am doing wrong in this process would be appreciated.

Chris_QPW

Make sure the data is in this format:
How do I import data into Quicken for Windows? | Quicken

bmciance

For one thing I know you also need a date column. See if that does it. 

Chris_QPW

Note on the dates.  I'm not sure with the Canadian version, but I wouldn't be surprised that they have to be in MM/DD/YY year format (opposed to say DD/MM/YY).  In the case of QIF imports Quicken only uses that format even with the Canadian version.

Jim_Harman

I edited the title of your post, it is a CSV file, not CVS.

From the Help,
-------------------------------------------

1. • About using the CSV format for security prices

     The data must be in standard ASCII format with one symbol/price/date per line, separated by either commas or double spaces (using only one type of separator per line).

     These import formats are all acceptable. Note that Quicken can handle quotation marks:

              ABC, 123.456

              ABC, 123.456, 12/31/02

              ABC 123.456 12/31/02

              ABC, "ABC", 123.456, "12/31/02"

               "ABC", "123.456", "12/31

2. In the Date field, change the date information if necessary.
   

   If the file doesn't contain specified dates, import the prices to the date entered here. The currently selected date in Portfolio is the preset display in this field.

   Quicken matches prices with your securities if they have the same symbol. If your securities don't have symbols, you'll need to edit them first so they do.

3. Click OK.

--------------------------------------------
The first row should not be a header, the file should just contain the data to import.

Chris_QPW

BTW from the help example you will notice they do give one without a date.  What will happen for that one is it will use the current date.

Chris_QPW

One more thing that might not be obvious, make sure you have the security already added to Quicken with the symbol filled in.

Andy McC

I filled in the symbol on the security (see attached screen shot) before I did the import. I also used the default date in the import window (see second attached screen shot). Third screen shot shows my layout in excel. Did not work.

q_lurker

a) I would open the csv file in Notepad or similar to confirm the formatting as the data is flowing to Quicken.
b) I typically use the second listed line formatting == ABC, 123.456, 12/30/20
c) For no particular reason, I always include the date.  Not trusting Quicken to fill it in as I intend it.  That also means I can build that csv file at any time and use it at any time.  
d) I have included header lines before without incident.  It seems to get treated as an indeterminable quote line.
e)  I would simplify the name of the file being imported to something much simpler - Quotes.csv, for example. 

Jim_Harman

In your CSV file, make sure there is one carriage return and linefeed after the last line. In Notepad, if you position the cursor at the end of the last line and hit the down arrow, the cursor should go to the next line. Excel may omit this when you export.

Andy McC

Jim - thank you. The one carriage return was the issue, I opened in note pad as you suggested, put in the return after the last line and Presto!

Paul Suchma

I am able to import security prices for Windows Quicken (Deluxe R38.30) into my investment account, but when I update the transactions in the account it seems to over-write my imported prices. How can I prevent this?

q_lurker

@“Paul Suchma” - Basically, you can’t. The CSV-file data is lower on the hierarchy than data downloaded from the brokerage. 

Why do you think the csv data is better, or why is it different than the brokerage data?  How different are the values?

Paul Suchma

In one of my investment accounts the imported data better matches the prices on my statements for some securities, which makes it easier to reconcile them. Would I be able to turn off Download Quotes in the Security List for the those that chronically mismatch as a work around?

Jim_Harman

Paul Suchma said:

Would I be able to turn off Download Quotes in the Security List for the those that chronically mismatch as a work around?

Yes, that is what the Download quotes checkbox for each security does.

Is there a specific type of security where you are seeing mismatches between Quicken's quote prices and the imported prices?

For example, I have seen some ETFs where it appears that Quicken's price is sometimes the closing trading price and sometimes the closing NAV. These are generally quite close to each other. If you are seeing much larger discrepancies, please post back for further ideas.

Paul Suchma

I am following up on an earlier post since I have found out the root cause of my problem with my Quicken balances not matching my brokerage reports. The original post (https://community.quicken.com/discussion/7893435/import-security-prices-from-csv-file) has since been closed, but I though I'd offer my results anyway, albeit a bit late. Perhaps someone with greater powers than I can merge this into the original post.

It seems the brokerage reports were using closing security prices in their reports so I might have to download the prices the day after to report end-date to get the matching values.

[Merged Post]