Manually updating Quicken security prices to include Price, High, Low and Volume

jaxgab

Does anyone know how to successfully update Quicken security security prices to include High, Low and Volume? I already tried this:

JPM,170.10,---,12/30/2023,---,170.69,169.63,6431,*

Quicken will import this price record (via File > File Import > Import security prices by CSV file …) but the new price information does not get reflected in the security price history. Quicken has also already claimed this is not possible.

Boatnmaniac

Your profile shows you are running Subscription. If your Subscription has not expired, have you tried using Quicken to download the security prices instead of manually importing them? This will populate all 3 of those fields.

But Quicken will download prices only for the days that the stock market is open. Your data shows a date of 12/30/2023 which is not an open stock market date. Maybe that is the reason why the data is not being accepted into Quicken?

Also, in Security List, is the box for Download Quotes checked? And if you are not holding any shares but still want to download quotes be sure to check the box for Watch List.

mshiggins

I import all my security prices with high/low and volume data. The format you show in your example looks correct.

Are you only entering the single line shown in your example in your CSV file? If so, try adding a carriage return after the asterisk.

Chris_QPW

Is the Symbol in your security list the same?

I just took what you posted created a security with JPM as the symbol and imported it with no problem:

Note that the article that Quicken Inc provides is missing how to import the information other than the price, the format you have can import the high/low and volume (with some limitations on volume).

https://www.quicken.com/support/how-do-i-import-data-quicken-windows

q_lurker

We have an FAQ for that style of import, and I think the OP is following it.

https://community.quicken.com/discussion/7542737/faq-importing-security-prices-including-hi-lo-vol

As far as I know it is still valid. @mshiggins thoughts are more on target in my opinion than the others.

When needed I prepare that type of file in Excel and save it as a .csv file. Provided I get it right, like no extraneous data, the data imports fine for any valid date past or future.

jaxgab

The interesting issue is that Quicken will always consistently report the import was successful ("Successfully imported 1 price(s)"), but the imported price never shows up in the JPM price history.

1. Yes, I am only entering a single line in the CSV file.
2. The ticker in the security list is the same ("JPM").
3. After adding a carriage return to the end of the single record in the CSV file, the results don't change.

It is plenty interesting that Chris_QPW can take the exact same record and get the data to show up in the JPM price history.

jaxgab

There is a personal desire to sometimes manually import security prices, especially when you own some Canadian oil & gas stock which has both Canadian tickers (i.e. PPR.TO in Yahoo or XTSE:PPR in Microsoft) and U.S. ADR tickers (i.e. PRPRF). The Canadian tickers will usually have significant volume compared to the U.S. ADR tickers. I own the U.S. ADR tickers.

Example:

For PPR.TO, the Friday price closed @ C $ .07 with a volume of 43,00 shares, while the U.S. ADR ticker closed @ U.S. $ .0361 with a volume of zero shares. The Canadian price of C $ .07 converted to U.S. currency is U.S. $ .0528 (C $ .07 x .0.7547).

Quicken will automatically download the U.S. ADR ticker prices but these prices are arguably significantly flawed, especially in the real world example I just provided.

mshiggins

How are you creating your CSV file?

I have the price data in an Excel file and copy and paste from Excel into Notepad. The Notepad file is saved with the .csv extension. If there is no carriage return on the last line, the last price will not import.

Chris_QPW

For the record I copied what was in the comment to a text editor and imported that.

jaxgab

I have always created my price data files within Excel (i.e. not using Notepad). This has always worked when I'm only importing ticker, date and price.

By moving the pride data from Excel to Notepad and adding a carriage return, however, I was able to import the price data and actually see it in Quicken. The CSV file created by Excel doesn't seem to ever work even with carriage returns, even though Quicken will consistently report "Successfully imported 1 price(s)".

q_lurker

Did you ever open the Excel produced csv file in Notepad? Maybe you’ll be able to see the problem. My excel produced files with hi-lo-vol have been fine.

jaxgab

