Stock/Bond/Index/Fundamental/Asset Class/Quotes - Sources

Discuss your favourite picks, broker, and trading or investment style.
User avatar
Peculiar_Investor
Administrator
Administrator
Posts: 13267
Joined: 01 Mar 2005 14:52
Location: Calgary
Contact:

Re: Stock/Bond/Index/Fundamental/Asset Class/Quotes - Source

Post by Peculiar_Investor »

It is beginning to look like Microsoft has totally decommissioned their stock quote service at http://moneycentral.msn.com/investor/ex ... quote=MSFT. :evil: On or about 12/22/14 it stopped being available and redirects to http://www.msn.com/en-us/money. AFAIK there has been no official announcement from Microsoft :twisted:

There have been many queries about the service on Technet forums - Excel IT Pro Discussions and this one MSN Central stock quotes not working indicates
After a great deal of time on the phone with Microsoft Level two support (Mr. Mir Azhar), has told me today (12/31/2014) that the stock quote service has been discontinued from the MSN server and will not be available in the future.
Talk about poor customer relations, end users having to rely on third parties to find out a service has been discontinued. :twisted:

I'm actually very disappointed in Microsoft for the way they've handled this. There are many articles on Microsoft's sites indicating how to use http://moneycentral.msn.com/investor/ex ... quotes.asp to get quotes. At the very minimum they could have used common courtesy and announced the upcoming decommissioning of this service. I'm left to wonder if they've really thought this decision through. If so, will they be updating all the references to it on Microsoft websites. Will they issue an Excel update to remove the .iqy files that come with their product and use this functionality? I'm guessing not.

Workarounds are being posted on various sites, for example Free Stock Quotes in Excel | Vertex42 indicates using GOOGLEFINANCE - Docs editors Help, but unfortunately
Google wrote:GOOGLEFINANCE is only available in English and does not support most international exchanges.
so getting TSX quotes becomes problematic.

Looks like I'm going to have to rewrite my spreadsheets to use finance.yahoo.com again.
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: Stock/Bond/Index/Fundamental/Asset Class/Quotes - Source

Post by Shakespeare »

Or G&M. That needs a lot of parsing to break out the data you want.

I'm using Yahoo Finance with a custom macro that builds the stock list then calls Firefox for the download. The code is now the same as that on Yahoo's portfolio download basic view so I can download to the tablet from a Yahoo Finance "dowload portfolio" and get the same column entries; I can then sideload the csv to the laptop. This is useful when I have internet in the lobby but not the room.

The Yahoo call sequence for this is

Code: Select all

&f=sl1d1t1c1ohgv
The full URL with the current stocklist is

Code: Select all

http://download.finance.yahoo.com/d/quotes.csv?s=ACO-X.TO,BCE.TO,BMO.TO,BNS.TO,CADUSD=X,CCO.TO,CM.TO,COS.TO,CU.TO,EMA.TO,ENB.TO,FTS.TO,RCI-B.TO,RY.TO,SAP.TO,SJR-B.TO,TD.TO,TRP.TO,T.TO,VT,ZCM.TO,ZRE.TO,^GSPTSE,XIC.TO,PKI.TO,FRU.TO,ZLB.TO&f=sl1d1t1c1ohgv
Sic transit gloria mundi. Tuesday is usually worse. - Robert A. Heinlein, Starman Jones
User avatar
LadyGeek
Veteran Contributor
Veteran Contributor
Posts: 1975
Joined: 26 Oct 2011 16:51
Location: Philadelphia, PA
Contact:

Re: Stock/Bond/Index/Fundamental/Asset Class/Quotes - Source

Post by LadyGeek »

Can you scrape the data from a website? The Bogleheads' wiki has a few GoogleFinance examples for that techqnique: Real-time tracking from a website

GoogleFinance doesn't own this techqnique, it's just what I've found as an example.
Imagefiniki, the Canadian financial wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
Peculiar_Investor
Administrator
Administrator
Posts: 13267
Joined: 01 Mar 2005 14:52
Location: Calgary
Contact:

Re: Stock/Bond/Index/Fundamental/Asset Class/Quotes - Source

Post by Peculiar_Investor »

LadyGeek wrote:Can you scrape the data from a website?
Been there, doing that! My spreadsheet was originally written to scrape data from finance.yahoo.com, but I ran into problems with how they handled stocks on Canadian stock exchanges, so I switched to Microsoft's recommended approach, which they've decided to suddenly discontinue. So I'm going back to using Yahoo!.
The Bogleheads' wiki has a few GoogleFinance examples for that techqnique: Real-time tracking from a website

