How to download data?

Discuss your favourite picks, broker, and trading or investment style.
Post Reply
User avatar
Bylo Selhi
Veteran Contributor
Veteran Contributor
Posts: 29494
Joined: 16 Feb 2005 10:36
Location: Waterloo, ON
Contact:

Re: How to download data?

Post by Bylo Selhi »

Arby wrote: 16 Jun 2017 14:45 I still can't download the stock prices with a direct call. I'm running LibreOffice version 5.2,7, but the SMF extension webpage says it is compatible with LibreOffice versions 4.0 to 4.4. I'm wondering if that is my problem. Shakes, what version of LibreOffice are you running?
See I just updated to 5.2.7. I've been using the extension GETYAHOO. - Ask LibreOffice. Despite what the title suggests, this is a Yahoo issue, not a LibreOffice issue.

For the solution see dottore's 02Jun17 post:
I have the same problem. IMHO Yahoo changed something and the original URL no longer works. One must find the file yahoo.py which going to be somewhere in the hierarchy of where the extension is installed. On a mac, it is down in ~/Library/Application Support. I don't know where it is on a PC. Once you find the file, you need to use a (dumb) text editor to change http://finance... to http://download.finance...
In Windows this file will be in something like C:\Users\<username>\AppData\Roaming\LibreOffice\4\user\uno_packages\cache\uno_packages\...
Sedulously eschew obfuscatory hyperverbosity and prolixity.
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 »

My SMF version works correctly on LO 5.3 with no mods.
Sic transit gloria mundi. Tuesday is usually worse. - Robert A. Heinlein, Starman Jones
User avatar
Arby
Veteran Contributor
Veteran Contributor
Posts: 3125
Joined: 20 Feb 2005 19:23
Location: Ottawa, ON

Re: How to download data?

Post by Arby »

Bylo Selhi wrote: 18 Jun 2017 10:01
For the solution see dottore's 02Jun17 post...
I found and modified the file yahoo.py, and the fix worked :thumbsup:
Thanks Bylo (and Shakes).
User avatar
adrian2
Veteran Contributor
Veteran Contributor
Posts: 13333
Joined: 19 Feb 2005 08:42
Location: Greater Toronto Area

Re: How to download data?

Post by adrian2 »

Bylo Selhi wrote: 18 Jun 2017 10:01 ...
Welcome back! :beer:

You've been missed!
Imagefiniki, the Canadian financial wiki
“It doesn't matter how beautiful your theory is, it doesn't matter how smart you are. If it doesn't agree with experiment, it's wrong.” [Richard P. Feynman, Nobel prize winner]
User avatar
kcowan
Veteran Contributor
Veteran Contributor
Posts: 16033
Joined: 18 Apr 2006 20:33
Location: Pacific latitude 20/49

Re: How to download data?

Post by kcowan »

:thumbsup:
adrian2 wrote: 18 Jun 2017 15:48
Bylo Selhi wrote: 18 Jun 2017 10:01 ...
Welcome back! :beer:

You've been missed!
:thumbsup:
For the fun of it...Keith
User avatar
Bylo Selhi
Veteran Contributor
Veteran Contributor
Posts: 29494
Joined: 16 Feb 2005 10:36
Location: Waterloo, ON
Contact:

Re: How to download data?

Post by Bylo Selhi »

It turns out a LibreOffice extension isn't even necessary.

See [Solved] Stock Market Functions LO Calc Extension and in particular this post:
To get stock quotes in Libre Office without using an extension, you can use the following...

Enter into a cell:

=NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quotes?s="&U4&"&f=l1");".")

where U4 is the cell that contains a stock symbol

Alternatively, the stock symbol can be put directly in place of the cell reference surrounded by double quotes (eg &"BGM.V"&) instead of (&U4&)

The l1 (Last Trade (Price Only)) in the statement can be replaced with the following, depending on what information you want displayed. (eg: instead of f=l1 make it f=c1 (change))...
Also mentioned on Bogleheads in GETYAHOO broken? [Spreadsheet extension]
Sedulously eschew obfuscatory hyperverbosity and prolixity.
User avatar
big easy
Veteran Contributor
Veteran Contributor
Posts: 1737
Joined: 27 Jul 2006 11:57
Location: Vancouver BC

Re: How to download data?

Post by big easy »

Bylo Selhi wrote: 19 Jun 2017 10:42 It turns out a LibreOffice extension isn't even necessary.

