How can I download and update the historical index quotes from foreign markets?

sslucas88

I created an index market with the same code used in Yahoo Finance (BVSP) to try to import the BOVESPA stock market historical data, but it didn't work.

It seems that Quicken only imports data from American and Canadian stock exchanges.

Could you please provide an upgrade to make it easier to update this foreign stock exchange data?

Boatnmaniac

@sslucas88 - I suggest that you click on this link, add your product improvement request there and then vote for it:  https://community.quicken.com/categories/product-enhancements-(windows).

Other users can then see it and if they like the idea they can cast their vote for it as well.

Quicken reviews this list and will take them into consideration and possibly will put them into their plans, especially if/when they get a lot of users voting for it.

sslucas88

@Boatnmaniac - Thanks for your suggestion, I will do that and I hope this improvement will be approved as soon as possible!

I took advantage and would like to share it with you and @Sherlock. I found a way to import this file with all this information.

I'm using the following layout (image.png - attached):

Symbol, Price, Price, Date, High, High, Low, Volume, Null

The only thing is:
Date - must be a text without /
Volume - is divided by 100 (one hundred) in the attached image. I carry the volume 113.86 which is equal to 11,300 (decimals is disregarded).

Attached, you will find the image of the results (result 1.png)

And now the graphics are working. (2.png result)

Hope this helps!

Sherlock

When we're not able to download quotes from within Quicken, we may import a CSV generated using a Google sheet.   For example:



When we download the Google sheet as a CSV file, the file is formatted appropriately for import into Quicken:



To learn more about Google sheets, I suggest you review: https://www.google.com/sheets/about/

sslucas88

Hello @Sherlock!

Thanks for your suggestion, but I would like something more robust.
In this case, suggested by you, it is not possible to import information such as
daily volume, high and low price.

If you have any other suggestions on how to import this data I would be very happy.

Thanks for your support.

Boatnmaniac

@sslucas88 - I suggest that you click on this link, add your product improvement request there and then vote for it:  https://community.quicken.com/categories/product-enhancements-(windows).

Other users can then see it and if they like the idea they can cast their vote for it as well.

Quicken reviews this list and will take them into consideration and possibly will put them into their plans, especially if/when they get a lot of users voting for it.

sslucas88

@Boatnmaniac - Thanks for your suggestion, I will do that and I hope this improvement will be approved as soon as possible!

I took advantage and would like to share it with you and @Sherlock. I found a way to import this file with all this information.

I'm using the following layout (image.png - attached):

Symbol, Price, Price, Date, High, High, Low, Volume, Null

The only thing is:
Date - must be a text without /
Volume - is divided by 100 (one hundred) in the attached image. I carry the volume 113.86 which is equal to 11,300 (decimals is disregarded).

Attached, you will find the image of the results (result 1.png)

And now the graphics are working. (2.png result)

Hope this helps!

Boatnmaniac

@sslucas88 - Thanks for updating us.  I am glad you found out how to do something that will meet your needs. 

Regarding the changes to date and volume:  Are you using a macro or a formula for making those changes?

This actually might be a pretty good process for many Quicken users who hold non-US and non-Canadian securities.  I think I'll spend some time looking into it some more.  Thank you, again!

sslucas88

HI @Boatnmaniac,

I haven't created any macro at the moment, but I'm thinking of automating this in some way.

I created some spreadsheets using the Google spreadsheet, made this sharing public and then connected to Microsoft Excel to consolidate and create only one CSV file to import.

q_lurker

@sslucas88 Too bad nobody pointed you to this FAQ in a timely fashion, or that you didn't find it via whatever searching you did through this site. 
https://community.quicken.com/discussion/7542737/faq-importing-security-prices-including-hi-lo-vol

You obviously found some other source with a similar setup offered.  I'd be curious about where.  Note the limitation in the FAQ about large volumes.  I believe that still applies, though I haven't checked today or recently.

I don't know which spreadsheet features might include Hi, Lo, and Volume functions.    

Sherlock

q_lurker said:

@sslucas88 Too bad nobody pointed you to this FAQ in a timely fashion, or that you didn't find it via whatever searching you did through this site. 
https://community.quicken.com/discussion/7542737/faq-importing-security-prices-including-hi-lo-vol

You obviously found some other source with a similar setup offered.  I'd be curious about where.  Note the limitation in the FAQ about large volumes.  I believe that still applies, though I haven't checked today or recently.

