How to download data?

Discuss your favourite picks, broker, and trading or investment style.
User avatar
Shakespeare
Veteran Contributor
Veteran Contributor
Posts: 23396
Joined: 15 Feb 2005 23:25
Location: Calgary, AB

Re: How to download data?

Post by Shakespeare »

Modified my 1-2-3 spreadsheet today to create a version that reads Globeinvestor data - imports the csv file, looks up the price and dividend, calculates the date (which is in the wrong format). It works. :thumbsup:
Sic transit gloria mundi. Tuesday is usually worse. - Robert A. Heinlein, Starman Jones
Hogwild
Contributor
Contributor
Posts: 393
Joined: 18 Aug 2015 21:46

Re: How to download data?

Post by Hogwild »

O-kay. Then why would I want to bother with it if it only knows how to access data from Yahoo and Yahoo data stopped being available a while back?

Shakespeare wrote: 09 Nov 2017 17:40
No, it uses the Yahoo portfolio view. Look at the example spreadsheet.
User avatar
Shakespeare
Veteran Contributor
Veteran Contributor
Posts: 23396
Joined: 15 Feb 2005 23:25
Location: Calgary, AB

Re: How to download data?

Post by Shakespeare »

Because the yahoo portfolio view works. Download the spreadsheet.
Sic transit gloria mundi. Tuesday is usually worse. - Robert A. Heinlein, Starman Jones
User avatar
Shakespeare
Veteran Contributor
Veteran Contributor
Posts: 23396
Joined: 15 Feb 2005 23:25
Location: Calgary, AB

Re: How to download data?

Post by Shakespeare »

For those of you using Globeinvestor, CADUSX is dead. Use XDC.
Sic transit gloria mundi. Tuesday is usually worse. - Robert A. Heinlein, Starman Jones
Hogwild
Contributor
Contributor
Posts: 393
Joined: 18 Aug 2015 21:46

Re: How to download data?

Post by Hogwild »

shakespeare:

Which spreadsheet are you suggesting I download? Mention was made of several of them, none of which seem to be in Excel.

I'm also looking at the FinAnsU plugin. It looks helpful, but there has been no development activity on it in years.
User avatar
Shakespeare
Veteran Contributor
Veteran Contributor
Posts: 23396
Joined: 15 Feb 2005 23:25
Location: Calgary, AB

Re: How to download data?

Post by Shakespeare »

Sic transit gloria mundi. Tuesday is usually worse. - Robert A. Heinlein, Starman Jones
Hogwild
Contributor
Contributor
Posts: 393
Joined: 18 Aug 2015 21:46

Re: How to download data?

Post by Hogwild »

I am not clear on how to enter the right code to get the data I want. I want to fetch from Google and just get the current market price of a stock and the stock's current dividend.

I read through the documentation briefly, but I must be too slow to get it. Plugin appears to be working correctly, I think. How do I retrieve current price and current annual dividend? I can't even find an element defition for close or current price or similar in the text files.

FinAnsU looks perhaps a little simpler for Excel novices like me, no?
Hogwild
Contributor
Contributor
Posts: 393
Joined: 18 Aug 2015 21:46

Re: How to download data?

Post by Hogwild »

Hmm.....I tried getting quotes with the SMF add-in example Excel workbook. No quotes showed up. The plugin is loaded successfully.
User avatar
AltaRed
Veteran Contributor
Veteran Contributor
Posts: 33398
Joined: 05 Mar 2005 20:04
Location: Ogopogo Land

Re: How to download data?

Post by AltaRed »

Hogwild wrote: 12 Nov 2017 12:43 Hmm.....I tried getting quotes with the SMF add-in example Excel workbook. No quotes showed up. The plugin is loaded successfully.
I will gently repeat my prior suggestion. Consider Quicken as a 'fix all' solution for yourself. Sometimes it is much better for your health to actually buy the car rather than trying to build one yourself.
Imagefiniki, the Canadian financial wiki The go-to place to bolster your financial freedom
Hogwild
Contributor
Contributor
Posts: 393
Joined: 18 Aug 2015 21:46