See [Solved] Stock Market Functions LO Calc Extension and in particular this post:
To get stock quotes in Libre Office without using an extension, you can use the following...

Enter into a cell:

=NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quotes?s="&U4&"&f=l1");".")

where U4 is the cell that contains a stock symbol

Alternatively, the stock symbol can be put directly in place of the cell reference surrounded by double quotes (eg &"BGM.V"&) instead of (&U4&)

The l1 (Last Trade (Price Only)) in the statement can be replaced with the following, depending on what information you want displayed. (eg: instead of f=l1 make it f=c1 (change))...
Also mentioned on Bogleheads in GETYAHOO broken? [Spreadsheet extension]
In excel its:
=NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quo ... &U4&"&f=l1"))
the quote symbol for VCN is VCN.to
"Everybody has a plan until they get punched in the face." Mike Tyson
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 »

My Yahoo download call isn't working right now - Error 999. Anyone else have this problem?
Sic transit gloria mundi. Tuesday is usually worse. - Robert A. Heinlein, Starman Jones
User avatar
Peculiar_Investor
Administrator
Administrator
Posts: 13271
Joined: 01 Mar 2005 14:52
Location: Calgary
Contact:

Re: How to download data?

Post by Peculiar_Investor »

Shakespeare wrote: 01 Nov 2017 19:29 My Yahoo download call isn't working right now - Error 999. Anyone else have this problem?
Yes. I'm downloading quotes via the RCHGetYahooQuotes() function in the SMF Add-in tool and it stopped working around 1:30 pm MDT today.
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
Shakespeare
Veteran Contributor
Veteran Contributor
Posts: 23396
Joined: 15 Feb 2005 23:25
Location: Calgary, AB

Re: How to download data?

Post by Shakespeare »

I can still download a portfolio from Yahoo Finance via the Export tool, although the format is slightly different - in particular dates are 2017/11/01.
Sic transit gloria mundi. Tuesday is usually worse. - Robert A. Heinlein, Starman Jones
User avatar
big easy
Veteran Contributor
Veteran Contributor
Posts: 1737
Joined: 27 Jul 2006 11:57
Location: Vancouver BC

Re: How to download data?

Post by big easy »

Download to excel not working right now - cell shows #VALUE! error
"Everybody has a plan until they get punched in the face." Mike Tyson
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 »

The download function from the portfolio page now uses a url of the form

Code: Select all

Export https://query1.finance.yahoo.com/v6/finance/portfolio/download?userId=somecharactersforme&pfId=pf_1
So it is now https and the url has changed. Investigation continues.


Added: Some relevant links

Yahoo Finance API changes (2017) - Stack Overflow
On June 8, 2017 Yahoo will be sold to Verizon. So Yahoo is now removing services which can not generate ad revenue. Access to the Yahoo Finance API by third party software has been eliminated.
It may be that the freebee is gone forever but I can still use my Yahoo Finance portfolio to export and then read the csv file.
Sic transit gloria mundi. Tuesday is usually worse. - Robert A. Heinlein, Starman Jones
User avatar
Peculiar_Investor
Administrator
Administrator
Posts: 13271
Joined: 01 Mar 2005 14:52
Location: Calgary
Contact:

Re: How to download data?

Post by Peculiar_Investor »

Others are noticing as well, see https://groups.yahoo.com/neo/groups/smf ... ages/33396. The author of the SMF Add-in that I linked above has also posted about it, SMF Add-in Announcements: Alert -- RCHGetYahooQuotes() problems.
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
Shakespeare
Veteran Contributor
Veteran Contributor
Posts: 23396
Joined: 15 Feb 2005 23:25
Location: Calgary, AB

Re: How to download data?

Post by Shakespeare »

I don't think the Nov. 1 date is coincidence; I suspect it has been shut down.

Workaround:

1. Register at Yahoo Finance.
2. Build a portfolio.
3. Export as quotes.csv .
4. Import on a new page of your spreadsheet and use the lookup functions to get your data.
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 »

You can also export or print a csv file from Globeinvestor. You get this:

Code: Select all

