[Yahoo Finance no longer works - Alternatives to download securities quotes]

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
User avatar
bertilak
Posts: 5669
Joined: Tue Aug 02, 2011 5:23 pm
Location: East of the Pecos, West of the Mississippi

[Yahoo Finance no longer works - Alternatives to download securities quotes]

Post by bertilak » Wed Nov 16, 2016 9:47 am

[2016 thread merged into here, see below, Page 5 --admin LadyGeek]

Yeah, I know this has been discussed many times before. I have even solved this problem to my satisfaction but the solution depends on Yahoo's financial web page, specifically (for example) the following will get the latest quote for VTSAX. It can be used as an argument to Excel's WEBSERVICE function.
My problem with this is that the URL above is no longer given on Yahoo's web page (that I can find). They used to have a link like that on each ticker's page but no longer do. I don't know how much longer this will work.

There are many OTHER solutions on the web (macros, add-ons, etc.) but when you look into them most of them are simply wrappers for the above URL. Installing the add-on just adds a unnecessary layer of complexity. Also, most of the stuff on the web is several years old. A lot of what is discussed are problems with those wrappers failing as Excel gets updated -- not something I want to bother with!

Yes, I know there are online (e.g. Google) spreadsheets with this capability but MY spreadsheet is an old-fashioned Excel spreadsheet that runs on my computer. I'm not looking to change that.

So, I don't like being dependent on a single, apparently unofficial, solution. Another, more robust, solution would be welcome. Anyone know of one?
Listen very carefully. I shall say this only once. (There! I've said it.)

pshonore
Posts: 5643
Joined: Sun Jun 28, 2009 2:21 pm

Re: Download quotes to excel spreadsheet

Post by pshonore » Wed Nov 16, 2016 10:00 am

Here's a simple function to retrieve Price. Definitely works in Office 2007 and 2010 and here is also version for 2013

http://www.parago.de/blog/2009/01/07/up ... -2007.html

User avatar
bertilak
Posts: 5669
Joined: Tue Aug 02, 2011 5:23 pm
Location: East of the Pecos, West of the Mississippi

Re: Download quotes to excel spreadsheet

Post by bertilak » Wed Nov 16, 2016 10:07 am

pshonore wrote:Here's a simple function to retrieve Price. Definitely works in Office 2007 and 2010 and here is also version for 2013

http://www.parago.de/blog/2009/01/07/up ... -2007.html

If you look inside all that you will find that the heart of it contains:

    Stream data = client.OpenRead("http://download.finance.yahoo.com/d/quotes.csv?s=" + symbol.Trim() + "&f=sl1d1t1n");
There is one heck of a lot of fluff wrapped around that!
Listen very carefully. I shall say this only once. (There! I've said it.)

EHEngineer
Posts: 611
Joined: Sat Feb 28, 2015 4:35 pm

Re: Download quotes to excel spreadsheet

Post by EHEngineer » Wed Nov 16, 2016 10:18 am

bertilak wrote:So, I don't like being dependent on a single, apparently unofficial, solution. Another, more robust, solution would be welcome. Anyone know of one?


Design: My excel portfolio workbook has a hyperlink to a web-published google sheet of price quotes. I use copy/paste to update excel.

Process to update prices:
1) click hyperlink in excel (webpage with prices loads)
2) Select all
3) copy
4) click excel spreadsheet
5) paste

I have the tickers in alphabetical order (in the google sheet), then use vlookup (in excel) to grab the correct price for each holding.

I started using this copy/paste method with excel's web functions stopped working several years ago. so far so good.

edit to add an example: https://docs.google.com/spreadsheets/d/ ... Fk/pubhtml
Last edited by EHEngineer on Wed Nov 16, 2016 10:28 am, edited 2 times in total.
Or, you can ... decline to let me, a stranger on the Internet, egg you on to an exercise in time-wasting, and you could say "I'm probably OK and I don't care about it that much." -Nisiprius

User avatar
bertilak
Posts: 5669
Joined: Tue Aug 02, 2011 5:23 pm
Location: East of the Pecos, West of the Mississippi

Re: Download quotes to excel spreadsheet

Post by bertilak » Wed Nov 16, 2016 10:26 am

EHEngineer wrote:
bertilak wrote:So, I don't like being dependent on a single, apparently unofficial, solution. Another, more robust, solution would be welcome. Anyone know of one?


Design: My excel portfolio workbook has a hyperlink to a web-published google sheet of price quotes. Use copy/paste to update excel.

Process to update prices:
1) click hyperlink in excel (webpage with prices loads)
2) Select all
3) copy
4) click excel spreadsheet
5) paste

I have the tickers in alphabetical order (in the google sheet), then use vlookup (in excel) to grab the correct price for each holding.

I started using this copy/paste method with excel's web functions stopped working several years ago. so far so good.