Re: How to download data?

Post by Hogwild »

AltaRed:

While I usually take your posts very seriously, I am not interested in Quicken or any commercial software. MS Money might have been an exception, but it is no longer actively developed.

1. I want something I can customize in Excel.
2. As an ex-IT technician, I got tired of trying to fix and listening to clients complain about endless technical and other problems with Quicken.
3. I got tired of trying to represent said IT clients when Intuit would treat them like garbage. Constant upgrade fees, data corruption
and endless migration issues. Intuit staff I spoke with on the phone were rarely if ever helpful. No thank you to "Slowen".

So, if I were going to go to a full finance software, I'd use KMyMonney. It's free, powerful and doesn't require an of Intuit's typical B*llshit.
User avatar
AltaRed
Veteran Contributor
Veteran Contributor
Posts: 33398
Joined: 05 Mar 2005 20:04
Location: Ogopogo Land

Re: How to download data?

Post by AltaRed »

Fair enough...with one comment. You no doubt know Intuit doesn't own Quicken any more, albeit it doesn't mean it necessarily will get better as a standalone product, hype notwithstanding. https://www.computerworld.com/article/3 ... -days.html
Imagefiniki, the Canadian financial wiki The go-to place to bolster your financial freedom
Hogwild
Contributor
Contributor
Posts: 393
Joined: 18 Aug 2015 21:46

Re: How to download data?

Post by Hogwild »

Yes, I knew that. But from what I heard, the same people are providing frontline support and if I never have to speak with one of those people again, it won't be soon enough.

We won't even talk about Quickbooks support. :evil:
chufinora
Contributor
Contributor
Posts: 769
Joined: 12 Oct 2009 15:03
Location: Ottawa

Re: How to download data?

Post by chufinora »

I can understand why you wouldn't want to use Quicken. I gave up on Quicken when it gave up on the Mac many moons ago, I didn't find it very good for investment tracking - different strokes for different folks.

What I don't get is why you are trying so hard for an Excel solution, the Google Spreadsheets are a superior spreadsheet for investment tracking due to the rich Google Finance functions. The spreadsheet I posted up-thread gets you started, and there are plenty of other finance functions that work without all the complicated mess of pulling and parsing data fields from external sources.
pmj
Veteran Contributor
Veteran Contributor
Posts: 3412
Joined: 27 Feb 2005 18:15
Location: Ottawa

Re: How to download data?

Post by pmj »

Does Google spreadsheets provide historical stock prices? The "price history" link on the Finance pages vanished a few weeks back. Can you "fix" the errors that appear on the graphs - double-entered stock splits, missing dividends, etc?
For sure Yahoo isn't perfect - but it seems to have far fewer errors like these ...
Peter

Patrick Hutber: Improvement means deterioration
chufinora
Contributor
Contributor
Posts: 769
Joined: 12 Oct 2009 15:03
Location: Ottawa

Re: How to download data?

Post by chufinora »

pmj wrote: 12 Nov 2017 20:04 Does Google spreadsheets provide historical stock prices? The "price history" link on the Finance pages vanished a few weeks back.
Yes Google provides historical stock prices
GOOGLEFINANCE("GOOG", "price", DATE(2014,1,1), DATE(2014,12,31), "DAILY")

GOOGLEFINANCE("GOOG","price",TODAY()-30,TODAY())

from https://support.google.com/docs/answer/3093281?hl=en that also gives a list of the finance functions available
pmj wrote: 12 Nov 2017 20:04 Can you "fix" the errors that appear on the graphs - double-entered stock splits, missing dividends, etc?
For sure Yahoo isn't perfect - but it seems to have far fewer errors like these ...
Not sure what you mean, but I think you are confusing Google Finance viewing with Google spreadsheet finance functions
pmj
Veteran Contributor
Veteran Contributor
Posts: 3412
Joined: 27 Feb 2005 18:15
Location: Ottawa

Re: How to download data?

Post by pmj »