Stocks and ETFs																																																																	
Company	Ticker	Stock Exchange	Currency	Latest Price	Price Last Updated	Chg Today	Chg Today %	Volume	Latest News	Headlines Last Updated	5d Chg	5d Chg %	1m Chg	1m Chg %	YTD Chg	YTD Chg %	1y Chg	1y Chg %	1-Day Low	1-Day High	5-Day Low	5-Day High	52-Week Low	52-Week High	Above/below 200d	Above/below 50d	Beta	Book Value per Share	Cash Flow per Share	Forward PE Ratio	Market Capitalization	PE Ratio	PEG Ratio	Price-to-Book Ratio	Price-to-Cash Flow Ratio	Price-to-Sales Ratio	Dividend Growth 1y	Dividend Growth 5y	Dividend Payout Ratio	Dividend Per Share	Dividend Ex Date	Dividend Pay Date	Total Rtn 1y	Total Rtn 5y	3y Return	5y Return	10y Return	Yield	Earnings	Profit Growth 1y	Profit Growth Next Qtr	Profit Surprise Last Qtr	EPS	Operating Margin	Rtn on Equity	Sales	Sales Growth 1y	Sales Growth Next Qtr	Sales Per Share	Sales Surprise Last Qtr	Analyst Recommendation	Target Price	Debt-to-Cash Flow	Debt-to-EBITDA	Debt-to-Equity
BCE Inc.	BCE	T	C$	59.48	Nov 01 2017, 04:00 PM EDT	-0.09	-0.15%	990,889	These stocks are set to see big moves Thursday	Nov 01 2017, 05:42 PM EDT	0.17	0.29%	1.02	1.74%	1.45	2.50%	-0.8	-1.33%	59.28	59.85	59.28	59.98	56.8	63	0.46%	1.09%	0.08	$16.74	$8.62	17.14	$53,552,844,052	18.36	5.11	3.55	6.9	2.36	5.13%	4.80%	33.28%	$2.87	Sep 14 2017, 12:00 AM EDT	Oct 15 2017, 12:00 AM EDT	3.38%	10.88%	5.92%	6.63%	3.80%	4.83%	$2,978,000,000	2.53%	-7.25%	1.15%	$3.24	39.81	18.85%	$22,192,000,000	1.01%	5.52%	$25.24	1.39%	Hold	$61.83	3.08	2.65	1.23
Bank of Montreal	BMO	T	C$	98.67	Nov 01 2017, 04:00 PM EDT	-0.16	-0.16%	1,039,141	New Bank of Montreal CEO promotes retail banking head on first day	Nov 01 2017, 06:20 PM EDT	-0.85	-0.85%	4.24	4.49%	2.1	2.17%	13.73	16.16%	98.61	99.18	98.51	100.32	83.58	104.15	2.47%	4.68%	0.88	$59.65	-$11.26	11.82	$64,005,783,485	12.09	2.16	1.65	--	2.35	4.65%	4.56%	-31.97%	$3.60	Oct 31 2017, 12:00 AM EDT	Nov 28 2017, 12:00 AM EST	20.36%	14.19%	6.48%	10.76%	4.79%	3.65%	$5,465,000,000	20.89%	-4.52%	2.01%	$8.16	26.5	13.36%	$27,271,000,000	7.17%	-18.35%	$42.01	4.50%	Hold	$98.90	-0.69	0.7	0.12
Bank of Nova Scotia	BNS	T	C$	83.09	Nov 01 2017, 04:00 PM EDT	-0.19	-0.23%	1,844,276	Why I love down-on-their-luck bank stocks	Nov 01 2017, 05:18 PM EDT	0.84	1.02%	2.89	3.60%	8.33	11.14%	11.06	15.35%	83.04	83.63	82.35	83.85	69.2	83.85	6.22%	4.87%	1.39	$45.83	-$1.99	11.89	$99,541,820,000	12.84	1.62	1.81	--	2.81	6.76%	6.75%	-159.06%	$3.16	Oct 02 2017, 12:00 AM EDT	Oct 27 2017, 12:00 AM EDT	19.59%	12.08%	6.38%	8.74%	4.57%	3.80%	$7,929,000,000	13.71%	5.54%	1.82%	$6.47	30.8	13.97%	$35,616,000,000	6.45%	-19.92%	$29.55	-1.21%	Strong Buy	$86.00	--	--	--
Brookfield Infrastructure LP	BIP.UN	T	C$	54.98	Nov 01 2017, 04:00 PM EDT	0.4	0.73%	175,674	Buyer beware: The messy truth about investing in foreign securities	Oct 18 2017, 12:03 PM EDT	0.43	0.79%	1.16	2.16%	10.06	22.40%	10.42	23.38%	54.34	54.98	54.34	54.98	41.73	56.61	5.97%	2.06%	0.89	$46.04	$ --	11.4	$14,366,274,000	47.81	1.21	0.93	--	4.44	10.59%	11.71%	--	$2.24	Aug 29 2017, 12:00 AM EDT	Sep 29 2017, 12:00 AM EDT	28.29%	23.04%	21.69%	19.39%	--	4.08%	$ --	24.53%	412.50%	--	$1.15	32.56	--	$1,918,000,000	238.10%	75.10%	$9.60	21.61%	Strong Buy	$55.10	--	10.8	0.8
CIBC	CM	T	C$	113.56	Nov 01 2017, 04:00 PM EDT	--	--	815,784	Wednesday&rsquo;s analyst upgrades and downgrades	Nov 01 2017, 08:31 AM EDT	-0.14	-0.12%	4.39	4.02%	4	3.65%	14.31	14.42%	113.4	114.11	112.6	114.29	97.52	120.83	2.80%	4.04%	1.07	$64.73	$33.39	10.24	$49,518,809,279	10.3	3.61	1.75	3.4	2.29	7.44%	6.70%	15.57%	$5.20	Sep 27 2017, 12:00 AM EDT	Oct 27 2017, 12:00 AM EDT	19.54%	11.36%	3.34%	7.50%	1.47%	4.58%	$4,467,000,000	6.68%	0.96%	5.32%	$11.02	30.28	16.68%	$19,952,000,000	6.93%	-10.42%	$49.62	2.34%	Hold	$114.59	0.24	0.53	0.11
Canadian Utilities	CU	T	C$	38.41	Nov 01 2017, 04:00 PM EDT	-0.55	-1.41%	192,069	Canadian Utilities Reports Third Quarter 2017 Earnings	Oct 26 2017, 07:46 AM EDT	-0.98	-2.49%	-0.34	-0.88%	2.22	6.13%	-0.16	-0.41%	38.35	39	38.35	39.19	34.83	42.44	-1.81%	-1.09%	0.19	$18.62	$7.15	16.63	$10,398,431,866	18.83	--	2.06	5.37	2.66	10.00%	10.07%	19.99%	$1.43	Nov 08 2017, 12:00 AM EST	Dec 01 2017, 12:00 AM EST	3.21%	5.70%	-0.47%	2.76%	3.77%	3.72%	$615,000,000	35.10%	--	-21.05%	$2.04	44.78	12.23%	$3,884,000,000	18.75%	--	$14.44	--	Hold	$45.00	4.6	5	1.76
Emera Inc.	EMA	T	C$	48.14	Nov 01 2017, 04:00 PM EDT	-0.46	-0.95%	429,901	Wednesday&rsquo;s analyst upgrades and downgrades	Oct 25 2017, 09:19 AM EDT	-0.62	-1.27%	0.88	1.86%	2.75	6.06%	1.57	3.37%	47.99	48.78	47.99	49.37	43.76	49.37	2.21%	0.98%	-0.18	$29.42	$6.35	15.84	$10,213,382,400	27.04	2.59	1.64	7.58	1.58	8.13%	10.05%	35.61%	$2.26	Oct 31 2017, 12:00 AM EDT	Nov 15 2017, 12:00 AM EST	7.95%	10.27%	8.31%	6.67%	9.22%	4.69%	$416,000,000	-45.06%	1.37%	-8.33%	$1.78	31.59	5.74%	$6,226,000,000	63.51%	11.46%	$30.47	--	Hold	$52.69	12.07	7.79	2.25
Enbridge Inc.	ENB	T	C$	49.4	Nov 01 2017, 04:00 PM EDT	-0.18	-0.36%	2,266,136	These stocks are set to see big moves Thursday	Nov 01 2017, 05:42 PM EDT	0.77	1.58%	-2.72	-5.22%	-7.1	-12.57%	-8.06	-14.03%	49.3	49.97	48.52	49.97	48.45	58.56	-7.21%	-2.26%	0.63	$39.75	$5.65	20.33	$81,263,000,000	35.8	2.38	1.24	8.75	1.39	15.09%	16.64%	43.22%	$2.44	Aug 11 2017, 12:00 AM EDT	Sep 01 2017, 12:00 AM EDT	-9.97%	7.81%	-2.52%	4.36%	9.20%	4.94%	$2,132,000,000	-3.50%	-4.26%	-10.87%	$1.38	13.92	2.78%	$40,088,000,000	-8.28%	27.84%	$35.58	-8.06%	Buy	$59.97	9.97	11.48	0.92
Enbridge Income Fund Holdings	ENF	T	C$	30.35	Nov 01 2017, 04:00 PM EDT	0.28	0.93%	318,036	Enbridge Income Fund Holdings Inc. Announces Monthly Dividend	Oct 16 2017, 05:15 PM EDT	0.96	3.27%	-1.79	-5.57%	-4.42	-12.71%	-3.36	-9.97%	29.98	30.36	29.03	30.36	29.03	36.31	-7.40%	-2.77%	0.39	$31.50	$2.18	12.75	$4,454,664,347	14.12	--	0.96	13.91	--	9.86%	10.65%	93.97%	$2.05	Oct 30 2017, 12:00 AM EDT	Nov 15 2017, 12:00 AM EST	-3.97%	10.39%	0.72%	5.35%	10.77%	6.75%	$277,000,000	2.38%	0.63%	-3.70%	$2.15	--	6.42%	$ --	--	--	$ --	--	Hold	$36.00	--	--	--
Fortis Inc.	FTS	T	C$	47.25	Nov 01 2017, 04:00 PM EDT	-0.26	-0.55%	701,983	Advisory: Fortis Inc. to Hold Teleconference on November 3 to Discuss Third Quarter 2017 Results	Oct 25 2017, 10:24 AM EDT	0.4	0.85%	2.47	5.52%	5.79	13.97%	3.73	8.57%	47.05	47.67	46.87	47.78	39.58	47.78	6.39%	3.61%	-0.13	$37.14	$6.25	18.31	$19,745,066,250	20.72	3.46	1.27	7.56	2.26	6.25%	7.21%	27.19%	$1.70	Nov 17 2017, 12:00 AM EST	Dec 01 2017, 12:00 AM EST	12.25%	9.84%	8.77%	6.87%	5.59%	3.60%	$938,000,000	15.74%	7.41%	10.91%	$2.28	39.83	5.59%	$7,893,000,000	-9.91%	39.85%	$20.95	-5.62%	Buy	$49.40	9.36	6.96	1.29
RioCan Real Estate Investment	REI.UN	T	C$	24.43	Nov 01 2017, 04:00 PM EDT	-0.04	-0.16%	407,845	RioCan Real Estate Investment Trust Announces Future Redevelopment of Centre RioCan Kirkland	Oct 26 2017, 04:00 PM EDT	-0.13	-0.53%	0.5	2.09%	-2.2	-8.26%	-1.44	-5.57%	24.41	24.58	24.31	24.77	23.46	27.25	-2.77%	1.16%	0.57	$24.35	$1.47	13.13	$7,992,714,240	10.96	--	1	16.57	7.4	--	0.43%	95.62%	$1.41	Oct 30 2017, 12:00 AM EDT	Nov 07 2017, 12:00 AM EST	-0.12%	2.87%	-2.74%	-2.21%	0.72%	5.77%	$733,722,000	95.61%	12.50%	9.76%	$2.23	49.24	9.21%	$1,078,696,000	1.38%	--	$3.30	--	Strong Buy	$27.00	12.3	9.07	0.74
Royal Bank of Canada	RY	T	C$	100.76	Nov 01 2017, 04:00 PM EDT	-0.11	-0.11%	1,366,165	Wednesday&rsquo;s analyst upgrades and downgrades	Nov 01 2017, 08:31 AM EDT	0.5	0.50%	4.22	4.37%	9.89	10.88%	17.54	21.08%	100.7	101.13	100.5	101.63	81.82	102.15	5.89%	5.29%	1.09	$45.34	$9.26	12.64	$147,011,377,137	13.69	--	2.22	10.88	3.08	9.64%	8.69%	39.32%	$3.64	Oct 25 2017, 12:00 AM EDT	Nov 24 2017, 12:00 AM EST	25.26%	15.02%	7.99%	11.84%	6.30%	3.61%	$11,132,000,000	6.98%	12.13%	2.16%	$7.36	31.27	16.33%	$48,110,000,000	7.23%	-15.00%	$32.66	7.53%	Buy	$98.96	0.67	0.61	0.13
TD Bank	TD	T	C$	73.16	Nov 01 2017, 04:00 PM EDT	-0.18	-0.25%	2,304,722	Wednesday&rsquo;s analyst upgrades and downgrades	Nov 01 2017, 08:31 AM EDT	1.02	1.41%	2.91	4.14%	6.94	10.48%	12.38	20.37%	73.07	73.69	72.11	73.69	59.6	73.59	9.84%	6.09%	0.8	$37.18	$8.37	12.34	$135,243,576,000	13.83	1.58	1.97	8.74	3.09	9.09%	9.28%	28.67%	$2.40	Oct 05 2017, 12:00 AM EDT	Oct 31 2017, 12:00 AM EDT	24.23%	15.26%	9.67%	12.35%	7.67%	3.28%	$9,993,000,000	19.14%	14.96%	11.03%	$5.29	28.63	13.95%	$43,857,000,000	9.97%	-14.44%	$23.67	6.48%	Buy	$72.71	0.63	0.77	0.13
TELUS Corp.	T	T	C$	46.88	Nov 01 2017, 04:00 PM EDT	0.16	0.34%	1,424,338	Telus keeps acquisition fires burning with Xavient deal	Oct 30 2017, 08:12 AM EDT	-0.22	-0.47%	2	4.46%	4.13	9.66%	3.58	8.27%	46.54	46.99	46.54	47.8	40.97	47.8	5.17%	4.35%	0.58	$14.18	$6.06	16.39	$27,799,840,000	22.32	2.19	3.31	7.73	2.14	7.07%	10.06%	32.49%	$1.97	Sep 07 2017, 12:00 AM EDT	Oct 02 2017, 12:00 AM EDT	12.76%	11.09%	5.09%	7.58%	5.18%	4.20%	$1,241,000,000	-12.13%	4.62%	-5.56%	$2.10	31.05	14.99%	$12,957,000,000	4.16%	4.89%	$21.91	0.99%	Buy	$47.91	3.8	3.37	1.62
TransCanada Corp.	TRP	T	C$	61.75	Nov 01 2017, 04:00 PM EDT	0.5	0.82%	1,406,064	New Service Connects More Western Canadian Natural Gas to Eastern Markets in Time for Winter Season	Nov 01 2017, 04:35 PM EDT	2.03	3.40%	0.08	0.13%	1.21	2.00%	1.16	1.91%	61.3	61.9	59.53	61.9	57.36	65.24	-1.30%	-0.59%	0.61	$25.69	$6.42	18.43	$53,784,250,000	53.7	2.5	2.4	9.61	3.74	10.62%	7.27%	38.92%	$2.50	Sep 28 2017, 12:00 AM EDT	Oct 31 2017, 12:00 AM EDT	5.94%	9.77%	3.59%	6.40%	4.40%	4.05%	$1,170,000,000	-156.93%	-1.79%	13.43%	$1.15	35.97	4.62%	$13,859,000,000	5.51%	-32.34%	$16.52	--	Buy	$70.71	8.02	8.16	1.64
Vangrd Total World E.T.F.	VT	N	US$	72.37	Nov 01 2017, 04:00 PM EDT	0.16	0.22%	865,252		--	0.7	0.98%	1.65	2.33%	11.37	18.64%	13.08	22.06%	72.3	72.64	71.64	72.64	58.4	72.29	7.20%	2.26%	0.99	$ --	$ --	--	$ --	--	--	--	--	--	13.83%	--	--	$1.42	Sep 27 2017, 12:00 AM EDT	Oct 02 2017, 12:00 AM EDT	24.64%	10.43%	5.89%	8.36%	--	1.96%	$ --	--	--	--	$ --	--	--	$ --	--	--	$ --	--	--	$ --	--	--	--
Vanguard Cdn Short-Term Bond	VSB	T	C$	23.96	Nov 01 2017, 03:59 PM EDT	-0.01	-0.04%	19,584	Vanguard announces cash distributions for the Vanguard ETFs (VAB, VSB, VSC, VLB, VSG, VCB, VGV, VRE and VDY)	Oct 12 2017, 03:18 PM EDT	0.05	0.21%	0.08	0.34%	-0.45	-1.84%	-0.65	-2.64%	23.95	23.97	23.9	23.97	23.79	24.65	-0.99%	0.20%	0.02	$ --	$ --	--	$ --	--	--	--	--	--	-31.55%	-3.27%	--	$0.58	Oct 20 2017, 12:00 AM EDT	Oct 30 2017, 12:00 AM EDT	-0.27%	1.54%	-1.00%	-0.81%	--	2.42%	$ --	--	--	--	$ --	--	--	$ --	--	--	$ --	--	--	$ --	--	--	--
Currencies, Indexes and Commodities																																																																	
Company	Ticker	Stock Exchange	Latest Price	Price Last Updated	Chg Today	Chg Today %	Volume	52-Week Low	52-Week High	Latest News	Headlines Last Updated																																																						
Canadian Dollar/U.S. Dollar	CADUSD	FX	0.798	Oct 06 2017, 05:00 PM EDT	--	--	--	0.72	0.83	At the open: TSX rises to new high as energy stocks rally	Nov 01 2017, 09:50 AM EDT																																																						
There's a lot of extra crap, but the first part is

