How to Import Securities - DATE, PRICE, HIGH, LOW, VOLUME- Data into Quicken?

DiogCuba

Hello, I am from Brazil and all my stocks are listed in BOVESPA which means I have to create a CSV file from Google Finance or Yahoo and import these data to Quicken every now and then to seet the poerfomance of them.

It turns out that if I do the manually update via de Security Detail View I can input: DATE, PRICE, HIGH, LOW, VOLUME. But I would have to waste a lot of time to do that operation everyday for all my stocks...

If I do the file import from a CSV file I then get only to import DATE and PRICE only.

I wonder if there is a way I can import the same information I can input manually: DATE, PRICE, HIGH, LOW, VOLUME.

It seems in the past it was possible but that format is not working on my Quicken 2017.

q_lurker

It is now pretty well documented in Quicken's Help files how to import security prices into the datafile as a csv file.  The actual format can be flexible, but the common sequence is:
Ticker, Closing Price, Date
CTXS, 3.00, 1/1/2016
CTXS, 8.12, 1/2/2016

While sometimes asked, importing HI, Lo, and volume data can also be done, but the sequence is not documented anywhere in Quicken that I am aware of.  mshiggins had a post some years ago in a discussion on the qlc site about "Importing Stock Prices Why the Change", but my current search for that post produced nothing from this site. 

But I did finally uncover the secret through the usenet forum feeds; a 5/4/2005 post from Marjohn.  The magical sequence is

Symbol, Close, ---, Date, ---, Hi, Lo, Vol/100, *

CTXS, 3.00, ---, 1/1/2016, ---, 4.00, 2.00, 500, * 
CTXS, 8.12, ---, 1/2/2016, ---, 9.34, 7.56, 1000, *

• I do not know that three dashes are required vs 1, 2, 4, or none, but it worked that way.
• The Volume does need to be divided by 100; my two examples will appear in Quicken as volumes of 50,000 and 100,000 shares, respectively.
• It had been identified that volumes over about 20,000,000 shares would not be read in.  I have confirmed that Vol/100 of 20,000,000 will appear as 0; Vol/100 of 200,000 will appear as 20,000,000 in the price history.  So there is some limit like that.
• No " " are needed but I suspect if your ticker has spaces, quotes might be beneficial.  Other than that, I do not think any other variation are allowed.  
• I have not tested for dates before 1/1/2000.  Given that the 4-digit year worked here, that is the direction I would try for old dates.
• I believe the closing asterisk is also required.
• I know this works in US QW2017 and did in prior versions as well.  No idea about CAN, or Mac versions.   

Happy importing. 