GoogleFinance doesn't own this techqnique, it's just what I've found as an example.
Yabbut GoogleFinance doesn't support international stock exchanges, i.e. Canadian ones.

<RANT>Canadian investors face challenges like this all the time. Most data services are US-centric and ignore the fact there are other stock exchanges around the world. And even if they do, they handle the exchange designation differently. Microsoft prefixes with "CA:", Yahoo! appends ".TO", Google uses starts with "TSE:". Canadian preferred shares introduce other challenges. This makes switching a bit more challenging.</RANT>
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
ig17
Veteran Contributor
Veteran Contributor
Posts: 3418
Joined: 21 Feb 2005 20:54

Re: Stock/Bond/Index/Fundamental/Asset Class/Quotes - Source

Post by ig17 »

Another option:

* migrate from Excel to OpenOffice or LibreOffice
* use GetQuote extension

GetQuote fetches data from Yahoo.

Extension Download
http://sourceforge.net/projects/getquot ... =directory

User Guide
https://www.scribd.com/doc/173263241/GE ... -Guide-pdf

EDIT: Wrong download link. Will post the right one if I can find it.
qasimodo
Contributor
Contributor
Posts: 355
Joined: 02 May 2010 12:08

Re: Stock/Bond/Index/Fundamental/Asset Class/Quotes - Source

Post by qasimodo »

Peculiar_Investor wrote:
Google wrote:GOOGLEFINANCE is only available in English and does not support most international exchanges.
so getting TSX quotes becomes problematic.

Years ago I migrated from Excel with macro to download out of Yahoo to Open Office with a rewrite of the macro for Yahoo.
I now use the googlefinance function in Google Spreadsheet. It DOES support the TSX

edited for clarity
Last edited by qasimodo on 31 Dec 2014 12:45, edited 1 time in total.
oxymoronic marketers pitch: "... and make ads more relevant"
User avatar
Peculiar_Investor
Administrator
Administrator
Posts: 13267
Joined: 01 Mar 2005 14:52
Location: Calgary
Contact:

Re: Stock/Bond/Index/Fundamental/Asset Class/Quotes - Source

Post by Peculiar_Investor »

Thanks, but moving to OpenOffice or LibreOffice brings other challenges due to the macros and other automation built into my spreadsheets.

My current quick workaround is using the SMF add-in and in particular the RCHGetYahooQuotes() function. This isn't a long term solution as retrieving quotes individually isn't efficient.

Shakespeare's solution above is similar to what I had been doing eons ago when using Yahoo! Finance, it will just take a bit longer to rewrite things due to differences in exchange designation in the ticker symbols for TSX listed symbols.
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
Peculiar_Investor
Administrator
Administrator
Posts: 13267
Joined: 01 Mar 2005 14:52
Location: Calgary
Contact:

Re: Stock/Bond/Index/Fundamental/Asset Class/Quotes - Source

Post by Peculiar_Investor »

qasimodo wrote:I now use googlefinance. It DOES support the TSX
I'll bite then because I tried Stock Portfolio Tracker (live, with charts) - Google Docs Templates and couldn't get it to recognize TSX listed stocks such as Cenovus (CVE).

I was taking this approach as suggested by Free Stock Quotes in Excel
UPDATE 12/23/2014: The GOOGLEFINANCE function in Google Sheets allows you to get current and historical quotes. You can create a Google Sheet with the stock quote information that you want and then publish the sheet as a web page (via File > Publish to the Web). If you still want the data in Excel, you can create a web query in Excel that uses the Google Sheet as the data source by going to Data > From Web and entering the link to the published Google Sheet in the address field.
Last edited by Peculiar_Investor on 31 Dec 2014 12:49, edited 1 time in total.
Reason: Add Vertex42 quote
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
DenisD
Veteran Contributor
Veteran Contributor
Posts: 4081
Joined: 19 Feb 2005 01:24
Location: Calgary

Re: Stock/Bond/Index/Fundamental/Asset Class/Quotes - Source

Post by DenisD »

I've been able to get googlefinance quotes of all my Canadian stocks by using the prefix TSE: before the ticker. But it has to be a new spreadsheet or one converted to the new format.
User avatar
Bylo Selhi
Veteran Contributor
Veteran Contributor
Posts: 29493
Joined: 16 Feb 2005 10:36
Location: Waterloo, ON
Contact:

Re: Stock/Bond/Index/Fundamental/Asset Class/Quotes - Source

Post by Bylo Selhi »

