How to download data?
- Shakespeare
- Veteran Contributor
- Posts: 23396
- Joined: 15 Feb 2005 23:25
- Location: Calgary, AB
Re: How to download data?
Modified my 1-2-3 spreadsheet today to create a version that reads Globeinvestor data - imports the csv file, looks up the price and dividend, calculates the date (which is in the wrong format). It works.
Sic transit gloria mundi. Tuesday is usually worse. - Robert A. Heinlein, Starman Jones
Re: How to download data?
O-kay. Then why would I want to bother with it if it only knows how to access data from Yahoo and Yahoo data stopped being available a while back?
Shakespeare wrote: ↑09 Nov 2017 17:40
No, it uses the Yahoo portfolio view. Look at the example spreadsheet.
- Shakespeare
- Veteran Contributor
- Posts: 23396
- Joined: 15 Feb 2005 23:25
- Location: Calgary, AB
Re: How to download data?
Because the yahoo portfolio view works. Download the spreadsheet.
Sic transit gloria mundi. Tuesday is usually worse. - Robert A. Heinlein, Starman Jones
- Shakespeare
- Veteran Contributor
- Posts: 23396
- Joined: 15 Feb 2005 23:25
- Location: Calgary, AB
Re: How to download data?
For those of you using Globeinvestor, CADUSX is dead. Use XDC.
Sic transit gloria mundi. Tuesday is usually worse. - Robert A. Heinlein, Starman Jones
Re: How to download data?
shakespeare:
Which spreadsheet are you suggesting I download? Mention was made of several of them, none of which seem to be in Excel.
I'm also looking at the FinAnsU plugin. It looks helpful, but there has been no development activity on it in years.
Which spreadsheet are you suggesting I download? Mention was made of several of them, none of which seem to be in Excel.
I'm also looking at the FinAnsU plugin. It looks helpful, but there has been no development activity on it in years.
- Shakespeare
- Veteran Contributor
- Posts: 23396
- Joined: 15 Feb 2005 23:25
- Location: Calgary, AB
Re: How to download data?
SMF Add-in Announcements: smfGetYahooPortfolioView()
The excel spreadsheet is smfGetBarchartPortfolioView-Example.xls
The excel spreadsheet is smfGetBarchartPortfolioView-Example.xls
Sic transit gloria mundi. Tuesday is usually worse. - Robert A. Heinlein, Starman Jones
Re: How to download data?
I am not clear on how to enter the right code to get the data I want. I want to fetch from Google and just get the current market price of a stock and the stock's current dividend.
I read through the documentation briefly, but I must be too slow to get it. Plugin appears to be working correctly, I think. How do I retrieve current price and current annual dividend? I can't even find an element defition for close or current price or similar in the text files.
FinAnsU looks perhaps a little simpler for Excel novices like me, no?
I read through the documentation briefly, but I must be too slow to get it. Plugin appears to be working correctly, I think. How do I retrieve current price and current annual dividend? I can't even find an element defition for close or current price or similar in the text files.
FinAnsU looks perhaps a little simpler for Excel novices like me, no?
Re: How to download data?
Hmm.....I tried getting quotes with the SMF add-in example Excel workbook. No quotes showed up. The plugin is loaded successfully.
Re: How to download data?
I will gently repeat my prior suggestion. Consider Quicken as a 'fix all' solution for yourself. Sometimes it is much better for your health to actually buy the car rather than trying to build one yourself.
finiki, the Canadian financial wiki The go-to place to bolster your financial freedom
Re: How to download data?
AltaRed:
While I usually take your posts very seriously, I am not interested in Quicken or any commercial software. MS Money might have been an exception, but it is no longer actively developed.
1. I want something I can customize in Excel.
2. As an ex-IT technician, I got tired of trying to fix and listening to clients complain about endless technical and other problems with Quicken.
3. I got tired of trying to represent said IT clients when Intuit would treat them like garbage. Constant upgrade fees, data corruption
and endless migration issues. Intuit staff I spoke with on the phone were rarely if ever helpful. No thank you to "Slowen".
So, if I were going to go to a full finance software, I'd use KMyMonney. It's free, powerful and doesn't require an of Intuit's typical B*llshit.
While I usually take your posts very seriously, I am not interested in Quicken or any commercial software. MS Money might have been an exception, but it is no longer actively developed.
1. I want something I can customize in Excel.
2. As an ex-IT technician, I got tired of trying to fix and listening to clients complain about endless technical and other problems with Quicken.
3. I got tired of trying to represent said IT clients when Intuit would treat them like garbage. Constant upgrade fees, data corruption
and endless migration issues. Intuit staff I spoke with on the phone were rarely if ever helpful. No thank you to "Slowen".
So, if I were going to go to a full finance software, I'd use KMyMonney. It's free, powerful and doesn't require an of Intuit's typical B*llshit.
Re: How to download data?
Fair enough...with one comment. You no doubt know Intuit doesn't own Quicken any more, albeit it doesn't mean it necessarily will get better as a standalone product, hype notwithstanding. https://www.computerworld.com/article/3 ... -days.html
finiki, the Canadian financial wiki The go-to place to bolster your financial freedom
Re: How to download data?
Yes, I knew that. But from what I heard, the same people are providing frontline support and if I never have to speak with one of those people again, it won't be soon enough.
We won't even talk about Quickbooks support.
We won't even talk about Quickbooks support.
Re: How to download data?
I can understand why you wouldn't want to use Quicken. I gave up on Quicken when it gave up on the Mac many moons ago, I didn't find it very good for investment tracking - different strokes for different folks.
What I don't get is why you are trying so hard for an Excel solution, the Google Spreadsheets are a superior spreadsheet for investment tracking due to the rich Google Finance functions. The spreadsheet I posted up-thread gets you started, and there are plenty of other finance functions that work without all the complicated mess of pulling and parsing data fields from external sources.
What I don't get is why you are trying so hard for an Excel solution, the Google Spreadsheets are a superior spreadsheet for investment tracking due to the rich Google Finance functions. The spreadsheet I posted up-thread gets you started, and there are plenty of other finance functions that work without all the complicated mess of pulling and parsing data fields from external sources.
Re: How to download data?
Does Google spreadsheets provide historical stock prices? The "price history" link on the Finance pages vanished a few weeks back. Can you "fix" the errors that appear on the graphs - double-entered stock splits, missing dividends, etc?
For sure Yahoo isn't perfect - but it seems to have far fewer errors like these ...
For sure Yahoo isn't perfect - but it seems to have far fewer errors like these ...
Peter
Patrick Hutber: Improvement means deterioration
Patrick Hutber: Improvement means deterioration
Re: How to download data?
Yes Google provides historical stock prices
GOOGLEFINANCE("GOOG", "price", DATE(2014,1,1), DATE(2014,12,31), "DAILY")
GOOGLEFINANCE("GOOG","price",TODAY()-30,TODAY())
from https://support.google.com/docs/answer/3093281?hl=en that also gives a list of the finance functions available
Not sure what you mean, but I think you are confusing Google Finance viewing with Google spreadsheet finance functions
Re: How to download data?
From your link abovechufinora wrote: ↑12 Nov 2017 20:53Yes Google provides historical stock prices
GOOGLEFINANCE("GOOG", "price", DATE(2014,1,1), DATE(2014,12,31), "DAILY")
GOOGLEFINANCE("GOOG","price",TODAY()-30,TODAY())
from https://support.google.com/docs/answer/3093281?hl=en that also gives a list of the finance functions availableNot sure what you mean, but I think you are confusing Google Finance viewing with Google spreadsheet finance functions
... so not an unreasonable assumption.GOOGLEFINANCE Fetches current or historical securities information from Google Finance
Tested with MRU, for which Google Finance incorrectly records two splits in Feb 2015. GOOGLEFINANCE repeats that error.
I also don't see dividend payments being available in the historical data?
Peter
Patrick Hutber: Improvement means deterioration
Patrick Hutber: Improvement means deterioration
- Peculiar_Investor
- Administrator
- Posts: 13269
- Joined: 01 Mar 2005 14:52
- Location: Calgary
- Contact:
Re: How to download data?
I was traveling and offline for the past week or so and am just catching up on this topic since I posted the original links to the SMF Add-in and their example spreadsheet. It continues to work properly for me with Excel 2010 on Windows 10. I'm currently using Version 2017.11.11 of the SMF Add-in.
What version of Excel are you using? On what OS?
Are you still having problems getting quotes using the SMF Add-in? Have you tried the smfGetYahooPortfolioView-Example.xls example file?
Have you looked over SMF Add-In --Tips and FAQS -- Installation, particularly the Special Notes section.
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?
This works, I've fixed my portfolio tracking spreadsheet, took about half an hour, thanks!Peculiar_Investor wrote: ↑16 Nov 2017 09:10 Have you tried the smfGetYahooPortfolioView-Example.xls example file?
Have you looked over SMF Add-In --Tips and FAQS -- Installation, particularly the Special Notes section.
finiki, the Canadian financial wiki: a knowledge base of financial subjects written from a Canadian perspective
Re: How to download data?
I have quotes downloading now. In the sample worksheet there are spaces between some rows I would like to get rid of the spaces. Is it a simple fix to get rid of the spaces? How do I do it? For example row 8 is blank when I paste ticker symbols into it I want NA info in that row.
NA NA -- -- -- -- -- -- -- -- -- -- -- NMS ECNQUOTE -- --
If I try to delete the row it says "can't change part of an array" there are numerous rows which show no down loaded data?
LOL, never mind!!!!!!!!
NA NA -- -- -- -- -- -- -- -- -- -- -- NMS ECNQUOTE -- --
If I try to delete the row it says "can't change part of an array" there are numerous rows which show no down loaded data?
LOL, never mind!!!!!!!!
Last edited by BRIAN5000 on 17 Nov 2017 16:09, edited 1 time in total.
This information is believed to be from reliable sources but may include rumor and speculation. Accuracy is not guaranteed
Re: How to download data?
Simple? Not really, but doable.BRIAN5000 wrote: ↑17 Nov 2017 15:48 I have quotes downloading now. In the sample worksheet there are spaces between some rows I would like to get rid of the spaces. Is it a simple fix to get rid of the spaces? How do I do it? For example row 8 is blank when I paste ticker symbols into it I want NA info in that row.
NA NA -- -- -- -- -- -- -- -- -- -- -- NMS ECNQUOTE -- --
If I try to delete the row it says "can't change part of an array" there are numerous rows which show no down loaded data?
From what I can tell, you need to delete the array (highlight its cells and delete it) and then "re-add" it again by doing this:
1) Select the cells where the array was.
2) With that array highlighted, (re-)enter the smf function you're using in the formula bar and then press <Ctrl> + <Shift> + <Enter>. (This is important -- because just pressing <Enter> alone will not work.)
May not be the best way, but it worked for me.
- Peculiar_Investor
- Administrator
- Posts: 13269
- Joined: 01 Mar 2005 14:52
- Location: Calgary
- Contact:
Using the SMF Add-in example spreadsheet
The sample SMF Add-in Excel workbook works as follows based on my review/understanding. In the smfGetYahooPortfolioView worksheet, column B and row 2 contain the user changeable inputs. Column B is where one enters the ticker symbols that you are following. Row 2 contains the fields to be retrieved. The Field Number worksheet documents the valid field numbers, i.e. Field Number 01 returns the "Symbol", Field Number 15 returns the "Last Price".
So to change what's returned, change the columns in row 2, there is no need to change/edit the array formula.
In my own usage, my Holdings worksheet, I'm using VLOOKUP to match to the Ticker field on the smfGetYahooPortfolioView worksheet and returning the columns of interest.
Hope this helps.
So to change what's returned, change the columns in row 2, there is no need to change/edit the array formula.
In my own usage, my Holdings worksheet, I'm using VLOOKUP to match to the Ticker field on the smfGetYahooPortfolioView worksheet and returning the columns of interest.
Hope this helps.
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?
The lines not returning any info we're not seeing the ticker symbol in column B as a correct symbol. Example NA changed to NA.TO and Bob's your uncle. Looks good will hold 411 stocks and lots of stock data.(columns)
This information is believed to be from reliable sources but may include rumor and speculation. Accuracy is not guaranteed
Re: How to download data?
I actually did try the sample, but I didn't understand how it worked.
I think I may have finally figured out how to get data. What data provider is reliable for market price (or close, if that's not available). I couldn't find that for Google. Funny, cause isn't that the most basic thing people would want?
I think I may have finally figured out how to get data. What data provider is reliable for market price (or close, if that's not available). I couldn't find that for Google. Funny, cause isn't that the most basic thing people would want?
Re: How to download data?
Spreadsheet seems to be working ok except how do request a reload of new/current info? On Gummies spreadsheet there was a button to click on must be something easy?
This information is believed to be from reliable sources but may include rumor and speculation. Accuracy is not guaranteed
Re: How to download data?
Easiest way is just to change a cell. Doesn't have to be part of the data. That will force a reload.
Peter
Patrick Hutber: Improvement means deterioration
Patrick Hutber: Improvement means deterioration