chufinora wrote: 12 Nov 2017 20:53
pmj wrote: 12 Nov 2017 20:04 Does Google spreadsheets provide historical stock prices? The "price history" link on the Finance pages vanished a few weeks back.
Yes Google provides historical stock prices
GOOGLEFINANCE("GOOG", "price", DATE(2014,1,1), DATE(2014,12,31), "DAILY")

GOOGLEFINANCE("GOOG","price",TODAY()-30,TODAY())

from https://support.google.com/docs/answer/3093281?hl=en that also gives a list of the finance functions available
pmj wrote: 12 Nov 2017 20:04 Can you "fix" the errors that appear on the graphs - double-entered stock splits, missing dividends, etc?
For sure Yahoo isn't perfect - but it seems to have far fewer errors like these ...
Not sure what you mean, but I think you are confusing Google Finance viewing with Google spreadsheet finance functions
From your link above
GOOGLEFINANCE Fetches current or historical securities information from Google Finance
... so not an unreasonable assumption.
Tested with MRU, for which Google Finance incorrectly records two splits in Feb 2015. GOOGLEFINANCE repeats that error.
I also don't see dividend payments being available in the historical data?
Peter

Patrick Hutber: Improvement means deterioration
User avatar
Peculiar_Investor
Administrator
Administrator
Posts: 13267
Joined: 01 Mar 2005 14:52
Location: Calgary
Contact:

Re: How to download data?

Post by Peculiar_Investor »

Hogwild wrote: 12 Nov 2017 12:43 Hmm.....I tried getting quotes with the SMF add-in example Excel workbook. No quotes showed up. The plugin is loaded successfully.
I was traveling and offline for the past week or so and am just catching up on this topic since I posted the original links to the SMF Add-in and their example spreadsheet. It continues to work properly for me with Excel 2010 on Windows 10. I'm currently using Version 2017.11.11 of the SMF Add-in.

What version of Excel are you using? On what OS?

Are you still having problems getting quotes using the SMF Add-in? Have you tried the smfGetYahooPortfolioView-Example.xls example file?

Have you looked over SMF Add-In --Tips and FAQS -- Installation, particularly the Special Notes section.
Imagefiniki, the Canadian financial wiki New editors wanted and welcomed, please help collaborate and improve the wiki.

Normal people… believe that if it ain’t broke, don’t fix it. Engineers believe that if it ain’t broke, it doesn’t have enough features yet. – Scott Adams
User avatar
Quebec
Veteran Contributor
Veteran Contributor
Posts: 1645
Joined: 24 Oct 2009 16:49
Location: Quebec City

Re: How to download data?

Post by Quebec »

Peculiar_Investor wrote: 16 Nov 2017 09:10 Have you tried the smfGetYahooPortfolioView-Example.xls example file?

Have you looked over SMF Add-In --Tips and FAQS -- Installation, particularly the Special Notes section.
This works, I've fixed my portfolio tracking spreadsheet, took about half an hour, thanks!
Imagefiniki, the Canadian financial wiki: a knowledge base of financial subjects written from a Canadian perspective
BRIAN5000
Veteran Contributor
Veteran Contributor
Posts: 9063
Joined: 08 Jun 2007 23:27

Re: How to download data?

Post by BRIAN5000 »

I have quotes downloading now. In the sample worksheet there are spaces between some rows I would like to get rid of the spaces. Is it a simple fix to get rid of the spaces? How do I do it? For example row 8 is blank when I paste ticker symbols into it I want NA info in that row.

NA NA -- -- -- -- -- -- -- -- -- -- -- NMS ECNQUOTE -- --

If I try to delete the row it says "can't change part of an array" there are numerous rows which show no down loaded data?

LOL, never mind!!!!!!!!
Last edited by BRIAN5000 on 17 Nov 2017 16:09, edited 1 time in total.
This information is believed to be from reliable sources but may include rumor and speculation. Accuracy is not guaranteed
User avatar
kjmcrae
Contributor
Contributor
Posts: 425
Joined: 26 Jun 2006 09:49
Location: Ontario