LadyGeek wrote:Can you scrape the data from a website?
Tsk. Tsk. Back in the '70s when users wanted raw data and management didn't provide it they'd scrape it off 3270 green screens. Then if I dared make even the tiniest change to a screen layout, even if I gave everyone plenty of notice, I'd get deluged with angry calls from screen scrapers whose "apps" stopped working. Now here we are 40-some years later and people still scrape screens because the owners of the raw data won't share it with the great unwashed out there. There ain't no justice in the world I tell you... ;)
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: Stock/Bond/Index/Fundamental/Asset Class/Quotes - Source

Post by big easy »

I use a Google spreadsheet stored on Google drive to automatically download quotes:

=googlefinance("TDB900","price")
or
=googlefinance("TSE:XIU","price")

Then I cut and paste the results into my Open Office spreadsheet. I couldn't figure out how to do that directly in Open Office.
"Everybody has a plan until they get punched in the face." Mike Tyson
User avatar
Peculiar_Investor
Administrator
Administrator
Posts: 13267
Joined: 01 Mar 2005 14:52
Location: Calgary
Contact:

Re: Stock/Bond/Index/Fundamental/Asset Class/Quotes - Source

Post by Peculiar_Investor »

Hopefully this is a left hand, right hand issue. From another Microsoft forum -- The Excel quotes service is currently down - Service will be back up - Microsoft Community
The Excel quotes service on MSN Money Central Investor is currently down due to some redirect issues. The product team will be deploying the fix by tomorrow and the service should be back up again in two days.
We apologize for the inconvenience.

Adriana
then a later post
Hi everybody,
The issue is fixed but redirection is still pending. As people are on vacation this is still pending. They will try to get this done at the earliest.
In the meantime, if you want to use the service directly, please use this link:
http://www.msn.com/en-us/money/quoteslookup?symbol=msft
Hope this helps!
Thanks,
Adriana
Can confirm that link provided does work, although I'm struggling to adapt my data sources to use it.

There is some hope after all.
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
Norbert Schlenker
Veteran Contributor
Veteran Contributor
Posts: 7960
Joined: 16 Feb 2005 09:56
Location: An Argument Surrounded By Water
Contact:

Re: Stock/Bond/Index/Fundamental/Asset Class/Quotes - Source

Post by Norbert Schlenker »

2014 nominal returns have been added to my spreadsheet(s) at http://libra-investments.com/re01.htm. Real returns will be updated once StatsCan publishes the Dec 2014 CPI level.
Nothing can protect people who want to buy the Brooklyn Bridge.
User avatar
NormR
Veteran Contributor
Veteran Contributor
Posts: 5234
Joined: 18 Feb 2005 11:19
Contact:

Re: Stock/Bond/Index/Fundamental/Asset Class/Quotes - Source

Post by NormR »

Norbert Schlenker wrote:2014 nominal returns have been added to my spreadsheet(s) at http://libra-investments.com/re01.htm. Real returns will be updated once StatsCan publishes the Dec 2014 CPI level.
My Asset Mixer and Periodic Table have been updated to reflect Norbert's new nominal return data for 2014. 8)
longinvest
Veteran Contributor
Veteran Contributor
Posts: 3956
Joined: 10 Sep 2012 17:26
Location: QC

Re: Stock/Bond/Index/Fundamental/Asset Class/Quotes - Source

Post by longinvest »

Thanks Norbert and NormR!
Variable Percentage Withdrawal (finiki.org/wiki/VPW) | One-Fund Portfolio (VBAL in all accounts)
User avatar
LadyGeek
Veteran Contributor
Veteran Contributor
Posts: 1975
Joined: 26 Oct 2011 16:51
Location: Philadelphia, PA
Contact:

Re: Stock/Bond/Index/Fundamental/Asset Class/Quotes - Source

Post by LadyGeek »

Also, thanks. I'm not sure what should be in this spreadsheet, but the total-returns spreadsheet is missing the 2014 Annual Canadian Inflation (Nominal!B50).
Imagefiniki, the Canadian financial wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
NormR
Veteran Contributor
Veteran Contributor
Posts: 5234
Joined: 18 Feb 2005 11:19
Contact:

Re: Stock/Bond/Index/Fundamental/Asset Class/Quotes - Source

Post by NormR »

LadyGeek wrote:Also, thanks. I'm not sure what should be in this spreadsheet, but the total-returns spreadsheet is missing the 2014 Annual Canadian Inflation (Nominal!B50).
Real returns will be updated once StatsCan publishes the Dec 2014 CPI level.
longinvest
Veteran Contributor
Veteran Contributor
Posts: 3956
Joined: 10 Sep 2012 17:26
Location: QC