I do see the problem now. The hi-low-vol price data, apparently because the fields are being separated by commas, is getting surrounded by Excel within double quotes when the file is saved as a CSV (MS-DOS) (*.csv) file. I'm still surprised Quicken will actually try to import this CSV file and generate the message "Successfully imported 1 price(s)". Quicken will not try to import this record:

TEST,100,12/31/2023

So how do you excel produce files with hi-lo-vol price data?

Chris_QPW

Do you have any spaces in those values?

Spaces in a data field will get cause Excel to quote the field.

I had no problem creating this file with Excel (saved as CSV (MS-DOS)):

q_lurker

With column D widened, in Excel, my csv file looks like:

Opening the same file in Notepad:

Note that Quicken seems to ignore the header line probably because there is no security with ticker = TICKER, and Date and other values don't fit.

jaxgab

I've already learned a lot this week about creating a Quicken .csv security price file within Excel. My old way was to use Excel formulas to build strings in column A cells (cell A1, A2, …), trying to match the acceptable Quicken formats. This actually worked fine if you are trying to import "normal" closing security prices and used double spaces to separate the three price fields (ticker, price and date).

The better way is to use Excel to build the file of security prices with fields in:

• columns A-C for "normal" closing security prices, or
• columns A-I for hi/lo/vol security prices

That's because Excel can save the data as a .csv file, automatically separating each field with a comma and each price data record won't be surrounded by double quotes.

q_lurker

Lesson 2: If you need historical prices, you can frequently get them from Yahoo finance (and others) as a csv file download. Open that file in Excel, move some columns around, add some columns, Compute the Vol/100 column and then copy that to itself as values. Pretty soon you have a file ready to import into Quicken.

Caution with respect to that path: Yahoo and others usually present the prices from before a split or spinoff type of corporate event as adjusted for the split. That is not normally what you want in Quicken. So be cautious around such circumstances.

jaxgab

I've now learned a lot about building a Quicken security price .csv file within Excel. I have always built a single string in each column A cell using Excel formulas and placed the entire string in cell A1, A2, … (i.e. trying to match the exact input formats that Quicken says are acceptable).

Some examples:

JPM 170.30 12/28/2023 in cell A1 (test1.csv) JPM 170.10 12/29/2023 in cell A2

This works. The price data fields are being separated by double spaces and Excel will not surround each price data record with double quotes.

JPM,170.30,12/28/2023 in cell A1 (test2.csv) JPM,170.10,12/29/2023 in cell A2

This doesn't work. The price data fields are now being separated by commas and Excel will surround each price data record within double quotes. You can see the double quotes when you look at the ,csv file using Notepad but the double quotes don't seem to be there when you open the .csv file with Excel. Quicken will message the price records were not imported ("No valid prices found to input").

JPM 170.30 --- 12/28/2023 --- 170.66 169.00 6320 * in cell A1 test3.csv JPM 170.10 --- 12/29/2023 --- 170.69 169.63 6432 * in cell A2

This doesn't work. The price data fields are being separated by double spaces and Excel does not surround each price data record within double quotes. Quicken will message that the price records were imported ("Successfully imported 2 price(s)") but the price records won't be accurately displayed within Quicken.

JPM,170.30,---,12/28/2023,---,170.66,169.00,6320,* in cell A1 test4.csv JPM,170.10,---,12/29/2023,---,170.69,169.63,6432,* in cell A2

This doesn't work. The price data fields are being separated by commas and Excel will surround each price data record within double quotes. Quicken will message that the price records were imported ("Successfully imported 2 price(s)") but the price records won't be accurately displayed within Quicken.

JPM 170.30 --- 12/28/2023 --- 170.66 169.00 6320 * in cell A1 to cell I1 test5.csv JPM 170.10 --- 12/29/2023 --- 170.69 169.63 6432 * in cell A2 to cell I2

This works, and seems to be only way to successfully build the hi/low/vol Quicken security price .csv file within Excel. Excel will automatically separate the nine fields for each price record with commas and also won't surround each price data record within double quotes. You are obviously no longer building a long price data string within column A cell A1, A2, … Quicken will message that the price records were imported ("Successfully imported 2 price(s)").