Code: Select all

Stocks and ETFs					
Company	Ticker	Stock Exchange	Currency	Latest Price	Price Last Updated
BCE Inc.	BCE	T	C$	59.48	Nov 01 2017, 04:00 PM EDT
Bank of Montreal	BMO	T	C$	98.67	Nov 01 2017, 04:00 PM EDT
Bank of Nova Scotia	BNS	T	C$	83.09	Nov 01 2017, 04:00 PM EDT
Brookfield Infrastructure LP	BIP.UN	T	C$	54.98	Nov 01 2017, 04:00 PM EDT
CIBC	CM	T	C$	113.56	Nov 01 2017, 04:00 PM EDT
Canadian Utilities	CU	T	C$	38.41	Nov 01 2017, 04:00 PM EDT
Emera Inc.	EMA	T	C$	48.14	Nov 01 2017, 04:00 PM EDT
Enbridge Inc.	ENB	T	C$	49.4	Nov 01 2017, 04:00 PM EDT
Enbridge Income Fund Holdings	ENF	T	C$	30.35	Nov 01 2017, 04:00 PM EDT
Fortis Inc.	FTS	T	C$	47.25	Nov 01 2017, 04:00 PM EDT
RioCan Real Estate Investment	REI.UN	T	C$	24.43	Nov 01 2017, 04:00 PM EDT
Royal Bank of Canada	RY	T	C$	100.76	Nov 01 2017, 04:00 PM EDT
TD Bank	TD	T	C$	73.16	Nov 01 2017, 04:00 PM EDT
TELUS Corp.	T	T	C$	46.88	Nov 01 2017, 04:00 PM EDT
TransCanada Corp.	TRP	T	C$	61.75	Nov 01 2017, 04:00 PM EDT
Vangrd Total World E.T.F.	VT	N	US$	72.37	Nov 01 2017, 04:00 PM EDT
Vanguard Cdn Short-Term Bond	VSB	T	C$	23.96	Nov 01 2017, 03:59 PM EDT
Currencies, Indexes and Commodities					
Company	Ticker	Stock Exchange	Latest Price	Price Last Updated	Chg Today
Canadian Dollar/U.S. Dollar	CADUSD	FX	0.798	Oct 06 2017, 05:00 PM EDT	--
© Copyright 2017 The Globe and Mail Inc. All Rights Reserved.					
Sic transit gloria mundi. Tuesday is usually worse. - Robert A. Heinlein, Starman Jones
DenisD
Veteran Contributor
Veteran Contributor
Posts: 4081
Joined: 19 Feb 2005 01:24
Location: Calgary