Re: Stock/Bond/Index/Fundamental/Asset Class/Quotes - Source

Post by longinvest »

@NormR: Your Asset Mixer is awesome. But, I have three questions about its data.

1- It includes nominal data for TSX Composite going back to 1958, as well as All Canadian Bonds and MSCI EAFE going back to 1961. Why is its S&P500 data limited to going back to 1970? Is it because Canadian vs US dollar data is missing for 1961 to 1969?

2- I also noticed that inflation-adjusted returns are even more limited. For example, All Canadian Bonds only go back to 1980. Using CPI data, it would seem easy to go back to 1961, as you already have nominal returns.

3- I would like to include its pre-1970 data into the VPW backtesting spreadsheet. How do I get the permission for this? :)

It would be awesome to model simple portfolios (all bonds, TSX, S&P500, EAFE) going back as far as possible.

Thanks.
Variable Percentage Withdrawal (finiki.org/wiki/VPW) | One-Fund Portfolio (VBAL in all accounts)
User avatar
Norbert Schlenker
Veteran Contributor
Veteran Contributor
Posts: 7960
Joined: 16 Feb 2005 09:56
Location: An Argument Surrounded By Water
Contact:

Re: Stock/Bond/Index/Fundamental/Asset Class/Quotes - Source

Post by Norbert Schlenker »

NormR wrote:Real returns will be updated once StatsCan publishes the Dec 2014 CPI level.
StatsCan's release date is 23 January. I will be away through the end of that week, most likely sans computer, so my spreadsheets will not be updated until the following week. Norm might use the headline number, but it's calculated on a different basis (ratio of trailing twelve month averages) than my figure (ratio of December vs December index levels).
longinvest wrote:It includes nominal data for TSX Composite going back to 1958, as well as All Canadian Bonds and MSCI EAFE going back to 1961. Why is its S&P500 data limited to going back to 1970? Is it because Canadian vs US dollar data is missing for 1961 to 1969?
I don't publish pre-1970 figures because I'm not happy with the accuracy nor granularity of the data I have. (With respect to EAFE, I don't have a clue where Norm gets his figures, because AFAIK the index didn't exist before 31 Dec 1969.) As for CAD/USD pre 1970, exchange rates were fixed for much of the time. From May of 1962 through April of 1970, it was pretty much fixed at US$0.925.
Nothing can protect people who want to buy the Brooklyn Bridge.
User avatar
LadyGeek
Veteran Contributor
Veteran Contributor
Posts: 1975
Joined: 26 Oct 2011 16:51
Location: Philadelphia, PA
Contact:

Re: Stock/Bond/Index/Fundamental/Asset Class/Quotes - Source

Post by LadyGeek »

I'm creating a new finiki article similar to the Bogleheads' Callan periodic table of investment returns, as I think it will help show the benefits of diversification for Canadian investors.

With behind-the-scenes encouragement from NormR, I created a table from Nortbert's data. Here's what I have so far: User:LadyGeek/Periodic table of annual returns (development page)

Here's the spreadsheet source: Canadian Periodic Table of Total-returns.xls (Google Drive, download the file)

NormR's table here has US Bond data, but that's not in Norbert's spreadsheet. Being somewhat bold, I found the data here: Annual Returns on Stock, T.Bonds and T.Bills: 1928 - Current (from this website: The Data Page: Current Datasets) and is based on the US St. Louis Fed, which I think is this one: 10-Year Treasury Constant Maturity Rate

However, my US Bond total return numbers don't match NormR's. What did I do wrong?

I used the nominal values. The spreadsheet is done in MS Excel using conditional formatting rules for the colors. I haven't tried this in LibreOffice Calc (yet), I want to get the numbers right first.

Update 12-Jan: Changed Google Drive link URL.
Last edited by LadyGeek on 12 Jan 2015 22:30, edited 3 times in total.
Imagefiniki, the Canadian financial wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
longinvest
Veteran Contributor
Veteran Contributor
Posts: 3956
Joined: 10 Sep 2012 17:26
Location: QC

Re: Stock/Bond/Index/Fundamental/Asset Class/Quotes - Source

Post by longinvest »

NormR's bond returns are probably the returns in Canadian dollars, so you have to account for currency fluctuations.