Re: How to download data?

Post by kjmcrae »

BRIAN5000 wrote: 17 Nov 2017 15:48 I have quotes downloading now. In the sample worksheet there are spaces between some rows I would like to get rid of the spaces. Is it a simple fix to get rid of the spaces? How do I do it? For example row 8 is blank when I paste ticker symbols into it I want NA info in that row.

NA NA -- -- -- -- -- -- -- -- -- -- -- NMS ECNQUOTE -- --

If I try to delete the row it says "can't change part of an array" there are numerous rows which show no down loaded data?
Simple? Not really, but doable.

From what I can tell, you need to delete the array (highlight its cells and delete it) and then "re-add" it again by doing this:
1) Select the cells where the array was.
2) With that array highlighted, (re-)enter the smf function you're using in the formula bar and then press <Ctrl> + <Shift> + <Enter>. (This is important -- because just pressing <Enter> alone will not work.)

May not be the best way, but it worked for me. :thumbsup:
User avatar
Peculiar_Investor
Administrator
Administrator
Posts: 13267
Joined: 01 Mar 2005 14:52
Location: Calgary
Contact:

Using the SMF Add-in example spreadsheet

Post by Peculiar_Investor »

The sample SMF Add-in Excel workbook works as follows based on my review/understanding. In the smfGetYahooPortfolioView worksheet, column B and row 2 contain the user changeable inputs. Column B is where one enters the ticker symbols that you are following. Row 2 contains the fields to be retrieved. The Field Number worksheet documents the valid field numbers, i.e. Field Number 01 returns the "Symbol", Field Number 15 returns the "Last Price".

So to change what's returned, change the columns in row 2, there is no need to change/edit the array formula.

In my own usage, my Holdings worksheet, I'm using VLOOKUP to match to the Ticker field on the smfGetYahooPortfolioView worksheet and returning the columns of interest.

Hope this helps.
Imagefiniki, the Canadian financial wiki New editors wanted and welcomed, please help collaborate and improve the wiki.

Normal people… believe that if it ain’t broke, don’t fix it. Engineers believe that if it ain’t broke, it doesn’t have enough features yet. – Scott Adams
BRIAN5000
Veteran Contributor
Veteran Contributor
Posts: 9063
Joined: 08 Jun 2007 23:27

Re: How to download data?

Post by BRIAN5000 »

The lines not returning any info we're not seeing the ticker symbol in column B as a correct symbol. Example NA changed to NA.TO and Bob's your uncle. Looks good will hold 411 stocks and lots of stock data.(columns)
This information is believed to be from reliable sources but may include rumor and speculation. Accuracy is not guaranteed
Hogwild
Contributor
Contributor
Posts: 393
Joined: 18 Aug 2015 21:46

Re: How to download data?

Post by Hogwild »

I actually did try the sample, but I didn't understand how it worked.

I think I may have finally figured out how to get data. What data provider is reliable for market price (or close, if that's not available). I couldn't find that for Google. Funny, cause isn't that the most basic thing people would want?
BRIAN5000
Veteran Contributor
Veteran Contributor
Posts: 9063
Joined: 08 Jun 2007 23:27

Re: How to download data?

Post by BRIAN5000 »

Spreadsheet seems to be working ok except how do request a reload of new/current info? On Gummies spreadsheet there was a button to click on must be something easy?
This information is believed to be from reliable sources but may include rumor and speculation. Accuracy is not guaranteed
pmj
Veteran Contributor
Veteran Contributor
Posts: 3412
Joined: 27 Feb 2005 18:15
Location: Ottawa

Re: How to download data?

Post by pmj »

BRIAN5000 wrote: 20 Nov 2017 15:28 Spreadsheet seems to be working ok except how do request a reload of new/current info? On Gummies spreadsheet there was a button to click on must be something easy?
Easiest way is just to change a cell. Doesn't have to be part of the data. That will force a reload.
Peter

Patrick Hutber: Improvement means deterioration
Post Reply