How to download data?

Discuss your favourite picks, broker, and trading or investment style.
User avatar
Arby
Veteran Contributor
Veteran Contributor
Posts: 3125
Joined: 20 Feb 2005 19:23
Location: Ottawa, ON

Post by Arby »

An add-in for downloading stock prices into an Open Office Calc spreadsheet is here. Has anyone used this add-in?
User avatar
Bylo Selhi
Veteran Contributor
Veteran Contributor
Posts: 29493
Joined: 16 Feb 2005 10:36
Location: Waterloo, ON
Contact:

Post by Bylo Selhi »

Seems to work for a variety of securities and indexes. Anyone know how to look up Canadian mutual funds via Yahoo, i.e. what the symbols look like?

Image
Sedulously eschew obfuscatory hyperverbosity and prolixity.
scampbell
Contributor
Contributor
Posts: 154
Joined: 15 Apr 2007 12:27
Location: Victoria, B.C.

Post by scampbell »

Here's the link for Yahoo mutual fund info. I don't believe the actual numbers have been updated in quite a while.

Yahoo Mutual Funds link
User avatar
Bylo Selhi
Veteran Contributor
Veteran Contributor
Posts: 29493
Joined: 16 Feb 2005 10:36
Location: Waterloo, ON
Contact:

Post by Bylo Selhi »

scampbell wrote:I don't believe the actual numbers have been updated in quite a while.
No kidding! The "Copyright © 1994-2004" is one clue. If only I could sell my remaining funds today for those prices :lol:

BTW one potentially serious problem with the GETPRICE add-in is that you have to do a separate lookup for each security. This causes a long delay when you open up the spreadsheet above and every time you refresh it. Yahoo allows multiple symbols per search, e.g.

Code: Select all

http://finance.yahoo.com/q/cq?s=^DJI ^SPX ^IXIC ^GSPTSE ^FTSE ^GDAXI ^SSMI ^HSI ^N225
but the add-in doesn't (yet?) support it.
Sedulously eschew obfuscatory hyperverbosity and prolixity.
User avatar
Arby
Veteran Contributor
Veteran Contributor
Posts: 3125
Joined: 20 Feb 2005 19:23
Location: Ottawa, ON

Post by Arby »

Bylo Selhi wrote: BTW one potentially serious problem with the GETPRICE add-in is that you have to do a separate lookup for each security. This causes a long delay when you open up the spreadsheet above and every time you refresh it. Yahoo allows multiple symbols per search, but the add-in doesn't (yet?) support it.
That's a good suggestion for an enhancement. You should include it in the comments section on the GETPRICE webpage.
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 »

Another Excel challenge. Wonder if gummy still checks in here? I use an Excel 2003 tracking spreadsheet which gathers, via webquery, quotes supplied by MSN Money. I use a webquery so that I can automate it to refresh itself over the course of the day. In one Excel tab, I have a column of stock symbols that I track. For example:

Column A
ADP
AEO
AIG
ca:ACM.a
...

I build up a query string, ie "ADP AEO AIG ca:ACM.a", which has a limit of 255 characters and pass the string to MSN Money via a webquery. The specific URL is http://moneycentral.msn.com/investor/ex ... g,ca:acm.a As you can see below, my webquery returns a table of stocks based on a list of symbols that I track.

My challenge is that I'm trying to extract, for each symbol, the current price and other data from the table. I'm using Excel's VLOOKUP() function. The problem is that the MSN Money table does not return the stock symbol, so I don't have a search value that I can supply in VLOOKUP(). The interesting thing that I've found is that when you hover the mouse over the first column in the the returned table from MSN Money, you see that it is a hyperlink to the stock quote and it does contain the stock symbol. I'm assuming that the hyperlink information is embedded in the Excel cell, but I cannot find a method to get at data. Any Excel gurus know how to get at this piece of data? Or an alternative method to index into the MSN Money table to pull data out?
Attachments
Excel webquery to MSN Money
Excel webquery to MSN Money
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: How to download data?

Post by Peculiar_Investor »

Just a follow-up, after some heavy duty Google work, found this solution, Excel: Get Underlying Hyperlink Address. Specifically,
This UDF will extract the underlying address from a cell containing a Hyperlink