Re: How to download data?

Post by DenisD »

GlobeInvestor watchlist export of fund prices is a good way to get NAVs of Canadian domiciled ETFs. Except for VCN for some reason. But sometimes you have to wait until the next day to get the current day's value. I prefer NAV over the market price for ETFs.
User avatar
Peculiar_Investor
Administrator
Administrator
Posts: 13271
Joined: 01 Mar 2005 14:52
Location: Calgary
Contact:

Re: How to download data?

Post by Peculiar_Investor »

There are some clues/workarounds to the Yahoo! download problem in SMF Add-in Announcements: New ZIP File -- Version 2017.10.31.

From what I can gather from some quick testing using his supplied smfGetYahooPortfolioView-Example.xls file, the
Randy Harmelink wrote:update for the smfGetYahooPortfolioView() function, which can be a workaround until Yahoo fixes whatever problem they are having with current quotes.
does address the issue. Now I've just got to figure out how to re-work my spreadsheet to use this updated function.
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
Shakespeare
Veteran Contributor
Veteran Contributor
Posts: 23396
Joined: 15 Feb 2005 23:25
Location: Calgary, AB

Re: How to download data?

Post by Shakespeare »

Well, a new error message from the finance API:
It has come to our attention that this service is being used in violation of the Yahoo Terms of Service. As such, the service is being discontinued. For all future markets and equities data research, please refer to finance.yahoo.com.
So much for that. :?
Sic transit gloria mundi. Tuesday is usually worse. - Robert A. Heinlein, Starman Jones
User avatar
big easy
Veteran Contributor
Veteran Contributor
Posts: 1737
Joined: 27 Jul 2006 11:57
Location: Vancouver BC

