How to download data?
- Bylo Selhi
- Veteran Contributor
- Posts: 29493
- Joined: 16 Feb 2005 10:36
- Location: Waterloo, ON
- Contact:
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
Yahoo Mutual Funds link
- Bylo Selhi
- Veteran Contributor
- Posts: 29493
- Joined: 16 Feb 2005 10:36
- Location: Waterloo, ON
- Contact:
No kidding! The "Copyright © 1994-2004" is one clue. If only I could sell my remaining funds today for those pricesscampbell wrote:I don't believe the actual numbers have been updated in quite a while.
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
Sedulously eschew obfuscatory hyperverbosity and prolixity.
That's a good suggestion for an enhancement. You should include it in the comments section on the GETPRICE webpage.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.
- Peculiar_Investor
- Administrator
- Posts: 13267
- Joined: 01 Mar 2005 14:52
- Location: Calgary
- Contact:
Re: How to download data?
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?
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?
finiki, 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
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
- Peculiar_Investor
- Administrator
- Posts: 13267
- Joined: 01 Mar 2005 14:52
- Location: Calgary
- Contact:
Re: How to download data?
Just a follow-up, after some heavy duty Google work, found this solution, Excel: Get Underlying Hyperlink Address. Specifically,
Note, in Excel 2003, I made this work by changing it toThis 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
Code: Select all
Function GetAddress(HyperlinkCell As Range)
GetAddress = HyperlinkCell.Hyperlinks(1).Address
End Function
finiki, 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
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
Re: How to download data?
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.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 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
Importing dividend data into spreadsheet
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:
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)
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:
There is a new version of this called GetQuoteArby wrote:An add-in for downloading stock prices into an Open Office Calc spreadsheet is here. Has anyone used this add-in?
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)
Re: Importing dividend data into spreadsheet
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.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:There is a new version of this called GetQuoteArby wrote:An add-in for downloading stock prices into an Open Office Calc spreadsheet is here. Has anyone used this add-in?
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)
One other thing - Yahoo no longer cover Canadian mutual funds.
- Peculiar_Investor
- Administrator
- Posts: 13267
- Joined: 01 Mar 2005 14:52
- Location: Calgary
- Contact:
Re: How to download data?
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.
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.
finiki, 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
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
Re: How to download data?
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)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.
- northbynorthwest
- Contributor
- Posts: 165
- Joined: 19 Jan 2007 01:07
- Location: Vancouver
Re: How to download data?
Chufinora,chufinora wrote: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)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.
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.
Re: How to download data?
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.
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.
Re: How to download data?
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.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.
Re: How to download data?
Many thanks that is very useful!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.
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.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.
Re: How to download data?
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.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.
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".
- Peculiar_Investor
- Administrator
- Posts: 13267
- Joined: 01 Mar 2005 14:52
- Location: Calgary
- Contact:
Re: How to download data?
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".Springbok wrote:I messed with automatic updates, but really why bother when on-line broker does it for you?
finiki, 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
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
- northbynorthwest
- Contributor
- Posts: 165
- Joined: 19 Jan 2007 01:07
- Location: Vancouver
Re: How to download data?
Like you, chufinora, I like the ability to track performance etc. for all accounts combined.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.
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: 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.
Re: How to download data?
Sorry - It comes from a career in formal and informal problem solving.Peculiar_Investor wrote: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".Springbok wrote:I messed with automatic updates, but really why bother when on-line broker does it for you?
One question was always "what else could we do" and another "what if we don't do anything at all!"
Re: How to download data?
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?
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?
- Shakespeare
- Veteran Contributor
- Posts: 23396
- Joined: 15 Feb 2005 23:25
- Location: Calgary, AB
Re: How to download data?
=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
Re: How to download data?
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 ?Shakespeare wrote: ↑13 Jun 2017 16:16 =VALUE(GETYAHOO($A3,21)) works with LibreOffice, where A3 has something like RY.TO
- Shakespeare
- Veteran Contributor
- Posts: 23396
- Joined: 15 Feb 2005 23:25
- Location: Calgary, AB
Re: How to download data?
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))
=VALUE(GETYAHOO("FTN-PA.TO",21))
Sic transit gloria mundi. Tuesday is usually worse. - Robert A. Heinlein, Starman Jones
Re: How to download data?
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?