The Code

Function GetAddress(HyperlinkCell As Range)

GetAddress = Replace _

(HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")

End Function
Note, in Excel 2003, I made this work by changing it to

Code: Select all

Function GetAddress(HyperlinkCell As Range)
    GetAddress = HyperlinkCell.Hyperlinks(1).Address
End 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
Gus
Veteran Contributor
Veteran Contributor
Posts: 2311
Joined: 11 Mar 2005 13:01
Location: Salt Spring Island, BC

Re: How to download data?

Post by Gus »

Peculiar_Investor wrote:
My challenge is that I'm trying to extract, for each symbol, the current price and other data from the table. I'm using Excel's VLOOKUP() function. The problem is that the MSN Money table does not return the stock symbol, so I don't have a search value that I can supply in VLOOKUP(). The interesting thing that I've found is that when you hover the mouse over the first column in the the returned table from MSN Money, you see that it is a hyperlink to the stock quote and it does contain the stock symbol. I'm assuming that the hyperlink information is embedded in the Excel cell, but I cannot find a method to get at data. Any Excel gurus know how to get at this piece of data? Or an alternative method to index into the MSN Money table to pull data out?
I may have misunderstood your question and I hesitate to write this since my solution is so obvious, but what I do for my preferred spreadsheet is to have a page of MSN Stock Quotes on a worksheet as you do and then have an additional worksheet within the same Excel workbook that links to specific cells on the MSN sheet. In this way I can extract numbers from the MSN sheet and calculate current yields etc on my own sheet for comparison purposes. [It wouldn't be difficult to use Shakespeare's YTW spreadsheet** in the same way, with each preferred having its own worksheet and assembling the YTW's for a range of preferreds on an additional summary sheet.] The numbers on both sheets refresh to current (20 min delayed) prices when I hit the data button. I'm using Excel 2007, but this worked also in 2003.

[**I have become lazy about calculating YTW's lately, since I only buy/sell perpetual discounts.]
Money ain't got no owners, just spenders. Omar Little
User avatar
Springbok
Veteran Contributor
Veteran Contributor
Posts: 5438
Joined: 22 Mar 2005 16:47

Importing dividend data into spreadsheet

Post by Springbok »

Seems like Yahoo and MSN don`t provide dividend data for Canadian stocks.

I would like to automate my investment spreadsheet for both stock prices and actual dividend (or % yield) but at least a number I can use in another calculation. I could use Excel 97 pro, Open Office or Google Docs, but would prefer OpenOffice Calc.

Anyone had any luck importing dividend data for CDN stocks ?

BTW:
Arby wrote:An add-in for downloading stock prices into an Open Office Calc spreadsheet is here. Has anyone used this add-in?
There is a new version of this called GetQuote
http://getquote-tedsoft.blogspot.com/p/about.html

I have installed it, but not used it. Looks like it still does stocks one at a time (No - Just checked it and it will do groups of stocks now. Much improved, but still relies on Yahoo with no CDN divvies)
User avatar
Springbok
Veteran Contributor
Veteran Contributor
Posts: 5438
Joined: 22 Mar 2005 16:47

Re: Importing dividend data into spreadsheet

Post by Springbok »

Springbok wrote:Seems like Yahoo and MSN don`t provide dividend data for Canadian stocks.

I would like to automate my investment spreadsheet for both stock prices and actual dividend (or % yield) but at least a number I can use in another calculation. I could use Excel 97 pro, Open Office or Google Docs, but would prefer OpenOffice Calc.

Anyone had any luck importing dividend data for CDN stocks ?

BTW:
Arby wrote:An add-in for downloading stock prices into an Open Office Calc spreadsheet is here. Has anyone used this add-in?
There is a new version of this called GetQuote
http://getquote-tedsoft.blogspot.com/p/about.html

I have installed it, but not used it. Looks like it still does stocks one at a time (No - Just checked it and it will do groups of stocks now. Much improved, but still relies on Yahoo with no CDN divvies)
Correction - Getquote will now get dividends for some Canadian Corps, but the one I tried (BNS.TO) comes up with the wrong number. Maybe just not updated for March increase.

One other thing - Yahoo no longer cover Canadian mutual funds.
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 »

Since CDK Global (CDK) was spun out from Automatic Data Processing (ADP) at the beginning of October, most websites have been updated to provide CDK quotes. Unfortunately MSN's stock quote service that is used in a number of spreadsheets has not. The link at http://moneycentral.msn.com/investor/ex ... symbol=cdk will not provide the expected quote. It seems that Microsoft has been fiddling with their MSN money site, and the quote URLs. The symbol lookup link from the above URL doesn't function as expected either.

If I go to http://www.msn.com/en-us/money and enter CDK it properly provides a quote via http://www.msn.com/en-us/money/stockdet ... .1.CDK.NAS. I've tried using MSN's feedback link to report the problem but I believe link most websites, it sends the feedback to /dev/null.

Any suggestions on how to fix this download problem, with the proviso that the downloaded data is populating a spreadsheet containing other stock quotes.
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
chufinora
Contributor
Contributor
Posts: 769
Joined: 12 Oct 2009 15:03
Location: Ottawa

Re: How to download data?

Post by chufinora »

Peculiar_Investor wrote: Any suggestions on how to fix this download problem, with the proviso that the downloaded data is populating a spreadsheet containing other stock quotes.
Well this may not appeal but Google sheets downloads data to a spreadsheet. I believe there are tools you can get to sync google sheets to Excel (It is also easy to export a google sheet to Excel). I don't bother as I find Google sheets quite a good alternative to Excel for portfoiio tracking and analysis. Native calls from Google sheets include, Price, historic closing prices, P/E, volume, F/X rates (Dividends are a glaring omission, and I manually input them)
User avatar
northbynorthwest
Contributor
Contributor
Posts: 165
Joined: 19 Jan 2007 01:07
Location: Vancouver

Re: How to download data?

Post by northbynorthwest »

chufinora wrote:
Peculiar_Investor wrote: Any suggestions on how to fix this download problem, with the proviso that the downloaded data is populating a spreadsheet containing other stock quotes.
Well this may not appeal but Google sheets downloads data to a spreadsheet. I believe there are tools you can get to sync google sheets to Excel (It is also easy to export a google sheet to Excel). I don't bother as I find Google sheets quite a good alternative to Excel for portfoiio tracking and analysis. Native calls from Google sheets include, Price, historic closing prices, P/E, volume, F/X rates (Dividends are a glaring omission, and I manually input them)
Chufinora,

I use the following Google sheets formula to auto-import dividend yield %:

=RIGHT(ImportXML(concatenate("http://finance.google.com/finance?q=";A2); "//td[@data-snapfield='latest_dividend-dividend_yield']/following-sibling::*"),4)

Where the A2 field is BNS.TO, for example, the current output is 3.88 for div % yield. Paste it in, fill down and see if that works for you.
User avatar
Springbok
Veteran Contributor
Veteran Contributor
Posts: 5438
Joined: 22 Mar 2005 16:47

Re: How to download data?

Post by Springbok »

I messed with automatic updates, but really why bother when on-line broker does it for you?

We use BMOIL and they provide a good portfolio summary either in pdf format or as a .csv file for each portfolio on a daily basis.

I do have a spreadsheet that combines our portfolios. Twice a year, I update it manually. This doesn't take long and it makes me look at actual numbers (rather than have some 3rd party website do it). I could try downloading and merging the .csv data. But, format is different and I just don't see the need.

My spreadsheet is really only useful in determining overall Total Return and yield of combined portfolios, overall asset allocation and history since we started DIY investing. It also includes our overall FI ladder.
Spudd
Veteran Contributor
Veteran Contributor
Posts: 1518
Joined: 22 Sep 2013 14:52

Re: How to download data?

Post by Spudd »

northbynorthwest wrote: I use the following Google sheets formula to auto-import dividend yield %:

=RIGHT(ImportXML(concatenate("http://finance.google.com/finance?q=";A2); "//td[@data-snapfield='latest_dividend-dividend_yield']/following-sibling::*"),4)

Where the A2 field is BNS.TO, for example, the current output is 3.88 for div % yield. Paste it in, fill down and see if that works for you.
Thanks for this - works great. Only issue is when the dividend is double-digits (e.g. for LRE.TO I get 2.00 instead of 12.00). So you need to double-check in case you have any such cases - if so, change the 4 to a 5 for that row.
chufinora
Contributor
Contributor
Posts: 769
Joined: 12 Oct 2009 15:03
Location: Ottawa

Re: How to download data?

Post by chufinora »

northbynorthwest wrote:
I use the following Google sheets formula to auto-import dividend yield %:

=RIGHT(ImportXML(concatenate("http://finance.google.com/finance?q=";A2); "//td[@data-snapfield='latest_dividend-dividend_yield']/following-sibling::*"),4)

Where the A2 field is BNS.TO, for example, the current output is 3.88 for div % yield. Paste it in, fill down and see if that works for you.
Many thanks that is very useful!
Springbok wrote:I messed with automatic updates, but really why bother when on-line broker does it for you?

We use BMOIL and they provide a good portfolio summary either in pdf format or as a .csv file for each portfolio on a daily basis.
As the saying goes "Each to their own". I find the broker updates woefully inadequate for tracking. First I have 6 accounts across 2 brokers, with multiple log-ins. Also the reported data is weak in some cases (eg. TD does not give access to transaction data more than a few months old). I track asset allocations, performance by sector, dividends accumulated, ROI, (ROI from dividend, and share price separately) dividend growth projections, ACB etc. in various google sheets all pulling data from google finance.
User avatar
Springbok
Veteran Contributor
Veteran Contributor
Posts: 5438
Joined: 22 Mar 2005 16:47

Re: How to download data?

Post by Springbok »

chufinora wrote:As the saying goes "Each to their own". I find the broker updates woefully inadequate for tracking. First I have 6 accounts across 2 brokers, with multiple log-ins. Also the reported data is weak in some cases (eg. TD does not give access to transaction data more than a few months old). I track asset allocations, performance by sector, dividends accumulated, ROI, (ROI from dividend, and share price separately) dividend growth projections, ACB etc. in various google sheets all pulling data from google finance.
At present, we have everything with BMOIL in 6 accounts. Previously we had about 10 accounts split between BMOIL and RBCDI and prior to that everything with FS broker, BMO, PH&N, etc. Having everything in one place does help.

BMOIL have transaction data for past 24 months. Useful for entering acb data (also done once/yr) and checking our cash withdrawals. Their portfolio summary is quite good and additional information like asset allocation etc is available (by account) on-line.

When I first prepared my spreadsheet, I was so impressed with myself for actually having made it with it's pretty piecharts for allocation, etc that I updated it monthly! Then I asked myself - why am I doing this? Analysis Paralysis?? Now, we get paper monthly reports and instant on-line reports anytime we want them from BMOIL site. Besides, everything these days is almost on auto-pilot with just a little tweaking from time to time. As a result, an annual overall summary suffices for us. But as you say, "To each their own".
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 »

Springbok wrote:I messed with automatic updates, but really why bother when on-line broker does it for you?
Good question, but isn't that that's a why question when the topic is how. I agree with the why and the answer of "to each his own".
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
northbynorthwest
Contributor
Contributor
Posts: 165
Joined: 19 Jan 2007 01:07
Location: Vancouver

Re: How to download data?

Post by northbynorthwest »

chufinora wrote:As the saying goes "Each to their own". I find the broker updates woefully inadequate for tracking. First I have 6 accounts across 2 brokers, with multiple log-ins. Also the reported data is weak in some cases (eg. TD does not give access to transaction data more than a few months old). I track asset allocations, performance by sector, dividends accumulated, ROI, (ROI from dividend, and share price separately) dividend growth projections, ACB etc. in various google sheets all pulling data from google finance.
Like you, chufinora, I like the ability to track performance etc. for all accounts combined.
Pulling from Google Finance is good, but I have taken to supplementing via another method for additional metrics I like to track.

Here's a grab of part of one of my Google sheets:
Screen Shot 2014-10-25 at 2.54.53 PM.png
You'll notice it pulls the current price from Google Finance but it also displays the % above or below the 200 day moving average, the analysts consensus target, and calculates the implied gain to consensus target from the current price.

Note the consensus target and 200 day % are not fully automated.
This may be a level of geekery beyond what anyone wants, but here's how I do it:

1. I use Globe Investor watchlist to track equities I am interested in.
2. When I want to update the elements like the 200d MA etc (usually I do this on weekends once or twice a month) I go to Globe watchlist, choose 'Import to/Export Data', then export the data to my downloads folder.
3. On my Google doc I have a sub-sheet called 'Data'. I click onto the Data sheet, go File, Import, Replace Current Sheet, and choose the exported GlobeInvestor data in my downloads and let the new data replace the old. Extreme caution to be on the Data sheet and choosing 'Replace current sheet' so you don't overwrite your entire spreadsheet by mistake rather than just the sub-sheet.
4. Ta da. The columns on the sheet main page fetch the fresh numbers from the 'Data' sheet using VLOOKUP formula.

The GlobeInvestor exported data set lets you pull in a huge variety of parameters if you wish: ROE, P/B, div payout %, div growth rates, you name it. Go to watch list, export it and open it to see for yourself. I think there's stuff there that's not even available on custom watch list.

The trick is setting the formulas up right, which I don't claim to be perfect at.
What I use for consensus target price is:
=RIGHT(VLOOKUP(B2 ,Data!B1:BN100, 62, FALSE),5)
For 200-day average:
=VLOOKUP(B2,Data!B1:BN100, 25, FALSE)

The bolded numbers are the desired column position on the data sheet. It's numeric instead of the actual columns, so target price is 62 instead of BK as per the top of the column. Change that number to 31 and you'll get forward P/E ratio instead.
Also:
- The target price formula has more stuff to extract the right 5 chars and strip out the $ and space to the left of the number, so that gain to target calc can then be performed on it.
- When a target price goes from $99.55 to $102.44 the change of digits may get wonky and need to be corrected in the formula (5 to 6 chars) as Spudd noted above re the double digit dividends.
- Also, when filling down the cell reference to the right of the ! goes up automatically. If you get a not available error for a stock, try making sure that part of the formula reads Data!B1:BN100 to ensure it's searching all stocks on the Data sheet. (If I knew how I would freeze that part of the formula so it doesn't change when filling down.)
- This system requires two sets of symbol refs, ie BNS.TO in column A and BNS in col B, because the column B style meshes best with GlobeInvestor data while column A is for Google Finance pulls. I normally have column B hidden.
Sometimes, I may use a formula drawing from A col symbol =RIGHT(VLOOKUP(LEFT(A2,3) ,Data!B1:BN100, 62, FALSE),5) or directly enter the desired symbol if that works better: =RIGHT(VLOOKUP("AGU" ,Data!B2:BM, 62, FALSE),6)
Again, I've figured out just enough of this to realize someone else is probably doing it way better and more elegantly.
User avatar
Springbok
Veteran Contributor
Veteran Contributor
Posts: 5438
Joined: 22 Mar 2005 16:47

Re: How to download data?

Post by Springbok »

Peculiar_Investor wrote:
Springbok wrote:I messed with automatic updates, but really why bother when on-line broker does it for you?
Good question, but isn't that that's a why question when the topic is how. I agree with the why and the answer of "to each his own".
Sorry - It comes from a career in formal and informal problem solving.

One question was always "what else could we do" and another "what if we don't do anything at all!"
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 »

Today I found that the GetQuote extension in my Open Office spreadsheet is not working. The GetQuote cells all display #VALUE!.
GetQuote was working a few weeks ago, and has worked for many years. Anyone have any information on whether there is a problem with GetQuote?
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 »

=VALUE(GETYAHOO($A3,21)) works with LibreOffice, where A3 has something like RY.TO
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 »

Shakespeare wrote: 13 Jun 2017 16:16 =VALUE(GETYAHOO($A3,21)) works with LibreOffice, where A3 has something like RY.TO
I installed LibreOffice and the SMF extension. But I get ERR:502 with a cell input of =VALUE(GETYAHOO($A3,21)). $A3 points to a cell which contains a valid stock symbol (ftn-pa.to) for Yahoo finance. Any suggestions for how to fix this ERR:502 ?
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 symbol FTN-PA.TO works for me. Try a direct call to "FTN-PA.TO" rather than A3.

=VALUE(GETYAHOO("FTN-PA.TO",21))
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 »

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?
Post Reply