Re: How to download data?

Post by big easy »

I guess i'll have to go back to Google Sheets using the Googlefinance() command and cut and paste the results into excel. :(

Too bad excel doesn't have a Googlefinance() function.
"Everybody has a plan until they get punched in the face." Mike Tyson
User avatar
Peculiar_Investor
Administrator
Administrator
Posts: 13271
Joined: 01 Mar 2005 14:52
Location: Calgary
Contact:

Re: How to download data?

Post by Peculiar_Investor »

Peculiar_Investor wrote: 02 Nov 2017 10:32 There are some clues/workarounds to the Yahoo! download problem in SMF Add-in Announcements: New ZIP File -- Version 2017.10.31.

From what I can gather from some quick testing using his supplied smfGetYahooPortfolioView-Example.xls file, the
Randy Harmelink wrote:update for the smfGetYahooPortfolioView() function, which can be a workaround until Yahoo fixes whatever problem they are having with current quotes.
does address the issue. Now I've just got to figure out how to re-work my spreadsheet to use this updated function.
A follow-up, I've been able to update my Excel spreadsheet, which uses the SMF Add-In to download Yahoo! quotes. I had to update to SMF Add-In Version 2017.11.02

I did this by following the information in the blog posts in SMF Add-in Announcements: smfGetYahooPortfolioView(). Rather than using the RCHGetYahooQuotes() function, I'm now using smfGetYahooPortfolioView() with a slightly different parameter list (still a bit of trial and error).