I don't like artificial bond returns made by inferring an annual return from only having a 10-year interest rate, with no information about the 9-year rate. For the period 1871-1971, it's what I use within a 10-year & 1 year bond barbell, based on Shiller's data, in the VPW backtesting spreadsheet, because I have not found any better free approximation of historical bond returns. Worse: these are government bonds, yet corporate bonds usually have higher coupons (and lower duration). For 1972 and later, I used Simba's spreadsheet to get the return of Vanguard's Total Bond Market fund (and a good approximation of total bond market for years before the fund's creation). I would recommend using this data after applying US/CAD currency variations.
Variable Percentage Withdrawal (finiki.org/wiki/VPW) | One-Fund Portfolio (VBAL in all accounts)
User avatar
LadyGeek
Veteran Contributor
Veteran Contributor
Posts: 1975
Joined: 26 Oct 2011 16:51
Location: Philadelphia, PA
Contact:

Re: Stock/Bond/Index/Fundamental/Asset Class/Quotes - Source

Post by LadyGeek »

Yes, NormR's returns are in Canadian dollars. He suggested to:

1) Ask Norbert for the conversion rates to be consistent with the other metrics (Norbert - is this available?), or
2) Just drop the US Bond data.

longinvest is now suggesting the Total Bond Market fund with a US/CAD currency conversion. I can see using the Total Bond Market fund as a choice - this table is for investor fund comparisons. US Treasury bonds don't represent the market.

Additionally, Annual Returns on Stock, T.Bonds and T.Bills: 1928 - Current is calculating a total return, which confuses things further.

Then perhaps, Total Bond Market with a US/CAD currency conversion?

If the conversion rates aren't available, I'll drop the US Bonds.
Imagefiniki, the Canadian financial wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
Norbert Schlenker
Veteran Contributor
Veteran Contributor
Posts: 7960
Joined: 16 Feb 2005 09:56
Location: An Argument Surrounded By Water
Contact:

Re: Stock/Bond/Index/Fundamental/Asset Class/Quotes - Source

Post by Norbert Schlenker »

Happy to give you the year end exchange rates. How much history do you want?

As for using Vanguard Total Bond as a proxy, I think that creates a problem because of the 2002 lunatic off road adventure.

All my returns, and presumably Norm's too, are total returns. Nothing else makes sense.
Nothing can protect people who want to buy the Brooklyn Bridge.
User avatar
LadyGeek
Veteran Contributor
Veteran Contributor
Posts: 1975
Joined: 26 Oct 2011 16:51
Location: Philadelphia, PA
Contact:

Re: Stock/Bond/Index/Fundamental/Asset Class/Quotes - Source

Post by LadyGeek »

Thanks. I'm using the last 10 years, 2005 - 2014.

Can you throw in a quick example? Simple, but I'm probably dyslexic and I always seem to go in the wrong direction.

Please double-check that the attribution in the finiki page is correct.
Norbert Schlenker wrote:All my returns, and presumably Norm's too, are total returns. Nothing else makes sense.
That helped. I checked further and found the description in the "Data Sources" tab- "...annual total returns (income plus price changes)".

I think I'll stick with the Annual Returns on Stock, T.Bonds and T.Bills: 1928 - Current 10-year bond return - which includes coupon and price appreciation. The detailed formulas are in the spreadsheet. I'll throw in the conversion factor and update the table.

I don't see anything wrong with this approach, as the purpose is to show the dispersion of returns. It's intended to be an illustrative example of the need for diversification. If it differs from another proxy, I don't think it will change the intent.
Imagefiniki, the Canadian financial wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
Norbert Schlenker
Veteran Contributor
Veteran Contributor
Posts: 7960
Joined: 16 Feb 2005 09:56
Location: An Argument Surrounded By Water
Contact:

Re: Stock/Bond/Index/Fundamental/Asset Class/Quotes - Source

Post by Norbert Schlenker »

The 31 December closing CAD/USD rates for the period were:

Code: Select all

2004 1.2020
2005 1.1630
2006 1.1654
2007 0.9913
2008 1.2180
2009 1.0510
2010 0.9946
2011 1.0170
2012 0.9949
2013 1.0636
2014 1.1601
I think it's a mistake to use the Ibbitson T-bond data and you should use the Barclays (formerly Lehman) Aggregate Index. Returns (in USD) were:

Code: Select all

2005  2.43%
2006  4.33%
2007  6.97%
2008  5.24%
2009  5.93%
2010  6.54%
2011  7.84%
2012  4.22%
2013 -2.02%
2014  5.97%
So the Canadian dollar returns each year were (1 + the US dollar return) x (year end CAD/USD) / (previous year end CAD/USD) - 1:

Code: Select all

2005 -0.89%
2006  4.55%
2007 -9.01%
2008 29.31%
2009 -8.59%
2010  0.82%
2011 10.27%
2012  1.96%
2013  4.75%
2014 15.58%
Nothing can protect people who want to buy the Brooklyn Bridge.
Post Reply