That could be a plan B but with what I have now it just happens automatically. Every time I open the spreadsheet the current prices are there. I have it arranged so the new prices don't replace the old but show up in an adjacent column, with another column showing the changes. There is a button (labeled "capture") that copies over the new prices.
Listen very carefully. I shall say this only once. (There! I've said it.)

EHEngineer
Posts: 611
Joined: Sat Feb 28, 2015 4:35 pm

Re: Download quotes to excel spreadsheet

Post by EHEngineer » Wed Nov 16, 2016 10:31 am

bertilak wrote:That could be a plan B but with what I have now it just happens automatically. Every time I open the spreadsheet the current prices are there. I have it arranged so the new prices don't replace the old but show up in an adjacent column, with another column showing the changes. There is a button (labeled "capture") that copies over the new prices.


I had something similar, but the second time I had to fix it I went for simplicity. good luck. I'll be watching to see what you find.
Or, you can ... decline to let me, a stranger on the Internet, egg you on to an exercise in time-wasting, and you could say "I'm probably OK and I don't care about it that much." -Nisiprius

User avatar
Raybo
Posts: 1540
Joined: Tue Feb 20, 2007 11:02 am
Location: San Francisco
Contact:

Re: Download quotes to excel spreadsheet

Post by Raybo » Wed Nov 16, 2016 11:13 am

I found this page (http://www.financialwisdomforum.org/gum ... o-data.htm) that describes what all the codes are and other useful description of using this yahoo feature.
No matter how long the hill, if you keep pedaling you'll eventually get up to the top.

User avatar
TimeRunner
Posts: 1161
Joined: Sat Dec 29, 2012 9:23 pm

Re: Download quotes to excel spreadsheet

Post by TimeRunner » Wed Nov 16, 2016 11:24 am

bertilak wrote:Yes, I know there are online (e.g. Google) spreadsheets with this capability but MY spreadsheet is an old-fashioned Excel spreadsheet that runs on my computer. I'm not looking to change that.

So, I don't like being dependent on a single, apparently unofficial, solution. Another, more robust, solution would be welcome. Anyone know of one?
OK, but why not upload your Excel sheet to Google, and then simply replace your convoluted code with =googlefinance("VTIAX") or whatever you are looking to get a current price for. The sheet runs in Google, and you can use any current and most future devices (phone? tablet? electronic yoga mat?) to use it. And you won't have to keep Excel around or updated either. Something to re-consider.
One cannot enlighten the unconscious. | Endurance athletes are the Bogleheads of sports. | "I like people - I just don't want to be around 'em." - Russell Gordy

User avatar
dumbbunny
Posts: 791
Joined: Thu Apr 24, 2014 10:00 pm
Location: Oregon coast

Re: Download quotes to excel spreadsheet

Post by dumbbunny » Wed Nov 16, 2016 11:45 am

Last edited by dumbbunny on Wed Nov 16, 2016 12:12 pm, edited 1 time in total.
“It’s the curse of old men to realize that in the end we control nothing." "Homeland" episode, "Gerontion"

mb2016
Posts: 13
Joined: Wed Aug 24, 2016 8:30 am

Re: Download quotes to excel spreadsheet

Post by mb2016 » Wed Nov 16, 2016 12:10 pm

It's a bit clunky but the MSN Money web query still works for some versions of Excel. I've used it on Excel 2007. I cannot vouch for its security.
http://www.thewindowsclub.com/get-stock-quotes-excel

CFM300
Posts: 1226
Joined: Sat Oct 27, 2007 5:13 am

Re: Download quotes to excel spreadsheet

Post by CFM300 » Wed Nov 16, 2016 12:33 pm

mb2016 wrote:It's a bit clunky but the MSN Money web query still works for some versions of Excel. I've used it on Excel 2007. I cannot vouch for its security.
http://www.thewindowsclub.com/get-stock-quotes-excel

That's the method I use in Excel 2010.

Unfortunately, the prices are not always current. Meaning, it could be 10pm EST and some of the funds still reflect the previous day's price.

User avatar
bertilak
Posts: 5669
Joined: Tue Aug 02, 2011 5:23 pm
Location: East of the Pecos, West of the Mississippi

Re: Download quotes to excel spreadsheet

Post by bertilak » Wed Nov 16, 2016 4:43 pm

Raybo wrote:I found this page (http://www.financialwisdomforum.org/gum ... o-data.htm) that describes what all the codes are and other useful description of using this yahoo feature.

Yup. Handy info. Good to have that page posted here for reference.
Listen very carefully. I shall say this only once. (There! I've said it.)

User avatar
bertilak
Posts: 5669
Joined: Tue Aug 02, 2011 5:23 pm
Location: East of the Pecos, West of the Mississippi

Re: Download quotes to excel spreadsheet

Post by bertilak » Wed Nov 16, 2016 4:48 pm

CFM300 wrote:
mb2016 wrote:It's a bit clunky but the MSN Money web query still works for some versions of Excel. I've used it on Excel 2007. I cannot vouch for its security.
http://www.thewindowsclub.com/get-stock-quotes-excel

That's the method I use in Excel 2010.

Unfortunately, the prices are not always current. Meaning, it could be 10pm EST and some of the funds still reflect the previous day's price.

I am able to use that as follows:

    http://www.msn.com/en-us/money/quoteslookup?SYMBOL=VTSAX
That gives me a web page with all kinds of info. The value I want (the quote, e.g. 54.77) is buried in the HTML with no obvious way of extracting it. There might be keywords that will simply return the 54.77 all by itself.

Are there directions somewhere on how to use it this way? If all I want to do is look at a bunch of stock info on (an image of) a web page I don't need a spreadsheet to do it. I can just go to the web page directly.
Last edited by bertilak on Wed Nov 16, 2016 5:10 pm, edited 1 time in total.
Listen very carefully. I shall say this only once. (There! I've said it.)

User avatar
telemark
Posts: 2057
Joined: Sat Aug 11, 2012 6:35 am

Re: Download quotes to excel spreadsheet

Post by telemark » Wed Nov 16, 2016 4:58 pm

I just tried the URL you gave in a browser and it created a file named quotes containing 54.77. Maybe it no longer works with Excel? But that seems odd.

earlyout
Posts: 1310
Joined: Tue Feb 20, 2007 5:24 pm

Re: Download quotes to excel spreadsheet

Post by earlyout » Wed Nov 16, 2016 5:08 pm

bertilak wrote:
Raybo wrote:I found this page (http://www.financialwisdomforum.org/gum ... o-data.htm) that describes what all the codes are and other useful description of using this yahoo feature.

Yup. Handy info. Good to have that page posted here for reference.

This same information is available in our Wiki - look for "Gummy Stuff". Gummy was a frequent contributor to BHs and its precusor forum on Morningstar and his financial calculations and explanations have often been discussed here on the forum. IIRC I've been using his method for pulling quotes from Yahoo into Excel for almost 10 years.

User avatar
bertilak
Posts: 5669
Joined: Tue Aug 02, 2011 5:23 pm
Location: East of the Pecos, West of the Mississippi

Re: Download quotes to excel spreadsheet

Post by bertilak » Wed Nov 16, 2016 5:13 pm

telemark wrote:I just tried the URL you gave in a browser and it created a file named quotes containing 54.77. Maybe it no longer works with Excel? But that seems odd.

The 54.77 is exactly what I want. That's the price I want to plug into my spreadsheet. That URL works perfectly. The WEBSERVICE function plops the 54.77 right into the cell having the WEBSERVICE function. No extraneous "quotes" file is left lying around. Perhaps some variation of the above MS Money URL can be made to do the same thing -- just return 54.77 and nothing else.
Listen very carefully. I shall say this only once. (There! I've said it.)

User avatar
telemark
Posts: 2057
Joined: Sat Aug 11, 2012 6:35 am

Re: Download quotes to excel spreadsheet

Post by telemark » Wed Nov 16, 2016 5:49 pm

bertilak wrote:
telemark wrote:I just tried the URL you gave in a browser and it created a file named quotes containing 54.77. Maybe it no longer works with Excel? But that seems odd.

The 54.77 is exactly what I want. That's the price I want to plug into my spreadsheet. That URL works perfectly. The WEBSERVICE function plops the 54.77 right into the cell having the WEBSERVICE function. No extraneous "quotes" file is left lying around. Perhaps some variation of the above MS Money URL can be made to do the same thing -- just return 54.77 and nothing else.

My mistake. Somehow I got the idea it had stopped working for you.

User avatar
pondering
Posts: 847
Joined: Fri Jan 30, 2015 11:04 pm
Location: 412-977-3526, originally 718-273-2422

Re: Download quotes to excel spreadsheet

Post by pondering » Wed Nov 16, 2016 7:41 pm

I used the GUI to create an external data link.

Data -> From Web

I entered the url and ask that the data range be refreshed when opening the spreadsheet.

Obviously if your url changes you will need to find another one that gets you the data you want.
--Robert Sterbal | 412-977-3526 call/text

User avatar
bertilak
Posts: 5669
Joined: Tue Aug 02, 2011 5:23 pm
Location: East of the Pecos, West of the Mississippi

Re: Download quotes to excel spreadsheet

Post by bertilak » Wed Nov 16, 2016 8:00 pm

pondering wrote:I used the GUI to create an external data link.

Data -> From Web

I entered the url and ask that the data range be refreshed when opening the spreadsheet.

Obviously if your url changes you will need to find another one that gets you the data you want.


What I am looking for is an ALTERNATIVE way to access quotes that doesn't rely on that URL. That Yahoo URL doesn't give me a great deal of confidence that it won't be discontinued or that Yahoo will suddenly do things in a different, undocumented, unsupported, way that I won't be able to figure out. It is a single point of failure and makes me uneasy.

I note that Yahoo DID change that URL in the past but since their website had a link I was able to discover the new URL. I can no longer find a link on their website so feel exposed. This is the point of my OP. I want to prepare for if and when that URL stops working.
Listen very carefully. I shall say this only once. (There! I've said it.)

jhd1945
Posts: 39
Joined: Sat Jul 24, 2010 5:04 pm

Re: Download quotes to excel spreadsheet

Post by jhd1945 » Wed Nov 16, 2016 8:13 pm

I set up a Watch list on Schwab then copy it to an excel worksheet that feeds to the excel portfolio.

CFM300
Posts: 1226
Joined: Sat Oct 27, 2007 5:13 am

Re: Download quotes to excel spreadsheet

Post by CFM300 » Wed Nov 16, 2016 10:40 pm

bertilak wrote:
    http://www.msn.com/en-us/money/quoteslookup?SYMBOL=VTSAX
That gives me a web page with all kinds of info. The value I want (the quote, e.g. 54.77) is buried in the HTML with no obvious way of extracting it. There might be keywords that will simply return the 54.77 all by itself.

You don't need to use that or any other URL. Just follow the directions on the page:

http://www.thewindowsclub.com/get-stock-quotes-excel

The result will be a well-structured page of information exactly as shown in the first image of the tutorial. Just have the imported data go on a separate sheet. The price for any particular fund or stock will always be in the exact same cell on the sheet, so you can reference that cell in formulas. I.e., pull the value into whatever cell you want on your original sheet.

The only thing I did differently than the tutorial was to supply a range of cell addresses containing my ticker codes, rather than a literal list as shown in the last animated gif.

AlohaJoe
Posts: 2556
Joined: Mon Nov 26, 2007 2:00 pm
Location: Saigon, Vietnam

Re: Download quotes to excel spreadsheet

Post by AlohaJoe » Wed Nov 16, 2016 10:45 pm

bertilak wrote:What I am looking for is an ALTERNATIVE way to access quotes that doesn't rely on that URL. That Yahoo URL doesn't give me a great deal of confidence that it won't be discontinued or that Yahoo will suddenly do things in a different, undocumented, unsupported, way that I won't be able to figure out. It is a single point of failure and makes me uneasy.


I think you are worrying about something that hasn't happened yet :beer . Most other sources you pick will have the same exact issues. You could pick Google Finance...

Image

But it isn't like you have a signed contract promising an unchanging URL with backwards compatibility forever. It could be discontinued. Or Google could suddenly do things in a different way that you can't figure out.

You could sign up to a service like EODData -- http://eoddata.com/products/default.aspx. Most of those kinds of services are targeted at traders and institutions that are willing to pay money. NASDAQ offers a Data On Demand service that starts at $2,400 a month. If you dig you might find some others that offer a free version like EODData does, though.

Personally, I would fix this if it breaks but not before. (I should note that professionally I have substantial experience building multi-million dollar products based on web apis both proprietary and offered by third-parties so I'm relatively familiar with the tradeoffs involved; not that you should always trust random strangers on the internet!)

My problem with this is that the URL above is no longer given on Yahoo's web page (that I can find). They used to have a link like that on each ticker's page but no longer do. I don't know how much longer this will work.


In any case, Yahoo does still have a link on each ticker page.

Image

It points to charts.yahoo.com, which is different than the URL you're using, which suggests that Yahoo is already doing a pretty decent job of maintaining backwards compatibility. To me, that means your efforts are likely premature.

User avatar
bertilak
Posts: 5669
Joined: Tue Aug 02, 2011 5:23 pm
Location: East of the Pecos, West of the Mississippi

Re: Download quotes to excel spreadsheet

Post by bertilak » Thu Nov 17, 2016 10:14 am

AlohaJoe wrote:
Image

It points to charts.yahoo.com, which is different than the URL you're using, which suggests that Yahoo is already doing a pretty decent job of maintaining backwards compatibility. To me, that means your efforts are likely premature.


Thanks for the pointer, but it does illustrate the problem I am hoping to prepare for. That link returns the data in a format that would require me to rewrite my spreadsheet but it does not make any promise that a year from now I won't need to go through another redesign. I would be much happier if Yahoo published something describing the supported use of that URL.
Listen very carefully. I shall say this only once. (There! I've said it.)

User avatar
pondering
Posts: 847
Joined: Fri Jan 30, 2015 11:04 pm
Location: 412-977-3526, originally 718-273-2422

Re: Download quotes to excel spreadsheet

Post by pondering » Thu Nov 17, 2016 10:18 pm

--Robert Sterbal | 412-977-3526 call/text

earlyout
Posts: 1310
Joined: Tue Feb 20, 2007 5:24 pm

Re: Download quotes to excel spreadsheet

Post by earlyout » Sat Nov 19, 2016 6:48 pm

Looking through some other information I ran across some Excel functions called SMF ADDIN (Stock Market Functions) that are authored and maintained by a Yahoo group. They started out to create a function for pulling quotes from the web and have expanded into other stock market functions. Apparently they are very active. I have not tried their functions so I can't say how well they work.

If you google "SMF ADDIN" you'll find numerous references.

User avatar
Peculiar_Investor
Posts: 959
Joined: Thu Oct 20, 2011 12:23 am
Location: Calgary, AB
Contact:

Re: Download quotes to excel spreadsheet

Post by Peculiar_Investor » Sun Nov 20, 2016 9:56 am

earlyout wrote:Looking through some other information I ran across some Excel functions called SMF ADDIN (Stock Market Functions) that are authored and maintained by a Yahoo group. They started out to create a function for pulling quotes from the web and have expanded into other stock market functions. Apparently they are very active. I have not tried their functions so I can't say how well they work.

If you google "SMF ADDIN" you'll find numerous references.

I've used the SMF (Stock Market Functions) add-in for years and can confirm the SMF Add-in works very well and is well maintained and updated whenever a data source provide makes changes. The SMF add-in group have developed many useful templates that can be used to either implement and/or learn a technique that might fit your own needs.

There is a previous topic, Vertex42 Excel Stock Quote Template stop working, that covers some options, including the SMF add-in. If you search the forum for SMF you'll find a number of mentions.

My experience over a number of years maintaining spreadsheets that download quotes from free data sources is you get what you paid for. You will have to periodically adapt as a data source provider (Yahoo, Microsoft) makes changes to what they make available and where (URL) you find the information. That's the price for getting the data source for free. The other price is there is no feedback loop and the accuracy of the data needs to be trusted but verified. Yahoo! Finance is known to have problems with a number of newer Vanguard ETF prices outside of market hours.
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
GKSD
Posts: 207
Joined: Sat Mar 19, 2016 9:01 am

Re: Download quotes to excel spreadsheet

Post by GKSD » Sun Nov 20, 2016 10:03 am

CFM300 wrote:You don't need to use that or any other URL. Just follow the directions on the page:
http://www.thewindowsclub.com/get-stock-quotes-excel


This is what I also use. Gets the job done.

User avatar
oldcomputerguy
Posts: 1989
Joined: Sun Nov 22, 2015 6:50 am
Location: In the middle of five acres of woods

[Yahoo Finance no longer works - Alternatives to download securities quotes]

Post by oldcomputerguy » Wed Feb 01, 2017 10:48 am

I use the SMF extension in LibreOffice to pull market quotes. This morning, for some reason, LibreOffice Calc is hanging whenever I try to call up a spreadsheet that uses this extension. I end up having to forcibly quit Calc. Is it just me, or is anyone else having trouble with the GETYAHOO function in Calc / Excel? :confused
Anybody know why there's a 20-pound frozen turkey up in the light grid?

User avatar
LadyGeek
Site Admin
Posts: 41053
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: GETYAHOO broken? [Spreadsheet extension]

Post by LadyGeek » Wed Feb 01, 2017 4:42 pm

This thread is now in the Personal Consumer Issues forum (spreadsheet). I also retitled the thread.
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.

Tanelorn
Posts: 1298
Joined: Thu May 01, 2014 9:35 pm

Re: GETYAHOO broken? [Spreadsheet extension]

Post by Tanelorn » Wed Feb 01, 2017 6:01 pm

Exporting Yahoo quotes has been glitchy for some time now as Yahoo has been screwing around with their Finance site (nearly all for the worse, I might add). Can you use Google Finance instead?

User avatar
oldcomputerguy
Posts: 1989
Joined: Sun Nov 22, 2015 6:50 am
Location: In the middle of five acres of woods

Re: GETYAHOO broken? [Spreadsheet extension]

Post by oldcomputerguy » Wed Feb 01, 2017 6:06 pm

Tanelorn wrote:Exporting Yahoo quotes has been glitchy for some time now as Yahoo has been screwing around with their Finance site (nearly all for the worse, I might add). Can you use Google Finance instead?

Is there a way to use Google Finance to scrape share price data into a spreadsheet? I'm certainly not wedded to Yahoo but I'm not aware of any alternatives.
Anybody know why there's a 20-pound frozen turkey up in the light grid?

Tanelorn
Posts: 1298
Joined: Thu May 01, 2014 9:35 pm

Re: GETYAHOO broken? [Spreadsheet extension]

Post by Tanelorn » Wed Feb 01, 2017 6:50 pm

smartinwate wrote:
Tanelorn wrote:Exporting Yahoo quotes has been glitchy for some time now as Yahoo has been screwing around with their Finance site (nearly all for the worse, I might add). Can you use Google Finance instead?

Is there a way to use Google Finance to scrape share price data into a spreadsheet? I'm certainly not wedded to Yahoo but I'm not aware of any alternatives.

It depends what you're trying to do. If you just want a single snapshot, you can create a Google docs spreadsheet and use the GoogleFinance function to get a stock's price. If you don't want to keep using the google spreadsheet, you'll need some way to import the prices into your program but I don't know about that so I can't help you. If you want to see an example, here's one we're using for the hedge fund stock picking contest:

viewtopic.php?f=10&t=206778

long_gamma
Posts: 186
Joined: Mon Sep 14, 2015 4:13 pm

Re: GETYAHOO broken? [Spreadsheet extension]

Post by long_gamma » Wed Feb 01, 2017 6:57 pm

You could try Quandl also.
https://www.quandl.com/

I believe 100 querries is free per day. But if you register and get a token, unlimited querries are allowed.
"Everyone has a plan 'till they get punched in the mouth." --Mike Tyson

madbrain
Posts: 4522
Joined: Thu Jun 09, 2011 5:06 pm
Location: San Jose, California

Re: GETYAHOO broken? [Spreadsheet extension]

Post by madbrain » Wed Feb 01, 2017 7:16 pm

smartinwate wrote:I use the SMF extension in LibreOffice to pull market quotes. This morning, for some reason, LibreOffice Calc is hanging whenever I try to call up a spreadsheet that uses this extension. I end up having to forcibly quit Calc. Is it just me, or is anyone else having trouble with the GETYAHOO function in Calc / Excel? :confused


This extension is still working for me today in Libreoffice 5.1.6.2 on Windows 10/x64.

LK2012
Posts: 169
Joined: Mon Apr 30, 2012 4:42 pm

Re: GETYAHOO broken? [Spreadsheet extension]

Post by LK2012 » Wed Feb 01, 2017 10:15 pm

It was working fine for me today. I use it in Excel 2003.

jasc15
Posts: 355
Joined: Wed Dec 19, 2012 1:36 pm

Excel Macro using Yahoo Finance- not working

Post by jasc15 » Fri May 12, 2017 3:30 pm

The Python thread reminded me. About a year ago I found an excel macro on the web that creates an function to grab stock prices from Yahoo Finance. The last few weeks, I've noticed that the macro no longer grabs those prices. Anyone else use this and notice that it isn't working?

penumbra
Posts: 255
Joined: Thu Mar 29, 2007 2:42 am

Re: Excel Macro using Yahoo Finance- not working

Post by penumbra » Fri May 12, 2017 3:40 pm

Yes, exactly the same experience. I had been using a sheet modified from Gummy's material, back when it was freely available. Stopped working around the first of the year. I'm not slick enough to troubleshoot the problem.

CantPassAgain
Posts: 430
Joined: Fri Mar 15, 2013 8:49 pm

Re: Excel Macro using Yahoo Finance- not working

Post by CantPassAgain » Fri May 12, 2017 4:10 pm

jasc15 wrote:The Python thread reminded me. About a year ago I found an excel macro on the web that creates an function to grab stock prices from Yahoo Finance. The last few weeks, I've noticed that the macro no longer grabs those prices. Anyone else use this and notice that it isn't working?
There's a data connection function that will pull stock prices from MSN. Click on Data>Connections>Add.

There should be a selection for "MSN MoneyCentral Investor Stock Quotes" all the way at the bottom. Click on it then click "open"

Go to properties and check enable background refresh and choose how often you want it to refresh, also if you want to refresh data when opening the file. Click OK and then close.

Then go to Data>Existing Connections and click "MSN MoneyCentral Investor Stock Quotes" all the way at the bottom and open.

The import data window will open, click ok, enter the ticker you want, then OK again and voila, there you go.

User avatar
Oicuryy
Posts: 1136
Joined: Thu Feb 22, 2007 10:29 pm

Re: Excel Macro using Yahoo Finance- not working

Post by Oicuryy » Fri May 12, 2017 7:40 pm

The macro in the first spreadsheet on this gummy-stuff page still works.

http://www.financialwisdomforum.org/gum ... o-data.htm

Ron
Money is fungible | Abbreviations and Acronyms

User avatar
whaleknives
Posts: 1210
Joined: Sun Jun 24, 2012 7:19 pm

Re: Excel Macro using Yahoo Finance- not working

Post by whaleknives » Fri May 12, 2017 7:43 pm

This is an argument for not trying to do your own aggregating, but to download CSV spreadsheets from providers instead.
"I'm an indexer. I own the market. And I'm happy." (John Bogle, "BusinessWeek", 8/17/07) ☕ Maritime signal flag W - Whiskey: "I require medical assistance."

User avatar
bertilak
Posts: 5669
Joined: Tue Aug 02, 2011 5:23 pm
Location: East of the Pecos, West of the Mississippi

Re: Excel Macro using Yahoo Finance- not working

Post by bertilak » Sat May 13, 2017 12:40 pm

Oicuryy wrote:The macro in the first spreadsheet on this gummy-stuff page still works.

http://www.financialwisdomforum.org/gum ... o-data.htm

Ron
I use Yahoo as well but recently found that the link given in the above-referenced URL no longer works.

I now use http://download.finance.yahoo.com/d/quotes?s= which does work.
Last edited by bertilak on Sun May 14, 2017 3:20 pm, edited 1 time in total.
Listen very carefully. I shall say this only once. (There! I've said it.)

User avatar
dratkinson
Posts: 3985
Joined: Thu Jul 26, 2007 6:23 pm
Location: Centennial CO

Re: Excel Macro using Yahoo Finance- not working

Post by dratkinson » Sun May 14, 2017 3:12 pm

Thought it was just me. As it seems to be affecting many, suspect yahoo has done something on their end. Why?

Thought I broke the macro so went looking for the original and found a letter from a yahoo lawyer telling gummy to "stop".
See original gummy page replaced by lawyer's letter: http://www.gummy-stuff.org/Yahoo-data.htm



Believe I may have found a PTF (permanent temporary fix). What?
--Copy/paste/submit marco's constructed URL (cell C1) in a browser window, and open returned CSV file in Excel (opens in new spreadsheet).
--Copy/paste extracted Excel data into our original spreadsheet.

Try it (right-click, open in new tab, then open with Excel as CSV):
http://quote.yahoo.com/d/quotes.csv?s=N ... c1p2d1t1jk

Which implies we could bookmark a webpage if our constructed URL is static.



Curiouser and curiouser. The results from the submitted URL is prevented from being opened by the Excel macro, but does work when submitted from a browser. How do yahoo know? Does Excel include a header identifying itself as the submitter?
Last edited by dratkinson on Sun May 14, 2017 3:23 pm, edited 1 time in total.
d.r.a, not dr.a. | I'm a novice investor, you are forewarned.

User avatar
bertilak
Posts: 5669
Joined: Tue Aug 02, 2011 5:23 pm
Location: East of the Pecos, West of the Mississippi

Re: Excel Macro using Yahoo Finance- not working

Post by bertilak » Sun May 14, 2017 3:17 pm

dratkinson wrote:As it seems to be affecting many, suspect yahoo has done something on their end. Why?
What they have done on their end is change the URL. Use the one I intended to post just above. I made a mistake and posted an old one which stopped working. Try http://download.finance.yahoo.com/d/quotes?s=. I corrected my above post.

Why did they do it? Perhaps they just liked it better that way.
Listen very carefully. I shall say this only once. (There! I've said it.)

User avatar
dratkinson
Posts: 3985
Joined: Thu Jul 26, 2007 6:23 pm
Location: Centennial CO

Re: Excel Macro using Yahoo Finance- not working

Post by dratkinson » Sun May 14, 2017 3:53 pm

bertilak wrote:
dratkinson wrote:As it seems to be affecting many, suspect yahoo has done something on their end. Why?
What they have done on their end is change the URL. Use the one I intended to post just above. I made a mistake and posted an old one which stopped working. Try http://download.finance.yahoo.com/d/quotes?s=. I corrected my above post.

Why did they do it? Perhaps they just liked it better that way.
Thanks, that worked. Now I can go back and replace all of the formatting overwritten when I copied/pasted the extracted CSV data.



Recall from gummy's posts that yahoo does occasionally change their URLs.

Do you have a link to the yahoo webpage in which they describe their format du jour.
--The link would be handy to include in a commented cell telling me where to check the next time gummy's macro stops working.
--Or maybe a better idea is to include the link in a cell that only becomes visible when yahoo's change blows away all requested data. :)
d.r.a, not dr.a. | I'm a novice investor, you are forewarned.

Gene S
Posts: 24
Joined: Mon Jul 27, 2015 12:27 pm

Re: Excel Macro using Yahoo Finance- not working

Post by Gene S » Tue May 16, 2017 8:09 pm

dratkinson's post gave a yahoo finance hyperlink that returned a cvs file of the current stock price for a bunch of stocks. Is it possible to get the end of day stock price for a specific day?

User avatar
dratkinson
Posts: 3985
Joined: Thu Jul 26, 2007 6:23 pm
Location: Centennial CO

Re: Excel Macro using Yahoo Finance- not working

Post by dratkinson » Tue May 16, 2017 8:59 pm

Gene S wrote:dratkinson's post gave a yahoo finance hyperlink that returned a cvs file of the current stock price for a bunch of stocks. Is it possible to get the end of day stock price for a specific day?
The link is the way gummy's macro constructed it. It's purpose is to get the day's closing prices. It's all I've ever needed.

However, I do recall seeing (while searching for what I might have done wrong that broke the macro) references to an Excel macro that could do more. You'll have to search for it. Could try searching for something like:
this: http://www.google.com/search?q=excel+ma ... tock+price
or this: http://www.google.com/search?q=excel+ma ... rice+range
d.r.a, not dr.a. | I'm a novice investor, you are forewarned.

User avatar
ThereAreNoGurus
Posts: 88
Joined: Fri Jan 24, 2014 11:41 pm

Re: Excel Macro using Yahoo Finance- not working

Post by ThereAreNoGurus » Tue May 16, 2017 9:09 pm

Just curious why you folks use Yahoo! Finance and an Excel spreadsheet when you can use Morningstar's free portfolio manager. You don't have to spend time downloading prices. Also when there are dividends or cap gains distributions they are very easy to post to your portfolio. Do these Excel macros have the same functionality or do you have to manually enter the info for each distribution?

JohnFiscal
Posts: 462
Joined: Mon Jan 06, 2014 4:28 pm
Location: USA

Re: Excel Macro using Yahoo Finance- not working

Post by JohnFiscal » Tue May 16, 2017 9:31 pm

ThereAreNoGurus wrote:Just curious why you folks use Yahoo! Finance and an Excel spreadsheet when you can use Morningstar's free portfolio manager. You don't have to spend time downloading prices. Also when there are dividends or cap gains distributions they are very easy to post to your portfolio. Do these Excel macros have the same functionality or do you have to manually enter the info for each distribution?
I have a large Excel workbook that integrates all my personal finances. I tried MS Money and Quicken in the 1990's but settled on Excel as my preference...still is.

I have made it easy on myself and have a Google spreadsheet that updates fund prices whenever it's opened, then I copy/paste into my Excel workbook; takes a few seconds. I have used various routines to work directly from Excel but I have never been pleased with the results, the data source, or the robustness.

My "piece de resistance" is a longish VBA routine that...assuming I have an open CSV format file open in Excel, as downloaded from Vanguard, with that month's (or other term) transactions... earning, dividends, etc, will read that file and copy all data to the corresponding account sheets in my workbook, update the IRR, do some other work, publish that month's Net Worth statement to pdf (and even email to my wife), and...importantly...has some check sums to verify that all the data is accurate. It all takes only about 2 minutes or less to run.

This automated system was found desirable when I ended up one year with I think it was 19 different transactions (or maybe it was 19 different accounts, I think the former). The large number occurs regularly, when there are LT cap gains, ST cap gains, and then dividends. And includes account registrations as one inherited IRA, two Roth IRA accounts (two different funds in one), rollover IRA, regular IRA, and then taxable joint accounts. Even though the same "fund" may be present in a number of account types/registration they are all a different entity and are kept track of that way. So a simple, interesting chore became over time a very tedious frustrating exercise. Now resolved with my VBA utilities.

I suppose at this point in time (the age of checking accounts on-line) there is really little-to no need for me to maintain my own books on a number of these accounts. But I am old school...

User avatar
dratkinson
Posts: 3985
Joined: Thu Jul 26, 2007 6:23 pm
Location: Centennial CO

Re: Excel Macro using Yahoo Finance- not working

Post by dratkinson » Tue May 16, 2017 9:58 pm

+1. M* portfolio manager doesn't do all I want.

I've built in the ability to display: when any IPS management action is required/cleared, AA is out of balance, TLH opportunity available, test the effect of selling by share lots to include producing a trial Sch D, time-based reminders (set/check withholding taxes, first of month required actions, end of quarter required actions,...), and other stuff I've forgotten.

Basically, if there is something I need to do to manage my investments/IPS, I've found a way to build it (and associated conditional formatting to highlight results of tests or reminders) into Excel.

So after close of business and gummy's macro runs, a year's worth of checking is done, and any required action is highlighted. It lowers my stress level knowing Excel locally (not dependent upon online services* that could be changed/withdrawn) is doing automatically everything I must remember to do.

* If the Yahoo price service goes away, I can manually update the closing prices of the few funds I use. So the Yahoo price macro is a very nice feature, but it's not a necessity.
d.r.a, not dr.a. | I'm a novice investor, you are forewarned.

User avatar
ThereAreNoGurus
Posts: 88
Joined: Fri Jan 24, 2014 11:41 pm

Re: Excel Macro using Yahoo Finance- not working

Post by ThereAreNoGurus » Tue May 16, 2017 10:38 pm

Thanks for the responses!

Post Reply