So it appears to me that getting Yahoo quotes into Excel isn't totally dead yet.
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
gobsmack
Contributor
Contributor
Posts: 447
Joined: 04 Sep 2015 13:16

Re: How to download data?

Post by gobsmack »

Peculiar_Investor wrote: 03 Nov 2017 09:37 So it appears to me that getting Yahoo quotes into Excel isn't totally dead yet.
Hooray! :thumbsup: Thanks for the link

Edit: It works for me on libreoffice
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 »

You realize, of course, that this means they will probably kill the portfolio view? :shock:
Sic transit gloria mundi. Tuesday is usually worse. - Robert A. Heinlein, Starman Jones
User avatar
Arby
Veteran Contributor
Veteran Contributor
Posts: 3125
Joined: 20 Feb 2005 19:23
Location: Ottawa, ON

Re: How to download data?

Post by Arby »

gobsmack wrote: 03 Nov 2017 09:44
Peculiar_Investor wrote: 03 Nov 2017 09:37 So it appears to me that getting Yahoo quotes into Excel isn't totally dead yet.
Hooray! :thumbsup: Thanks for the link

Edit: It works for me on libreoffice
Can you explain how to incorporate the add-in for Libre Office?
gobsmack
Contributor
Contributor
Posts: 447
Joined: 04 Sep 2015 13:16