I don't know which spreadsheet features might include Hi, Lo, and Volume functions.    

@q_lurker The GoogleFinance function supports Hi, Lo, and Volume: https://support.google.com/docs/answer/3093281

JimBob

Hello.

I did see the link that @q_lurker posted. It was very helpful.
I have also been playing around with what @sslucas88 posted as well

In my own playing, I have gotten the standard "Symbol,Price,Date" to import well. Well, pretty well.

I am less successful in importing volume, hi, low.

First, as the links point out, the "third" and "fifth" columns are meaningless. . .you can have any character in those columns.

When I run the import, I get a dialoug box, labeled "security detail" (or something like that) for EVERY price. The box is white with no text. I acknowledge. . .and on to the next price. This repeats for EVERY row in the CSV file. Even worse, it seems like Quicken won't import ALL the prices. It goes a certain (inconsistent) amount, and then stops. It works ok with 10-20 prices. . .with 200 prices. . . nope. Anywhere from the last 25 to 75 won't import.

I suspect there is an issue with the last "Null" column. I am creating a CSV file from an excel spreadsheet. Excel doesn't really do "null" to my knowledge. I have tried leaving off the column; doesn't work. I tried going into the CSV file with notepad and "adding" a trailing column to each row. . . that doesn't work.
So. . clearly I am not doing something correctly.

There is one other minor issue. . .when I import prices; with 10-20 rows; no issues. With ~200 prices. . . .Quicken has a pop up that says "Successfully imported 230 prices". Curious; because the file only has about 200 rows of data. Hmmm.

Any insight would be helpful.
Quicken 17R20; Win10; using excel for data manipulation and saving to CSV file.

Jim_Harman

If you have been manipulating the data in Excel, have you tried looking at the CSV file with a text editor - Notepad or similar?

Sometimes Excel's CSV files have null columns and/or rows. There should not be any extra commas at the end of a line of data or lines in the file that have just commas.

q_lurker

@JimBob I don't believe the last 'column' is a 'null column'.  Each line needs 9 fields separated by 8 commas.  The third, fifth and ninth fields need characters.  What was passed down to me was ---, ---, and an asterisk.  That is what is represented in the FAQ post.  Other characters might work.  'Blanks' aka spaces might work.  Never tried them. 

I suggest you copy the two lines in the FAQ post to a Notepad or similar file, change the ticker appropriately for your data file, and try that as a two line import. 

From there, set up an Excel (or similar) sheet with the nine columns, save that as a CSV file from Excel and import.     

JimBob

Well; Thanks for your fast responses.

After some testing, I have concluded it is not the "creation" of the CSV file that's the problem. I have tried a few things, including pasting the lines from the FAQ, and creating a CSV file from SCRATCH (i.e. create new document in notepad).

I think the fundamental issue is the pop-up that I get as the program imports EACH price. Note that the actual import DOES occur, and the data appears fine. It's just this crazy acknowledgement that comes along with each price import.

The pop up I get is shown below; pretty sure that if I can stop this pop up from happening, then I will have success.

Note: I could do this in bit size chunks, but my intent is to import about 30-40 securities every 5-15 days. That can yield several hundred prices imported at once. I can prepare a properly formatted single CSV file in a minute or two using my spreadsheet tools. I wonder if it is a size thing. . . ..

sslucas88

Hi @JimBob

I loaded the 5-year history for 15 symbols, which generated a file with more than 20,000 lines and worked well.

sslucas88

@JimBob , @q_lurker, @Jim_Harman, @Sherlock, @Boatnmaniac

I suggested the product improvement for this topic in the link below:

https://community.quicken.com/discussion/7874826/one-step-update-for-non-us-and-non-canadian-securities#latest

If you are interested on that I appreciate your vote for this improvement.

JimBob

Yup. I still have the issue.

Just uploaded about 700 prices. . . had to acknowledge 700 times.
I broke the file into 6 chuncks so I wouldn't go mad.

In the end, however, it seems to have worked ok.

-----
Huh. . . Quicken today updated my "2017 Premier" to version R20.5
Previously, I was on R17.20. The import behavior was the same today as a few weeks ago when I was running R17.20. (Since I am not paying for a subscription. . .I am very surprised the program ran an update.)

-----
Any insight appreciated