[Edited . to , in example per JIm's later comment.]

https://community.quicken.com/discussion/7542737/faq-importing-security-prices-including-hi-lo-vol/p1?new=1

Jim_Harman

The example data for CTSX worked for me with the following observations

-- in the second line change the dot to a comma after the first price
-- need a carriage return and linefeed after the last line or it does not import
-- there must be an existing security with the ticker CTSX for the import to work properly.


@DiogCuba maybe the second item is what made your data not import. The other numbers in the Quote section are all NA because they come from the Quote feed.

smayer97

For reference for formatting, you can read this article:

https://www.quicken.com/support/how-import-historical-security-data-quicken

Ps56k2

Do you really look at the daily high/low and volume info for each security

vs just having the date & closing price - which would show your trends...

EDIT - updated topic title to better reflect actual Q&A

DiogCuba

Thankf for your time to see my problem, ps56k.

Currently I don´t use Quicken to run my investiments, but since I use it for my financial life I would like to bring my stocks to it too.

I understand Quicken needs that information to run some reports. Am I wrong to assume that? Because, for instance, the "Quote" box is empty without all that information.

If that information can´t be imported please let me know.

Ps56k2

DiogCuba said:  Because, for instance, the "Quote" box is empty without all that information.

What "quote" box - where ?

Jim_Harman

The only data you can import via the CSV format is the closing prices for your securities. The other quote data comes from Quicken's quote provider, which for the US version of Quicken only supports securities traded on US exchanges.

q_lurker

It is now pretty well documented in Quicken's Help files how to import security prices into the datafile as a csv file.  The actual format can be flexible, but the common sequence is:
Ticker, Closing Price, Date
CTXS, 3.00, 1/1/2016
CTXS, 8.12, 1/2/2016

While sometimes asked, importing HI, Lo, and volume data can also be done, but the sequence is not documented anywhere in Quicken that I am aware of.  mshiggins had a post some years ago in a discussion on the qlc site about "Importing Stock Prices Why the Change", but my current search for that post produced nothing from this site. 

But I did finally uncover the secret through the usenet forum feeds; a 5/4/2005 post from Marjohn.  The magical sequence is

Symbol, Close, ---, Date, ---, Hi, Lo, Vol/100, *

CTXS, 3.00, ---, 1/1/2016, ---, 4.00, 2.00, 500, * 
CTXS, 8.12, ---, 1/2/2016, ---, 9.34, 7.56, 1000, *

• I do not know that three dashes are required vs 1, 2, 4, or none, but it worked that way.
• The Volume does need to be divided by 100; my two examples will appear in Quicken as volumes of 50,000 and 100,000 shares, respectively.
• It had been identified that volumes over about 20,000,000 shares would not be read in.  I have confirmed that Vol/100 of 20,000,000 will appear as 0; Vol/100 of 200,000 will appear as 20,000,000 in the price history.  So there is some limit like that.
• No " " are needed but I suspect if your ticker has spaces, quotes might be beneficial.  Other than that, I do not think any other variation are allowed.  
• I have not tested for dates before 1/1/2000.  Given that the 4-digit year worked here, that is the direction I would try for old dates.
• I believe the closing asterisk is also required.
• I know this works in US QW2017 and did in prior versions as well.  No idea about CAN, or Mac versions.   

Happy importing. 

[Edited . to , in example per JIm's later comment.]

https://community.quicken.com/discussion/7542737/faq-importing-security-prices-including-hi-lo-vol/p1?new=1

DiogCuba

> @q_lurker said:
> But I did finally uncover the secret through the usenet forum feeds; a 5/4/2005 post from Marjohn.  The magical sequence is
>
> Symbol, Close, ---, Date, ---, Hi, Lo, Vol/100, *
>
> CTXS, 3.00, ---, 1/1/2016, ---, 4.00, 2.00, 500, * 
> CTXS, 8.12. ---, 1/2/2016, ---, 9.34, 7.56, 1000, ** I do not know that three dashes are required vs 1, 2, 4, or none, but it worked that way.

q_lurker I tested this in my Quicken and it is not working. Here is my data if you are willing to test it on your own machine:

PRIO3, 29.33, ---, 12/13/2019, ---, 29.85, 28.13, 21764, *

Thanks for the input! :'(

DiogCuba

> @ps56k said:
> (Quote)
> What "quote" box - where ?

When you are on the on Security Detail View (Once you click the security ticker), you will see 3 boxes of information about that security:

1. Secutiry Details
2. Holdings
3. Qhote

On the quote box you will see a bunch of calculations that can be done and in my securities they are all listed as "N/A"

Ps56k2

ok... FYI ... my stocks show a fully populated set of stats,

but my mutual funds are missing some of that same info.... as a comparison.
I'll try a test to see what things look like with most of the "activity" numbers missing.

Here are two example of the Security Detail Quote screen you are referencing...

Ps56k2

Here is what things look like for a manual entry with only - Date & Closing Price -
Don't know where the Avg Vol and Market Cap are coming from - as they don't exist

Jim_Harman

The example data for CTSX worked for me with the following observations

-- in the second line change the dot to a comma after the first price
-- need a carriage return and linefeed after the last line or it does not import
-- there must be an existing security with the ticker CTSX for the import to work properly.


@DiogCuba maybe the second item is what made your data not import. The other numbers in the Quote section are all NA because they come from the Quote feed.

DiogCuba

> @ps56k said:
> Here is what things look like for a manual entry with only - Date & Closing Price -
> Don't know where the Avg Vol and Market Cap are coming from - as they don't exist
> (Image)

THanks for the input.

DiogCuba

> @Jim_Harman said:
> The example data for CTSX worked for me with the following observations
>
> -- in the second line change the dot to a comma after the first price
> -- need a carriage return and linefeed after the last line or it does not import
> -- there must be an existing security with the ticker CTSX for the import to work properly.
> (Image)
>
>
> @DiogCuba maybe the second item is what made your data not import. The other numbers in the Quote section are all NA because they come from the Quote feed.

@Jim_Harman @q_lurker thanks for the help! It is working now. I will get to work on the carriage return via Excel but it won´t take much.

Jim_Harman

You can put the CSV data in a text editor like Notepad and fix it there.

DiogCuba

> @Jim_Harman said:
> You can put the CSV data in a text editor like Notepad and fix it there.

I have just noticed that it is necessary to add a line on the very first line, right after the header.

Just have finished adding 1900+ historic prices and volumes in less than 10min. The "hard" work was mostly done to collect the data and paste it to the correct columns, then replacing some commas to points and dividing the volume by 100.

Again, thanks a lot for you time to help me!

q_lurker

@DiogCuba:  It should be clear to you now that much of the missing data is NOT filled in by including those additional 3 pieces of data per day (Hi, Lo, and Volume).  So the data can be read in as part of the csv price import in lieu of manually typing in that information for each date, and that can make the price history graph of the security detail a little more complete, but all those other pieces of data (P/E, 52-wk Hi and Lo, market cap, etc.) come only from downloads from Quicken's US and Canadian exchange sources for securities traded on applicable exchanges.  

Glad you were able to import the data you did.  

smayer97

For reference for formatting, you can read this article:

https://www.quicken.com/support/how-import-historical-security-data-quicken