Re: How to download data?

Post by gobsmack »

Arby wrote: 03 Nov 2017 14:20 Can you explain how to incorporate the add-in for Libre Office?
After downloading the zip file, I clicked on 'Tools'->'Extension Manager'. In there, I disabled the old SMF extension and I clicked the 'Add' button at the bottom. A window will show up allowing you to browse the directory where you downloaded the zip file to. From that window, I just double-clicked the zip file and libreoffice add it as an extension.

Hmm.. after taking a second look, I may have jumped the gun a bit. It is downloading quotes now instead of giving me an error. However, the quotes do not appear to be up to date. So it may still require some tweaking.
chufinora
Contributor
Contributor
Posts: 769
Joined: 12 Oct 2009 15:03
Location: Ottawa

Re: How to download data?

Post by chufinora »

big easy wrote: 02 Nov 2017 21:41 I guess i'll have to go back to Google Sheets using the Googlefinance() command and cut and paste the results into excel. :(

Too bad excel doesn't have a Googlefinance() function.
Why do you have to cut and paste into excel? you could download the sheet in .xslx format in Google sheets - or just use google sheets. I find it quite acceptable for nearly all of my spreadsheets (And all of my account and investment tracking sheets) and I rarely use excel sheets (Well I never use Excel any more I use LibreOffice).
The Googlefinance() functions have been pretty consistent for me for the past 5+ years
Post Reply